Keeping statistics current, Can this be done automatically in 10g Options

[rgoldwrx]

Hi all, we have a table that grows intermittently, but can grow significantly. Unfortunately, with the nature of our system I have found that the plans used by the optimzer can be much better when the stats are very accurate, sometimes with a difference of 30 seconds to 20 minutes.

I expect that as the table can grow from 16000 to 32000 rows in one statement, some sort of trigger to recalc stats as a post commit might be the sort of thing that could do it.

Any experiences, ideas?

[dave]

why not have the stats recaclualted by your code at the end of the procedure

[burleson]

Hi,

Experts don't recommend refreshing CBO statistics without a reason, but it looks like you have a good eason:

>> Unfortunately, with the nature of our system I have found that the plans used by the optimzer can be much better when the stats are very accurate, sometimes with a difference of 30 seconds to 20 minutes.

Have you looked at dynamic sampling? Very nice:

http://www.dba-oracle.com/art_dbazine_orac...mpling_hint.htm
http://www.dba-oracle.com/t_sample_table_s...ic_sampling.htm

Me, I like to take a "deep" sample and keep it, only refreshing when I want to change SQL execution plans:

http://www.dba-oracle.com/art_otn_cbo_p2.htm

>> I expect that as the table can grow from 16000 to 32000 rows in one statement, some sort of trigger to recalc stats as a post commit might be the sort of thing that could do it.

Yes, loooik at the automatic refresh using the dbms_stats "auto" option which refreshes after a 10% table row change:

http://www.dba-oracle.com/art_orafaq_cbo_stats.htm

[rgoldwrx]

Thanks for all the advice, really appreciated. I'll have a closer look into a few of these options.

Hi,

Experts don't recommend refreshing CBO statistics without a reason, but it looks like you have a good eason:

>> Unfortunately, with the nature of our system I have found that the plans used by the optimzer can be much better when the stats are very accurate, sometimes with a difference of 30 seconds to 20 minutes.

Have you looked at dynamic sampling? Very nice:

http://www.dba-oracle.com/art_dbazine_orac...mpling_hint.htm
http://www.dba-oracle.com/t_sample_table_s...ic_sampling.htm

Me, I like to take a "deep" sample and keep it, only refreshing when I want to change SQL execution plans:

http://www.dba-oracle.com/art_otn_cbo_p2.htm

>> I expect that as the table can grow from 16000 to 32000 rows in one statement, some sort of trigger to recalc stats as a post commit might be the sort of thing that could do it.

Yes, loooik at the automatic refresh using the dbms_stats "auto" option which refreshes after a 10% table row change:

http://www.dba-oracle.com/art_orafaq_cbo_stats.htm

[rgoldwrx]

This is really becoming a test, I have implemented for the time being a recalc of stats on the 3 offending tables until we can test a database wide alterntives.

but...

the optimiser in 10g seems smarter (perhaps) than first expected, I think now that its not so much how much the stats reflect the actual size, rather than what it has recorded, perhaps even to actual keys..

I say this because on a given query, it insert a number of rows, as mentioned, but this should not really affect the plan in my opinion, but it does. It runs really poorly. This query uses 4 tables, 3 of which get new rows added just before the query runs. I recalced stats on each of the 3 tables, checking the plan after each, and after each recalc the plan changed more.

It almost appears as though the optimiser is looking at actual values in the table to formulate a plan, rather than the number of rows, distribution of indexed data etc...

Does this make any sense?


If this is the case then I'm guessing dynamic sampling will not improve this. It almost appears that I need to recalc stats in between inserting data and running the query. This however is not really an option, as its part of a peoplesoft nvision application where these things are out of my control.


Any ideas?

ps..

I don't beleive this was an issue under oracle 9i
Going to rule based does not give as good of a result as 'updated' stats

[burleson]

Hi,

>> It almost appears as though the optimiser is looking at actual values in the table to formulate a plan, rather than the number of rows, distribution of indexed data etc...

Well, this sounds like dynamic sampling. Do you have it emabled, and maybe you don;t know it?

http://www.dba-oracle.com/art_dbazine_orac...mpling_hint.htm

The optimizer_dynamic_sampling initialization parameter controls the number of blocks read by the dynamic sampling query. The parameter can be set to a value from 0 to 10. In 10g, the default for this parameter is set to “2,” automatically enabling dynamic sampling. Beware that the optimizer_features_enable parameter will turns off dynamic sampling if it is set to a version earlier than 9.2.0.

