Home » RDBMS Server » Performance Tuning » Slow query - Please help.
Slow query - Please help. [message #291087] Thu, 03 January 2008 00:04 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi Experts,

While executing the following query takes me 10 minutes to execute.
 SELECT a.id  
 FROM test1 a, test2 b 
 WHERE a.database_id = b.id 
 AND  b.client_id=-1 
 AND b.id<10137 and rownum=1;


But when i execute same query with count(1). It dispays count(1) = 1 within a second.

 SELECT count(1)
 FROM test1 a, test2 b 
 WHERE a.database_id = b.id 
 AND  b.client_id=-1 
 AND b.id<10137 and rownum=1;


Please tell me where i can change so that i will get faster output.

Thanks in advance
Re: Slow query - Please help. [message #291089 is a reply to message #291087] Thu, 03 January 2008 00:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>FROM test1 a, test2 b
why "test2 b" when test2 contributes NOTHING to the SELECT clause?
Remove it & subordinate into WHERE clause.
Re: Slow query - Please help. [message #291095 is a reply to message #291087] Thu, 03 January 2008 00:26 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Thanks for your reply.

Do you mean the query should be in the following way?
Please suggest me.

SELECT a.id 
FROM  test1 a 
WHERE a.database_id IN (SELECT b.id 
	  		FROM   test2 b 
			WHERE b.client_id=-1 
			AND b.id<10137) 
AND ROWNUM = 1;


Thanks.

[Updated on: Thu, 03 January 2008 00:26]

Report message to a moderator

Re: Slow query - Please help. [message #291098 is a reply to message #291087] Thu, 03 January 2008 00:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
How does the new SQL statement perform?
Re: Slow query - Please help. [message #291100 is a reply to message #291098] Thu, 03 January 2008 00:40 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
It takes more time.

Now query executes in 15 minutes.
The solution doesn't works effectively.

Please suggest me if you have any other idea.

Thanks in advance.
Re: Slow query - Please help. [message #291101 is a reply to message #291087] Thu, 03 January 2008 00:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Are there indexes on a.database_id , b.id , & b.client_id & current statistics on each?

[Updated on: Thu, 03 January 2008 00:43] by Moderator

Report message to a moderator

Re: Slow query - Please help. [message #291103 is a reply to message #291101] Thu, 03 January 2008 00:47 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Yes, every column has an endex.

Thats why i confused why it takes so much time. Sad

Thanks.

[Updated on: Thu, 03 January 2008 00:49]

Report message to a moderator

Re: Slow query - Please help. [message #291104 is a reply to message #291087] Thu, 03 January 2008 00:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
current statistics on each?

as ALWAYS to know what is happening
ALTER SESSION SET SQL_TRACE=TRUE & process results with TKPROF to see where time is being spent.
Re: Slow query - Please help. [message #291114 is a reply to message #291104] Thu, 03 January 2008 01:16 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Please find the tkprof output.

I executed tkprof for my previous query.
Now it takes me Elapsed: 00:05:50.51 time while executed.

I am having less knowlegde to read the output and pointout the cause of the problem.

Please help to find where is the problem.


SELECT a.id 
FROM
 test1 a, test2 b WHERE a.database_id = b.id AND b.client_id=-1 AND 
  b.id<10137 and ROWNUM = 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.09          0         12          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     23.86     342.19     140356    3193510          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     23.89     342.28     140356    3193522          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  COUNT STOPKEY (cr=3193510 pr=140356 pw=0 time=342192508 us)
      1   HASH JOIN  (cr=3193510 pr=140356 pw=0 time=342192501 us)
     88    VIEW  index$_join$_002 (cr=12 pr=0 pw=0 time=3124 us)
     88     HASH JOIN  (cr=12 pr=0 pw=0 time=2856 us)
   1227      INDEX RANGE SCAN DATABASE_CLIENT_FK_I (cr=8 pr=0 pw=0 time=1268 us)(object id 65747)
   1422      INDEX RANGE SCAN DATABASE_PK (cr=4 pr=0 pw=0 time=1444 us)(object id 65750)
3174831    TABLE ACCESS BY INDEX ROWID test1 (cr=3193498 pr=140356 pw=0 time=368321083 us)
3174831     INDEX RANGE SCAN LEAD_DATABASE_FK_I (cr=1509618 pr=7126 pw=0 time=31788488 us)(object id 65913)




Thanks in advance.
Re: Slow query - Please help. [message #291141 is a reply to message #291087] Thu, 03 January 2008 02:32 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Can anyone help me to solve this issue?
Please suggest me to give better performance of the query.

Thanks in advance.
Re: Slow query - Please help. [message #291185 is a reply to message #291141] Thu, 03 January 2008 05:24 Go to previous messageGo to next message
orafan23
Messages: 13
Registered: December 2005
Junior Member
Hi,
Can you describe the objects.As a first point, I would look at the Optmizer mode,since we are trying to retreive just 1 row It might pay off going with First_rows.

Try forcing FIRST_ROWS and let know.

Would let seniors to comment more.

Regards
Re: Slow query - Please help. [message #291357 is a reply to message #291087] Fri, 04 January 2008 00:02 Go to previous messageGo to next message
panelan
Messages: 9
Registered: December 2006
Location: Seoul, R.O.Korea
Junior Member

Hi,
First time, if you want to use rownum=1, you should use 'order by'
rownum condition without ordering might give you wrong result which you are not desired.
If this query from some kind of WEB page which it shows one row and it will show next row, ordering are not neccessary.
But if you want to get just one desired value, you must use 'order by'.
Maybe current query shows a value what you think because it use an index.
However, you could have differen value sometime because of changed plan.

'first_rows' hint is good recommandation for your query and I also recommand another hint 'ordered' if 'first_rows' hint does not work.
When you use 'ordered' hint, you should change 'from' clause like this 'FROM test2 b, test1 a.

Table 'test2' has static condition so if test2 is leading table, block reads from test1 could be reduced by test1 condition from test2 result values.

If you can show the tkprof file from new query, it will be grateful.

[Updated on: Fri, 04 January 2008 00:04]

Report message to a moderator

Re: Slow query - Please help. [message #291819 is a reply to message #291357] Mon, 07 January 2008 00:03 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you get an explain plan for your COUNT(1) query you will find it performs a NESTED LOOP join to get TEST1 using the LEAD_DATABASE_FK_I index.

With the SELECT a.id, Oracle recognises that the query cannot be satisfied entirely by the LEAD_DATABASE_FK_I index - it needs to go to the table to pick up the ID column. Index-only Nested Loops are pretty efficient over medium volumes, but not when a table lookup is involved, so Oracle reckons (wrongly) that a HASH join will be better.

Unfortunately, the HASH join means that every row satisfying a.database_id<10137 must be scanned from test1. All 3 million of them. This is what makes the query slow.

What you need to do is to encourage Oracle to perform the Nested Loops join that it used for the COUNT(1) query.

First try re-gathering the statistics on both tables with DBMS_STATS.GATHER_TABLE_STATS(). This is not a complex query, and there's no excuse for Oracle choosing a crappy plan.

If that doesn't work, you could try encouraging Oracle NOT to use a Hash join with a hint
SELECT /*+NO_USE_HASH(test1)*/ ...


If that doesn't work, you could try dictating the join order and type:
SELECT /*+LEADING(test2) USE_NL(test1)*/ ...


Ross Leishman
Re: Slow query - Please help. [message #353816 is a reply to message #291095] Wed, 15 October 2008 04:09 Go to previous messageGo to next message
pmnegrete
Messages: 3
Registered: October 2008
Location: Spain
Junior Member
It is allways better to do an EXISTS than an IN. Try it, and check if you have an index on test2 for the columns you are accesing (ID)

SELECT a.id
FROM test1 a
WHERE EXISTS (SELECT 1
FROM test2 b
WHERE b.id = a.database_id
AND b.client_id=-1
AND b.id<10137)
AND ROWNUM = 1;

Re: Slow query - Please help. [message #354081 is a reply to message #353816] Thu, 16 October 2008 06:40 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I reckon even the poorly tuned query would have finished some time in the 10 months since this question was asked.
Re: Slow query - Please help. [message #354202 is a reply to message #291087] Fri, 17 October 2008 00:00 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
try to use /*+ FIRST_ROWS */
hint
Re: Slow query - Please help. [message #354433 is a reply to message #354202] Sat, 18 October 2008 22:07 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The OP didn't respond to that advice when @orafan23 offered it 10 months ago. But you never know, they might now.
Previous Topic: Slow cpu-bound hard parse in one environment, fast in another
Next Topic: ORA-03113 after performance tuning
Goto Forum:
  


Current Time: Mon Dec 09 20:43:40 CST 2024