28 | 03 | 2024
Latest Articles
Popular Articles

SQL Tuning

An introduction to SQL Tuning : Delayed publishing of optimizer statistics

User Rating:  / 1
PoorBest 

An introduction to SQL Tuning : Delayed publishing of optimizer statistics


1. You are a DBA and suddenly you have to deal with a performance issue
2. You vave learnt that good optimizer statistics are important for the parsing of the best possible execution plan
3. You found out that most probably you can fix the performance issue by gathering fresh optimizer statistics in a specific manner
4. You are not 100% that with the new optimizer statistics the performance issue will be fixed


New in 11G is the possibility to gather statistics and keep the fresh stats in a private pending area until you publish them after your validation.


STEP 1 : THE PROBLEM QUERY WITH THE SUB-OPTMAL EXPLAIN PLAN


11:20:57 SILVER 30/10/2013 11:16 > explain plan for select * from T_ORDER_OR,
11:21:36 2 T_DELIVERY_DL
11:21:48 3 where OR_FK = OR_PK
11:21:58 4 and CR_FK = 'EUR' and extract(year from OR_ORDERDATE) = '2012'
11:22:08 5 and su_fk = 1;

Explained.

11:22:43 SILVER 30/10/2013 11:16 > select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 319855565

------------------------------------------------------------------------------------------
| Id | Operation                  |          Name | Rows | Bytes | Cost (%CPU)|     Time |
------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT           |               |   26 | 12272 |    6985 (1)| 00:00:02 |
|  1 | NESTED LOOPS               |               |      |       |            |          |
|  2 | NESTED LOOPS               |               |   26 | 12272 |    6985 (1)| 00:00:02 |
|* 3 | TABLE ACCESS FULL          |    T_ORDER_OR |   26 |  6266 |    6907 (1)| 00:00:02 |
|* 4 | INDEX RANGE SCAN           |       DL_IDX1 |    1 |       |       2 (0)| 00:00:01 |
|  5 | TABLE ACCESS BY INDEX ROWID| T_DELIVERY_DL |    1 |   231 |       3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("SU_FK"=1 AND "CR_FK"='EUR' AND EXTRACT(YEAR FROM INTERNAL_FUNCTION("OR_ORDERDATE"))=2012)
4 - access("OR_FK"="OR_PK")

19 rows selected.


STEP 2 : YOU DECIDE TO KEEP NEW STATS INTO A PENDING AREA FOR A TABLE

 

11:22:55 SILVER 30/10/2013 11:16 > begin
11:26:07 2 dbms_stats.set_table_prefs(user,'T_ORDER_OR','PUBLISH','false');
11:26:58 3 end;
11:26:59 4 /

PL/SQL procedure successfully completed.

 

STEP 3 : YOU GATHER FRESH OPTIMIZER STATS IN A SPECIFIC MANNER


In this example I use Oracle' s virtual column and expression statistics technology


11:27:00 SILVER 30/10/2013 11:16 > select dbms_stats.create_extended_stats(null,'T_ORDER_OR','(extract(year from OR_ORDERDATE))') from dual;

11:28:56 SILVER 30/10/2013 11:16 > begin
11:29:06 2 dbms_stats.gather_table_stats(user,'T_ORDER_OR',estimate_percent => 100, cascade => true, method_opt => 'FOR ALL COLUMNS SIZE AUTO',no_invalidate => false);
11:29:41 3 end;
11:29:42 4 /

PL/SQL procedure successfully completed.

 

STEP 4 : YOU VALIDATE THE NOT YET PUBLISHED STATS

 

The original plan


11:30:25 SILVER 30/10/2013 11:16 > explain plan for select * from T_ORDER_OR,
11:32:11 2 T_DELIVERY_DL
11:32:21 3 where OR_FK = OR_PK
11:32:33 4 and CR_FK = 'EUR' and extract(year from OR_ORDERDATE) = '2012'
11:33:03 5 and su_fk = 1;

Explained.


11:33:09 SILVER 30/10/2013 11:16 > select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 319855565

------------------------------------------------------------------------------------------
| Id |                  Operation |          Name | Rows | Bytes | Cost (%CPU)|     Time |
------------------------------------------------------------------------------------------
|  0 |           SELECT STATEMENT |               |   26 | 12376 |    6985 (1)| 00:00:02 |
|  1 |               NESTED LOOPS |               |      |       |            |          |
|  2 |               NESTED LOOPS |               |   26 | 12376 |    6985 (1)| 00:00:02 |
|* 3 |          TABLE ACCESS FULL |    T_ORDER_OR |   26 |  6370 |    6907 (1)| 00:00:02 |
|* 4 |           INDEX RANGE SCAN |       DL_IDX1 |    1 |       |       2 (0)| 00:00:01 |
|  5 | TABLE ACCESS BY INDEX ROWID| T_DELIVERY_DL |    1 |   231 |       3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("SU_FK"=1 AND "CR_FK"='EUR' AND EXTRACT(YEAR FROM INTERNAL_FUNCTION("OR_ORDERDATE"))=2012)
4 - access("OR_FK"="OR_PK")

