Home » RDBMS Server » Performance Tuning » Help- slow running sql
Help- slow running sql [message #295893] Wed, 23 January 2008 17:22 Go to next message
Messages: 70
Registered: November 2006

Following is the slow running SQL statement in our system.
it is taking many seconds to execute.
However it picks correct index.

select MIN ( dtdue )
FROM com_pol_due
WHERE strpolnbr = :b2
AND dtdue >= '01-mar-2008'
AND NVL ( nisrvpassed, 0 ) = 0
AND NVL ( dtotdueamnt, 0 )
- NVL ( dtotpaidamnt, 0 )
- NVL ( dtotwaivedamnt, 0 )
- NVL ( dtotadjustamnt, 0 ) > 0

The cardinality of the index is good.

Since the sql is picking correct index I believe there will not be use of gathering stats of table or index

Also index is composite index on 5 columns out of which first column is Strpolnbr and third is Dtdue.

the index stats updated and values are as following
num rows 65890654
distinct keys 18624247
leaf blocks 574195
clustering factor 47786333

also table data is
NUM ROES 66710368
BLOCKS 1484926

Can anybody please suggest on this?

Thanks and Regards,

Re: Help- slow running sql [message #295899 is a reply to message #295893] Wed, 23 January 2008 20:55 Go to previous messageGo to next message
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
  1. How many rows have strpolnbr = :b2
  2. How many of those rows have dtdue >= '01-mar-2008'
  3. What does the SQL return if you select COUNT(*) intead of MIN(dtdue)?
You've been around long enough now to know that you should format your SQL with CODE tags - make sure you do next time.

Ross Leishman
Re: Help- slow running sql [message #296433 is a reply to message #295893] Sun, 27 January 2008 03:10 Go to previous messageGo to next message
Messages: 611
Registered: July 2006
Senior Member
What is the "correct index" you are writing about?
What columns does it contain and what is the order of these columns?

Re: Help- slow running sql [message #296443 is a reply to message #296433] Sun, 27 January 2008 08:05 Go to previous message
Messages: 12
Registered: March 2007
Location: Boston, MA
Junior Member
Also, it helps to post something like "explain plan" ? Smile


[Updated on: Sun, 27 January 2008 09:21] by Moderator

Report message to a moderator

Previous Topic: Rollback and trace/tkprof
Next Topic: Measurements
Goto Forum:

Current Time: Mon Oct 24 13:59:47 CDT 2016

Total time taken to generate the page: 0.05042 seconds