More than often, it turns out my task is to explore what other people have already explored. Thanks google, more than often, I can find the right experts who are willing to share their knowledge. Below are the two posts about SQL Profiles that makes this happen.
Plan stability in 10g - using existing cursors to create Stored Outlines and SQL profiles by Randolf Geist
SQL Profiles by Kerry Osborne
What I am going to describe is my personal experience about exploring SQL Profiles in order to deal with a problem SQL in the production environment.
The following query in one of our production 10g database has sub-optimal execution plan:
SELECT COUNT(SESSION_KEY) FROM RMS WHERE cust_btn='8136777776' AND
SESSION_KEY like 'BillingSummary%' AND LAST_ACCESS_DATE >= (SYSDATE-1)There are hundreds of such kind of queries that are only different in the literals are runing at any time during the day. Yeah, they do not use bind variables. This is not my concern in my position now. I rememberred once they said for some reason, they can not adopt bind variables. The table is truncated every night, and it then grows from 0 to about 200K rows prior to being truncated. There are an index on SESSION_KEY column and another index on cust_btn column. The bad plan uses the index on SESSION_KEY, while the good plan uses the index on cust_btn.
I have tried to use DBMS_SQLTUNE package to generate a SQL Profile to accept automatically, however, Oracle have not given any recommenations (first time due to stale stats, after gathering stats, no lucky either). This left me the choice to create it manually. My steps are as follows:
(1) Execute the sql with hint on good index and display the outline
SQL> SELECT /*+ index (RMS,IX_CUST_BTN_RMAS ) */
2 COUNT(SESSION_KEY) FROM RMS WHERE cust_btn='8136777776' AND
3 SESSION_KEY like 'BillingSummary%' AND LAST_ACCESS_DATE >= (SYSDATE-1);
COUNT(SESSION_KEY)
------------------
1
Elapsed: 00:00:00.00
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'OUTLINE'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID b5mp8fdcxdxav, child number 0
-------------------------------------
SELECT /*+ index (RMS,IX_CUST_BTN_RMAS ) */ COUNT(SESSION_KEY) FROM
RMS WHERE cust_btn='8136777776' AND SESSION_KEY like 'BillingSummary%'
AND LAST_ACCESS_DATE >= (SYSDATE-1)
Plan hash value: 2513418175
----------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------------------
0 SELECT STATEMENT 7 (100)
1 SORT AGGREGATE 1 69
* 2 TABLE ACCESS BY INDEX ROWID RMS 1 69 7 (0) 00:00:01
* 3 INDEX RANGE SCAN IX_CUST_BTN_RMAS 7 1 (0) 00:00:01
-----------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "RMS"@"SEL$1" ("RMS"."CUST_BTN"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("SESSION_KEY" LIKE 'BillingSummary%' AND "LAST_ACCESS_DATE">=SYSDATE@!-1))
3 - access("CUST_BTN"='8136777776')
(2) Create a SQL Profile manually.
Note: using the outline info otained in (1) to specify the profile attribute.
begin dbms_sqltune.import_sql_profile( name => 'profile_myacc_2', description => 'SQL profile created manually', category => 'TEST', sql_text => q'[SELECT COUNT(SESSION_KEY) FROM RMS WHERE cust_btn='8136777776' AND SESSION_KEY like 'BillingSummary%' AND LAST_ACCESS_DATE >= (SYSDATE-1)]', profile => sqlprof_attr( 'IGNORE_OPTIM_EMBEDDED_HINTS', 'ALL_ROWS', 'OUTLINE_LEAF(@"SEL$1")', 'INDEX_RS_ASC(@"SEL$1" "RMS"@"SEL$1" ("RMS"."CUST_BTN"))' ), replace => FALSE, force_match => TRUE ); end; /
(3) Verify the SQL profile can be used
a. Without using the profile, consistent gets= 17999
SQL> set autotrace on
SQL> SELECT COUNT(SESSION_KEY) FROM RMS WHERE cust_btn='6095884091' AND
SESSION_KEY like 'LinkSummary%' AND LAST_ACCESS_DATE >= (SYSDATE-1) 2
3 ;
COUNT(SESSION_KEY)
------------------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 769759756
---------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 69 4 (0) 00:00:01
1 SORT AGGREGATE 1 69
* 2 TABLE ACCESS BY INDEX ROWID RMS 1 69 4 (0) 00:00:01
* 3 INDEX RANGE SCAN IX_MYACC_SUM_KEY 1 3 (0) 00:00:01
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CUST_BTN"='6095884091' AND "LAST_ACCESS_DATE">=SYSDATE@!-1)
3 - access("SESSION_KEY" LIKE 'LinkSummary%')
filter("SESSION_KEY" LIKE 'LinkSummary%')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
17990 consistent gets
0 physical reads
0 redo size
525 bytes sent via SQL*Net to client
488 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
b. With the profile, consistent gets= 7
SQL> alter session set sqltune_category=test;
Session altered.
SQL> SELECT COUNT(SESSION_KEY) FROM RMS WHERE cust_btn='6095884091' AND
SESSION_KEY like 'LinkSummary%' AND LAST_ACCESS_DATE >= (SYSDATE-1) 2 ;
COUNT(SESSION_KEY)
------------------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 2513418175
--------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 69 7 (0) 00:00:01
1 SORT AGGREGATE 1 69
* 2 TABLE ACCESS BY INDEX ROWID RMS 1 69 7 (0) 00:00:01
* 3 INDEX RANGE SCAN IX_CUST_BTN_RMAS 7 1 (0) 00:00:01
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SESSION_KEY" LIKE 'LinkSummary%' AND "LAST_ACCESS_DATE">=SYSDATE@!-1)
3 - access("CUST_BTN"='6095884091')
Note
-----
- SQL profile "profile_myacc_2" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
525 bytes sent via SQL*Net to client
488 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Sep 16, 2013 update:
A tool called SQLT provides a script called coe_xfr_sql_profile.sql that can be used to create a sql profile in a very convenient way.
see: SQLT (SQLTXPLAIN) - Tool that helps to diagnose a SQL statement performing poorly or one that produces wrong results (Doc ID 215187.1)
No comments:
Post a Comment