| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Which Tuning Method? System/Resource Tuning?
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 ...Received on Thu Nov 08 2001 - 01:19:48 CST
>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.
 |
 |