Re: Indicators of potential scaling issues

From: Boris Dali <boris_dali_at_yahoo.ca>
Date: Sat, 11 Feb 2006 14:13:07 -0500 (EST)
Message-ID: <20060211191307.65599.qmail@web32813.mail.mud.yahoo.com>

Kevin,

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;
CREATE TABLE t1 (

	 id
	,range_id
	,data
	,padding
	,CONSTRAINT t1_pk PRIMARY KEY (id)

)
nologging
pctfree 90 pctused 1
AS
SELECT

	 rownum
	,(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', cascade=>true)

set autotrace on

• 1) Select the whole bit: SELECT Count(*) cd FROM t1
  /
  
• 2) Break the whole data set into 3 non-overlapping ranges: var p_range_id number exec :p_range_id := 1 SELECT Count(*) cd FROM t1 WHERE range_id = :p_range_id
  /
  exec :p_range_id := 2 SELECT Count(*) cd FROM t1 WHERE range_id = :p_range_id
  /
  exec :p_range_id := 3 SELECT Count(*) cd FROM t1 WHERE range_id = :p_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 all...

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.

Thanks,
Boris Dali.

• Kevin Lidh <kevin.lidh_at_gmail.com> 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 http://autos.yahoo.ca

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Feb 11 2006 - 13:13:07 CST