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: Mladen Gogala <mladen_at_wangtrading.com>
Date: Thu, 22 Jan 2004 10:09:26 -0800
Message-ID: <F001.005DDE7B.20040122100926@fatcity.com>


Comments in line.
On 01/22/2004 12:24:26 PM, Tracy Rahmlow wrote:
> 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?

ALTER DATABSE ENABLE OPTIMAL [DW|OLTP] DESIGN; statement will work in Oracle 18e ("E" comes from expensive). Until then, I'd try materialized views. What you are trying to do is to build one table based on selecting records from several others. If you reformulate the previous sentence, you'll get the definition of a MV.

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mladen_at_wangtrading.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 Thu Jan 22 2004 - 12:09:26 CST

Original text of this message

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