OTN Discussion Forums : Explain plans differ as parameter value ...

Randolf Geist

Posts: 1,669
Registered: 07/03/08

Re: Explain plans differ as parameter value changes
Posted: Jun 16, 2009 7:02 AM

in response to: jeneesh

Reply

jeneesh wrote:
I ran as default ( Not an expert in this part)

dbms_stats.gather....('COREADMIN','GACC_DTL_V1');

It takes long. Batch_id will be there in every week days with almost same number of records.

Since you seem to say that you're using a custom statistics generation job, you can control the histogram generation as stated above using an appropriate METHOD_OPT parameter of your DBMS_STATS call.

Thanks Randolf Geist . That will work, I think.

If you are not familiar with the options available with the DBMS_STATS.GATHER_*_STATS calls, you need to ensure that you get it right, in particular which METHOD_OPT to use to get rid of the histogram on BATCH_ID and leave everything else as it is now.

I can't tell you since I don't know what you're using at present to gather the statistics. If you post what you're currently using I can provide suggestions.

May be there is also room for improvement regarding the time it takes to gather the statistics.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/

jeneesh

Posts: 3,457
Registered: 04/03/06

Re: Explain plans differ as parameter value changes
Posted: Jun 16, 2009 7:13 AM

in response to: Randolf Geist

Reply

Randolf Geist wrote:

I can't tell you since I don't know what you're using at present to gather the statistics. If you post what you're currently using I can provide suggestions.

Thanks a lot for your concern.

Currently it is done through

dbms_stats.gather_table_stats('COREADMIN','GACC_DTL_V1',cascade => true)

Every week end.

Randolf Geist

Posts: 1,669
Registered: 07/03/08

Re: Explain plans differ as parameter value changes
Posted: Jun 16, 2009 7:42 AM

in response to: jeneesh

Reply

jeneesh wrote:
Currently it is done through

dbms_stats.gather_table_stats('COREADMIN','GACC_DTL_V1',cascade => true)

Every week end.

Just to repeat that question: Have you disabled the default statistics gathering job?

