Re: Indicators of potential scaling issues

  • From: Boris Dali <boris_dali@xxxxxxxx>
  • To: kevin.lidh@xxxxxxxxx
  • Date: Sat, 11 Feb 2006 14:13:07 -0500 (EST)


I think you can ask this consultant back the following
question: Given that a job can be done by a single
statement at a cost of (say) 23 LIOs would he rather
break it down into 3 statements of (say) 7-8 LIOs each
working on a non-overlapping ranges and than combine
the answers to get the same result?

Here's one (maybe a little simplistic, but I believe
valid) way to answer this question:

drop table t1;
        ,CONSTRAINT t1_pk PRIMARY KEY (id)
pctfree 90 pctused 1
        ,(CASE WHEN rownum <= 3000 THEN 1 ELSE (CASE WHEN
rownum <= 6000 THEN 2 ELSE 3 END) END)
        ,owner || '.' || object_name 
        ,Rpad( 'x', 100)
FROM    all_objects
WHERE   rownum <= 9000

CREATE INDEX t1_range_idx ON t1(range_id)

exec dbms_stats.gather_table_stats ( user, 't1',

set autotrace on

-- 1) Select the whole bit:
SELECT Count(*) cd FROM t1

-- 2) Break the whole data set into 3 non-overlapping
var p_range_id number
exec :p_range_id := 1
SELECT Count(*) cd FROM t1 WHERE range_id =
exec :p_range_id := 2
SELECT Count(*) cd FROM t1 WHERE range_id =
exec :p_range_id := 3
SELECT Count(*) cd FROM t1 WHERE range_id =

3 statements in the 2nd case result in 3 IRS of only
7-8 LIOs/exec, but combine them to get the same result
as a single SQL (using FFIS) and you get the same
total of 23 LIOs.

So a frequency of execution is a factor. But let's say
you take these too into account - LIOs/exec and a
frequency of execution - is that all needed? After

single SQL: LIOs/exec=23 * Number of Exec=1 --> 23
3 SQLs   : LIOs/exec=7-8 * Number of Exec=3 --> 23

... are these two identical from the scalability (with
respect to a number of concurrent users at least)
point of view? Here's what I get from running Tom
Kyte's test-harness comparing 2 alternatives above
over a 1,000 executions:

1265 cs
1247 cs
Count with Code1=9000, Count with Code2=9000

stat/latch              Single SQL  3 SQLs
----------------------  ---------- -------
STAT...consistent gets      23,007  23,005   

So far so good, right? Roughly the same response time
and the same (combined) number of LIOs to get the same
result. But look what happens with the latches:

LATCH...shared pool          1,006   3,050
LATCH...library cache pin    2,020   6,060
LATCH...library cache        2,022   6,101

So 3 times more latches. And extra latches is not
something that helps a SQL to scale, is it? I'd much
rather see a job done by a single statement in this
case on my system.

Boris Dali.

--- Kevin Lidh <kevin.lidh@xxxxxxxxx> wrote:
> Our customer's Oracle
> consultant said high buffer gets per execution
> (+3000).  I said there has to
> be more that would be an indication of an SQL that
> won't scale when a
> greater load is applied, meaning frequency and
> concurrency.  He asked,
> "Isn't buffer gets the leading indication of a
> scaling issue?"


Find your next car at

Other related posts: