| Urgent:-SQL taking long time to execute [message #65722] |
Wed, 15 December 2004 00:15  |
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  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
