Re: SQL tuning

srivenu wrote:

> The following SQL statement is issued from one of my applications.
> SELECT count ( *)
> FROM task
> WHERE ( task.queue_status='N' OR task.queue_status ='C' )
> AND task.task_prompt='N'
> AND task.task_open_ind ='Y'
> AND EXISTS
> ( SELECT 'X'
> FROM work_queue
> WHERE work_queue.employee_number =:x
> AND work_queue.work_queue_id =task.work_queue_id )
>
> The following is the plan for this statement.
>
> COUNT(*)
> ----------
> 0
>
> 1 row selected.
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1348 Card=1 Bytes=10
> )
>
> 1 0 SORT (AGGREGATE)
> 2 1 FILTER
> 3 2 INDEX (FAST FULL SCAN) OF 'IX_TASK_WORK_QUEUE_TASK_OPE
> N' (NON-UNIQUE) (Cost=1348 Card=10698 Bytes=106980)
>
> 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'WORK_QUEUE' (Cost=2
> Card=1 Bytes=16)
>
> 5 4 INDEX (UNIQUE SCAN) OF 'PK_WORK_QUEUE' (UNIQUE) (Cos
> t=1 Card=1)
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 18 db block gets
> 10167 consistent gets
> 9 physical reads
> 0 redo size
> 200 bytes sent via SQL*Net to client
> 317 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 4 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> Now i change the above statement to this
> SELECT count ( *)
> FROM task, work_queue
> WHERE ( task.queue_status='N' OR task.queue_status ='C' )
> AND task.task_prompt='N'
> AND task.task_open_ind ='Y'
> and work_queue.work_queue_id =task.work_queue_id
> and work_queue.employee_number =:x
>
> COUNT(*)
> ----------
> 0
>
> 1 row selected.
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=1 Bytes=26)
> 1 0 SORT (AGGREGATE)
> 2 1 NESTED LOOPS (Cost=27 Card=28911 Bytes=751686)
> 3 2 TABLE ACCESS (FULL) OF 'WORK_QUEUE' (Cost=2 Card=5 Byt
> es=80)
>
> 4 2 INLIST ITERATOR
> 5 4 INDEX (RANGE SCAN) OF 'IX_TASK_WORK_QUEUE_TASK_OPEN'
> (NON-UNIQUE) (Cost=5 Card=213941 Bytes=2139410)
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 4 db block gets
> 46 consistent gets
> 0 physical reads
> 0 redo size
> 200 bytes sent via SQL*Net to client
> 317 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> It has improved a lot.
> Now my problem is i cant change the application.
> Hoc can i make the optimizer to use the second execution plan for the
> first statement ?
> Thanks in advance for any help.
>
> regards
> Srivenu

My version/edition info. but ... I'll bet that you can't.

If others confirm my suspicion ... go to the vendor and see if they will fix it.

Well that and hire you as you are obviously more conscientious than they are.

Daniel Morgan Received on Fri Apr 04 2003 - 10:41:33 CST