Home » SQL & PL/SQL » SQL & PL/SQL » Response of the Select statement is extreme slow!!
icon5.gif  Response of the Select statement is extreme slow!! [message #186413] Mon, 07 August 2006 20:38 Go to next message
rebeccaz
Messages: 18
Registered: August 2005
Junior Member
I have the following select query below, how can I fine tune it such that the response is faster?
It seems to hang for quite sometime when I try to query million records and I found out that the line that hangs it is highlighted in red but how can I fine tune that?

SELECT FM.F_ID AS F_ID,
FM.F_REF AS F_REF,
FM.F_ST AS F_ST,
FM.A_S_ID AS A_S_ID,
FM.B_S_ID AS B_S_ID,
FC.A_P_ID AS A_P_ID,
FM.B_P_ID AS B_P_ID,
FC.A_F_ID AS A_F_ID,
FC.C_REF AS C_REF ,
FC.C_ID AS C_ID
FROM FM, FC
WHERE FM.F_ID = FC.F_ID
AND ROWNUM < 2
AND FM.F_ST = 'A'
AND FC.C_REF IS NOT NULL
AND FC.C_ST = 'S'
and FM.F_T_ID = 'COM'
and FM.A_S_ID like 'ABC'
AND FC.A_P_ID > 0
AND FC.A_P_ID NOT IN
(select a_p_id
from fc f1,
(select c_id from sr where c_id > 0) s1
where s1.c_id = f1.c_id)
AND (select fc1_st
from fc fc1, fm fm1
where fc1.f_id = fm1.f_id
and fm1.f_t_id = 'PUT'
and fc1.a_p_id = FC.A_P_ID) = 'S'
ORDER BY FC.C_ID
Re: Response of the Select statement is extreme slow!! [message #186414 is a reply to message #186413] Mon, 07 August 2006 21:00 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>fc1.a_p_id = FC.A_P_ID
Possibly be ensuring both of these fields are indexed & have current statistics.
Re: Response of the Select statement is extreme slow!! [message #186426 is a reply to message #186414] Mon, 07 August 2006 23:33 Go to previous messageGo to next message
rebeccaz
Messages: 18
Registered: August 2005
Junior Member
There are the same field in a table and I have checked that it has been indexed. Pardon me as I am not a dba administrator, what do you meant by having current statistics?
Re: Response of the Select statement is extreme slow!! [message #186440 is a reply to message #186426] Tue, 08 August 2006 00:07 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A simple way to gather statistics would be (executing at the SQL*Plus prompt)

SQL> ANALYZE TABLE fm ESTIMATE STATISTICS;
SQL> ANALYZE INDEX fm_index_1 ESTIMATE STATISTICS;
icon7.gif  Re: Response of the Select statement is extreme slow!! [message #186472 is a reply to message #186440] Tue, 08 August 2006 01:43 Go to previous messageGo to next message
rebeccaz
Messages: 18
Registered: August 2005
Junior Member
Hi,

I have tried to entered that and the output were as follows,

SQL> analyze table fc estimate statistics;

Table analyzed.

SQL> analyze index a_p_id_idx estimate statistics;

Index analyzed.

SQL>

Pls advise on the next step. Thanks!
Re: Response of the Select statement is extreme slow!! [message #186490 is a reply to message #186472] Tue, 08 August 2006 03:09 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Now run the query and see whether it is faster than before or not (measure unit could be elapsed time, or - even better - compare explain plans).
icon8.gif  Re: Response of the Select statement is extreme slow!! [message #186516 is a reply to message #186490] Tue, 08 August 2006 05:15 Go to previous messageGo to next message
rebeccaz
Messages: 18
Registered: August 2005
Junior Member
Hi,

I have tried and the improvement was slightly millisec for hundred of record but when it comes to million of records, the response is still very slow.
Re: Response of the Select statement is extreme slow!! [message #186518 is a reply to message #186516] Tue, 08 August 2006 05:28 Go to previous message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, so what does explain plan say?
Previous Topic: alter table drop column
Next Topic: Getting error while compiling the procedure
Goto Forum:
  


Current Time: Mon Dec 05 10:51:43 CST 2016

Total time taken to generate the page: 0.12435 seconds