Home » RDBMS Server » Performance Tuning » Urgent:-SQL taking long time to execute
Urgent:-SQL taking long time to execute [message #65722] Wed, 15 December 2004 00:15 Go to next message
Milind Deshpande
Messages: 93
Registered: May 2004
Member
Hi Experts,

I have this SQL which is taking long time to execute.

Can anybody give some Tuning suggestions.There is an Index on Added_z field and is being used by the SQL.


select

to_char(t.added_z, 'dd/mm/yyyy HH24'),
count(to_char(t.added_z, 'dd/mm/yyyy HH24'))
from
cus.tsop_transmittal t
where
to_char(t.added_z, 'dd/mm/yyyy') in
(select Added_Date from (select Added_Date,count(Transactions) Tot_Transactions
from
(select to_char(t.added_z, 'dd/mm/yyyy') Added_Date,
RANK() OVER(PARTITION BY to_char(t.added_z, 'dd/mm/yyyy')
ORDER BY
to_char(t.added_z, 'dd/mm/yyyy')) Transactions
from
cus.tsop_transmittal t
where
t.added_z between add_months (sysdate, -24) and sysdate order by t.added_z)
group by
Added_Date
order by
Tot_Transactions DESC) where rownum < 4)
group by
to_char(t.added_z, 'dd/mm/yyyy HH24')

Execution Plan:-

SELECT STATEMENT, GOAL = FIRST_ROWS   4313 235102 3291428  
 SORT GROUP BY   4313 235102 3291428 9462000 
  NESTED LOOPS   3525 235102 3291428  
   VIEW SYS VW_NSO_1 3516 3 21  
    SORT UNIQUE    3 21  
     COUNT STOPKEY       
      VIEW CUS  3516 391837 2742859  
       SORT ORDER BY STOPKEY   3516 391837 7836740  
        SORT GROUP BY   3516 391837 7836740  
         VIEW CUS  1820 391837 7836740  
          SORT ORDER BY   1820 391837 2742859 12608000 
           WINDOW SORT   1820 391837 2742859 12608000 
            FILTER       
             INDEX RANGE SCAN CUS TEMP_TSOP_TRANSMITTAL 2 391837 2742859  1
   INDEX FULL SCAN CUS TEMP_TSOP_TRANSMITTAL 11 78367 548569  

Thanks in Advance

Milind.
Re: Urgent:-SQL taking long time to execute [message #65724 is a reply to message #65722] Wed, 15 December 2004 04:08 Go to previous message
Alan
Messages: 68
Registered: October 1999
Member
Hi
Have you analyzed the tables, indexes for the query?
you are performing a group by, is this doing disk or memory sorts. how big are the tables that your are selecting from.

run the followinfg to get some stats of the query

select c.* from
(select disk_reads,
buffer_gets,
rows_processed,
executions,
first_load_time,
sql_text
from v$sqlarea
order by
disk_reads/decode(rows_processed,null,1,0,1,rows_processed) desc ) c
where rownum < 11
/

if your query is one of the top 10 then post it back here

cheers

Alan
Previous Topic: So much rows
Next Topic: Urgent, SQL tuning
Goto Forum:
  


Current Time: Fri Mar 29 07:29:22 CDT 2024