A value of 0 means dynamic sampling will not be performed. Increasing the value of the parameter results in more aggressive dynamic sampling, in terms of both the type of tables sampled (analyzed or un-analyzed) and the amount of I/O spent on sampling.

[rgoldwrx]

Thanks again for keeping with me on this one.

Changing the optimizer_dynamic_sampling doesn't seem to have had an effect on my example, I've included a few results in case I have missed something on this..

SELECT * FROM v$parameter WHERE NAME LIKE '%opt%'

NUM NAME TYPE VALUE DISPLAY_VALUE ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE
156 filesystemio_options 2 asynch asynch TRUE TRUE IMMEDIATE
710 plsql_optimize_level 3 2 2 TRUE TRUE IMMEDIATE
771 object_cache_optimal_size 3 102400 102400 TRUE TRUE DEFERRED
785 optimizer_features_enable 2 10.1.0.4 10.1.0.4 TRUE TRUE IMMEDIATE
803 optimizer_mode 2 ALL_ROWS ALL_ROWS TRUE TRUE IMMEDIATE
879 optimizer_index_cost_adj 3 100 100 TRUE TRUE IMMEDIATE
880 optimizer_index_caching 3 0 0 TRUE TRUE IMMEDIATE
968 optimizer_dynamic_sampling 3 2 2 TRUE TRUE IMMEDIATE

----






SELECT * FROM v$parameter WHERE NAME = 'optimizer_dynamic_sampling'

-->
NUM NAME TYPE VALUE DISPLAY_VALUE ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE ISINSTANCE_MODIFIABLE ISMODIFIED ISADJUSTED ISDEPRECATED DESCRIPTION UPDATE_COMMENT HASH
968 optimizer_dynamic_sampling 3 2 2 TRUE TRUE IMMEDIATE TRUE MODIFIED FALSE FALSE optimizer dynamic sampling 549852465


SELECT a.ACCOUNT, SUM (a.posted_total_amt)
FROM PS_LEDGER a,
PSTREESELECT15 l2,
PSTREESELECT10 l3,
PSTREESELECT05 l,
PSTREESELECT10 l1
WHERE a.ledger = 'ACTUALS'
AND a.fiscal_year = 2006
AND (a.accounting_period BETWEEN 0 AND 7 OR a.accounting_period = 998)
AND l2.selector_num = 156726
AND a.project_id = l2.range_from_15
AND l3.selector_num = 156727
AND a.ACCOUNT = l3.range_from_10
AND l.selector_num = 156728
AND a.business_unit = l.range_from_05
AND l1.selector_num = 156729
AND a.deptid = l1.range_from_10
AND a.currency_cd = 'AUD'
AND a.statistics_code = ' '
GROUP BY a.ACCOUNT

plan-->

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 11.0110514914415
SORT GROUP BY 1 96 11.0110514914415
TABLE ACCESS BY INDEX ROWID SYSADM.PS_LEDGER 1 51 4.00437685195311
NESTED LOOPS 1 96 10.0110514914415
MERGE JOIN CARTESIAN 1 45 6.00667463948843
MERGE JOIN CARTESIAN 1 31 4.00445941654045
MERGE JOIN CARTESIAN 1 20 3.00327575712469
INDEX RANGE SCAN SYSADM.PSAPSTREESELECT10 1 10 2.00209209770893
BUFFER SORT 1 10 1.00118365941576
INDEX RANGE SCAN SYSADM.PSAPSTREESELECT10 1 10 1.00118365941576
BUFFER SORT 1 11 3.00327575712469
INDEX RANGE SCAN SYSADM.PS_PSTREESELECT05 1 11 1.00118365941576
BUFFER SORT 1 14 5.00549098007267
INDEX RANGE SCAN SYSADM.PSAPSTREESELECT15 1 14 2.00221522294798
INDEX RANGE SCAN SYSADM.PS_LEDGER 1 3.00325403089076


-----

ALTER SESSION SET optimizer_dynamic_sampling=0;

----

no change to the plan

----

analysis of stats compared to actual row count


stats actual
pstreeselect05 343 366
pstreeselect10 17034 17208
pstreeselect15 198534 204954


----

start updating stats on at a time and checking plan changes as a result:

ANALYZE TABLE PSTREESELECT05 COMPUTE STATISTICS

----

