Need help in tuning a set of query.

From: Kenny Lim <kennylim_at_earthlink.net>
Date: Mon, 27 Mar 2000 06:44:36 GMT
Message-ID: <orDD4.3726$na.242500@newsread1.prod.itd.earthlink.net>

Hi All,

I am just a beginner and would like to know if anyone have any advise as to how can I better tune or rewrite this query in able to yield better performance. (Small case scenario that I am testing on)

Based on the result of the trace file that I had obtained, the table "time_sheets" seems to be
taking the most resources. Is there a way that I can force the optimizer to perform a fast full scan or at least an index scan using the oracle hints instead of a full table access scan defaulted by the optimizer ? If so, may I ask how ? I had tried both the "index_ffs" and "index" hints and so far it does not seem to work. (Sample of my query enclosed at bottom of the page)

(This case scenario was tested in Oracle Enterprise Edition version 8.1.5 )

Any advise would be greatly appreciated and please let me know if you need more
explicit information.

Thanks All in advance and you have a pleasant evening.

Kenny-

SELECT * FROM employees
WHERE emp_seq IN
(SELECT emp_seq FROM time_sheets t

WHERE proj_seq =
(SELECT proj_seq FROM projects

WHERE name = 'Debugger')
AND rpt_date =
(SELECT MAX(effective_date) FROM sal_history s
WHERE s.emp_seq = t.emp_seq))

--Autotrace plan result--

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=1 Bytes=63)    1 0 NESTED LOOPS (Cost=49 Card=1 Bytes=63)

   2    1     VIEW (Cost=47 Card=1 Bytes=13)
   3    2       SORT (UNIQUE) (Cost=47 Card=1 Bytes=19)
   4    3         FILTER
   5    4           TABLE ACCESS (FULL) OF 'TIME_SHEETS' (Cost=45 Card=1

Bytes=19)

   6 4 TABLE ACCESS (FULL) OF 'PROJECTS' (Cost=1 Card=2 Bytes=36)

   7    4           SORT (AGGREGATE)
   8    7             TABLE ACCESS (FULL) OF 'SAL_HISTORY' (Cost=46 Card=2

Bytes=38)

   9 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' (Cost=2 Card=25000

                Bytes=1250000)

  10    9       INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE) (Cost=1 Card=25000)

--Trace file result--

SELECT * FROM employees
WHERE emp_seq IN
(SELECT emp_seq FROM time_sheets t

WHERE proj_seq =
(SELECT proj_seq FROM projects

WHERE name = 'Debugger')
AND rpt_date =
(SELECT MAX(effective_date) FROM sal_history s
WHERE s.emp_seq = t.emp_seq))

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 68.33 69.04 0 500407 6676 3
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 4 68.33 69.04 0 500407 6676 3

Misses in library cache during parse: 0 Optimizer goal: CHOOSE
Parsing user id: 37

Rows Row Source Operation

-------  ---------------------------------------------------
      3  NESTED LOOPS
      4   VIEW
      4    SORT UNIQUE
      3     FILTER
  25010      TABLE ACCESS FULL TIME_SHEETS
      3   TABLE ACCESS BY INDEX ROWID EMPLOYEES
      6    INDEX UNIQUE SCAN (object id 12249)
   3334      SORT AGGREGATE
   1677       TABLE ACCESS FULL SAL_HISTORY
      2      TABLE ACCESS FULL PROJECTS

****************************************************************************

---

--Sample of hints that I was attempting to use to optimize the query.--

select /*+INDEX_FFS(employees emp_seq) */emp_seq from employees where emp_seq IN

    (select /*+INDEX_FFS(time_sheets emp_seq) */emp_seq from time_sheets

        where proj_seq =
        (select /*+INDEX_FFS(projects proj_seq) */proj_seq
        from projects
        where name = 'Debugger'));

Received on Mon Mar 27 2000 - 00:44:36 CST