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@xxxxxxxxxxxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
30.06.2004 11:42
Please respond to oracle-l
 
        To:     <oracle-l@xxxxxxxxxxxxx>
        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

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


----- Original Message ----- 
From: <J.Velikanovs@xxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, June 29, 2004 10:45 AM
Subject: Re: Physics of the FILTER operation within SQL_PLANE.


One important conceptual personal conclusion I got from this 
investigation:
vvvvvvvvvvvvvvvv
We can't compare two SQL executions plans (for one query) efficiency by 
BUFFERs gotten (LIO) executing query.
^^^^^^^^^^^^^^^^



----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: