| Newbie: Query uses index and runs slow [message #293609] |
Mon, 14 January 2008 04:30  |
hristo
Messages: 258
Registered: May 2007
|
Senior Member |
|
|
Hi!
I can see that LUID.STORER_ID uses its index. But it still takes so long to run this query. (both are analyzed)
Any ideas for a newbie?
SELECT "LUID"."LUID_STATUS" , "LUID"."TIMESTAMP" , "LUID"."QUANTITY"
FROM
"LUID" WHERE "LUID"."LUID" =:1 and "LUID"."STORER_ID" =:2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 32 0.00 0.00 0 0 0 0
Execute 32 0.03 0.02 0 0 0 0
Fetch 32 6.79 47.91 5576 119949 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 96 6.82 47.94 5576 119949 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 160
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID LUID (cr=3599 pr=500 pw=0 time=8086963 us)
214785 INDEX RANGE SCAN LUID_STR_FK (cr=422 pr=55 pw=0 time=214827 us)(object id 26851)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache lock 4 0.00 0.00
library cache pin 1 0.00 0.00
row cache lock 23 0.00 0.00
SQL*Net message to client 32 0.00 0.00
db file sequential read 5540 0.14 28.50
gc cr grant 2-way 4772 0.01 1.13
gc current block 3-way 810 0.00 0.40
gc current block 2-way 1275 0.01 0.40
gc cr multi block request 15 0.00 0.00
db file scattered read 10 0.01 0.03
latch: cache buffers chains 14 0.00 0.00
gc buffer busy 3851 0.12 9.65
read by other session 322 0.11 1.79
buffer busy waits 1 0.00 0.00
SQL*Net message from client 32 0.00 0.14
db file parallel read 1 0.01 0.01
|
|
|
|
| Re: Newbie: Query uses index and runs slow [message #293613 is a reply to message #293609] |
Mon, 14 January 2008 04:38  |
 |
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
It executes in 1.5 s to do 3750 logical reads including 160 physical one. I don't think this is bad.
But as you do 32 parses for 32 executions, you should investigate why and first if they are hard or soft ones.
What is the number of rows of table, what the columns of index, what the number of distinct values and so on?
You already posted many questions, you should know you have to post these informations.
Regards
Michel
[Updated on: Mon, 14 January 2008 04:39] Report message to a moderator |
|
|
|
| Re: Newbie: Query uses index and runs slow [message #293619 is a reply to message #293613] |
Mon, 14 January 2008 05:04 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
You can see from the trace that 214785 were read from the index but NONE satisfied the query.
Since your query has only 2 WHERE predicates:
WHERE "LUID"."LUID" =:1
and "LUID"."STORER_ID" =:2
- Either Index LUID_STR_FK contains only a single column, or
- Index LUID_STR_FK contains BOTH columns, but you are binding the wrong datatype (casting problem).
Based on the name of the index, I infer that it contains only one column: STORER_ID.
The problem is that there are 214785 rows that match your STORER_ID, and NONE of them match your LUID.
If you were to rebuild the index to contain both columns, theis SQL would be almost instantaneous.
Ross Leishman
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
