Re: Doubt on optimizer costs when using date and subquery on dual

On Feb 1, 12:29 pm, "Lucho" <lucio..._at_gmail.com> wrote:
> On Feb 1, 8:54 am, DA Morgan <damor..._at_psoug.org> wrote:
> > I'd suggest posting real code if you want real answers. ;-)
> > --
> > Daniel A. Morgan
> > University of Washington
> > damor..._at_x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Groupwww.psoug.org-Hide quoted text -
>
> > - Show quoted text -
>
> You are right Morgan, here is the real code:
>
> QUERY 1:
> ========
> SELECT /*+ PARALLEL(iw, 4) */
> *
> FROM ods_wo_wmin06_fb iw
> WHERE iw.srce_sys_id = 1121
> AND iw.cpos_time_perd_date = (SELECT MAX(START_DATE)
> start_date
> FROM
> cpos_wmin06_fb_time_perd_mv);
> PLAN_TABLE_OUTPUT
> | Id | Operation | Name |
> Rows | Bytes| Cost | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
> ---------------------------------------------------------------------------­-----------------------------------------------------------
> | 0 | SELECT STATEMENT | |
> 204K| 67M| 5979 | | | | | |
> | 1 | PARTITION RANGE ITERATOR|
> | | | | KEY | KEY | 31,00 | PCWP | |
> | 2 | TABLE ACCESS FULL | ODS_WO_WMIN06_FB |
> 204K| 67M| 5979 | KEY | KEY | 31,00 | P->S | QC (RAND) |
> | 3 | SORT AGGREGATE | |
> 1 | 8 | | | | 31,00 | PCWP | |
> | 4 | TABLE ACCESS FULL | CPOS_WMIN06_FB_TIME_PERD_MV |
> 119 | 952 | 2 | | | 31,00 | PCWP | |
>
> Now compare with this one, where I am providing the date:
>
> QUERY 2
> ========
> SELECT /*+ PARALLEL(iw, 4) */
> *
> FROM ods_wo_wmin06_fb iw
> WHERE iw.srce_sys_id = 1121
> AND iw.cpos_time_perd_date = '26-JAN-2007';
>
> PLAN_TABLE_OUTPUT
> | Id | Operation | Name | Rows | Bytes |
> Cost | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
> ---------------------------------------------------------------------------­--------------------------------------------
> | 0 | SELECT STATEMENT | | 258K| 94M|
> 71 | | | | | |
> | 1 | TABLE ACCESS FULL | ODS_WO_WMIN06_FB | 258K| 94M|
> 71 | 118 | 118 | 32,00 | P->S | QC (RAND) |
>
> Cost in query 1 is 5979, in query 2 is 71. Cost to get the MAX date in
> first query is only 2. Do you have a clue why do I have such a
> difference?
>
> Regards- Hide quoted text -
>
> - Show quoted text -

The answer to your question is a bit different now that you have posted your SQL code. Note that parallel execution automatically decreases the cost of a query, even if the query takes longer to execute when executed in parallel. If memory serves me correctly, parallel execution with a degree parallel of 4 actually decreases the reported cost to 25% of the original cost. That is one source of the problem that you reported.

Ignore parallel for a moment. Your first SQL statement can be thought about like this:
SELECT
  *
FROM
  ODS_WO_WMIN06_FB IW
WHERE
  IW.SRCE_SYS_ID = 1121
  AND IW.CPOS_TIME_PERD_DATE = :BIND_VARIABLE1; And the second one like this:
SELECT
  *
FROM
  ODS_WO_WMIN06_FB IW
WHERE
  IW.SRCE_SYS_ID = 1121
  AND IW.CPOS_TIME_PERD_DATE = '26-JAN-2007'; COLUMN = :BIND_VARIABLE1 is treated as if 5% of the table will be retrieved when the costs are calculated for retrieving the rows from ODS_WO_WMIN06_FB when the execution plan is developed. COLUMN = '26- JAN-2007' typically provides a much more accurate estimated cardinality when the costs are calculated for retrieving the rows from ODS_WO_WMIN06_FB. Now, take this a step further. Assume that the same very specific partition satisifes the first and second queries above. If the SQL statement remains in the shared pool for a year, will the same, very specific partition still satisfy the first and second queries above? Possibly, but then again, possibly not. Do you want an answer from the Oracle database, or do you want the correct answer from the Oracle database?

Parallel queries are best used when a query would normally require several minutes or longer to execute. Using parallel query where not needed can and will cause performance problems. I may not be remembering correctly (and cannot find the source), but I believe that there is an optimization in Oracle that allows the second query above to execute without actually generating additional operating system processes (no additional processes show when ps is executed at a command prompt) - not actually executed in parallel.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Feb 01 2007 - 13:28:18 CST