Home » RDBMS Server » Performance Tuning » Help - Will Reducing cardinality using histogram make query run faster?
Help - Will Reducing cardinality using histogram make query run faster? [message #267105] Wed, 12 September 2007 08:45 Go to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hi,

Please refer following query.


SQL>select  * from cprd
  2  where cprd.nprodver = :nprodver
  3  AND   cprd.strprodcd = :strprodcd
  4  ;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1335 Card=15342 Byte
          s=1334754)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'CPRD' (Cost=
          1335 Card=15342 Bytes=1334754)

   2    1     INDEX (RANGE SCAN) OF 'CPRD_IDX' (NON-UNIQ
          UE) (Cost=60 Card=15342)



select num_rows,blocks from user_tables where table_name='CPRD'
NUM_ROWS	BLOCKS
55923223	971229

select leaf_blocks,distinct_keys,avg_leaf_blocks_per_key,avg_data_blocks_per_key,clustering_factor,num_rows from user_indexes where index_name='CPRD__IDX'
LEAF_BLOCKS	DISTINCT_KEYS	AVG_LEAF_BLOCKS_PER_KEY	AVG_DATA_BLOCKS_PER_KEY	CLUSTERING_FACTOR	NUM_ROWS
203848	140	1456	41722	5841106	52737933


select column_name,nullable,num_distinct,num_buckets from user_TAB_COLUMNS where table_name='CPRD' AND COLUMN_NAME IN('STRPRODCD','NPRODVER')
COLUMN_NAME	NULLABLE	NUM_DISTINCT	NUM_BUCKETS
STRPRODCD	N	81	79
NPRODVER	Y	45	44

Computing histograms with size 254 for both index key columns produced following plan

SQL>select  * from  cprd
  2  where cprd.nprodver = :nprodver
  3  AND   cprd.strprodcd = :strprodcd;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=252 Card=7744 Bytes=
          681472)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'CPRD' (Cost=
          252 Card=7744 Bytes=681472)

   2    1     INDEX (RANGE SCAN) OF 'CPRD_IDX' (NON-UNIQ
          UE) (Cost=32 Card=7744)


My question is that why it is accesing 15k rows in the first plan?and after computing histograms if those are reducing down to 7k will it really make the query faster, since access path hasn't change only the cardinality?

Thanks and Regards,
Pratap
Re: Help - Will Reducing cardinality using histogram make query run faster? [message #267107 is a reply to message #267105] Wed, 12 September 2007 08:58 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Apologies..didn't mention version

It is Oracle 9i R2 on Linux

Regards,
Pratap
Re: Help - Will Reducing cardinality using histogram make query run faster? [message #267121 is a reply to message #267105] Wed, 12 September 2007 09:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I wouldn't anticipate any change in the performance - the only thinig that adding histograms has done is to give the CBO a more accurate estimate of the number of rows it will be returning.

As the result set hasn't changed, and the execution plan hasn't changed, you're fetching the same number of rows in the same manner, so it should take the same length of time.
Re: Help - Will Reducing cardinality using histogram make query run faster? [message #267193 is a reply to message #267105] Wed, 12 September 2007 12:37 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hi,

Thanks for the quick reply

So unless until the access path in execution plan does not changes and only cardinality changes, setting , regathering statistics has no meaning. Right?

Thanks and Regards,
Pratap

Re: Help - Will Reducing cardinality using histogram make query run faster? [message #268048 is a reply to message #267193] Mon, 17 September 2007 05:48 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Not quite - if the new statistics tell the CBO that a different access path would be quicker then it will use a new plan for the query, and the performance will change.

What I'm saying is that if you gather additional information (such as histograms) and the only things that change in the execution plan after that are the cardinalities, then the prerformance of the query will not change.
Previous Topic: Urgent help needed in tuning the query
Next Topic: Processing lots of data
Goto Forum:
  


Current Time: Thu Dec 08 18:45:08 CST 2016

Total time taken to generate the page: 0.08801 seconds