Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Sql Tuning Thoughts?

RE: Sql Tuning Thoughts?

From: <k.sriramkumar_at_iflexsolutions.com>
Date: Thu, 22 Jan 2004 23:09:24 -0800
Message-ID: <F001.005DDECD.20040122230924@fatcity.com>






Hi Tracy,
 
A few observations
 
1. Can we use a more selective where clause?( Currently we are going for a PK Range Scan. Can we change the where clause to go for a PK Uniq scan?)
2. The Fetch time is very high. I guess the fetch is a single row fetch. We can tune the code for bulk fetch by fetching say 1000 to 5000 rows at a time?
 
Best Regards
 
Sriram Kumar


From: Tracy Rahmlow [mailto:tracy.rahmlow@aexp.com]
Sent: Thursday, January 22, 2004 10:54 PM
To: Multiple recipients of list ORACLE-L
Subject: Sql Tuning Thoughts?


This statement is from a batch program within a pl/sql procedure. (Also, I have many similar ones within the process)  The policy table has approximately 6.2 million rows.  The procedure is to incrementally(daily) build an extract table from multiple tables.  The extract table is then used for reporting purposes. The statement performs well per policy, however it is being executed 43,000+ times.  Is there a design option available to me to reduce the number of executions and be more scaleable?    I am considering the creation of an index to incorporate both the policy_number and the pol_eff_date hopefully eliminating the table access.  

We are currently on 8.1.7.


***************************************************************************************


SELECT MIN(P.POL_EFF_DATE)  
FROM
 PHXADM.POLICY P  WHERE P.POLICY_NUMBER = :b1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute  43814      1.95       1.57          0          0          0           0
Fetch    43814     55.88     599.11     408248     568098          0       43814
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    87629     57.83     600.69     408248     568098          0       43814

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 547  (RPTADM)   (recursive depth: 1)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   SORT (AGGREGATE)
      0    TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'POLICY'
      0     INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'POLICY_PK' (UNIQUE)

American Express made the following
annotations on 01/22/2004 10:24:24 AM
------------------------------------------------------------------------------
******************************************************************************

"This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you."

******************************************************************************


==============================================================================

DISCLAIMER:
This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system.E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted,corrupted,lost,destroyed,arrive late or incomplete or contain viruses.The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <k.sriramkumar_at_iflexsolutions.com
  INET: k.sriramkumar_at_iflexsolutions.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Fri Jan 23 2004 - 01:09:24 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US