Home » RDBMS Server » Performance Tuning » Performance Issue (Oracle 10g, Windows XP)
Performance Issue [message #513850] Wed, 29 June 2011 12:36 Go to next message
Messages: 98
Registered: November 2010
Firstly I am very sorry for not following the posting guidelines but I am unable to provide the explain plan for the below issue as the same is happening in production database and we dont have access to it create a explain plan Sad. Anyhow, I have tried hard to explain the issue below and request your expertise on the same.

I have a table T which is partitioned using date column. So for each day, the table has around 22k records being entered for each day. total records in the table nearly 191000
There is an unique key composite index on (col1,col2,col3,col4) columns
Now, I have a query as
select col1,col2...,col4 from T t1 where calendar_dt = sysdate
and <some condition>
and ....<some condition>
and exists (select 1
            from T2 t2
             where t1.col1 = t2.col2
              and  t1.col2 = t2.col3
              group by t1.col1,t1.col2 
              having count(1)=1);

Now, this query needs to be tuned as the same is takes around 2 minutes for execution. For the same, i ammended the above to somthing like:

select col1,col2,,.col4 from (
select col1,
       count(t1.col1) over(partition by t1.col1,t1.col2) tot_cnt
from T t1,T2 t2 where t1.calendar_dt = sysdate
and <some condition>
and ....<some condition>
and t1.col1 = t2.col2
and  t1.col2 = t2.col3) where tot_cnt =1;

The ammended query is amazingly fast and gets executed in les than a second. But, below are the confusions that i have:

The trace shows that there for the original query it considers the unique key index on col1 and col2 and for the ammended query the same table undergoes a FULL TABLE SCAN and yet it is faster.
the information is something like:

Partition Range Single T <rows 1> <some bytes> <cost which is higer than the original query>
Full table scan T <rows 1> <some bytes> <cost which is higher than the original query>

Well the above plan I get is from TOAD yb simply selcting the autotrace tab and dont know if we can trust it (I am sure Michel is not going to like it)
Qstn1: Is full table scan refers the partitioned part only so it is faster?
Qstn2: If the original query is prefixed with a FULL hint then it is also faster and retrives the records in a second with the same plan as of below. So, which one should i opt, the ammended query or the original query with the a simple /*+FULL(<table T>) */?

Or finally, all my approach above is not correct Smile
Re: Performance Issue [message #513854 is a reply to message #513850] Wed, 29 June 2011 12:50 Go to previous message
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please refer to URL above & be sure to provide the details requested:
If you can't/won't provide SQL & EXPLAIN PLANS, then I doubt any assistance is possible.
Previous Topic: Local Index vs Global Index on partitioned table
Next Topic: AWR Report network foreground wait events
Goto Forum:

Current Time: Wed Oct 04 15:56:31 CDT 2023