| Slow query - Please help. [message #291087] |
Thu, 03 January 2008 00:04  |
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 #291095 is a reply to message #291087] |
Thu, 03 January 2008 00:26  |
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 #291100 is a reply to message #291098] |
Thu, 03 January 2008 00:40 |
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 #291114 is a reply to message #291104] |
Thu, 03 January 2008 01:16 |
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 #291185 is a reply to message #291141] |
Thu, 03 January 2008 05:24 |
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 |
|
|
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 |
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 |
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;
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