new plan ->

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 17.0181389661127
SORT GROUP BY 1 94 17.0181389661127
TABLE ACCESS BY INDEX ROWID SYSADM.PS_LEDGER 1 51 4.00437685195311
NESTED LOOPS 1 94 16.0181389661127
MERGE JOIN CARTESIAN 3 129 6.00725405237808
MERGE JOIN CARTESIAN 1 34 5.00549098007267
MERGE JOIN CARTESIAN 1 20 3.00327575712469
INDEX RANGE SCAN SYSADM.PSAPSTREESELECT10 1 10 2.00209209770893
BUFFER SORT 1 10 1.00118365941576
INDEX RANGE SCAN SYSADM.PSAPSTREESELECT10 1 10 1.00118365941576
BUFFER SORT 1 14 4.00430732065691
INDEX RANGE SCAN SYSADM.PSAPSTREESELECT15 1 14 2.00221522294798
BUFFER SORT 20 180 4.0050388294301
INDEX RANGE SCAN SYSADM.PS_PSTREESELECT05 20 180 1.00176307230541
INDEX RANGE SCAN SYSADM.PS_LEDGER 1 3.00325403089076


---

ANALYZE TABLE PSTREESELECT10 COMPUTE STATISTICS

---

new plan-->

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 121.71777485367
SORT GROUP BY 1 90
CONCATENATION
NESTED LOOPS 1 90 44.3933148876619
NESTED LOOPS 1 82 43.391222789953
NESTED LOOPS 1 74 42.3891306922441
MERGE JOIN CARTESIAN 10 230 4.00488673354657
INDEX RANGE SCAN SYSADM.PSAPSTREESELECT15 1 14 3.00312366124116
BUFFER SORT 20 180 1.00176307230541
INDEX RANGE SCAN SYSADM.PS_PSTREESELECT05 20 180 1.00176307230541
TABLE ACCESS BY INDEX ROWID SYSADM.PS_LEDGER 1 51 4.00533143293938
INDEX RANGE SCAN SYSADM.PSDLEDGER 8 2.00244698810384
INDEX RANGE SCAN SYSADM.PSAPSTREESELECT10 1 8 1.00209209770893
INDEX RANGE SCAN SYSADM.PSAPSTREESELECT10 1 8 1.00209209770893
NESTED LOOPS 1 90 76.3244599660078
NESTED LOOPS 1 82 75.3223678682989
NESTED LOOPS 1 74 74.32027577059
MERGE JOIN CARTESIAN 10 230 4.00488673354657
INDEX RANGE SCAN SYSADM.PSAPSTREESELECT15 1 14 3.00312366124116
BUFFER SORT 20 180 1.00176307230541
INDEX RANGE SCAN SYSADM.PS_PSTREESELECT05 20 180 1.00176307230541
TABLE ACCESS BY INDEX ROWID SYSADM.PS_LEDGER 1 51 7.03153890370434
INDEX RANGE SCAN SYSADM.PSDLEDGER 28 6.030416082642
INDEX RANGE SCAN SYSADM.PSAPSTREESELECT10 1 8 1.00209209770893
INDEX RANGE SCAN SYSADM.PSAPSTREESELECT10 1 8 1.00209209770893


----

ANALYZE TABLE PSTREESELECT15 COMPUTE STATISTICS

----

new plan -->

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 408 223.996126805247
SORT GROUP BY 408 35 K 223.996126805247
HASH JOIN 443 38 K 222.970710229958
HASH JOIN 324 24 K 174.859486460189
INDEX RANGE SCAN SYSADM.PSAPSTREESELECT10 323 2 K 4.01348377229681
HASH JOIN 275 18 K 170.33500108565
INDEX RANGE SCAN SYSADM.PSAPSTREESELECT10 323 2 K 4.01348377229681
HASH JOIN 347 20 K 165.80947276791
INDEX RANGE SCAN SYSADM.PS_PSTREESELECT05 20 180 2.00267151059858
TABLE ACCESS BY INDEX ROWID SYSADM.PS_LEDGER 560 27 K 163.298254917189
INDEX SKIP SCAN SYSADM.PSDLEDGER 560 68.1469619840371
INDEX RANGE SCAN SYSADM.PSAPSTREESELECT15 12 K 149 K 47.4189600876592



Initial Result runs in about 4 minutes
Final result runs in 2 seconds

[SatheeshB]

Hi,
Along with dynamic_sampling can you add the hint dynamic_sampling_est_cdn to your query and see whether it helps.


