Re: Which Tuning Method? System/Resource Tuning?

Jonathan,

You are absolutely right! there is nothing to do with directio, originally my SQL1 does not have hint, only SQL2 has hint, without an INDEX hint, SQL1 took 10 seconds on machine A, and it took less than 1 second on machine B, why it has
such big difference, could it be version 8.1.7 has better optimizer than version 8.1.6?

Anyway, when I put an INDEX hint in SQL1, machine A ran just as quick as machine B (within 1 second) when executing SQL1. I think my problem is solved.

I really appreciate you and other friend spent precious time to diagnose my problem.

Thanks again,
Vance.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<1005204445.18757.0.nnrp-02.9e984b29_at_news.demon.co.uk>...
> vanve,
>
> This has NOTHING to do with direct io.
>
> Bear in mind that Oracle's cost figure is an
> estimate - which is not always precise enough
> and varies in derivation from version to version.
>
> Look at your hint - the correct syntax for an
> index hint is
> /*+ index (table_alias index_name) */
>
> The hint you supplied in you note did not include
> the table_alias.
>
> >uses /*+ INDEX(I_PLCY_N) */.
>
> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
> Screen saver or Life saver: http://www.ud.com
> Use spare CPU to assist in cancer research.
>
> Vance Wu wrote in message ...
> >Hi Jonathan,
> >
> >You are right, also Fraser McCallum suggested me earlier to SQL traced
> >the "one particular query" on both machines to see if the optimizer is
> >coming up with the same plan.
> >
> >As the explain plan result shows the execution paths on two machines
> >are different; The "one particular query" involves two SQL statements
> >SQL1 AND SQL2, the following is the plan tables for these two SQL
> >statements on machine A and B.
> >
> >Plan Table for SQL1 on machine A:
> >-------------------------------------------------------------------
> >| Operation | Name |Rows|Bytes|Cost|Pstart|Pstop|
> -------------------------------------------------------------------
> >| SELECT STATEMENT | | 1 | 41 | 2 | | |
> >| SORT AGGREGATE | | 1 | 41 | | | |
> >| TABLE ACCESS BY GLOBAL |PLCY | 1 | 41 | 2 | 1 | 1 |
> >| INDEX ROWID | | | | | | |
> >| INDEX RANGE SCAN |I_PLCY_F | 1 | | 1 | | |
> >-------------------------------------------------------------------
> >
> >
> >Plan Table for SQL2 on machine A:
> >----------------------------------------------------------------------
> >| Operation | Name |Rows|Bytes|Cost|Pstart|Pstop|
> ----------------------------------------------------------------------
> >| SELECT STATEMENT | | 1 | 379 | 6 | | |
> >| SORT ORDER BY | | 1 | 379 | 6 | | |
> >| COUNT STOPKEY | | | | | | |
> >| NESTED LOOPS | | 1 | 379 | 3 | | |
> >| TABLE ACCESS BY |PLCY | 1 | 362 | 2 | 1 | 1 |
> >| GLOBAL INDEX ROWID | | | | | | |
> >| INDEX RANGE SCAN |I_PLCY_F | 1 | | 1 | | |
> >| TABLE ACCESS BY |TRANS_FUNC | 82 | 1K| 1 | | |
> >| INDEX ROWID | | | | | | |
> >| INDEX UNIQUE SCAN |PK_TRANS_FUNC | 82 | | | | |
> >----------------------------------------------------------------------
> >
> >#############################################################
> >
> >Plan Table for SQL1 on machine B:
> >--------------------------------------------------------------------
> >| Operation | Name |Rows|Bytes|Cost|Pstart|Pstop|
> --------------------------------------------------------------------
> >| SELECT STATEMENT | | 1 | 42 | 2 | | |
> >| SORT AGGREGATE | | 1 | 42 | | | |
> >| TABLE ACCESS BY GLOBAL |PLCY | 1 | 42 | 2 | 1 | 1 |
> >| INDEX ROWID | | | | | | |
> >| INDEX RANGE SCAN |I_PLCY_N | 1 | | 1 | | |
> >--------------------------------------------------------------------
> >
> >Plan Table for SQL2 on machine B:
> >----------------------------------------------------------------------
> >| Operation | Name |Rows|Bytes|Cost|Pstart|Pstop|
> ----------------------------------------------------------------------
> >| SELECT STATEMENT | | 1 | 268 | 6 | | |
> >| SORT ORDER BY | | 1 | 268 | 6 | | |
> >| COUNT STOPKEY | | | | | | |
> >| NESTED LOOPS | | 1 | 268 | 3 | | |
> >| TABLE ACCESS BY |PLCY | 1 | 251 | 2 | 1 | 1 |
> >| GLOBAL INDEX ROWID | | | | | | |
> >| INDEX RANGE SCAN |I_PLCY_N | 1 | | 1 | | |
> >| TABLE ACCESS BY |TRANS_FUNC | 82| 1K| 1 | | |
> >| INDEX ROWID | | | | | | |
> >| INDEX UNIQUE SCAN |PK_TRANS_FUNC | 82| | | | |
> >----------------------------------------------------------------------
> >
> >As you can see the differences are the "INDEX RANGE SCAN" on machine A
> >is I_PLCY_F instead of I_PLCY_N as machine B, even the hint in SQL2
> >uses /*+ INDEX(I_PLCY_N) */. But the cost in the plan table are
> >relatively low too on both machine A and B. Anyway the result of the
> >query are identical, may be like "Wisdom" said (message 6 in this
> >subject) I do need to mount my filesystems containing datafile using
> >directio, but I afraid to change the SUN box's configuration, any
> >idea?
> >
> >Thanks,
> >Vance.
Received on Thu Nov 08 2001 - 13:18:38 CST