[oracle-l] Re: Sql Tuning Thoughts?
- From: Stephane Faroult <sfaroult@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 22 Jan 2004 21:21:26 +0100
My guess if that if there is a PL/SQL procedure, it comes from some
perceived complication in the aggregation process which would make using
a view, materialized or not, unpractical.
What worries me most is that this query seems typical of something
called in a loop. When you have large volumes (both in terms of data
searched and of data returned), nested loops are the worst possible way
of accessing your data. Putting this type of query inside a loop leaves
no chance to Oracle ...
HTH,
SF
Justin Cave wrote:
>
> Since you're doing an aggregate function, you may want to investigate
> using materialized views here. Since, I'm assuming, policy effective
> dates aren't something that changes on a minute-to-minute basis, you
> could set up a materialized view that refreshed every night and would
> answer this question in nothing flat.
>
> Justin Cave
>
> At 10:24 AM 1/22/2004, 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? 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."
> >
> > **********************************************************
> > *******************
> >
> >
> > ==============================================================================
Other related posts:
