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 > > -- > //www.freelists.org/webpage/oracle-l > > > -- Mladen Gogala http://www.mgogala.com -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l