Re: Physics of the FILTER operation within SQL

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 30 Jun 2004 09:42:22 +0100
Message-ID: <008f01c45e7e$2cd39310$7102a8c0@Primary>

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_at_alise.lv> To: <oracle-l_at_freelists.org> 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_at_freelists.org 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

Received on Wed Jun 30 2004 - 03:39:10 CDT

Re: Physics of the FILTER operation within SQL_PLANE.