OraFAQ Forum: Performance Tuning » Urgent:-SQL taking long time to execute

Home » RDBMS Server » Performance Tuning » Urgent:-SQL taking long time to execute

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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.

Report message to a moderator

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

Report message to a moderator

Previous Topic:	So much rows
Next Topic:	Urgent, SQL tuning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 08:02:05 CDT 2025