Re: Which Tuning Method? System/Resource Tuning?

From: Vance Wu <vwu_at_anacomp.com>
Date: 7 Nov 2001 15:32:20 -0800
Message-ID: <c3d4638a.0111071532.2309d77@posting.google.com>

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:

| 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:

| 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:

| 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:

| 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.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<1005138270.20289.0.nnrp-08.9e984b29_at_news.demon.co.uk>...

> You only need to look at row fetch counts to
> be confident that the queries are using different
> access paths.  Your issue is a SQL one, not
> a machine/resource one.
> 
> As a starting point use explain plan to check
> the execution paths.
> 
> 
> >NAME                                    Machine A   Machine B
> >-----------------------------------     ---------   ---------
> >table fetch by rowid                    1120840     12
> 
> 
> --
> 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 Friends,
> >
> >I need your help, I have two machines A and B:
> >

Received on Wed Nov 07 2001 - 17:32:20 CST