Home » RDBMS Server » Performance Tuning » Newbie: Query uses index and runs slow
Newbie: Query uses index and runs slow [message #293609] Mon, 14 January 2008 04:30 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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
we can conclude one of the following:
- 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
Re: Newbie: Query uses index and runs slow [message #293643 is a reply to message #293619] Mon, 14 January 2008 06:23 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!

The index LUID_STR_FK only contains one column.
So, should I drop the current one and create a new index with two columns?

Regards
H
Re: Newbie: Query uses index and runs slow [message #293766 is a reply to message #293619] Mon, 14 January 2008 20:29 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
rleishman wrote on Mon, 14 January 2008 22:04
If you were to rebuild the index to contain both columns, this SQL would be almost instantaneous.


Ross Leishman
Previous Topic: How get full data from table
Next Topic: Query slow
Goto Forum:
  


Current Time: Mon Dec 05 07:07:23 CST 2016

Total time taken to generate the page: 0.38191 seconds