Performance tuning 1

[aravindk]

Hi all,

I have two tables using simple inner join. For example,

SELECT SUM(ACTUAL_AM) FROM SALES_SUMMARY SS, CALENDAR_TABLE CT WHERE SS.MONTH_KEY = CT.CALENDAR_KEY AND CT.YEARMONTH = 200612

I have an index on the sales_summary table on the month_key column. However, when i see the explain plan it seem to be going for a full table scan on the sales_summary table. How can I avoid this fts? It doesnt work even if I add the index as an hint.

Pl. help.

Arvind.

 

[SantaMufasa]

Arvind,

Connected as the owner of the SALES_SUMMARY table, can you please post the results to this query:

select last_analyzed from user_tables
where table_name = 'SALES_SUMMARY'

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[aravindk]

LAST_ANALYZED

2/7/2007 11:24:24 AM

I ran the analyzer few times today :-( hoping that'd help.

 

[SantaMufasa]

Sounds like a job for "Oracle Tech Support/Metalink"

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[aravindk]

Well, thats my job ;-)

 

[carp]

What is the cardinality of your index? There is a threshold for the optimizer (it used to be 7%, but I think it might be 3% now)past which it assumed better efficiency with a full table scan. So if "CT.YEARMONTH = 200612" represents a higher percentage of the index entries, then you will probably get a FTS.
Try this:

SELECT (v1.entries/v2.total_rows)*100
FROM 
(SELECT count(*) entries
   FROM SALES_SUMMARY SS, CALENDAR_TABLE CT 
  WHERE SS.MONTH_KEY = CT.CALENDAR_KEY 
    AND CT.YEARMONTH = 200612) v1,
 (SELECT count(*) total_rows FROM sales_summary) v2;

 

[aravindk]

Here is the actual query:
select sum(mtd_written_prm_am) /*+ index(t5 covsumm_monthsk), index(t1 idx_tdate_year_month_ct)*/
from "MISP"."TCOVERAGES_SUMMARY" T5,
"MISP"."TDATE" T1
where (T5."MONTH_SK" = T1."DATE_SK")
and (T1."YEAR_MONTH_CT" = 200312)

In this the TCOVERAGES_SUMMARY has nearly 12 million rows and
this filter should return only 19K rows, which is about .1%.
I would expect the optimizer uses a index scan, but it goes for a full table scan.

For troubleshooting purposes I am forcing the sql to use hint, but to no avail.

 

[aravindk]

I tried this in another model environment. When I first tried the same query, the explain plan used the index as expected. I did not use any hints. Now I looked at the stats for the table and realized that the table was never analyzed. Now I went ahead and analyzed the table and retried the query, but now it doest use the index anymore, the optimizer chose the full table scan. Any clue what is going on?

 

[carp]

Yes - now that the optimizer has statistics, it has determined that a full table scan is more efficient. Please see my previous post; I suspect the answer lies therein.

 

[aravindk]

A) How can a FTS be more efficient when it returns only .1% of the table?
B) When it used the index (before analyze) it ran the query in 200 msecs and when it did a FTS (after analyze) it took 13 secs for returning the same output.

So obviously FTS was not more efficient than using the index. My question is what other parameter could influence the optimizer's choice other that the size of the data and indexes?

 

[SantaMufasa]

I ran the analyzer few times today hoping that'd help.

Now I looked at the stats for the table and realized that the table was never analyzed.

So you analyzed statistics a few times on a table that was not the one you were querying?


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[carp]

The optimizer assumes that at some point, it is more efficient to retrieve just the data blocks from the table instead of data blocks from the index followed by data blocks from the table. And remember, all of this is based on rules of thumb - which can sometimes yield the wrong answer. Is a FTS less efficient in your specific case? Apparently so. But you are the one who is observing the activities and results, so unless you have left something out, here is what you have told us:

1. Prior to analyzing the table, the query used the index. If no statistics existed prior to this, then the RBO was being used, which would probably account for the use of the index.
2. After analyzing the table, new statistics were generated and the optimizer quit using the index. So apparently some threshold was crossed whereby the optimizer concluded the index would actually slow things down. The fact that it even ignores your hint would indicate how strongly it feels about that index!

Given the performance drop, you might want to get rid of the statistics and check the results. If they go back to 200ms, then I would include a comment on the table indicating that statistics should not be gathered on it (assuming this is the only query that involves this table, or all queries involving this table suffer degradation with statistics present.).

 

[SantaMufasa]

Very, very well stated, Dave (Carp)...Excellent advice. Hava

!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[aravindk]

So apparently some threshold was crossed whereby the optimizer concluded the index would actually slow things down."

I am trying to find out what that threshold is and adjust it so that the optimizer helps my query rather than hurt it.

By the way, the I added a use_nl hint to the query and this time it picked the index correctly and returned the results in 113 msecs! Now my next quest is to find out why it works with a hint and what index can I create to avoid using the hint.

I dont want to skip the analyze part since that is being one of our largest tables.

 

[aravindk]

Santa,

"I tried this in another model environment." - thats what I said in the beginning of my prior posting. Pl. go back and read.

Btw, Thanks for your valuable input.

 

[carp]

I am trying to find out what that threshold is and adjust it so that the optimizer helps my query rather than hurt it.

I'm afraid you may be getting into areas where Oracle will be very stingy with information, since it might give insight to proprietary (classified) information. However, there are going to be variables like cardinality/selectivity, and possible distribution of values throughout the table (e.g, even though the rows retrieved represent only 1% of the rows in the table, they are spread across 40% of the blocks in the table).
I think the Server Concepts Manual would be a good place to look for clues. If that doesn't work, then you might try Metalink.

 

[carp]

What you are seeing might also be due to the structure of the index itself. Is it a large index? Has it been reorganized lately? If it has become relatively large compared to the amount of data being tracked, it could be that reorganizing the index and then reanalyzing would compact the index to the point where the optimizer would select its usage again.

 

[carp]

Here we go! On Metalink:
Subject: Diagnosing Why a Query is Not Using an Index
Doc ID: Note:67522.1

 

[aravindk]

I will try reorganizing the index and try the query again. Also thanks for forwarding me the metalink document. I appreciate it.