Graham’s presentation on the “Hidden” Features of the Oracle Database:
Since, these presentations aren’t generally published, I took notes, not guaranteed to be correct.
Graham Wood Hidden Features
Free Features
dbms_monitor.session_trace_enable 10g
plan_stat 11g argument added to dbms_monitor.session_trace_enable
dump plan when cursor is closed
now, after the first execution, never, all_executions, they dump row source statistics
cursor mapping broken 11gr1, statement text printed on every execute
long cursor numbers 11gR2, not so human readable
TKPROF enhancements
Prints sql ID and plan hash for each statement
Reports First, Avg, Max from multiple row source dunmps
But not CLOSE … DOH!
Enable trace foe individual statements
Alter session set events ‘sql_trace [sql:…]
Getting sql info V$SQLstat (10g), doesn’t use any latches
Using V$SQL can make latch problems worse
DBMS_XPLAN.DISPLAY_CURSOR,
EXPLAIN_PLAN doesn’t work correctly, so use DBMS_XPLAN
Select * from table (dbms_xplan.display_cursor (“atnoianf82398nhfa’, NULL, ‘ADVANCED’));
Select * from table (dbms_xplan.display_cursor (format=>’ALLSTATS LAST’))
Use the CARDINALITY HINT to check the actual plan
Select /*+ gather_plan_statistics +/
Set a parameter for running an individual statement
SELECT /* + OPT_PARAM(‘star_transformation’….
_optimizer_ignore_hints=TRUE, useful for testing
Optimizer Statistics
Pending Statistics
Separates stats collection from stats publishing
Capture stats as pending
Test using pending stats
Publish once verified
Controlled by parameter – optimizer_use_pending_statistics
Determines if the optimizer will use pending stats
Set to FALSE by default in 11g
Use dbms_stats.set_*_stats
Very useful for min max values
Setting Statistics
Dbms_stats.datearray(to_date(‘01/01/1999’, ‘MM/DD/YYYY)’, sysdate)
Dbms_stats.set_column_stats(‘scott’,’emp’,’hiredate’,srec=>sr);
Gather incremental stats in 11g uses “synopsis”. Make sure to clear all the stats and use one pass to create the “SYNOPSIS”.
LISTAGG – Analytic Query
First_value, nth value, last value
External Tables
External Table Preprocessor
Stop use SQL Loader – user External tables - parallel loads with a degree of parallelism
The use of a “preprocessor” allows for uncompressing zip files
External tables allow us to filter the data before loading the data.
Sqlplus –prelim / as sysdba gives access to in memory structures with no SQL access, no latches or SQL allowed.
Aren’t Free
AWR captures top 5
dbms_workload_repository.modify_snapshot_settings (topsql=>200)
dbms_workload_repository.add_colored_sql(‘sql_id’) to capture marked SQL, this marks the SQL
Reporting
AWR SQL report
AWR Diff Report
AWR RAC report
ASH report any time period, instance or filtered, filtering at session level is like SQL trace
Use ADDM report!
Check Cardinality using SQL Monitor, key to work out why the execution plan may not be optimal.
Loading ...