Hi. I'm Scott

There are many variables which need to be considered while trying to optimize a query or performance tune a database. There is also a constant, which is often over looked by DBAs. I'm talking about system stats and fixed object stats.  What are System Statistics? These are collected database points which inform the database of the hardware available. Specifically, system stats store information regarding the speed and number of CPU cores as well as information regarding the performance of the underling storage. These data points are used to calculate the cost for the (Cost-based) Optimizer when choosing an execution plan.  If you want to see what your database thinks about your hardware, look in the SYS.AUX_STATS$  SQL> select * from SYS.AUX_STATS$; If the results of this query are mostly empty, it's highly likely that Oracle is using the default assumptions. You should collect some new stats.  Collecting System Statistics is pretty simple:...

by Jeff Scheidel, Oracle NOTE: hackers hangout in Support forums/blogs for employee information for insider information for software with known bugs. (Google Alerts) Advanced Persistent Threat Smart People Dedicated People People With A Purpose Patient, have a purpose, will find a way in, and then Sell info to someone else.  Monitor service accounts! they contain more privs control applications Do you appreciate the threat? Some people get it Understand the risks how do you assess the risk how do you midigate the risk Some don't concerned about security concerned about compliance (not the same as security!) Can't protect what you don't know "I don't think we ever tried to be hacked" Compliance standards NERCIP  is identify focused PCI, HIPPA, FERPA is data centric Principles of Access Least Privilege Do you have the privs you need?  Do you have MORE privs than you need?  Do you use the priv you...

Alex Gorbachev @alexgorbachev Pythian, CTO What is Machine Learning? study of computer algorithms that improve over time Applications of Machine Learning Spam filters Amazon interest maps facial recognition (facebook, etc) self driving cars Classes of Machine Learning Supervised Learning input: data + known facts; output: predictions Example: Linear Regression with given dataset, find point  Example: Classification given  a dataset, identify where point falls into which category auto code review 'when others null' in PLSQL code ratio of pl/sql code over comments Unsupervised Learning input: data; Output: hypothesis Example: Clustering Example: Anomaly Detection Why Machine Learning in Oracle Database? that's there the data is data in RDBMS is 'clean' transformation with SQL is easy Powerful algorithms implemented (data mining, analytic sql) Machine Learning by Example Goal: Automated PL/SQL Grading cl...

Mark Bobak ProQuest Company http://markjbobak.wordpress.com Options for Managing Large Numbers of Net Service Names TNSNames.ora OIM/OID Free, if only used for Net Service Name Resolution Can be difficult/complex to install and use Alternate LDAP Server ActiveDirectory Apache Directory OpenDJ OpenDS OpenLDAP (presenter preferred) most modern linux systems support tnsManager no longer supported Install OpenLDAP Prerequistes OpenLdap phpLDAPAdmin web gui for making single changes not really a requirement, unless you want to avoid cli ??? Define  default searchbase suffix  root dn import some OID schema files srv record in DNS server Root dn=proquest;dn=com cn=ContextOracle Secret to making it work:  add NULL Tree called ContextOracle under Root domain add ContextOracle under Configure OpenLDAP [Author Note: at this point, I lost track of the presentation, as it shifted in and out of a live demo. Lots ...

Day 4 was easily my best day at a conference...ever! Day 5, being the last day, I don't have much hope for GREAT sessions today, but I think I'll have some good ones. Implementing OpenLDAP for TNS resolution Practical Machine Learning for DBAs Using Enterprise Manager for Enterprise Monitoring Exceptional PL/SQL Exceptions And DONE, well not really While I'll be finished with my conference sessions, I can start outlining and testing some of the things I've learned. 

Wayne Sharp Union Gas REQUIREMENT: Diagnostics pack Problem description Three high-profile production applications were experiencing severe performance degradation Response time for other 49 applications was either  normal degraded, but not reported I/O Latency appeared to be the culprit SAN Admin blamed I/O on DB Looked at AWR for information regarding issue for all 54 databases to collect information about problem THERE HAS TO BE A BETTER WAY.  DBA_HIST_SYSSTAT dbms_workload_repository(to generate AWR report) Walkthrough of scripts(!!!!!) Dropbox link to scripts and presentation Solution: OEM query taking forever on a quiet system (3m reads) on DBMS_SCHEDULER_TABLE