19 rows selected.

 

What would the new plan be with the new statistics ?

 

11:34:42 SILVER 30/10/2013 11:16 > alter session set optimizer_use_pending_statistics = true;

Session altered.

11:35:08 SILVER 30/10/2013 11:16 > explain plan for select * from T_ORDER_OR,
11:35:21 2 T_DELIVERY_DL
11:35:30 3 where OR_FK = OR_PK
11:35:38 4 and CR_FK = 'EUR' and extract(year from OR_ORDERDATE) = '2012'
11:35:48 5 and su_fk = 1;

Explained.

11:35:56 SILVER 30/10/2013 11:16 > select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1384926585

--------------------------------------------------------------------------------
| Id |        Operation |          Name | Rows | Bytes | Cost (%CPU)|     Time |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT |               | 2584 |  1201K|   13538 (1)| 00:00:03 |
|* 1 |        HASH JOIN |               | 2584 |  1201K|   13538 (1)| 00:00:03 |
|* 2 | TABLE ACCESS FULL|    T_ORDER_OR | 2580 |   617K|    6907 (1)| 00:00:02 |
|  3 | TABLE ACCESS FULL| T_DELIVERY_DL | 1001K|   220M|    6625 (1)| 00:00:02 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("OR_FK"="OR_PK")
2 - filter("SU_FK"=1 AND "CR_FK"='EUR' AND EXTRACT(YEAR FROM INTERNAL_FUNCTION("OR_ORDERDATE"))=2012)

17 rows selected.

 

The new explain plan is better - in this case - we decide to publish the stats

 

STEP 5 : YOU PUBLISH THE PENDING STATISTICS

 

11:46:47 SILVER 30/10/2013 11:16 > begin
11:47:26 2 dbms_stats.publish_pending_stats(user,'T_ORDER_OR');
11:47:44 3 end;
11:47:51 4 /

PL/SQL procedure successfully completed.

 

STEP 6 : VALIDATE THE STATS ARE NOW USED

 

11:47:53 SILVER 30/10/2013 11:16 > alter session set optimizer_use_pending_statistics = false;

Session altered.

11:48:43 SILVER 30/10/2013 11:16 > explain plan for select * from T_ORDER_OR,
11:48:53 2 T_DELIVERY_DL
11:49:02 3 where OR_FK = OR_PK
11:49:12 4 and CR_FK = 'EUR' and extract(year from OR_ORDERDATE) = '2012'
11:49:26 5 and su_fk = 1;

Explained.

11:49:37 SILVER 30/10/2013 11:16 > select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1384926585

--------------------------------------------------------------------------------
| Id |        Operation |          Name | Rows | Bytes | Cost (%CPU)|     Time |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT |               | 2584 |  1201K|   13538 (1)| 00:00:03 |
|* 1 |        HASH JOIN |               | 2584 |  1201K|   13538 (1)| 00:00:03 |
|* 2 | TABLE ACCESS FULL|    T_ORDER_OR | 2580 |   617K|    6907 (1)| 00:00:02 |
|  3 | TABLE ACCESS FULL| T_DELIVERY_DL | 1001K|   220M|    6625 (1)| 00:00:02 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("OR_FK"="OR_PK")
2 - filter("SU_FK"=1 AND "CR_FK"='EUR' AND EXTRACT(YEAR FROM INTERNAL_FUNCTION("OR_ORDERDATE"))=2012)

17 rows selected.

 

STEP 7 : DO NOT DELAY THE PUBLISHING OF STATS FOR THE TABLE


11:52:49 SILVER 30/10/2013 11:16 > begin
11:52:52 2 dbms_stats.set_table_prefs(user,'T_ORDER_OR','PUBLISH','true');
11:53:02 3 end;
11:53:03 4 /

PL/SQL procedure successfully completed.

 

Some dictionary views


11:50:51 SILVER 30/10/2013 11:16 > select view_name from dba_views where view_name like '%PEND%STAT%' order by 1;

VIEW_NAME
------------------------------
ALL_COL_PENDING_STATS
ALL_IND_PENDING_STATS
ALL_TAB_HISTGRM_PENDING_STATS
ALL_TAB_PENDING_STATS
DBA_COL_PENDING_STATS
DBA_IND_PENDING_STATS
DBA_TAB_HISTGRM_PENDING_STATS
DBA_TAB_PENDING_STATS
USER_COL_PENDING_STATS
USER_IND_PENDING_STATS
USER_TAB_HISTGRM_PENDING_STATS
USER_TAB_PENDING_STATS

12 rows selected.