FreeLists / oracle-l / RE: Optimizing a query

Amir,

I see that this is on an APPS database. Be careful while turning on PQO.
The WSH tables can be accessed by a _lot_ of users simultaneously, and
assigning a DOP on the table might result in PQ slaves starting up for
all queries on that table. If you are tuning a custom report, then of
course you can use the parallel hint.

I remember the early days of 11i when some 'duh'leveloper@Oracle assign
a DOP of 20 to a large table in the hope of making it 'run faster' - it
killed our (at that time) 8 CPU machine until we drastically scaled down
PARALLEL_MAX_SLAVES . Sun was very happy to sell us a 24 CPU box just
for that query!

John

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Hameed, Amir
Sent: Monday, April 03, 2006 4:48 PM
To: Mladen Gogala
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Optimizing a query

Thanks Mladen! The production server does have 40 CPUs. I am looking at
the optimization from the RAC standpoint. Would PQO-based FTS be better
than regular (buffer-based) FTS in RAC?

-----Original Message-----
From: Mladen Gogala [mailto:gogala@xxxxxxxxxxxxx]
Sent: Monday, April 03, 2006 7:01 PM
To: Hameed, Amir
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Optimizing a query

Amir, you don't have any limiting condition. Table
apps.WSH_DELIVERY_ASSIGNMENT, according to your query, must be read in
its entirety. The most efficient way to do so is the full table scan. Yu
can try with /*+ parallel */ if the hardware is such that you can do
that.

On 04/03/2006 05:21:46 PM, Hameed, Amir wrote:
> Folks,
> I need to optimize the following query:
> 
> SELECT DELIVERY_DETAIL_ID,DELIVERY_ID
> FROM
>  ( SELECT MIN(DELIVERY_DETAIL_ID) OVER (PARTITION BY DELIVERY_ID) 
> MIN_DELIVERY_DET_ID,
>               DELIVERY_DETAIL_ID,DELIVERY_I
>     FROM apps.WSH_DELIVERY_ASSIGNMENTS
>   )
> WHERE MIN_DELIVERY_DET_ID = DELIVERY_DETAIL_ID /
> 
> This table has the following indices:
> 
>                                                          COL  DISTINCT
>           INDEX NAME                  COLUMN NAME        POS    ROWS
> SELECTIVITY
> ------------------------------ ------------------------- ---
> -----------
> -------
> WSH_DELIVERY_ASSIGNMENTS_N1    DELIVERY_ID                 1
630,301
> 8.29
> WSH_DELIVERY_ASSIGNMENTS_N2    PARENT_DELIVERY_ID          1
0
> 0.00
> WSH_DELIVERY_ASSIGNMENTS_N3    DELIVERY_DETAIL_ID          1
7,605,650
> 100.00
> WSH_DELIVERY_ASSIGNMENTS_N4    PARENT_DELIVERY_DETAIL_ID   1
377,456
> 4.96
> WSH_DELIVERY_ASSIGNMENTS_U1    DELIVERY_ASSIGNMENT_ID      1
7,605,650
> 100.00
> 
> What would be the best way to optimize it. It currently does a FTS on 
> this table. Any help will be appreciated.
> Thanks
> Amir
> 
> --
> http://www.freelists.org/webpage/oracle-l
> 
> 
> 

--
Mladen Gogala
http://www.mgogala.com

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l