Patrick Cimolini SkillBuilders, Inc SWOT -- Strength, Weakness, Opportunities, Threats Inside the box development declarative sql or pl/sql Fast delivers more than CRUD operations relies on Oracle testing Outside the box development Programatic results that can't be achieved declaratively Advanced: PL/SQL, html, CSS and javascript increased time,cost,risk Apex Strengths Rapid Application Development  Pages data loading charts trees Components web services security Declarative environment wizards do 80% of work 20% customizable no compilations necessary Few Moving Parts easy to install Stable and reliable pl/sql based as stable as your database Supports Desktop, Responsive and Mobile Interfaces Community Support http://www.odtug.com/apexblogroll APEX Weakness Declarative Environment Property inter-relationships can be confusing refactoring can be hard/painful One datasource per declarative tabular form One Intera...

Tim Herring Packaging Corp of America How to setup and use Logminer select * from users where clue > 0; users may not know what they did logminer can help by reading from archive redo logs Undo also available in redo log Source Database -- where you got the logs Mining Database -- target database (can be same as source) Logminer dictionary can be flatfile dictionary can be extracted to redo requirements utl_file_dir archive log mode supplimental logging enabled same platofrm, blocksize,character set Build log dictionary dbms_logmnr_d.build refresh dicionary when necessary specify redologs dbms_logmnr.add_logfile() select * from v@logmnr_logs start analysis dbms_start_logmnr(dictionary,) select * from v$logmnr_contents Searching Strategies select * from v$loghist on source gather information on logs for estimated time where sql_redo -- shows sql executed where sql_undo -- shows sql to rollback transaction Appropriate use of LogMi...

Arup Nanda http://arup.blogspot.com @ArupNanda What is tracing? Debugging information by oracle Execution plan tracing 10053 - Cost Based Optimizer Trace alter session set sql_trace =  TRUE; alter session set tracefile_idenitfier = something something will be appended to any tracefile Analyze tracefile with TKPROF tkprof tracefilename output_file tkprof tracefile output_file explain=sh/sh  generates explain plans for sql  NOT execution plan used tkprof  tracefile output_file  sys = YES all recurse SQL used in session tkprof  tracefile output_file  insert = file.sql output all insert statements tkprof  tracefile output_file  record = file.sql output ALL sql statements in session trace also various SORT options Types of Tracing? Extended Tracing actiivty logging 10046 trace alter session set events '10046 trace name context forever, level 8'; Levels 2 = regular trace 4 = put in bind vars 8 = puts...

Alfredo Krieg, DBA from Sherwin Williams http://bitkode.blogspot.com Performance Challenges difficult to tune for performance without a performance methodology what metrics do you use? is user report enough? no! ask questions, get more information THe Method (or A method) set Goals establish tuning criteria define scope of tuning Measure Oracles Kernal instrumentation wait interface system views V$sysstat v$system_event v$sys_time_model Calculate DB response time service time  queue time user cals snapshot's time frame ResponseTime = (service time + queue time) / user calls formula from queue theory Choose a Unit of Work User calls (overall activity) number of logins, parses, execute calls during sampel period overall metric for activity Physical reads/write (i/o bound) logical read/writes (CPU bound) Graph this information make it easily parsable by humans! DBA != Default Blame Acceptor Anticipate AWR is n...

Janis Griffin Confio/SolarWinds @ D o B out A nything What are Execution Plans? sequence of operations  How to View Them? Explain Plan (realtime guess ) explain plan for sql statement set autotrace (on | trace | exp| stat|off) V$SQL_PLAN -- best way actual execution plan use DBMS_XPLAN for display DISPLAY DISPLAY_AWR DISPLAY_PLAN DISPLAY_SQLSET DISPLAY_SQL_PLANBASELINE Tracing  & TKPROF Historical Plans -- AWR shows how plans change overtime Table info script http://support.confio.com/kb/1534 Interpret Plan Details Understand your table (script above will help) *_TAB_COLUMNs.NUM_BUCKETS will indicate histogram usage lots of good information here regarding column status DBMS_STATS.gather_table_stats to collect this information V$SQL_BIND_CAPTURE WHERE SQL_ID is sql_id shows what bind variables are passed collected at ~15 minute intervals Adaptive Cursor Sharing DB will create new alternative excution plan due to ...