select /*+dynamic_sampling_est_cdn(your_big_table_name)*/

Regards,
Satheesh Babu.S

[SatheeshB]

Hi,
I question my above comment will help you. I am not able to force sampling in 10g. Something fishy happens when i am testing in 10.2.0.2. Optimizer_dyanamic_sampling is not getting effect if set in session level, however it works when i set it as hint. I don't have pre 10g around to test it.


SQL> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------

ID1 NUMBER(3)
ID2 NUMBER(3)

SQL> select * from t1;

ID1 ID2
---------- ----------
1 33
7 34
7 34
1 33
1 33

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('ARYAA','T1');

PL/SQL procedure successfully completed.


SQL> alter session set optimizer_dynamic_sampling=2;

Session altered.

SQL> set autotrace on;
SQL> select * from t1 where id1=1 and id2=33;

ID1 ID2
---------- ----------
1 33
1 33
1 33


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 6 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("ID1"=1 AND "ID2"=33)


....

SQL> select /*+dynamic_sampling_est_cdn(t1) */ * from t1 where id1=1 and id2=33
;

ID1 ID2
---------- ----------
1 33
1 33
1 33


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 6 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("ID1"=1 AND "ID2"=33)

.......


SQL> select /*+ dynamic_sampling(t1 2) */ * from t1 where id1=1 and id2=33;

ID1 ID2
---------- ----------
1 33
1 33
1 33


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 18 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 3 | 18 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("ID1"=1 AND "ID2"=33)

Note
-----
- dynamic sampling used for this statement


...........

So, set the dynamic_sampling hint in the query and see if it helps.

Regards,
Satheesh Babu.S

[burleson]

Hi Sateesh,

It might be a "bug".

Have you checked Metalink?

http://metalink.oracle.com

[rgoldwrx]

True Sateesh, I also get no change when setting at a session level, but do at a hint level.
In my latest test I have 2 of the same queries one before the analyze and 1 after. Even by trying all
of the dynamic sampling options , ie from 0 to 9, I cannot get the plan the same as to post-stats
plan. It does improve, down to 20 secs, but not the 1 second that I'm after.

post stats:

SELECT a.deptid, l2.tree_node_num, a.ACCOUNT, SUM (a.posted_total_amt)
FROM PS_LEDGER a,
PSTREESELECT10 l1,
PSTREESELECT15 l2,
PSTREESELECT10 l3,
PSTREESELECT05 l
WHERE a.ledger = 'ACTUALS'
AND a.fiscal_year = 2006
AND a.accounting_period BETWEEN 0 AND 998
AND l1.selector_num = 156794
AND a.deptid = l1.range_from_10
AND l2.selector_num = 156795
AND a.project_id = l2.range_from_15
AND l3.selector_num = 156796
AND a.ACCOUNT = l3.range_from_10
AND l.selector_num = 156797
AND a.business_unit = l.range_from_05
AND a.currency_cd = 'AUD'
AND a.statistics_code = ' '
GROUP BY a.deptid, l2.tree_node_num, a.ACCOUNT

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 51 927.998966938032
SORT GROUP BY 51 5 K 927.998966938032
TABLE ACCESS BY INDEX ROWID SYSADM.PSTREESELECT15 2 42 3.00418274688564
NESTED LOOPS 51 5 K 926.997078920846
HASH JOIN 33 2 K 827.85904827362
HASH JOIN 47 3 K 825.354964444102
HASH JOIN 70 4 K 819.824167606074
INDEX RANGE SCAN SYSADM.PSAPSTREESELECT10 3 30 2.0021500389979
TABLE ACCESS BY INDEX ROWID SYSADM.PS_LEDGER 4 K 205 K 817.262055575658
INDEX SKIP SCAN SYSADM.PSDLEDGER 4 K 162.208237987933
INDEX RANGE SCAN SYSADM.PSAPSTREESELECT10 555 5 K 5.02123652534901
INDEX RANGE SCAN SYSADM.PS_PSTREESELECT05 23 253 2.00272945188755
INDEX RANGE SCAN SYSADM.PSAPSTREESELECT15 1 2.00312366124116

--------------

pre stats:

