Working Notes

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:...

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 ...

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

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...

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

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...

[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:...

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]