Janis Griffin (SolarWinds/CONFIO) @DoBoutAnything Cost Based Optimizer first released in Oracle 7.3 (allowed for things like HASH Joins, partition, etc 12c -- Adaptive Plans, Stats and Auto re-optimized sql plan directives 12c DBMS_STATS package DBMS_STATS rewritten in 11g release use only these parameters schema_name table_name Partition_name Degree (of parallelism) DON'T USE extimate_percent (default is auto_samples_size)  DON'T USE analyze <- for the love of Jebus! analyze will prevent histograms from being calculated...at all REPORT_GATHER_*_STATS to show what will happen won't change stats report on status before they are enabled Global Temp tables can have Stats on a per/session Adaptive Stats Complex queries require more info than base table stats Dynamic Stats (dynamic sampling) enabled by default in 12c happens automatically for tables with no stats (ie after CTAS) gathered during parsing of SQL ALTER SESSION SET o...

I'll try not to screw up my schedule today! Statistics, Histograms, Baselines, OH MY! Fine Tune Oracle Execution Plans for Performance Gains Oracle Database Performance: Are Database Users Telling Me The Truth? LUNCH The Art and Craft of Tracing Logminer Basics -- Or How To Pull Your Bacon Out of the Fire Oracle Application Express -- A SWOT Analysis Mining the AWR Repository to Find Unreported Performance Problems EM SIG Meeting [late addition] Yep, that's everyting, no conflicts In other news, I tried to install Oracle12c database on my MacBook Pro, and I don't have enough memory. :(

Why Database as a Service standardize and optimze environment  self-service portal for database creation, etc Options for database as a service for creation DBCA Template SnapClone Schema Refresh How much can you do with DBaaS? Schema as a service new optimization with RAT ability to add data-masking ability to automate preliminary and post-scripting full-featured chargeback Making a SnapClone Image from production masking/sub-setting moves it into Test-Master database All other databases are created using Test-Master Rapid Start Kit simplifies the creation of creation steps for the database pools, zones, etc python scripts which call EMCLI commands and a setup or XML config files deploy changes on a global level (many targets) So, Kellyn Pot'Vin is an awesome speaker, and knows a LOT about OEM. She knows so much that I'm going to stop taking notes and just try to listen. Fortunatly, she has uploaded her slides AND a whitepaper on ...

Oracle 12c is the 'center of the oracle universe'. OMS Oracle Database, repository emcli console agents Autodiscoverty a way for EM to automatically search for targets can be configured to pool for targets regularly can pool on a range of IP address ranges Inspecting Targets managed targets  Discovered targets Promote discovered target to managed Monitoring Templates ensures consistant monitors for groups of targets can modify at any time but have to re-apply to all deployed targets Insident Rules sets rules for which you like to be notified for can be deployed to targets, groups, or all Kellyn's rule: Don't use create white noise Warnings? set critcal setting thresholds set warning thresholds for metric alerts Professional preference on this... when to change a rule: notification does not provide value always retain the email notificiation to ensure you are editing the correct notification Edit Actions cho...

All Day workshop takes about 6 hrs. http://www.racattack.org/12c Oracle Community Project, From the community, for the community How do you learn new features? Learn theory courses Conferences books/blogs/references Learn on a project hands on limited time May not be the best solution at all not the best way to learn.. Check the theory implement in sandbox play with features check edge cases Started in 2006, with VMware and 10g database what do you need to install?  Oracle Virtual Box 4.x Oracle LInux 6 Oracle database 12c What will RAC Attack Teach you?  Create VMs Create multiple Virtual networks? Create different storage types DNS/DHCP services BIND/ALL VMs/SCAN Clone VMs Working on Advanced Labs, including 3 nodes RAC to play with FLEX SCAN

Ray Smith @RaySmithACE http://oramanageability.wordpress.com How do you provide more value to other parts of the organization using OEM reports? Not really a question, but you can do that OEM. Custom Reports: You define the content You determine the refresh frequency you share the reports for answers! Information Publisher Workscreen NOTE: Information Publisher will be replaced with BI Publisher at some point, Info Pub is still supported in 12c Create copy of Oracle Delivered Report General tab add Title, category, etc Target type is not necessary, default should be available to ALL targets Element tab add Element, usually info from table Set schedule for refresh And you can email reports to others Access Tab will allow reports to be published w/o logging into OEM http://yourconsole_url/public/reports/ Where do I get my data?  sysman.MGMT$ views when in doubt, try the obvious name to find the data create SCOOP Schema (or other report sc...

Use case for PeopleSoft : Oracle Patches Create a production like environment (web tier, app tier, db tier) install the patch test the patch once certified, throw away environment (no capital invested in hardware) Cost Savings: $0.03/GB in S3 Storage $0.01/GB in Glacier Storage for backup Amazon itself saved 1.2M by migrating from tape backup to S3 Storage. Storage Options:  Elastic Block Store (EBS) high  performance block storage device 1GB - 1TB Mount as Drives to instances with snapshot/cloning  charged based on provisioned IOPS (manage costs) Simple Storage Service (S3) Highly scalable object storage 1B to 5TB Glacier Long term object archive extremely low cost / GB slow performance highly durable Relational Database as a Service Oracle SQL Server MySQL PostgreSQL Project Overview:  Setup a VPC Create Foundational Infrastructure Copy Database into AWS Complete configuration Test Environment & Appl...

Here's my agenda for Day 3 of Collaborate 14: Screwed up my schedule, by putting two sessions in the 8:30am timeslot (why does a schedule builder allow this?!). Coffee Deploying PeopleSoft to Amazon Cloud -- dud  Lies, Damn Lies and I/O Statistics   Oracle Database 12c: Pluggable Database - Why Do I need them?  You've got it, Flaunt It (OEM Reports) Lunch (almost as important as coffee) RAC Attack 101 - How to Install 12c RAC on Your Laptop Mastering Enterprise Manager 12c DBaaS - Database as a Service in DBAs World It's also supposed to be 91 degrees today in Las Vegas, so I'm going to try to get outside at least once. Also, I got outside, and it was glorious!

Kellyn Pot'vin Oracle [Author note: showed up on-time, and it was standing room only!] Blocking sessions are SO slow in nearly every Enterprise Manager instance. Use the command-line. AWR will ASH will tell you activity over time ADDM Compare will run two ASH reports and the difference between the two. Clear description of impact of change what you need to do address the issue identifies causes behind the change list regressed SQL NOTE: EM12c should use preferred credentials SQL Monitor Developers should have access to SQL Monitor See differences in duration easily within the tool You can see FULL detail for SQL Execution plan stats other goodies and view a Report of SQL Monitor for stuff CLI available DMBS_SQLTUNE.REPORT_SQL_MONITOR One of the Best & Least Used Features in EM Search SQL! Choose AWR snapshots, AWR Baselines and SQL_ID or where SQL 'like' something AWR and ASH from the CLI -- ALL DBAs should know how to do this...

[Also known ask the longest title in Collaborate14] by Dan & Tim Vlamis Good news! Analytic tools are readily available & very powerful significant performance gains are being achieved through analytics Everyone has a basic understanding sports teams casinos and now retail Companies spend lots of time doing 'standard reporting' and don't take the extra step and modeling the results for predictive. Standard reports are low value, compared to analytics, by order of magnitude.  So, when and where do the decisions to USE some of these analytics as part of their business? Are these discussions happening? Are they conscious decisions? How do we know? Who is entitled to information? are employees able to see their own employee score? are customers?  How do you balance the interests of the organizations and individuals: customers, employees, etc What frameworks and philosophies are available for guiding these decisions? Stakeholder ana...

[Author Note: Blogger app for IOS doesn't have all the nifty formating features of the web. I'll try to clean up later.] Tim Gorman, Evergreen Database Technologies (EvDBT.com) Three Case studies - WHen someone complains of a performance problem or an error (but not happening right now) Active Session History is a sampling mechanism MMNL process scraps data about active sessions from session state in SGA to the ASH buffers in SGA (MMON light process) parameter _ASH_SAMPLING_INTERVAL defaults to 1000 (usecs) At hourly intervals, or as ASH buffers get more than 66% full, one of the 10 samples of ASH are writtent to partitioned tables in AWR in SYSAUX. - configure AWR retension to mirror business cycles (quarterly, monthly, etc) ASH is not a trace or audit trail. But it's not perfect. Only queries that are run in more than 1 second are captured in buffer.  DBA_HIST_ACTIVE_SESS_HISTORY is samples of ASH taken every 10 seconds.  [Updated: 2:...

Using DDL trigger to call the DBMS_METADATA package to get source code of object, or use Oracle Dictionary Versions Compare between versions Use DIFF java class, deployed as Stored Procedure Developed by Stuart D Garthman  API to query the objects versions are just SQL queries, pipeline function, etc to return the differences - could use APEX front end DDL triggers are a bit risky, use appropriate error handling [Author note: formating is crappy here because my laptop ran out of battery. Need to charge. Also, not the best presntation]

This is a series of talk from IOUG Leaders. Improving Your Work PerMaria Anderson, from Encana. We call some to discussions with some assumptions.  You are not going to think clearly if you are triggered emotionally proven to last for 20 minutes approach situations in a curious way, ask 'why'  establishes common ground foundation for discussion ask 'what else' to further conversation Humans usually working on next response, or listening to what someone is saying? Conversations are culture! (Adam Savage said that) Open Discussion What two things about talk have struck a chord? be curious ask why restate what you hearing What action items?  same as above and recognize when OTHERs are emotionally triggered Rising Through The Ranks: Leadership is not about a Title  by John Matelski (DeKalb Co, GA) Leadership begins with self-knowledge it's the basis for character, character is the root of integrity, integrity provides the foun...

Check out @IOUG's twitter feed for some of the highlights from Adam Savage's keynote.  I hope I can find someone's video posted on YouTube, because it was particularly great, and far too much stuff to post as notes. If I find a link, I'll update this post. 

Yesterday was awesome, great sessions. Day two is set to be even more awesome, and with more variety. [8:00am PST] Coffee [9:00am PST] Keynote: Curiosity: Asking the Right Questions with Adam Savage (the Mythbuster) [10:15am PST]  IOUG Strategic Leadership Program: Tech Talks: Finding Your Inner Leader -- Developing Leadership in IT [12:30pm PST] LUNCH!!! [12:45pm PST]  The Database Objects Version Control in Oracle [1:45pm PST] RDBMS Forensics: Troubleshooting Using ASH [4:15pm PST]  IOUG Strategic Leadership Program: Managing the Social Responsibility Challenges of Analytics in the 21st Century Organizations [5:30pm PST]  Deep Dive into ASH and AWR [6:30pm PST] IOUG RAC SIG: Open Discussion: Best ways to Learn Oracle RAC 12c [6:30pm PST] Welcome Reception for Exhibitor Showcase [8:30pm PST] IOUG Hackfest (maybe) Yep, going to be a busy one!

Use the Right Tools: Don't use AUTOTRACE with bind vars Function abuse may cause cardinality issues may reduce access paths (ie not use indexes) in WHERE clause, compare like datatypes  can increase CPU Could ignore partitions on table (ie partition elimination) use AUTOTRACE to identify implicit conversions In PL/SQL, use of WHEN OTHERS without a RAISE some sort of error  IS A BUG. You've just coded a bug in your code, because errors will never be seen.  [Updated 1:51pm PST] Optimizer Hints Adding hints won't magically improve every query you encounter Optimizer hints should be used only with great care hints allow you influence the Optimizer Hints are directives   Two Types of Hints Non-Optimizer (aka 'good hints') Parallel APPEND MONITOR Dynamic_sampling -- provide more information to optimizer cardinality -- provide more information to optimizer Optimizer (aka 'bad hints') Bad, because hints are applied...

Presentation Slides can be downloaded from the AskTom website. [Updated : 8:58am PDT] What happens when a SQL Statement is issued? Syntax Check -- is it SQL? Semantic Check -- What is being queried? What Objects? Dictionary check.  Shared Pool Check (steps 1-3 == Soft parse) ways to reduce soft parse: Enable JDBC Statement cacheing Bind vars PL/SQL functions [Updated : 9:13am PDT]

Day One schedule for Collaborate 14 looks like so:  Optimizer One-Day Master Class w/ Tom Kyte PeopleSoft Technology SIG, if I have time Collaborate 14 App Training (because why not) IOUG Welcome Reception I'll be trying to post interesting and relevant information here as necessary.