| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Physics of the FILTER operation within SQL_PLANE.
It is very cool!
Life not so easy and it is mean we will not stay without work ;)
As you pointed: we speaking about “trouble-shooting” of existing
production.
At the moment I am trying to figure out the best method for Developers, for system, which will be in production after some time. What will be your recommendation for developer? What is the best meted to figure out the best execution plan (SQL) for some business activity? As we know LIO not the best indicator.
Jurijs
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
Sent by: oracle-l-bounce_at_freelists.org
30.06.2004 11:42
Please respond to oracle-l
To: <oracle-l_at_freelists.org>
cc:
Subject: Re: Physics of the FILTER operation within
SQL_PLANE.
Your observation is absolutely true.
You don't even have to use two separate queries to demonstrate the point, all you have to do is change the sql*plus arraysize on a simple
select non-indexed_column
from table
where primary_key between X and Y
If you check v$sql, you will see that the LIO count goes up as the arraysize goes down.
Identifying statements with high LIO counts is a necessary, but not sufficient, tuning exercise.
Identifying statements with high PIO counts is a necessary, but not sufficient, tuning exercise.
Identifying statements with high CPU usage is a necessary, but not sufficient, tuning exercise.
Identifying statements that do a lot of sorting is a necessary, but not sufficient, tuning exercise.
Identifying statements with a high-latch impact is a necessary is another necessary, but not sufficient, tuning exercise in a system with a high degree of concurrency.
I could go on - but I'd be sure to miss a few.
It's usually relatively easy to spot the big hitters, whatever you do, and work out how to fix them. But the point does come where the ONLY possible way to do trouble-shooting is to breathe down the neck of someone who has a complaint, and watch what the system does as they go through the task that giving them grief. i.e. Cary's 10046 approach.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st
One important conceptual personal conclusion I got from this investigation:
vvvvvvvvvvvvvvvv
BUFFERs gotten (LIO) executing query. ^^^^^^^^^^^^^^^^Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
 |
 |