RE: Sql Tuning Thoughts?

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Fri, 23 Jan 2004 09:14:25 -0800
Message-ID: <F001.005DDFCC.20040123091425@fatcity.com>

Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: 7bit

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."

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Nullius in verba

Upcoming events:
- Performance <http://www.hotsos.com/training/PD101.html> Diagnosis
101: 1/27 Atlanta
- SQL Optimization 101: 2/16 Dallas

Hotsos Symposium 2004 <http://www.hotsos.com/events/symposium/2004> : March 7-10 Dallas
Visit www.hotsos.com for schedule details...

-----Original Message-----
Tracy Rahmlow
Sent: Thursday, January 22, 2004 11:24 AM To: Multiple recipients of list ORACLE-L

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."

------=_NextPart_000_0357_01C3E1A1.DD2EEC50 Content-Type: text/html;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

<html>

	{font-family:Tahoma;
	panose-1:2 11 6 4 3 5 4 4 2 4;}

/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal

	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
p
	{margin-right:0in;
	margin-left:0in;
	font-size:12.0pt;
	font-family:"Times New Roman";}
tt
	{font-family:"Courier New";}
span.EmailStyle19
	{font-family:Arial;
	color:navy;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
	{page:Section1;}

-->
</style>

</head>

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.”

<div>

Cary = Millsap 
Hotsos Enterprises, Ltd. 
<a href=3D"http://www.hotsos.com">http://www.hotsos.com</a> Nullius in = verba 
 
Upcoming events: 
- <a href=3D"http://www.hotsos.com/training/PD101.html">Performance
Diagnosis 101</a>: 1/27 Atlanta 
- SQL Optimization 101: 2/16 Dallas 

<a href=3D"http://www.hotsos.com/events/symposium/2004">Hotsos = Symposium 2004</a>: March 7–10 Dallas
Visit <a href=3D"http://www.hotsos.com">www.hotsos.com</a> for = schedule details...

</div>

-----Original = Message----- 
From: = ml-errors_at_fatcity.com
[mailto:ml-errors_at_fatcity.com] On = Behalf Of Tracy
Rahmlow 
Sent: Thursday, January = 22, 2004
11:24 AM 
To: Multiple recipients = of list
ORACLE-L 
Subject: Sql Tuning = Thoughts?

<tt>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.  </tt> 
<tt>We are
currently on 8.1.7.</tt>

<br>
<br>
<tt><font size=3D2 face=3D"Courier New"><span =
style=3D'font-size:10.0pt'>**********************************************=
*****************************************</span></font></tt>
<br>
<br>

<tt>SELECT
MIN(P.POL_EFF_DATE)   </tt> <tt>FROM</tt> 
<tt> PHXADM.POLICY P  WHERE P.POLICY_NUMBER =3D :b1</tt>

<br>
<br>
<tt><font size=3D2 face=3D"Courier New"><span =

style=3D'font-size:10.0pt'>call  
  count       cpu    elapsed     =  
disk      query    current        rows</tt> 
<tt><span =

style=3D'font-size:10.0pt'>-------
------ &nbsp;-------- ---------- ---------- ---------- ----------
&nbsp;----------</span></font></tt> <br>

<tt>Parse

&nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp;0.00 &nbsp; &nbsp; &nbsp; 0.01 =
&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; =
&nbsp;
&nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; =

0</tt> 
<tt>Execute

&nbsp;43814 &nbsp; &nbsp; &nbsp;1.95 &nbsp; &nbsp; &nbsp; 1.57 &nbsp; =
&nbsp;
&nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; =
&nbsp;
&nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0</span></font></tt> =

<tt>Fetch  
 43814     55.88     599.11     = 408248
    568098          0     =  
43814</tt> 
<tt><span =

style=3D'font-size:10.0pt'>------- ------
&nbsp;-------- ---------- ---------- ---------- ---------- =
&nbsp;----------</span></font></tt>

<tt>total  
 87629     57.83     600.69     = 408248
    568098          0     =  
43814</tt> 
 
<tt>Misses in
library cache during parse: 1</tt> <tt>Optimizer
goal: CHOOSE</tt> 
<tt>Parsing user
id: 547  (RPTADM)   (recursive depth: 1)</tt> =

<br>
<br>
<tt><font size=3D2 face=3D"Courier New"><span =

style=3D'font-size:10.0pt'>Rows  
  Execution Plan</tt> <tt>-------

&nbsp;---------------------------------------------------</span></font></=

tt> 
<tt> 
    0  SELECT STATEMENT   GOAL: = CHOOSE</tt> 
<tt> 
    0   SORT (AGGREGATE)</tt> <tt> 
    0    TABLE ACCESS   GOAL: ANALYZED (BY = INDEX
ROWID) OF 'POLICY'</tt> <tt> 
    0     INDEX   GOAL: ANALYZED (RANGE SCAN) = OF
'POLICY_PK' (UNIQUE)</tt>

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."

*************************************************************************=

*****

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D</span></font></p>

</div>

</body>

</html>

------=_NextPart_000_0357_01C3E1A1.DD2EEC50--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Cary Millsap
  INET: cary.millsap_at_hotsos.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 - 11:14:25 CST