Thank you very much for the example. We do know that one of this
application in production's major problems is latch contention. Since the
developers use a GUI to create the application which auto-generates the SQL,
it's up to us as the DBAs of the test system to find the potentially bad SQL
before it makes it into production. This is an interesting navigation of
politics and ignorance (on my part as well as others). We, in my section,
are trying to get the requirements analysis people to actually acquire and
provide information that can be used in determining criticality as well as
scaling. The exciting thing for me is that in the last three months, I've
gone from being told we can't provide any input to having several meetings
to discuss issues like requirements gathering as well as the topic of this
thread. I think we're moving in the right direction.
On 2/11/06, Boris Dali <boris_dali_at_yahoo.ca> wrote:
>
> 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 Mon Feb 13 2006 - 08:38:46 CST
