Tracy,
Take a look at the thing calling this 43,814
times. Can this query be used as an inline view for the thing using this query’s
result set? If so, then you’ll eliminate 87,629 database calls.
As Tom Kyte says, “Tune the
QUESTION, not the query.”
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tracy
Rahmlow
Sent: Thursday, January 22, 2004
11:24 AM
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."
******************************************************************************
==============================================================================