SELECT a.deptid, l2.tree_node_num, a.ACCOUNT, SUM (a.posted_total_amt)
FROM PS_LEDGER a,
PSTREESELECT10 l1,
PSTREESELECT15 l2,
PSTREESELECT10 l3,
PSTREESELECT05 l
WHERE a.ledger = 'ACTUALS'
AND a.fiscal_year = 2006
AND a.accounting_period BETWEEN 0 AND 998
AND l1.selector_num = 156798
AND a.deptid = l1.range_from_10
AND l2.selector_num = 156799
AND a.project_id = l2.range_from_15
AND l3.selector_num = 156800
AND a.ACCOUNT = l3.range_from_10
AND l.selector_num = 156801
AND a.business_unit = l.range_from_05
AND a.currency_cd = 'AUD'
AND a.statistics_code = ' '
GROUP BY a.deptid, l2.tree_node_num, a.ACCOUNT

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 11.0110514899542
SORT GROUP BY 1 103 11.0110514899542
TABLE ACCESS BY INDEX ROWID SYSADM.PS_LEDGER 1 51 4.00437685046573
NESTED LOOPS 1 103 10.0110514899542
MERGE JOIN CARTESIAN 1 52 6.00667463948843
MERGE JOIN CARTESIAN 1 31 4.00445941654045
MERGE JOIN CARTESIAN 1 21 3.00327575712469
INDEX RANGE SCAN SYSADM.PS_PSTREESELECT05 1 11 2.00209209770893
BUFFER SORT 1 10 1.00118365941576
INDEX RANGE SCAN SYSADM.PSAPSTREESELECT10 1 10 1.00118365941576
BUFFER SORT 1 10 3.00327575712469
INDEX RANGE SCAN SYSADM.PSAPSTREESELECT10 1 10 1.00118365941576
BUFFER SORT 1 21 5.00549098007267
INDEX RANGE SCAN SYSADM.PS_PSTREESELECT15 1 21 2.00221522294798
INDEX RANGE SCAN SYSADM.PS_LEDGER 1 3.00325402940339

[rgoldwrx]

Further testing, I am getting inconsistant results with the dynamic sampling. I am unable to achive the results from a hint via oracle ini, session or system changes.


The plan does come up different with the hint, my previous test musn't have been on the alias:

SELECT /*+ dynamic_sampling(l1 1) dynamic_sampling(l3 1) dynamic_sampling(l2 1) dynamic_sampling(1 1) dynamic_sampling(a 1) */
a.deptid, l2.tree_node_num, a.ACCOUNT, SUM (a.posted_total_amt)
FROM PS_LEDGER a,
PSTREESELECT10 l1,
PSTREESELECT15 l2,
PSTREESELECT10 l3,
PSTREESELECT05 l
WHERE a.ledger = 'ACTUALS'
AND a.fiscal_year = 2006
AND a.accounting_period BETWEEN 0 AND 998
AND l1.selector_num = 156798
AND a.deptid = l1.range_from_10
AND l2.selector_num = 156799
AND a.project_id = l2.range_from_15
AND l3.selector_num = 156800
AND a.ACCOUNT = l3.range_from_10
AND l.selector_num = 156801
AND a.business_unit = l.range_from_05
AND a.currency_cd = 'AUD'
AND a.statistics_code = ' '
GROUP BY a.deptid, l2.tree_node_num, a.ACCOUNT

Currently set to 2, so great, lets set it to 1 so that I don't have to use a hint ........

Test 1:
alter session set optimizer_dynamic_sampling=1;

Test plan without the hint, did not do the same plan as above, as though no change


Test 2:
alter system set optimizer_dynamic_sampling=1;

Test plan without the hint, did not do the same plan as above, as though no change

Test 3:
change the setting in the oracle ini file and restart database

Test plan without the hint, did not do the same plan as above, as though no change

[SatheeshB]

"
It does improve, down to 20 secs, but not the 1 second that I'm after.
"
I believe, you won't be able to get 1 sec response time. That's because in 20 secs the sampling overhead gets counted.

Regards,
Satheesh Babu.S

[SatheeshB]

Currently set to 2, so great, lets set it to 1 so that I don't have to use a hint ........

Test 1:
alter session set optimizer_dynamic_sampling=1;

Test plan without the hint, did not do the same plan as above, as though no change
Test 2:
alter system set optimizer_dynamic_sampling=1;

Test plan without the hint, did not do the same plan as above, as though no change

Test 3:
change the setting in the oracle ini file and restart database

Test plan without the hint, did not do the same plan as above, as though no change

Setting alter session/system set optimizer_dynamic_sampling =>4 generates sampling for me.

Regard,
Satheesh Babu.S