Sage Computing Services, profile picture
Uploaded bySage Computing Services
PDF, PPTX536 views

New Tuning Features in Oracle 11g - How to make your database as boring as possible

The document discusses advanced Oracle database tuning features in version 11g, emphasizing the use of the Automatic Diagnostic Repository (ADR) for diagnostic information management. It covers techniques such as bind peeking and adaptive cursors, detailing how they influence SQL execution plans and performance. The document also explores the importance of multi-column statistics in optimizing query performance across complex database joins.

Download as PDF, PPTX
1 / 118
2 / 118
3 / 118
4 / 118
5 / 118
6 / 118
7 / 118
8 / 118
9 / 118
10 / 118
11 / 118
12 / 118
13 / 118
14 / 118
15 / 118
16 / 118
17 / 118
18 / 118
19 / 118
20 / 118
21 / 118
22 / 118
23 / 118
24 / 118
25 / 118
26 / 118
27 / 118
28 / 118
29 / 118
30 / 118
31 / 118
32 / 118
33 / 118
34 / 118
35 / 118
36 / 118
37 / 118
38 / 118
39 / 118
40 / 118
41 / 118
42 / 118
43 / 118
44 / 118
45 / 118
46 / 118
47 / 118
48 / 118
49 / 118
50 / 118
51 / 118
52 / 118
53 / 118
54 / 118
55 / 118
56 / 118
57 / 118
58 / 118
59 / 118
60 / 118
61 / 118
62 / 118
63 / 118
64 / 118
65 / 118
66 / 118
67 / 118
68 / 118
69 / 118
70 / 118
71 / 118
72 / 118
73 / 118
74 / 118
75 / 118
76 / 118
77 / 118
78 / 118
79 / 118
80 / 118
81 / 118
82 / 118
83 / 118
84 / 118
85 / 118
86 / 118
87 / 118
88 / 118
89 / 118
90 / 118
91 / 118
92 / 118
93 / 118
94 / 118
95 / 118
96 / 118
97 / 118
98 / 118
99 / 118
100 / 118
101 / 118
102 / 118
103 / 118
104 / 118
105 / 118
106 / 118
107 / 118
108 / 118
109 / 118
110 / 118
111 / 118
112 / 118
113 / 118
114 / 118
115 / 118
116 / 118
117 / 118
118 / 118
SAGE Computing Services Customised Oracle Training Workshops and Consulting New Tuning Features in Oracle 11g - How to make your database as boring as possible Penny Cookson Managing Director
Automatic Diagnostic Repository (ADR) File based repository of diagnostic information including:- trace files alert log dump files health monitoring reports incidents and problem descriptions Command line interface ADRCI Enterprise Manager Support Workbench
Finding Trace Files diagnostic directory directory for each product default trace directory ADR base
Finding Trace Files SELECT name, description, value , isdeprecated FROM v$parameter WHERE name = 'diagnostic_dest' OR name like '%dump%'
Finding Trace Files SELECT * FROM v$diag_info
Finding Trace Files SELECT pid, program, tracefile FROM v$process
Bind Peeking and Adaptive Cursors
Bind Peeking + Adaptive Cursors SELECT resource_code, count(*) FROM bookings_large GROUP BY resource_code; Majority value Minority value
Bind Peeking Look at the value of the bind variable when the statement is parsed Plan is based on that value BEGIN :v3 := ‘PC1'; END; SELECT COUNT(quantity) FROM bookings_large WHERE resource_code = :v3; BEGIN :v3 := ‘BRLG'; END; SELECT COUNT(quantity) FROM bookings_large WHERE resource_code = :v3; SAME PLAN STILL INDEX for 34% of rows INDEX for <1% of rows
Bind Peeking + Adaptive Cursors SELECT COUNT(l.quantity) FROM train.bookings_large l WHERE resource_code = :v1 SELECT sql_id FROM v$sqlarea WHERE sql_text = 'SELECT COUNT(l.quantity) FROM train.bookings_large l WHERE resource_code = :v1'
Bind Peeking + Adaptive Cursors BEGIN :v1 := 'PC1'; END; SELECT COUNT(l.quantity) FROM train.bookings_large l WHERE resource_code = :v1 SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware FROM v$sql WHERE sql_id ='95jktg3mza0qm'; IS_BIND_SENSITIVE - may need to change the plan - set on first execution of the statement IS_BIND_AWARE - do need to change the plan
Bind Peeking + Adaptive Cursors SELECT child_number, bind_set_hash_value, peeked, executions, rows_processed, buffer_gets FROM v$sql_cs_statistics WHERE sql_id ='95jktg3mza0qm'
Bind Peeking + Adaptive Cursors call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.01 0 0 0 0 Fetch 2 0.12 0.43 119 6635 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.14 0.44 119 6635 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 82 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=6635 pr=119 pw=119 time=0 us) 48510 TABLE ACCESS BY INDEX ROWID BOOKINGS_LARGE (cr=6635 pr=119 pw=119 time=2870 us cost=1977 size=316008 card=39501) 48510 INDEX RANGE SCAN BK_RES2 (cr=104 pr=0 pw=0 time=527 us cost=90 size=0 card=39501)(object id 69870) ******************************************************************************** Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 0.00 0.00 ********************************************************************************
Bind Peeking + Adaptive Cursors BEGIN :v1 := ‘BRLG'; END; SELECT COUNT(l.quantity) FROM train.bookings_large l WHERE resource_code = :v1 SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware FROM v$sql WHERE sql_id ='95jktg3mza0qm';
Bind Peeking + Adaptive Cursors SELECT child_number, bind_set_hash_value, peeked, executions, rows_processed, buffer_gets FROM v$sql_cs_statistics WHERE sql_id ='95jktg3mza0qm'
Bind Peeking + Adaptive Cursors call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 6.29 78.82 34335 39594 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 6.29 78.82 34335 39594 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 82 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=39594 pr=34335 pw=34335 time=0 us) 1991051 TABLE ACCESS BY INDEX ROWID BOOKINGS_LARGE (cr=39594 pr=34335 pw=34335 time=626800 us cost=1977 size=316008 card=39501) 1991051 INDEX RANGE SCAN BK_RES2 (cr=4437 pr=4436 pw=4436 time=61616 us cost=90 size=0 card=39501)(object id 69870) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 db file sequential read 34335 0.29 72.08 resmgr:cpu quantum 30 0.10 1.29 SQL*Net message from client 2 0.00 0.00 ********************************************************************************
Bind Peeking + Adaptive Cursors BEGIN :v1 := ‘BRLG'; END; SELECT COUNT(l.quantity) FROM train.bookings_large l WHERE resource_code = :v1 SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware FROM v$sql WHERE sql_id ='95jktg3mza0qm';
Bind Peeking + Adaptive Cursors SELECT child_number, bind_set_hash_value, peeked, executions, rows_processed, buffer_gets FROM v$sql_cs_statistics WHERE sql_id ='95jktg3mza0qm'
Bind Peeking + Adaptive Cursors call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 1.23 21.71 35341 35346 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 1.23 21.72 35341 35346 0 1 Misses in library cache during parse: 0 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 82 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=35346 pr=35341 pw=35341 time=0 us) 1991051 TABLE ACCESS FULL BOOKINGS_LARGE (cr=35346 pr=35341 pw=35341 time=414975 us cost=9652 size=15700664 card=1962583) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 direct path read 314 0.42 19.45 SQL*Net message from client 2 0.10 0.10 ********************************************************************************
Bind Peeking + Adaptive Cursors - Executions SELECT s.executions v$sqlex, c.sql_id, c.executions cs_stat_exec , c.peeked FROM v$sql_cs_statistics c, v$sql s WHERE s.sql_id = c.sql_id AND s.child_number = c.child_number AND s.sql_id ='95jktg3mza0qm'
Bind Peeking + Adaptive Cursors - Summary Statements with histograms and bind variables are bind sensitive The first time you execute a statement with different selectivity it uses the original plan The second time it changes the plan and become bind aware Be careful when statements become invalidated by:- Gathering statistics Flushing the shared pool Restarting the database Or when they are aged out Otherwise you don’t need to worry any more
Gathering Statistics
Multi Column Statistics Gather statistics on combinations of columns Improves plans for complex dependency patterns SELECT count(b.comments) FROM train.events_large e, train.bookings_large b WHERE e.org_id = 2264 AND e.event_no = b.event_no AND e.comments = 'TEST'
Multi Column Data Patterns SELECT count(*) FROM train.events_large e SELECT count(*) FROM train.events_large e WHERE e.org_id = 2264 SELECT count(e.comments) FROM train.events_large e WHERE e.comments = 'TEST' SELECT count(e.comments) FROM train.events_large e WHERE e.comments = 'TEST‘ AND e.org_id = 2264
With Individual Statistics call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 5.00 22.52 35341 36337 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 5.00 22.52 35341 36337 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 82 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=36337 pr=35341 pw=35341 time=0 us) 175 HASH JOIN (cr=36337 pr=35341 pw=35341 time=13 us cost=9966 size=33365083 card=1076293) 4 TABLE ACCESS FULL EVENTS_LARGE (cr=991 pr=0 pw=0 time=3 us cost=274 size=319770 card=18810) 5767168 TABLE ACCESS FULL BOOKINGS_LARGE (cr=35346 pr=35341 pw=35341 time=225583 us cost=9665 size=80740352 card=5767168) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 direct path read 314 0.38 15.88 resmgr:cpu quantum 11 0.14 0.27 SQL*Net message from client 2 0.04 0.04 ********************************************************************************
Auto Created Column Statistics BEGIN dbms_stats.gather_table_stats(ownname=>'TRAIN', tabname=>'EVENTS_LARGE', cascade=>TRUE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', no_invalidate=>FALSE); END; SELECT column_name, histogram, num_buckets FROM user_tab_col_statistics WHERE table_name = 'EVENTS_LARGE'
Auto Created Column Statistics SELECT extension_name, extension FROM user_stat_extensions WHERE table_name='EVENTS_LARGE‘ Same access path used ******************************************************************************** Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=36337 pr=35341 pw=35341 time=0 us) 175 HASH JOIN (cr=36337 pr=35341 pw=35341 time=13 us cost=9966 size=33365083 card=1076293) 4 TABLE ACCESS FULL EVENTS_LARGE (cr=991 pr=0 pw=0 time=3 us cost=274 size=319770 card=18810) 5767168 TABLE ACCESS FULL BOOKINGS_LARGE (cr=35346 pr=35341 pw=35341 time=225583 us cost=9665 size=80740352 card=5767168)
Manually Create Multi Column Statistics BEGIN dbms_stats.gather_table_stats(ownname=>'TRAIN', tabname=>'EVENTS_LARGE', cascade=>TRUE, method_opt => 'FOR COLUMNS (org_id, comments) SIZE SKEWONLY', no_invalidate=>FALSE); END; SELECT extension_name, extension FROM user_stat_extensions WHERE table_name='EVENTS_LARGE'
Manually Create Multi Column Statistics SELECT column_name, histogram, num_buckets FROM user_tab_col_statistics WHERE table_name = 'EVENTS_LARGE'
Multi Column Statistics ******************************************************************************** SELECT count(b.comments) FROM train.events_large e, train.bookings_large b WHERE e.org_id = 2264 AND e.event_no = b.event_no AND e.comments = 'TEST' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.01 0.01 0 1175 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.01 0.01 0 1175 0 1 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=1175 pr=0 pw=0 time=0 us) 175 NESTED LOOPS (cr=1175 pr=0 pw=0 time=71 us) 175 NESTED LOOPS (cr=1001 pr=0 pw=0 time=39 us cost=1129 size=26815 card=865) 4 TABLE ACCESS FULL EVENTS_LARGE (cr=991 pr=0 pw=0 time=4 us cost=274 size=255 card=15) 175 INDEX RANGE SCAN BK_EVT2 (cr=10 pr=0 pw=0 time=10 us cost=2 size=0 card=57)(object id 69868) 175 TABLE ACCESS BY INDEX ROWID BOOKINGS_LARGE (cr=174 pr=0 pw=0 time=0 us cost=57 size=798 card=57) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 0.02 0.02 ********************************************************************************
Multi Column Statistics ******************************************************************************** SELECT count(b.comments) FROM train.organisations o, train.events_large e, train.bookings_large b, train.resources r WHERE o.org_id = e.org_id AND e.event_no = b.event_no AND b.resource_code = r.code AND o.name = 'Australian Medical Systems' AND r.description = 'Buffet Lunch' AND e.comments = 'TEST' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 4.64 21.66 35341 36351 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 4.64 21.66 35341 36351 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 82
Multi Column Statistics ******************************************************************************** Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=36351 pr=35341 pw=35341 time=0 us) 25 HASH JOIN (cr=36351 pr=35341 pw=35341 time=1966789 us cost=9972 size=6555490 card=79945) 4 HASH JOIN (cr=1005 pr=0 pw=0 time=8 us cost=281 size=792225 card=12575) 1 MERGE JOIN CARTESIAN (cr=14 pr=0 pw=0 time=0 us cost=6 size=46 card=1) 1 TABLE ACCESS FULL ORGANISATIONS (cr=7 pr=0 pw=0 time=0 us cost=3 size=26 card=1) 1 BUFFER SORT (cr=7 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 1 TABLE ACCESS FULL RESOURCES (cr=7 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 75243 TABLE ACCESS FULL EVENTS_LARGE (cr=991 pr=0 pw=0 time=1020 us cost=274 size=1282616 card=75448) 5767168 TABLE ACCESS FULL BOOKINGS_LARGE (cr=35346 pr=35341 pw=35341 time=350849 us cost=9665 size=109576192 card=5767168) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 direct path read 314 0.35 15.49 resmgr:cpu quantum 2 0.12 0.12 SQL*Net message from client 2 0.04 0.05 ********************************************************************************
Multi Column Statistics - Summary Manually create column groups and statistics on them Complex joins may still need SQL Profiles Use for dependent column such as Country, State
Expression Statistics SELECT comments, count(*) FROM events_large GROUP by comments ORDER by count(*) desc
Expression Statistics BEGIN dbms_stats.gather_table_stats(ownname=>'TRAIN', tabname=>'EVENTS_LARGE', cascade=>TRUE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', no_invalidate=>FALSE); END; SELECT column_name, histogram, num_buckets FROM user_tab_col_statistics WHERE table_name = 'EVENTS_LARGE'
Expression Statistics ******************************************************************************** SELECT count(b.comments) FROM train.events_large e, train.bookings_large b WHERE e.event_no = b.event_no AND e.comments = 'Comments8' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.01 0.01 0 1052 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.01 0.01 0 1052 0 1
Expression Statistics Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=1052 pr=0 pw=0 time=0 us) 58 NESTED LOOPS (cr=1052 pr=0 pw=0 time=28 us) 58 NESTED LOOPS (cr=994 pr=0 pw=0 time=7 us cost=331 size=1178 card=38) 1 TABLE ACCESS FULL EVENTS_LARGE (cr=991 pr=0 pw=0 time=0 us cost=274 size=17 card=1) 58 INDEX RANGE SCAN BK_EVT2 (cr=3 pr=0 pw=0 time=3 us cost=2 size=0 card=57)(object id 69868) 58 TABLE ACCESS BY INDEX ROWID BOOKINGS_LARGE (cr=58 pr=0 pw=0 time=0 us cost=57 size=798 card=57) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 0.02 0.02 ********************************************************************************
Expression Statistics ******************************************************************************** SELECT count(b.comments) FROM train.events_large e, train.bookings_large b WHERE e.event_no = b.event_no AND initcap(e.comments) = 'Comments8' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 5.70 21.52 35341 36337 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 5.71 21.52 35341 36337 0 1
Expression Statistics Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=36337 pr=35341 pw=35341 time=0 us) 58 HASH JOIN (cr=36337 pr=35341 pw=35341 time=624644 us cost=9967 size=1779493 card=57403) 1 TABLE ACCESS FULL EVENTS_LARGE (cr=991 pr=0 pw=0 time=0 us cost=275 size=17051 card=1003) 5767168 TABLE ACCESS FULL BOOKINGS_LARGE (cr=35346 pr=35341 pw=35341 time=301177 us cost=9665 size=80740352 card=5767168) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 direct path read 314 0.41 15.12 resmgr:cpu quantum 2 0.10 0.10 SQL Net message from client 2 0.03 ********************************************************************************
Expression Statistics BEGIN dbms_stats.gather_table_stats(ownname=>'TRAIN', tabname=>'EVENTS_LARGE', cascade=>TRUE, method_opt => 'FOR COLUMNS (INITCAP(comments)) SIZE SKEWONLY', no_invalidate=>FALSE); END; SELECT extension_name, extension FROM user_stat_extensions WHERE table_name='EVENTS_LARGE'
Expression Statistics SELECT column_name, histogram, num_buckets FROM user_tab_col_statistics WHERE table_name = 'EVENTS_LARGE'
Expression Statistics ******************************************************************************** SELECT count(b.comments) FROM train.events_large e, train.bookings_large b WHERE e.event_no = b.event_no AND initcap(e.comments) = 'Comments8' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 2 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.03 0.04 0 1052 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.03 0.04 0 1054 0 1
Expression Statistics Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=1052 pr=0 pw=0 time=0 us) 58 NESTED LOOPS (cr=1052 pr=0 pw=0 time=27 us) 58 NESTED LOOPS (cr=994 pr=0 pw=0 time=7 us cost=332 size=2223 card=57) 1 TABLE ACCESS FULL EVENTS_LARGE (cr=991 pr=0 pw=0 time=0 us cost=275 size=25 card=1) 58 INDEX RANGE SCAN BK_EVT2 (cr=3 pr=0 pw=0 time=3 us cost=2 size=0 card=57)(object id 69868) 58 TABLE ACCESS BY INDEX ROWID BOOKINGS_LARGE (cr=58 pr=0 pw=0 time=0 us cost=57 size=798 card=57) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 0.02 0.02
Gathering Statistics - Preferences
Setting Statistics Gathering Defaults Obsolete: GET_PARAM SET_PARAM Procedure Use: GET_PREFS Function SET_TABLE_PREFS (set for individual tables) SET_DATABASE_PREFS (sets for all tables, can include/exclude SYS tables) SET_GLOBAL_PREFS – for new objects
Setting Statistics Gathering Defaults
Setting Statistics Gathering Defaults
Setting Statistics Gathering Defaults Doesn’t work
Table Prefs PROCEDURE SET_TABLE_PREFS Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- OWNNAME VARCHAR2 IN TABNAME VARCHAR2 IN PNAME VARCHAR2 IN PVALUE VARCHAR2 IN PNAME CASCADE DEGREE ESTIMATE_PERCENT METHOD_OPT NO_INVALIDATE GRANULARITY PUBLISH INCREMENTAL (only scan partitions that have been changed) STALE_PERCENT
Table Prefs - Example BEGIN dbms_stats.set_table_prefs(ownname=>'TRAIN', tabname=>'EVENTS_LARGE', pname=>'STALE_PERCENT', pvalue=>'30'); dbms_stats.set_table_prefs(ownname=>'TRAIN', tabname=>'EVENTS_LARGE', pname=>'METHOD_OPT', pvalue=>'FOR ALL COLUMNS SIZE AUTO'); END; SELECT * FROM DBA_TAB_STAT_PREFS;
Gathering and Publishing Set preferences to PUBLISH = ‘FALSE’ Gather Statistics user_tab_pending_stats user_ind_pending_stats user_col_pending_stats Run test case Alter session set optimizer_pending_statistics = TRUE Run test case Worse Better dbms_stats. delete_pending_stats dbms_stats. publish_pending_stats user_tab_statistics user_ind_statistics user_tab_col_statistics
Gathering and Publishing SELECT obj#, TO_CHAR(savtime,'dd/mm/yyyy') save_time, rowcnt, blkcnt, avgrln, samplesize, analyzetime FROM wri$_optstat_tab_history ORDER BY savtime desc
Gathering and Publishing Never publish any statistics until you are happy they work better
Automatic Tuning
Automatic Tuning Automatic SQL Tuning (maintenance window) Automatic Workload Repository (AWR) Identify high load candidates for tuning for each statement Run SQL Tuning Advisor Test with and without profile ACCEPT_SQL_PROFILES=true ACCEPT_SQL_PROFILES=false (default) SQL Profiles Report
Automatic Tuning
Automatic Tuning
Automatic Tuning
Automatic Tuning
Automatic Tuning
Automatic Tuning
Automatic Tuning SELECT count(b.comments) FROM train.organisations o, train.events_large e, train.bookings_large b, train.resources r WHERE o.org_id = e.org_id AND e.event_no = b.event_no AND b.resource_code = r.code AND o.name = 'Australian Medical Systems' AND r.description = 'Buffet Lunch' AND e.comments = 'TEST'
Automatic Tuning
Automatic Tuning
Automatic Tuning call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 5.84 19.13 35341 36351 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 5.84 19.13 35341 36351 0 1 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=36351 pr=35341 pw=35341 time=0 us) 149 HASH JOIN (cr=36351 pr=35341 pw=35341 time=66 us cost=9972 size=6504732 card=79326) 19 HASH JOIN (cr=1005 pr=0 pw=0 time=10 us cost=281 size=786051 card=12477) 1 MERGE JOIN CARTESIAN (cr=14 pr=0 pw=0 time=0 us cost=6 size=46 card=1) 1 TABLE ACCESS FULL ORGANISATIONS (cr=7 pr=0 pw=0 time=0 us cost=3 size=26 card=1) 1 BUFFER SORT (cr=7 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 1 TABLE ACCESS FULL RESOURCES (cr=7 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 74511 TABLE ACCESS FULL EVENTS_LARGE (cr=991 pr=0 pw=0 time=1410 us cost=274 size=1272688 card=74864) 5767168 TABLE ACCESS FULL BOOKINGS_LARGE (cr=35346 pr=35341 pw=35341 time=233788 us cost=9665 size=109576192 card=5767168) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 4.20 4.20 direct path read 314 0.45 11.84 ********************************************************************************
Automatic Tuning call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.05 1 3 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.17 1.61 100 2078 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.18 1.66 101 2081 0 1 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=2078 pr=100 pw=100 time=0 us) 149 NESTED LOOPS (cr=2078 pr=100 pw=100 time=137634 us) 1032 NESTED LOOPS (cr=1047 pr=14 pw=14 time=159 us cost=1649 size=12382 card=151) 19 HASH JOIN (cr=1005 pr=0 pw=0 time=19 us cost=281 size=1512 card=24) 1 MERGE JOIN CARTESIAN (cr=14 pr=0 pw=0 time=0 us cost=6 size=46 card=1) 1 TABLE ACCESS FULL ORGANISATIONS (cr=7 pr=0 pw=0 time=0 us cost=3 size=26 card=1) 1 BUFFER SORT (cr=7 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 1 TABLE ACCESS FULL RESOURCES (cr=7 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 74511 TABLE ACCESS FULL EVENTS_LARGE (cr=991 pr=0 pw=0 time=1127 us cost=274 size=1272688 card=74864) 1032 INDEX RANGE SCAN BK_EVT2 (cr=42 pr=14 pw=14 time=68 us cost=2 size=0 card=57)(object id 69868) 149 TABLE ACCESS BY INDEX ROWID BOOKINGS_LARGE (cr=1031 pr=86 pw=86 time=0 us cost=57 size=114 card=6) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 17.58 17.58 db file sequential read 79 0.07 1.44 db fil tt d d 4 0 02 0 05
Automatic Tuning SELECT signature, inuse_features, flags FROM sql$ WHERE signature = ‘1697466342721355938’ SELECT /*+ INDEX(sqlobj$ (signature category obj_type plan_id)) */ DISTINCT category FROM sqlobj$ WHERE signature = ‘1697466342721355938’ SELECT obj_type, plan_id, name, flags, last_executed FROM sqlobj$ WHERE signature = ‘1697466342721355938’ AND category = :2 SELECT obj_type, plan_id, comp_data FROM sqlobj$data WHERE signature = ‘1697466342721355938’ AND category = :2
Automatic Tuning
Automatic Tuning
Automatic Tuning
Automatic Tuning call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 17.67 227.31 9818 1177977 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 17.67 227.32 9818 1177977 0 1 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=1177977 pr=9818 pw=9818 time=0 us) 398155 NESTED LOOPS (cr=1177977 pr=9818 pw=9818 time=1802295 us) 1148142 NESTED LOOPS (cr=29836 pr=2508 pw=2508 time=209241 us cost=15 size=144 card=4) 19800 INDEX RANGE SCAN EVTLG_PK (cr=37 pr=37 pw=37 time=395 us cost=2 size=17 card=1)(object id 69847) 1148142 INDEX RANGE SCAN BK_EVT2 (cr=29799 pr=2471 pw=2471 time=545049 us cost=2 size=0 card=11)(object id 69868) 398155 TABLE ACCESS BY INDEX ROWID BOOKINGS_LARGE (cr=1148141 pr=7310 pw=7310 time=0 us cost=13 size=76 card=4) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 283.03 283.03 db file sequential read 7621 0.71 193.61 db file scattered read 496 0.60 13.09
Automatic Tuning call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.03 1 3 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 1.48 18.43 35341 35383 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 1.50 18.47 35342 35386 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 82 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=35383 pr=35341 pw=35341 time=0 us) 398155 HASH JOIN (cr=35383 pr=35341 pw=35341 time=131437 us cost=9656 size=14112720 card=392020) 19800 INDEX RANGE SCAN EVTLG_PK (cr=37 pr=0 pw=0 time=304 us cost=2 size=338470 card=19910)(object id 69847) 398155 TABLE ACCESS FULL BOOKINGS_LARGE (cr=35346 pr=35341 pw=35341 time=107228 us cost=9652 size=7321631 card=385349) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 30.55 30.55 direct path read 314 0.25 15.87
Automatic Tuning However good this looks: Let it do its automatic tuning Don’t let it automatically implement (in a transaction database) Check its recommendations and implement if you like them
SQL Plan Management
CBO - Instability CBO ?access path decision System parameters Session parameters Optimiser StatisticsIndexes SQL Profile Software version System statistics DBA playing around
CBO - Instability • Solution 1 – Leave it all alone • Solution 2 – SQL Plan Management – Store plan baseline – Plans not used till accepted – Manually accept or – Allow Oracle to evolve plans
SQL Plan Management Manual capture DBMS_SPM.LOAD_PLANS_FROM_SQLSET DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE Auto capture of repeatable statements OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE = TRUE Baseline = (Stored and Accepted plans) SQL Management Base New Plan identified during execution Stored not accepted SQL Tuning Advisor identifies new plan – SQL*Profile accepted Auto accept of new plan (if it performs better) DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE Manual load/accept of new plan DBMS_SPM.LOAD_PLANS_FROM_SQLSET DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
SQL Plan Management – Fixed Plans Fixed plan SQL Management Base New Plan identified but not added Non fixed plans SQL Tuning Advisor identifies new plan – SQL*Profile accepted Manual load/accept of new plan DBMS_SPM.LOAD_PLANS_FROM_SQLSET DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE FIXED=> Y
SQL Plan Management alter system flush shared_pool No SQL Profiles, No baselines Execute twice alter system flush shared_pool
SQL Plan Management
SQL Plan Management
SQL Plan Management
SQL Plan Management
SQL Plan Management
SQL Plan Management
SQL Plan Management – Plan Retention • Default 53 weeks • Only unused plans purged • Why would you keep a plan for a year if it has only been used once BEGIN DBMS_SPM.CONFIGURE( 'plan_retention_weeks',13); END;
SQL Plan Management – Binds • Will not automatically handle adaptive cursors • New plan identified on first execution • New plan recorded as not accepted • Plan will not evolve • All bind variable values use same baseline plan • Plans show as not bind sensitive or aware
SQL Plan Management – Binds
SQL Plan Management – Binds
SQL Plan Management – Binds Force Acceptance of the plan
SQL Plan Management – Binds Once you have forced the plan to be accepted the statement will be bind aware
Cached Query Results
Results Cache SGA SQL Query Results Cache Results Cache PL/SQL Function Results Cache populate invalidate populate
Query Results Cache RESULT_CACHE_MODE MANUAL FORCE --+ result_cache --+ no_result_cache Will not cache if:- Dictionary tables Temporary tables Sequences Dates/times Non deterministic PL/SQL
Monitoring the Results Cache • SELECT * FROM v$result_cache_memory • SELECT * FROM v$result_cache_objects • SELECT * FROM v$result_cache_statistics • SELECT * FROM v$result_cache_dependency • RESULT_CACHE_MAX_SIZE • RESULT_CACHE_MAX_RESULT • Explain Plan
Query Results Cache SELECT * FROM v$result_cache_objects
PL/SQL Function Results Cache CREATE OR REPLACE FUNCTION quantity_booked (p_resource_code in resources.code%TYPE,p_event_date in date) RETURN NUMBER RESULT_CACHE RELIES_ON (BOOKINGS,EVENTS) IS v_total_booked number := 0; BEGIN SELECT sum(b.quantity) INTO v_total_booked FROM bookings b, events e WHERE e.event_no = b.event_no AND p_event_date between e.start_date and e.end_date AND b.resource_code = p_resource_code; book_pkg.add_one(v_total_booked, v_total_booked); RETURN (v_total_booked); END;
PL/SQL Function Results Cache DECLARE v_num number; BEGIN v_num := quantitybooked('VCR2',’06-NOV-2007’); dbms_output.put_line(v_num); END; SELECT * FROM v$result_cache_objects 9
PL/SQL Function Results Cache UPDATE bookings SET quantity = quantity*2; COMMIT; DECLARE v_num number; BEGIN v_num := quantitybooked('VCR2',’06-NOV-2007’); dbms_output.put_line(v_num); END; 17
PL/SQL Function Results Cache CREATE OR REPLACE PACKAGE BODY book_pkg AS PROCEDURE add_one (p_result IN NUMBER, p_result_out OUT NUMBER) IS BEGIN p_result_out := p_result+10; END; END;
PL/SQL Function Results Cache DECLARE v_num number; BEGIN v_num := quantitybooked('VCR2',’06-NOV-2007’); dbms_output.put_line(v_num); END; 17
PL/SQL Function Results Cache Don’t do stupid stuff CREATE OR REPLACE FUNCTION non_determ RETURN VARCHAR2 RESULT_CACHE IS BEGIN RETURN TO_CHAR(sysdate,'ddmmyyyy hh:mi:ss'); END;
Designing for the PL/SQL Function Results Cache Data Store Result Cache Business Object EvtOrg = PL/SQL function result Processing
Designing for the PL/SQL Function Results Cache
Designing for the PL/SQL Function Results Cache
Designing for the PL/SQL Function Results Cache So why do we need a middle tier?
SQL Performance Analyzer
SQL Performance Analyzer
SQL Performance Analyzer
SQL Performance Analyzer
SQL Performance Analyzer
SQL Performance Analyzer
View the Comparison Report
Summary - Setup Migrate to Test instance of 11g Run application Collect SQL Tuning set Run SQL Performance Analyzer Add extra statistics Run automatic tuning – without automatic implementation Look at suggestions fix the problem or accept the profile Lock any statistics where required (e.g very dynamic tables) Run SQL Tuning set in SQL Performance Analyzer and compare For a transaction database Baseline the application SQL If you are really paranoid fix the baseline plans
Summary WORTH A TRIAL Selectively cache PL/SQL results If you have lots of spare memory turn results caching on
Summary – Maintenance (Transaction DB) Keep auto stats gathering on (at a sensible interval), or manually gather stats when you have a quiet evening Publish stats if performance not worse Keep automatic tuning scheduled– automatic implementation off Look at suggestions fix the problem or accept the profile Monitor new baseline plans manually test and accept Before you change anything (your hairstyle/shirt/partner) Run SQL Performance Analyzer and compare old and new configuration
Summary – Maintenance (Reporting DB) Keep auto stats gathering on (at a sensible interval), or manually gather stats when you have a quiet evening Publish stats if performance not worse Keep automatic tuning scheduled – automatic implementation on Before you make significant changes (version) Run SQL Performance Analyzer and compare old and new configuration
SAGE Computing Services Customised Oracle Training Workshops and Consulting Questions and Answers? Presentations are available from our website: http://www.sagecomputing.com.au enquiries@sagecomputing.com.au penny@sagecomputing.com.au

More Related Content

PPT
Informix Warehouse Accelerator (IWA) features in version 12.1
byKeshav Murthy
 
PPT
Rmoug ashmaster
byKyle Hailey
 
PDF
Advanced pg_stat_statements: Filtering, Regression Testing & more
byLukas Fittl
 
DOCX
Exadata - Smart Scan Testing
byMonowar Mukul
 
PPTX
Adaptive Query Optimization in 12c
byAnju Garg
 
ODP
Basic Query Tuning Primer - Pg West 2009
bymattsmiley
 
PDF
Histograms : Pre-12c and Now
byAnju Garg
 
PDF
Strategic autovacuum
byJim Mlodgenski
 
Informix Warehouse Accelerator (IWA) features in version 12.1
byKeshav Murthy
 
Rmoug ashmaster
byKyle Hailey
 
Advanced pg_stat_statements: Filtering, Regression Testing & more
byLukas Fittl
 
Exadata - Smart Scan Testing
byMonowar Mukul
 
Adaptive Query Optimization in 12c
byAnju Garg
 
Basic Query Tuning Primer - Pg West 2009
bymattsmiley
 
Histograms : Pre-12c and Now
byAnju Garg
 
Strategic autovacuum
byJim Mlodgenski
 

What's hot

PPT
Ash masters : advanced ash analytics on Oracle
byKyle Hailey
 
PDF
Aspects of 10 Tuning
bySage Computing Services
 
PDF
12c SQL Plan Directives
byFranck Pachot
 
PDF
[Pgday.Seoul 2019] Citus를 이용한 분산 데이터베이스
byPgDay.Seoul
 
PDF
PostgreSQL: Advanced indexing
byHans-Jürgen Schönig
 
PPT
Oracle 10g Performance: chapter 00 sampling
byKyle Hailey
 
PDF
PostgreSQL Procedural Languages: Tips, Tricks and Gotchas
byJim Mlodgenski
 
PDF
Rac 12c optimization
byRiyaj Shamsudeen
 
PDF
PostgreSQL query planner's internals
byAlexey Ermakov
 
PDF
Top 10 Mistakes When Migrating From Oracle to PostgreSQL
byJim Mlodgenski
 
PDF
Modern query optimisation features in MySQL 8.
byMydbops
 
PDF
Deep review of LMS process
byRiyaj Shamsudeen
 
DOCX
Oracle 12c Automatic Data Optimization (ADO) - ILM
byMonowar Mukul
 
PPTX
Adapting to Adaptive Plans on 12c
byMauro Pagano
 
PDF
Same plan different performance
byMauro Pagano
 
PPTX
SQL Plan Directives explained
byMauro Pagano
 
PDF
pstack, truss etc to understand deeper issues in Oracle database
byRiyaj Shamsudeen
 
PDF
collectd & PostgreSQL
byMark Wong
 
PDF
Adaptive Query Optimization
byAnju Garg
 
PDF
Deep dive into PostgreSQL statistics.
byAlexey Lesovsky
 
Ash masters : advanced ash analytics on Oracle
byKyle Hailey
 
Aspects of 10 Tuning
bySage Computing Services
 
12c SQL Plan Directives
byFranck Pachot
 
[Pgday.Seoul 2019] Citus를 이용한 분산 데이터베이스
byPgDay.Seoul
 
PostgreSQL: Advanced indexing
byHans-Jürgen Schönig
 
Oracle 10g Performance: chapter 00 sampling
byKyle Hailey
 
PostgreSQL Procedural Languages: Tips, Tricks and Gotchas
byJim Mlodgenski
 
Rac 12c optimization
byRiyaj Shamsudeen
 
PostgreSQL query planner's internals
byAlexey Ermakov
 
Top 10 Mistakes When Migrating From Oracle to PostgreSQL
byJim Mlodgenski
 
Modern query optimisation features in MySQL 8.
byMydbops
 
Deep review of LMS process
byRiyaj Shamsudeen
 
Oracle 12c Automatic Data Optimization (ADO) - ILM
byMonowar Mukul
 
Adapting to Adaptive Plans on 12c
byMauro Pagano
 
Same plan different performance
byMauro Pagano
 
SQL Plan Directives explained
byMauro Pagano
 
pstack, truss etc to understand deeper issues in Oracle database
byRiyaj Shamsudeen
 
collectd & PostgreSQL
byMark Wong
 
Adaptive Query Optimization
byAnju Garg
 
Deep dive into PostgreSQL statistics.
byAlexey Lesovsky
 

Viewers also liked

PPTX
Лепті сөйлемді қайталау
byBilim All
 
PPTX
Why to go to hawaii
bystephen fry
 
PDF
Application Express - A web development environment for the masses - and for ...
bySage Computing Services
 
PPTX
keseimbangan cairan dan elektrolit
byCahya
 
PPTX
BASE Group platform deck
bySharon Turnoy, Senior Communications Manager
 
PPTX
Top 8 department director resume samples
bytonychoper5605
 
PDF
Revista ProMéxico Julio 2013 NeteMed reprint
byGerardo Iglesias
 
DOCX
HALEY SUMMERS RESUME 2015
byHaley Summers
 
PPT
Research Fellow Project
byOwen Donohoe
 
PPTX
Welcome to the World of Stocks and Shares
byJulia Boyko
 
PDF
Windermere Listing Book - Hedy Goldman
byWindermere Homes & Estates
 
PDF
Geografía
byGrupo Educación y Empresa
 
PDF
MailChimp vs Constant Contact
byMary Ann S
 
DOCX
Wknj newscast0
byerika0104
 
Лепті сөйлемді қайталау
byBilim All
 
Why to go to hawaii
bystephen fry
 
Application Express - A web development environment for the masses - and for ...
bySage Computing Services
 
keseimbangan cairan dan elektrolit
byCahya
 
BASE Group platform deck
bySharon Turnoy, Senior Communications Manager
 
Top 8 department director resume samples
bytonychoper5605
 
Revista ProMéxico Julio 2013 NeteMed reprint
byGerardo Iglesias
 
HALEY SUMMERS RESUME 2015
byHaley Summers
 
Research Fellow Project
byOwen Donohoe
 
Welcome to the World of Stocks and Shares
byJulia Boyko
 
Windermere Listing Book - Hedy Goldman
byWindermere Homes & Estates
 
Geografía
byGrupo Educación y Empresa
 
MailChimp vs Constant Contact
byMary Ann S
 
Wknj newscast0
byerika0104
 

Similar to New Tuning Features in Oracle 11g - How to make your database as boring as possible

PPTX
Top 10 tips for Oracle performance
byGuy Harrison
 
PPTX
Oracle Query Optimizer - An Introduction
byadryanbub
 
PDF
Meet the CBO in Version 11g
bySage Computing Services
 
PDF
2013 Collaborate - OAUG - Presentation
byBiju Thomas
 
PDF
Results cache
bySage Computing Services
 
PPT
Cost Based Optimizer - Part 1 of 2
byMahesh Vallampati
 
PDF
Hailey_Database_Performance_Made_Easy_through_Graphics.pdf
bycookie1969
 
PPTX
Oracle performance tuning_sfsf
byMao Geng
 
PDF
About Multiblock Reads v4
byEnkitec
 
PPSX
Cost Based Oracle
bySantosh Kangane
 
PDF
Database and application performance vivek sharma
byaioughydchapter
 
PDF
ADAPTIVE FEATURES OR: HOW I LEARNED TO STOP WORRYING AND TROUBLESHOOT THE BOMB
byLudovico Caldara
 
PDF
The Cost Based Optimiser in 11gR2
bySage Computing Services
 
PDF
Bind Peeking - The Endless Tuning Nightmare
bySage Computing Services
 
PDF
query_tuning.pdf
byssuserf99076
 
PDF
R07_Senegacnik_CBO.pdf
bycookie1969
 
PDF
Whose fault is it? - a review of application tuning problems
bySage Computing Services
 
PDF
8.4 Upcoming Features
byPostgreSQL Experts, Inc.
 
PPT
Compression ow2009 r2
bycarldudley
 
PDF
Cbo100053
bymysqlops
 
Top 10 tips for Oracle performance
byGuy Harrison
 
Oracle Query Optimizer - An Introduction
byadryanbub
 
Meet the CBO in Version 11g
bySage Computing Services
 
2013 Collaborate - OAUG - Presentation
byBiju Thomas
 
Results cache
bySage Computing Services
 
Cost Based Optimizer - Part 1 of 2
byMahesh Vallampati
 
Hailey_Database_Performance_Made_Easy_through_Graphics.pdf
bycookie1969
 
Oracle performance tuning_sfsf
byMao Geng
 
About Multiblock Reads v4
byEnkitec
 
Cost Based Oracle
bySantosh Kangane
 
Database and application performance vivek sharma
byaioughydchapter
 
ADAPTIVE FEATURES OR: HOW I LEARNED TO STOP WORRYING AND TROUBLESHOOT THE BOMB
byLudovico Caldara
 
The Cost Based Optimiser in 11gR2
bySage Computing Services
 
Bind Peeking - The Endless Tuning Nightmare
bySage Computing Services
 
query_tuning.pdf
byssuserf99076
 
R07_Senegacnik_CBO.pdf
bycookie1969
 
Whose fault is it? - a review of application tuning problems
bySage Computing Services
 
8.4 Upcoming Features
byPostgreSQL Experts, Inc.
 
Compression ow2009 r2
bycarldudley
 
Cbo100053
bymysqlops
 

More from Sage Computing Services

PDF
Vpd
bySage Computing Services
 
PPTX
Common Coding and Design mistakes (that really mess up performance)
bySage Computing Services
 
PPTX
OHarmony - How the Optimiser works
bySage Computing Services
 
PDF
Take a load off! Load testing your Oracle APEX or JDeveloper web applications
bySage Computing Services
 
PPTX
Oracle Discoverer is dead - Where to next for BI?
bySage Computing Services
 
PDF
How Can I tune it When I Can't Change the Code?
bySage Computing Services
 
PDF
Lost without a trace
bySage Computing Services
 
PDF
Transformations - how Oracle rewrites your statements
bySage Computing Services
 
PDF
Oracle XML DB - What's in it for me?
bySage Computing Services
 
PPT
Back to basics: Simple database web services without the need for SOA
bySage Computing Services
 
Vpd
bySage Computing Services
 
Common Coding and Design mistakes (that really mess up performance)
bySage Computing Services
 
OHarmony - How the Optimiser works
bySage Computing Services
 
Take a load off! Load testing your Oracle APEX or JDeveloper web applications
bySage Computing Services
 
Oracle Discoverer is dead - Where to next for BI?
bySage Computing Services
 
How Can I tune it When I Can't Change the Code?
bySage Computing Services
 
Lost without a trace
bySage Computing Services
 
Transformations - how Oracle rewrites your statements
bySage Computing Services
 
Oracle XML DB - What's in it for me?
bySage Computing Services
 
Back to basics: Simple database web services without the need for SOA
bySage Computing Services
 

Recently uploaded

PDF
Delegating Behaviors in C++: A Practical Tour of the Available Mechanisms
byDaniele Pallastrelli
 
PDF
The journey from (not)REST to GraphQL at scale - The Deezer experience
byLoïc Doubinine
 
PPTX
Trailblazer Community Session - Patterns & Anti-Patterns_ The Good, The Bad, ...
bymansoorahmad941764
 
PDF
SoftCorePC Full Updated Software Link Here
bySoftCore
 
PDF
Comprehensive Logging with mORMot 2 on Delphi and FPC
byArnaud Bouchez
 
PDF
Do customers need Windows Server Device CALs for devices if they have User CA...
byQ-Advise
 
PDF
Insurance Underwriting Software for Smarter Operations
byInsurance Tech Services
 
PPTX
Best Stepwise Approach to Scrape IMDb Data Effectively.pptx
byArcTechnolbs
 
PDF
ICTlecture2 for 1st Semester Students of BS(CS)
bySirRafiLectures
 
PPTX
Building smarter with AI - From Idea to Market at Warp Speed
byLuca Grulla
 
PPTX
Empower Your Business with Busy Software from MovingCloud360.pptx
byMoving Cloud
 
PPTX
Foundations of Marketo Engage - Professional Exam Preparation - October 2025
bybbedford2
 
PDF
HTML_Final notes_with_header_and_footer.pdf
byJaydeep Kale
 
PPTX
White Label Travel Portal Solutions for B2C, B2B & B2E
byRayds Services Limited
 
PDF
How to Find an App Developer in Phoenix.pdf
byNet-Craft.com
 
PDF
AI and Generative AI: Dual-Use Risks and Autonomous Threats.pdf
byCyberneticGI
 
DOCX
Digital Dominance_ Why You Need a Top Web Design Agency in Dubai.docx
bydigpal3
 
PDF
How App Developers in London UK Empower Digital Businesses – US APP Developer...
byIndia App Developer
 
DOCX
A A Comprehensive Guide on Unicode Text Converter
byJosh Bell
 
PPTX
Soft(ware) Stories: Tecnologia, Identità e Giustizia di genere nel mondo digi...
byLetizia Jaccheri
 
Delegating Behaviors in C++: A Practical Tour of the Available Mechanisms
byDaniele Pallastrelli
 
The journey from (not)REST to GraphQL at scale - The Deezer experience
byLoïc Doubinine
 
Trailblazer Community Session - Patterns & Anti-Patterns_ The Good, The Bad, ...
bymansoorahmad941764
 
SoftCorePC Full Updated Software Link Here
bySoftCore
 
Comprehensive Logging with mORMot 2 on Delphi and FPC
byArnaud Bouchez
 
Do customers need Windows Server Device CALs for devices if they have User CA...
byQ-Advise
 
Insurance Underwriting Software for Smarter Operations
byInsurance Tech Services
 
Best Stepwise Approach to Scrape IMDb Data Effectively.pptx
byArcTechnolbs
 
ICTlecture2 for 1st Semester Students of BS(CS)
bySirRafiLectures
 
Building smarter with AI - From Idea to Market at Warp Speed
byLuca Grulla
 
Empower Your Business with Busy Software from MovingCloud360.pptx
byMoving Cloud
 
Foundations of Marketo Engage - Professional Exam Preparation - October 2025
bybbedford2
 
HTML_Final notes_with_header_and_footer.pdf
byJaydeep Kale
 
White Label Travel Portal Solutions for B2C, B2B & B2E
byRayds Services Limited
 
How to Find an App Developer in Phoenix.pdf
byNet-Craft.com
 
AI and Generative AI: Dual-Use Risks and Autonomous Threats.pdf
byCyberneticGI
 
Digital Dominance_ Why You Need a Top Web Design Agency in Dubai.docx
bydigpal3
 
How App Developers in London UK Empower Digital Businesses – US APP Developer...
byIndia App Developer
 
A A Comprehensive Guide on Unicode Text Converter
byJosh Bell
 
Soft(ware) Stories: Tecnologia, Identità e Giustizia di genere nel mondo digi...
byLetizia Jaccheri
 

New Tuning Features in Oracle 11g - How to make your database as boring as possible

  • 1.
    SAGE Computing Services CustomisedOracle Training Workshops and Consulting New Tuning Features in Oracle 11g - How to make your database as boring as possible Penny Cookson Managing Director
  • 2.
    Automatic Diagnostic Repository(ADR) File based repository of diagnostic information including:- trace files alert log dump files health monitoring reports incidents and problem descriptions Command line interface ADRCI Enterprise Manager Support Workbench
  • 3.
    Finding Trace Files diagnosticdirectory directory for each product default trace directory ADR base
  • 4.
    Finding Trace Files SELECTname, description, value , isdeprecated FROM v$parameter WHERE name = 'diagnostic_dest' OR name like '%dump%'
  • 5.
    Finding Trace Files SELECT* FROM v$diag_info
  • 6.
    Finding Trace Files SELECTpid, program, tracefile FROM v$process
  • 7.
    Bind Peeking andAdaptive Cursors
  • 8.
    Bind Peeking +Adaptive Cursors SELECT resource_code, count(*) FROM bookings_large GROUP BY resource_code; Majority value Minority value
  • 9.
    Bind Peeking Look atthe value of the bind variable when the statement is parsed Plan is based on that value BEGIN :v3 := ‘PC1'; END; SELECT COUNT(quantity) FROM bookings_large WHERE resource_code = :v3; BEGIN :v3 := ‘BRLG'; END; SELECT COUNT(quantity) FROM bookings_large WHERE resource_code = :v3; SAME PLAN STILL INDEX for 34% of rows INDEX for <1% of rows
  • 10.
    Bind Peeking +Adaptive Cursors SELECT COUNT(l.quantity) FROM train.bookings_large l WHERE resource_code = :v1 SELECT sql_id FROM v$sqlarea WHERE sql_text = 'SELECT COUNT(l.quantity) FROM train.bookings_large l WHERE resource_code = :v1'
  • 11.
    Bind Peeking +Adaptive Cursors BEGIN :v1 := 'PC1'; END; SELECT COUNT(l.quantity) FROM train.bookings_large l WHERE resource_code = :v1 SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware FROM v$sql WHERE sql_id ='95jktg3mza0qm'; IS_BIND_SENSITIVE - may need to change the plan - set on first execution of the statement IS_BIND_AWARE - do need to change the plan
  • 12.
    Bind Peeking +Adaptive Cursors SELECT child_number, bind_set_hash_value, peeked, executions, rows_processed, buffer_gets FROM v$sql_cs_statistics WHERE sql_id ='95jktg3mza0qm'
  • 13.
    Bind Peeking +Adaptive Cursors call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.01 0 0 0 0 Fetch 2 0.12 0.43 119 6635 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.14 0.44 119 6635 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 82 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=6635 pr=119 pw=119 time=0 us) 48510 TABLE ACCESS BY INDEX ROWID BOOKINGS_LARGE (cr=6635 pr=119 pw=119 time=2870 us cost=1977 size=316008 card=39501) 48510 INDEX RANGE SCAN BK_RES2 (cr=104 pr=0 pw=0 time=527 us cost=90 size=0 card=39501)(object id 69870) ******************************************************************************** Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 0.00 0.00 ********************************************************************************
  • 14.
    Bind Peeking +Adaptive Cursors BEGIN :v1 := ‘BRLG'; END; SELECT COUNT(l.quantity) FROM train.bookings_large l WHERE resource_code = :v1 SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware FROM v$sql WHERE sql_id ='95jktg3mza0qm';
  • 15.
    Bind Peeking +Adaptive Cursors SELECT child_number, bind_set_hash_value, peeked, executions, rows_processed, buffer_gets FROM v$sql_cs_statistics WHERE sql_id ='95jktg3mza0qm'
  • 16.
    Bind Peeking +Adaptive Cursors call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 6.29 78.82 34335 39594 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 6.29 78.82 34335 39594 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 82 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=39594 pr=34335 pw=34335 time=0 us) 1991051 TABLE ACCESS BY INDEX ROWID BOOKINGS_LARGE (cr=39594 pr=34335 pw=34335 time=626800 us cost=1977 size=316008 card=39501) 1991051 INDEX RANGE SCAN BK_RES2 (cr=4437 pr=4436 pw=4436 time=61616 us cost=90 size=0 card=39501)(object id 69870) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 db file sequential read 34335 0.29 72.08 resmgr:cpu quantum 30 0.10 1.29 SQL*Net message from client 2 0.00 0.00 ********************************************************************************
  • 17.
    Bind Peeking +Adaptive Cursors BEGIN :v1 := ‘BRLG'; END; SELECT COUNT(l.quantity) FROM train.bookings_large l WHERE resource_code = :v1 SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware FROM v$sql WHERE sql_id ='95jktg3mza0qm';
  • 18.
    Bind Peeking +Adaptive Cursors SELECT child_number, bind_set_hash_value, peeked, executions, rows_processed, buffer_gets FROM v$sql_cs_statistics WHERE sql_id ='95jktg3mza0qm'
  • 19.
    Bind Peeking +Adaptive Cursors call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 1.23 21.71 35341 35346 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 1.23 21.72 35341 35346 0 1 Misses in library cache during parse: 0 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 82 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=35346 pr=35341 pw=35341 time=0 us) 1991051 TABLE ACCESS FULL BOOKINGS_LARGE (cr=35346 pr=35341 pw=35341 time=414975 us cost=9652 size=15700664 card=1962583) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 direct path read 314 0.42 19.45 SQL*Net message from client 2 0.10 0.10 ********************************************************************************
  • 20.
    Bind Peeking +Adaptive Cursors - Executions SELECT s.executions v$sqlex, c.sql_id, c.executions cs_stat_exec , c.peeked FROM v$sql_cs_statistics c, v$sql s WHERE s.sql_id = c.sql_id AND s.child_number = c.child_number AND s.sql_id ='95jktg3mza0qm'
  • 21.
    Bind Peeking +Adaptive Cursors - Summary Statements with histograms and bind variables are bind sensitive The first time you execute a statement with different selectivity it uses the original plan The second time it changes the plan and become bind aware Be careful when statements become invalidated by:- Gathering statistics Flushing the shared pool Restarting the database Or when they are aged out Otherwise you don’t need to worry any more
  • 22.
  • 23.
    Multi Column Statistics Gatherstatistics on combinations of columns Improves plans for complex dependency patterns SELECT count(b.comments) FROM train.events_large e, train.bookings_large b WHERE e.org_id = 2264 AND e.event_no = b.event_no AND e.comments = 'TEST'
  • 24.
    Multi Column DataPatterns SELECT count(*) FROM train.events_large e SELECT count(*) FROM train.events_large e WHERE e.org_id = 2264 SELECT count(e.comments) FROM train.events_large e WHERE e.comments = 'TEST' SELECT count(e.comments) FROM train.events_large e WHERE e.comments = 'TEST‘ AND e.org_id = 2264
  • 25.
    With Individual Statistics callcount cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 5.00 22.52 35341 36337 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 5.00 22.52 35341 36337 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 82 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=36337 pr=35341 pw=35341 time=0 us) 175 HASH JOIN (cr=36337 pr=35341 pw=35341 time=13 us cost=9966 size=33365083 card=1076293) 4 TABLE ACCESS FULL EVENTS_LARGE (cr=991 pr=0 pw=0 time=3 us cost=274 size=319770 card=18810) 5767168 TABLE ACCESS FULL BOOKINGS_LARGE (cr=35346 pr=35341 pw=35341 time=225583 us cost=9665 size=80740352 card=5767168) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 direct path read 314 0.38 15.88 resmgr:cpu quantum 11 0.14 0.27 SQL*Net message from client 2 0.04 0.04 ********************************************************************************
  • 26.
    Auto Created ColumnStatistics BEGIN dbms_stats.gather_table_stats(ownname=>'TRAIN', tabname=>'EVENTS_LARGE', cascade=>TRUE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', no_invalidate=>FALSE); END; SELECT column_name, histogram, num_buckets FROM user_tab_col_statistics WHERE table_name = 'EVENTS_LARGE'
  • 27.
    Auto Created ColumnStatistics SELECT extension_name, extension FROM user_stat_extensions WHERE table_name='EVENTS_LARGE‘ Same access path used ******************************************************************************** Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=36337 pr=35341 pw=35341 time=0 us) 175 HASH JOIN (cr=36337 pr=35341 pw=35341 time=13 us cost=9966 size=33365083 card=1076293) 4 TABLE ACCESS FULL EVENTS_LARGE (cr=991 pr=0 pw=0 time=3 us cost=274 size=319770 card=18810) 5767168 TABLE ACCESS FULL BOOKINGS_LARGE (cr=35346 pr=35341 pw=35341 time=225583 us cost=9665 size=80740352 card=5767168)
  • 28.
    Manually Create MultiColumn Statistics BEGIN dbms_stats.gather_table_stats(ownname=>'TRAIN', tabname=>'EVENTS_LARGE', cascade=>TRUE, method_opt => 'FOR COLUMNS (org_id, comments) SIZE SKEWONLY', no_invalidate=>FALSE); END; SELECT extension_name, extension FROM user_stat_extensions WHERE table_name='EVENTS_LARGE'
  • 29.
    Manually Create MultiColumn Statistics SELECT column_name, histogram, num_buckets FROM user_tab_col_statistics WHERE table_name = 'EVENTS_LARGE'
  • 30.
    Multi Column Statistics ******************************************************************************** SELECTcount(b.comments) FROM train.events_large e, train.bookings_large b WHERE e.org_id = 2264 AND e.event_no = b.event_no AND e.comments = 'TEST' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.01 0.01 0 1175 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.01 0.01 0 1175 0 1 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=1175 pr=0 pw=0 time=0 us) 175 NESTED LOOPS (cr=1175 pr=0 pw=0 time=71 us) 175 NESTED LOOPS (cr=1001 pr=0 pw=0 time=39 us cost=1129 size=26815 card=865) 4 TABLE ACCESS FULL EVENTS_LARGE (cr=991 pr=0 pw=0 time=4 us cost=274 size=255 card=15) 175 INDEX RANGE SCAN BK_EVT2 (cr=10 pr=0 pw=0 time=10 us cost=2 size=0 card=57)(object id 69868) 175 TABLE ACCESS BY INDEX ROWID BOOKINGS_LARGE (cr=174 pr=0 pw=0 time=0 us cost=57 size=798 card=57) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 0.02 0.02 ********************************************************************************
  • 31.
    Multi Column Statistics ******************************************************************************** SELECTcount(b.comments) FROM train.organisations o, train.events_large e, train.bookings_large b, train.resources r WHERE o.org_id = e.org_id AND e.event_no = b.event_no AND b.resource_code = r.code AND o.name = 'Australian Medical Systems' AND r.description = 'Buffet Lunch' AND e.comments = 'TEST' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 4.64 21.66 35341 36351 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 4.64 21.66 35341 36351 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 82
  • 32.
    Multi Column Statistics ******************************************************************************** RowsRow Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=36351 pr=35341 pw=35341 time=0 us) 25 HASH JOIN (cr=36351 pr=35341 pw=35341 time=1966789 us cost=9972 size=6555490 card=79945) 4 HASH JOIN (cr=1005 pr=0 pw=0 time=8 us cost=281 size=792225 card=12575) 1 MERGE JOIN CARTESIAN (cr=14 pr=0 pw=0 time=0 us cost=6 size=46 card=1) 1 TABLE ACCESS FULL ORGANISATIONS (cr=7 pr=0 pw=0 time=0 us cost=3 size=26 card=1) 1 BUFFER SORT (cr=7 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 1 TABLE ACCESS FULL RESOURCES (cr=7 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 75243 TABLE ACCESS FULL EVENTS_LARGE (cr=991 pr=0 pw=0 time=1020 us cost=274 size=1282616 card=75448) 5767168 TABLE ACCESS FULL BOOKINGS_LARGE (cr=35346 pr=35341 pw=35341 time=350849 us cost=9665 size=109576192 card=5767168) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 direct path read 314 0.35 15.49 resmgr:cpu quantum 2 0.12 0.12 SQL*Net message from client 2 0.04 0.05 ********************************************************************************
  • 33.
    Multi Column Statistics- Summary Manually create column groups and statistics on them Complex joins may still need SQL Profiles Use for dependent column such as Country, State
  • 34.
    Expression Statistics SELECT comments,count(*) FROM events_large GROUP by comments ORDER by count(*) desc
  • 35.
    Expression Statistics BEGIN dbms_stats.gather_table_stats(ownname=>'TRAIN', tabname=>'EVENTS_LARGE', cascade=>TRUE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', no_invalidate=>FALSE); END; SELECT column_name, histogram, num_buckets FROM user_tab_col_statistics WHERE table_name = 'EVENTS_LARGE'
  • 36.
    Expression Statistics ******************************************************************************** SELECT count(b.comments) FROMtrain.events_large e, train.bookings_large b WHERE e.event_no = b.event_no AND e.comments = 'Comments8' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.01 0.01 0 1052 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.01 0.01 0 1052 0 1
  • 37.
    Expression Statistics Rows RowSource Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=1052 pr=0 pw=0 time=0 us) 58 NESTED LOOPS (cr=1052 pr=0 pw=0 time=28 us) 58 NESTED LOOPS (cr=994 pr=0 pw=0 time=7 us cost=331 size=1178 card=38) 1 TABLE ACCESS FULL EVENTS_LARGE (cr=991 pr=0 pw=0 time=0 us cost=274 size=17 card=1) 58 INDEX RANGE SCAN BK_EVT2 (cr=3 pr=0 pw=0 time=3 us cost=2 size=0 card=57)(object id 69868) 58 TABLE ACCESS BY INDEX ROWID BOOKINGS_LARGE (cr=58 pr=0 pw=0 time=0 us cost=57 size=798 card=57) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 0.02 0.02 ********************************************************************************
  • 38.
    Expression Statistics ******************************************************************************** SELECT count(b.comments) FROMtrain.events_large e, train.bookings_large b WHERE e.event_no = b.event_no AND initcap(e.comments) = 'Comments8' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 5.70 21.52 35341 36337 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 5.71 21.52 35341 36337 0 1
  • 39.
    Expression Statistics Rows RowSource Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=36337 pr=35341 pw=35341 time=0 us) 58 HASH JOIN (cr=36337 pr=35341 pw=35341 time=624644 us cost=9967 size=1779493 card=57403) 1 TABLE ACCESS FULL EVENTS_LARGE (cr=991 pr=0 pw=0 time=0 us cost=275 size=17051 card=1003) 5767168 TABLE ACCESS FULL BOOKINGS_LARGE (cr=35346 pr=35341 pw=35341 time=301177 us cost=9665 size=80740352 card=5767168) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 direct path read 314 0.41 15.12 resmgr:cpu quantum 2 0.10 0.10 SQL Net message from client 2 0.03 ********************************************************************************
  • 40.
    Expression Statistics BEGIN dbms_stats.gather_table_stats(ownname=>'TRAIN', tabname=>'EVENTS_LARGE', cascade=>TRUE, method_opt=> 'FOR COLUMNS (INITCAP(comments)) SIZE SKEWONLY', no_invalidate=>FALSE); END; SELECT extension_name, extension FROM user_stat_extensions WHERE table_name='EVENTS_LARGE'
  • 41.
    Expression Statistics SELECT column_name,histogram, num_buckets FROM user_tab_col_statistics WHERE table_name = 'EVENTS_LARGE'
  • 42.
    Expression Statistics ******************************************************************************** SELECT count(b.comments) FROMtrain.events_large e, train.bookings_large b WHERE e.event_no = b.event_no AND initcap(e.comments) = 'Comments8' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 2 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.03 0.04 0 1052 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.03 0.04 0 1054 0 1
  • 43.
    Expression Statistics Rows RowSource Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=1052 pr=0 pw=0 time=0 us) 58 NESTED LOOPS (cr=1052 pr=0 pw=0 time=27 us) 58 NESTED LOOPS (cr=994 pr=0 pw=0 time=7 us cost=332 size=2223 card=57) 1 TABLE ACCESS FULL EVENTS_LARGE (cr=991 pr=0 pw=0 time=0 us cost=275 size=25 card=1) 58 INDEX RANGE SCAN BK_EVT2 (cr=3 pr=0 pw=0 time=3 us cost=2 size=0 card=57)(object id 69868) 58 TABLE ACCESS BY INDEX ROWID BOOKINGS_LARGE (cr=58 pr=0 pw=0 time=0 us cost=57 size=798 card=57) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 0.02 0.02
  • 44.
  • 45.
    Setting Statistics GatheringDefaults Obsolete: GET_PARAM SET_PARAM Procedure Use: GET_PREFS Function SET_TABLE_PREFS (set for individual tables) SET_DATABASE_PREFS (sets for all tables, can include/exclude SYS tables) SET_GLOBAL_PREFS – for new objects
  • 46.
  • 47.
  • 48.
    Setting Statistics GatheringDefaults Doesn’t work
  • 49.
    Table Prefs PROCEDURE SET_TABLE_PREFS ArgumentName Type In/Out Default? ------------------------------ ----------------------- ------ -------- OWNNAME VARCHAR2 IN TABNAME VARCHAR2 IN PNAME VARCHAR2 IN PVALUE VARCHAR2 IN PNAME CASCADE DEGREE ESTIMATE_PERCENT METHOD_OPT NO_INVALIDATE GRANULARITY PUBLISH INCREMENTAL (only scan partitions that have been changed) STALE_PERCENT
  • 50.
    Table Prefs -Example BEGIN dbms_stats.set_table_prefs(ownname=>'TRAIN', tabname=>'EVENTS_LARGE', pname=>'STALE_PERCENT', pvalue=>'30'); dbms_stats.set_table_prefs(ownname=>'TRAIN', tabname=>'EVENTS_LARGE', pname=>'METHOD_OPT', pvalue=>'FOR ALL COLUMNS SIZE AUTO'); END; SELECT * FROM DBA_TAB_STAT_PREFS;
  • 51.
    Gathering and Publishing Setpreferences to PUBLISH = ‘FALSE’ Gather Statistics user_tab_pending_stats user_ind_pending_stats user_col_pending_stats Run test case Alter session set optimizer_pending_statistics = TRUE Run test case Worse Better dbms_stats. delete_pending_stats dbms_stats. publish_pending_stats user_tab_statistics user_ind_statistics user_tab_col_statistics
  • 52.
    Gathering and Publishing SELECTobj#, TO_CHAR(savtime,'dd/mm/yyyy') save_time, rowcnt, blkcnt, avgrln, samplesize, analyzetime FROM wri$_optstat_tab_history ORDER BY savtime desc
  • 53.
    Gathering and Publishing Neverpublish any statistics until you are happy they work better
  • 54.
  • 55.
    Automatic Tuning Automatic SQLTuning (maintenance window) Automatic Workload Repository (AWR) Identify high load candidates for tuning for each statement Run SQL Tuning Advisor Test with and without profile ACCEPT_SQL_PROFILES=true ACCEPT_SQL_PROFILES=false (default) SQL Profiles Report
  • 56.
  • 57.
  • 58.
  • 59.
  • 60.
  • 61.
  • 62.
    Automatic Tuning SELECT count(b.comments) FROMtrain.organisations o, train.events_large e, train.bookings_large b, train.resources r WHERE o.org_id = e.org_id AND e.event_no = b.event_no AND b.resource_code = r.code AND o.name = 'Australian Medical Systems' AND r.description = 'Buffet Lunch' AND e.comments = 'TEST'
  • 63.
  • 64.
  • 65.
    Automatic Tuning call countcpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 5.84 19.13 35341 36351 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 5.84 19.13 35341 36351 0 1 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=36351 pr=35341 pw=35341 time=0 us) 149 HASH JOIN (cr=36351 pr=35341 pw=35341 time=66 us cost=9972 size=6504732 card=79326) 19 HASH JOIN (cr=1005 pr=0 pw=0 time=10 us cost=281 size=786051 card=12477) 1 MERGE JOIN CARTESIAN (cr=14 pr=0 pw=0 time=0 us cost=6 size=46 card=1) 1 TABLE ACCESS FULL ORGANISATIONS (cr=7 pr=0 pw=0 time=0 us cost=3 size=26 card=1) 1 BUFFER SORT (cr=7 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 1 TABLE ACCESS FULL RESOURCES (cr=7 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 74511 TABLE ACCESS FULL EVENTS_LARGE (cr=991 pr=0 pw=0 time=1410 us cost=274 size=1272688 card=74864) 5767168 TABLE ACCESS FULL BOOKINGS_LARGE (cr=35346 pr=35341 pw=35341 time=233788 us cost=9665 size=109576192 card=5767168) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 4.20 4.20 direct path read 314 0.45 11.84 ********************************************************************************
  • 66.
    Automatic Tuning call countcpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.05 1 3 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.17 1.61 100 2078 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.18 1.66 101 2081 0 1 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=2078 pr=100 pw=100 time=0 us) 149 NESTED LOOPS (cr=2078 pr=100 pw=100 time=137634 us) 1032 NESTED LOOPS (cr=1047 pr=14 pw=14 time=159 us cost=1649 size=12382 card=151) 19 HASH JOIN (cr=1005 pr=0 pw=0 time=19 us cost=281 size=1512 card=24) 1 MERGE JOIN CARTESIAN (cr=14 pr=0 pw=0 time=0 us cost=6 size=46 card=1) 1 TABLE ACCESS FULL ORGANISATIONS (cr=7 pr=0 pw=0 time=0 us cost=3 size=26 card=1) 1 BUFFER SORT (cr=7 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 1 TABLE ACCESS FULL RESOURCES (cr=7 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 74511 TABLE ACCESS FULL EVENTS_LARGE (cr=991 pr=0 pw=0 time=1127 us cost=274 size=1272688 card=74864) 1032 INDEX RANGE SCAN BK_EVT2 (cr=42 pr=14 pw=14 time=68 us cost=2 size=0 card=57)(object id 69868) 149 TABLE ACCESS BY INDEX ROWID BOOKINGS_LARGE (cr=1031 pr=86 pw=86 time=0 us cost=57 size=114 card=6) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 17.58 17.58 db file sequential read 79 0.07 1.44 db fil tt d d 4 0 02 0 05
  • 67.
    Automatic Tuning SELECT signature,inuse_features, flags FROM sql$ WHERE signature = ‘1697466342721355938’ SELECT /*+ INDEX(sqlobj$ (signature category obj_type plan_id)) */ DISTINCT category FROM sqlobj$ WHERE signature = ‘1697466342721355938’ SELECT obj_type, plan_id, name, flags, last_executed FROM sqlobj$ WHERE signature = ‘1697466342721355938’ AND category = :2 SELECT obj_type, plan_id, comp_data FROM sqlobj$data WHERE signature = ‘1697466342721355938’ AND category = :2
  • 68.
  • 69.
  • 70.
  • 71.
    Automatic Tuning call countcpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 17.67 227.31 9818 1177977 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 17.67 227.32 9818 1177977 0 1 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=1177977 pr=9818 pw=9818 time=0 us) 398155 NESTED LOOPS (cr=1177977 pr=9818 pw=9818 time=1802295 us) 1148142 NESTED LOOPS (cr=29836 pr=2508 pw=2508 time=209241 us cost=15 size=144 card=4) 19800 INDEX RANGE SCAN EVTLG_PK (cr=37 pr=37 pw=37 time=395 us cost=2 size=17 card=1)(object id 69847) 1148142 INDEX RANGE SCAN BK_EVT2 (cr=29799 pr=2471 pw=2471 time=545049 us cost=2 size=0 card=11)(object id 69868) 398155 TABLE ACCESS BY INDEX ROWID BOOKINGS_LARGE (cr=1148141 pr=7310 pw=7310 time=0 us cost=13 size=76 card=4) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 283.03 283.03 db file sequential read 7621 0.71 193.61 db file scattered read 496 0.60 13.09
  • 72.
    Automatic Tuning call countcpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.03 1 3 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 1.48 18.43 35341 35383 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 1.50 18.47 35342 35386 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 82 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=35383 pr=35341 pw=35341 time=0 us) 398155 HASH JOIN (cr=35383 pr=35341 pw=35341 time=131437 us cost=9656 size=14112720 card=392020) 19800 INDEX RANGE SCAN EVTLG_PK (cr=37 pr=0 pw=0 time=304 us cost=2 size=338470 card=19910)(object id 69847) 398155 TABLE ACCESS FULL BOOKINGS_LARGE (cr=35346 pr=35341 pw=35341 time=107228 us cost=9652 size=7321631 card=385349) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 30.55 30.55 direct path read 314 0.25 15.87
  • 73.
    Automatic Tuning However goodthis looks: Let it do its automatic tuning Don’t let it automatically implement (in a transaction database) Check its recommendations and implement if you like them
  • 74.
  • 75.
    CBO - Instability CBO ?access path decision Systemparameters Session parameters Optimiser StatisticsIndexes SQL Profile Software version System statistics DBA playing around
  • 76.
    CBO - Instability •Solution 1 – Leave it all alone • Solution 2 – SQL Plan Management – Store plan baseline – Plans not used till accepted – Manually accept or – Allow Oracle to evolve plans
  • 77.
    SQL Plan Management Manualcapture DBMS_SPM.LOAD_PLANS_FROM_SQLSET DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE Auto capture of repeatable statements OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE = TRUE Baseline = (Stored and Accepted plans) SQL Management Base New Plan identified during execution Stored not accepted SQL Tuning Advisor identifies new plan – SQL*Profile accepted Auto accept of new plan (if it performs better) DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE Manual load/accept of new plan DBMS_SPM.LOAD_PLANS_FROM_SQLSET DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
  • 78.
    SQL Plan Management– Fixed Plans Fixed plan SQL Management Base New Plan identified but not added Non fixed plans SQL Tuning Advisor identifies new plan – SQL*Profile accepted Manual load/accept of new plan DBMS_SPM.LOAD_PLANS_FROM_SQLSET DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE FIXED=> Y
  • 79.
    SQL Plan Management altersystem flush shared_pool No SQL Profiles, No baselines Execute twice alter system flush shared_pool
  • 80.
  • 81.
  • 82.
  • 83.
  • 84.
  • 85.
  • 86.
    SQL Plan Management– Plan Retention • Default 53 weeks • Only unused plans purged • Why would you keep a plan for a year if it has only been used once BEGIN DBMS_SPM.CONFIGURE( 'plan_retention_weeks',13); END;
  • 87.
    SQL Plan Management– Binds • Will not automatically handle adaptive cursors • New plan identified on first execution • New plan recorded as not accepted • Plan will not evolve • All bind variable values use same baseline plan • Plans show as not bind sensitive or aware
  • 88.
  • 89.
  • 90.
    SQL Plan Management– Binds Force Acceptance of the plan
  • 91.
    SQL Plan Management– Binds Once you have forced the plan to be accepted the statement will be bind aware
  • 92.
  • 93.
    Results Cache SGA SQL Query ResultsCache Results Cache PL/SQL Function Results Cache populate invalidate populate
  • 94.
    Query Results Cache RESULT_CACHE_MODEMANUAL FORCE --+ result_cache --+ no_result_cache Will not cache if:- Dictionary tables Temporary tables Sequences Dates/times Non deterministic PL/SQL
  • 95.
    Monitoring the ResultsCache • SELECT * FROM v$result_cache_memory • SELECT * FROM v$result_cache_objects • SELECT * FROM v$result_cache_statistics • SELECT * FROM v$result_cache_dependency • RESULT_CACHE_MAX_SIZE • RESULT_CACHE_MAX_RESULT • Explain Plan
  • 96.
    Query Results Cache SELECT* FROM v$result_cache_objects
  • 97.
    PL/SQL Function ResultsCache CREATE OR REPLACE FUNCTION quantity_booked (p_resource_code in resources.code%TYPE,p_event_date in date) RETURN NUMBER RESULT_CACHE RELIES_ON (BOOKINGS,EVENTS) IS v_total_booked number := 0; BEGIN SELECT sum(b.quantity) INTO v_total_booked FROM bookings b, events e WHERE e.event_no = b.event_no AND p_event_date between e.start_date and e.end_date AND b.resource_code = p_resource_code; book_pkg.add_one(v_total_booked, v_total_booked); RETURN (v_total_booked); END;
  • 98.
    PL/SQL Function ResultsCache DECLARE v_num number; BEGIN v_num := quantitybooked('VCR2',’06-NOV-2007’); dbms_output.put_line(v_num); END; SELECT * FROM v$result_cache_objects 9
  • 99.
    PL/SQL Function ResultsCache UPDATE bookings SET quantity = quantity*2; COMMIT; DECLARE v_num number; BEGIN v_num := quantitybooked('VCR2',’06-NOV-2007’); dbms_output.put_line(v_num); END; 17
  • 100.
    PL/SQL Function ResultsCache CREATE OR REPLACE PACKAGE BODY book_pkg AS PROCEDURE add_one (p_result IN NUMBER, p_result_out OUT NUMBER) IS BEGIN p_result_out := p_result+10; END; END;
  • 101.
    PL/SQL Function ResultsCache DECLARE v_num number; BEGIN v_num := quantitybooked('VCR2',’06-NOV-2007’); dbms_output.put_line(v_num); END; 17
  • 102.
    PL/SQL Function ResultsCache Don’t do stupid stuff CREATE OR REPLACE FUNCTION non_determ RETURN VARCHAR2 RESULT_CACHE IS BEGIN RETURN TO_CHAR(sysdate,'ddmmyyyy hh:mi:ss'); END;
  • 103.
    Designing for thePL/SQL Function Results Cache Data Store Result Cache Business Object EvtOrg = PL/SQL function result Processing
  • 104.
    Designing for thePL/SQL Function Results Cache
  • 105.
    Designing for thePL/SQL Function Results Cache
  • 106.
    Designing for thePL/SQL Function Results Cache So why do we need a middle tier?
  • 107.
  • 108.
  • 109.
  • 110.
  • 111.
  • 112.
  • 113.
  • 114.
    Summary - Setup Migrateto Test instance of 11g Run application Collect SQL Tuning set Run SQL Performance Analyzer Add extra statistics Run automatic tuning – without automatic implementation Look at suggestions fix the problem or accept the profile Lock any statistics where required (e.g very dynamic tables) Run SQL Tuning set in SQL Performance Analyzer and compare For a transaction database Baseline the application SQL If you are really paranoid fix the baseline plans
  • 115.
    Summary WORTH A TRIAL Selectivelycache PL/SQL results If you have lots of spare memory turn results caching on
  • 116.
    Summary – Maintenance(Transaction DB) Keep auto stats gathering on (at a sensible interval), or manually gather stats when you have a quiet evening Publish stats if performance not worse Keep automatic tuning scheduled– automatic implementation off Look at suggestions fix the problem or accept the profile Monitor new baseline plans manually test and accept Before you change anything (your hairstyle/shirt/partner) Run SQL Performance Analyzer and compare old and new configuration
  • 117.
    Summary – Maintenance(Reporting DB) Keep auto stats gathering on (at a sensible interval), or manually gather stats when you have a quiet evening Publish stats if performance not worse Keep automatic tuning scheduled – automatic implementation on Before you make significant changes (version) Run SQL Performance Analyzer and compare old and new configuration
  • 118.
    SAGE Computing Services CustomisedOracle Training Workshops and Consulting Questions and Answers? Presentations are available from our website: http://www.sagecomputing.com.au enquiries@sagecomputing.com.au penny@sagecomputing.com.au