To run your custom job as it is but remove the histogram on BATCH_ID (this assumes you haven't changed the default METHOD_OPT parameter of "FOR ALL COLUMNS SIZE AUTO" using DBMS_STATS.SET_PARAM):

exec dbms_stats.gather_table_stats('COREADMIN','GACC_DTL_V1',method_opt=>'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 1 BATCH_ID', cascade => true)

You might want to try a different sample size to reduce the run time. What is the SAMPLE_SIZE shown for the table GACC_DTL_V1 in DBA/ALL/USER_TAB_STATISTICS?

Depending on the result, you can explicitly specify a sample size using the "estimate_percent" parameter:

exec dbms_stats.gather_table_stats('COREADMIN','GACC_DTL_V1',method_opt=>'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 1 BATCH_ID', estimate_percent=><your_sample_percentage_goes_here>, cascade => true)

Since these seem to be large objects another option to reduce the run time of the statistics job is to use the parallel option (if you have a suitable hardware and license);

exec dbms_stats.gather_table_stats('COREADMIN','GACC_DTL_V1',method_opt=>'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 1 BATCH_ID', degree=>DBMS_STATS.DEFAULT_DEGREE, cascade => true)

The parallel execution can be combined with the "estimate_percent" option to reduce the sample size.

Footnote: Have you considered partitioning these objects, e.g. by BATCH_ID? This would allow you to take advantage of all sorts of options, like loading by partition exchange, partition pruning at query time and gather statistics only on the newly loaded data.

It introduces a further layer of complexity though, and can make your queries even slower depending on your queries, the statistics management for the partitions and the optimizations of the query optimizer. So it's nothing you can easily implement but I think it might well worth a thought in your particular case.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/

Edited by: Randolf Geist on Jun 16, 2009 4:45 PM

Added parallel option

Added partitioning suggestion

MScallion

Posts: 924
Registered: 01/18/07

Re: Explain plans differ as parameter value changes
Posted: Jun 16, 2009 7:51 AM

in response to: jeneesh

Reply

Sorry MScallion . I didnt understand your point.

As an example, if the txn table joins to the acc table which returns 3 records and the cus table which returns 2 records, the result will contain 6 records (3X2).

Discoverer has fantrap detection which can be enabled or disabled.

WITH gacc_dtl_v1 AS (
   SELECT 10 ID, '150609' batch_id FROM dual UNION ALL
   SELECT 10 ID, '150609' batch_id FROM dual UNION ALL
   SELECT 10 ID, '150609' batch_id FROM dual),
     gcus_dtl_v1 AS (
   SELECT 1 customer_number, '150609' batch_id, '30' target FROM dual UNION ALL
   SELECT 1 customer_number, '150609' batch_id, '30' target FROM dual),
     gtxn_dtl_v1 AS (
   SELECT 1 customer_id, 10 account_number, '150609' batch_id FROM dual)
--
SELECT COUNT(*)
FROM gacc_dtl_v1 acc, gcus_dtl_v1 cus, gtxn_dtl_v1 txn
WHERE txn.customer_id = cus.customer_number(+)
AND txn.batch_id = cus.batch_id(+)
AND txn.account_number = acc.id
AND acc.batch_id = '150609'
AND cus.batch_id(+) = '150609'
AND txn.batch_id = '150609'
AND cus.target IN ('30');
 
  COUNT(*)
----------
         6

Randolf Geist

Posts: 1,669
Registered: 07/03/08

Re: Explain plans differ as parameter value changes
Posted: Jun 16, 2009 8:06 AM

in response to: MScallion

Reply

MScallion wrote:
As an example, if the txn table joins to the acc table which returns 3 records and the cus table which returns 2 records, the result will contain 6 records (3X2).

Discoverer has fantrap detection which can be enabled or disabled.

WITH gacc_dtl_v1 AS (
   SELECT 10 ID, '150609' batch_id FROM dual UNION ALL
   SELECT 10 ID, '150609' batch_id FROM dual UNION ALL
   SELECT 10 ID, '150609' batch_id FROM dual),
     gcus_dtl_v1 AS (
   SELECT 1 customer_number, '150609' batch_id, '30' target FROM dual UNION ALL
   SELECT 1 customer_number, '150609' batch_id, '30' target FROM dual),
     gtxn_dtl_v1 AS (
   SELECT 1 customer_id, 10 account_number, '150609' batch_id FROM dual)
--
SELECT COUNT(*)
FROM gacc_dtl_v1 acc, gcus_dtl_v1 cus, gtxn_dtl_v1 txn
WHERE txn.customer_id = cus.customer_number(+)
AND txn.batch_id = cus.batch_id(+)
AND txn.account_number = acc.id
AND acc.batch_id = '150609'
AND cus.batch_id(+) = '150609'
AND txn.batch_id = '150609'
AND cus.target IN ('30');
 
  COUNT(*)
----------
         6

But only in your made-up example which violates primary key rules on the join columns, i.e. you don't want to have two customers with the same customer number and three accounts with the same account number.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/

coskan

Posts: 169
Registered: 03/13/07

Re: Explain plans differ as parameter value changes
Posted: Jun 16, 2009 8:42 AM

in response to: Randolf Geist

Reply

You have a frequency histogram on the column BATCH_ID that misses at least 2 values according to your index statistics (235 buckets vs. 237 distinct keys).

If the value you're using in the query is missing then this might be the explanation for the bad cardinality estimate (since you're on pre-10.2.0.4. In 10.2.0.4 this behaviour changes).

Randolf can you please tell a bit about the change in 10.2.0.4

Thanks

Randolf Geist

Posts: 1,669
Registered: 07/03/08

Re: Explain plans differ as parameter value changes
Posted: Jun 16, 2009 8:55 AM

in response to: coskan

Reply

coskan wrote:
Randolf can you please tell a bit about the change in 10.2.0.4

Coskan,

there is an MetaLink document 5483301.8 and corresponding bug "Bug 5483301: Cardinality of 1 when predicate value non-existent in frequency histogram" describing the change.

Also this has already been covered in various posts:

http://jonathanlewis.wordpress.com/2009/04/23/histogram-change/

http://jonathanlewis.wordpress.com/2009/05/28/frequency-histograms/

http://oracle-randolf.blogspot.com/2009/01/correlation-nocorrelation-and-extended.html

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/

Edited by: Randolf Geist on Jun 17, 2009 9:45 AM

Added MetaLink reference