Hi Jim, Thanks for your info. Regards, Keld
"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
news:5rac8.80205$fK1.4210984_at_rwcrnsc54...
> Have you tried compressing the indexes?
> alter index blah rebuild compress;
> Also in your init.ora change
> OPTIMIZER_INDEX_CACHING from the default from 0 (meaning the optimizer is
> assuming that 0% of the blocks of the index are cached - thus an index
read
> is as expensive as a table read; unless you don't have much in the
> db_block_buffers, probably not a good assumption) to something higher like
> 100. (%)
> also
> OPTIMIZER_INDEX_COST_ADJ is defaulted to 100 which means that the cost of
> accessing an index is = to cost of accessing a table. Try 50 (meaning it
is
> half as expensive to access an index as a table).
>
> The optimizer in 8.1.6 seems a little pessimistic about the cost of
> accessing indexes vs tables. In reality most of the blocks of the index
are
> probably cached in ram.
>
> I can understand in SQLPlus not using bind variables to do this statement
> tuning etc. But in your application, if performance and scalability are a
> concern, then you need bind variables. I worked with a developer who
didn't
> understand that and on an import process that we had benchmarked would
bring
> the server to its knees. The process too 26 minutes to run and no one
could
> get any work done on the server while his client process ran (CPU usage
was
> 100%). Once we converted him to using bind variables the same exact
process
> , same machine, data everything, took 6 minutes and had peaks of 35% CPU
> usage - average was 5%. Other users could not tell he was running his
> process. Since we had built a nice framework and set of classes for him
it
> only took him maybe an hour to make the "conversion" - well worth is since
> it was a commercial application going out to 1,000's of customers.
>
> Jim
>
>
> "Andy Rigby" <andy_at_softap.co.uk> wrote in message
> news:56c32d01.0202180206.577c31d7_at_posting.google.com...
> > Jim
> >
> > 1. Bind variables.
> > Agreed and I probably will later. However, these timings are bad just
> > executing these statements from the SQLPLUS command prompt.
> >
> > 2. Version is 8.1.6. 'compatible' is set to 8.1.6
> >
> > 3. All tables and indexes fully analyzed.
> >
> > 4. Execution plan for the slower statement is:
> >
> > OPERATN OPTS OPTIMIZE COST
> > ------------------ -------------------- --------------- ----------
> > SELECT STATEMENT CHOOSE 495
> > COUNT STOPKEY
> > VIEW 495
> > SORT ORDER BY STOPKEY 495
> > TABLE ACCESS FULL ANALYZED 44
> >
> >
> > As a further piece of information, I tried simplifying the select
> > statement and discovered that this simpler one takes just as long:
> >
> > SELECT * FROM
> > (
> > SELECT * FROM tbl19 where
> > (ffcode>'RLACKWFJ')
> > ORDER BY ffcode)
> > WHERE ROWNUM<=12
> >
> > The problem is obviously the TABLE ACCESS FULL as shown in the explain
> > plan. Trouble is, I've tried all the index hints I can and the
> > performance still does not improve.
> >
> > Andy
> >
> >
> >
> >
> >
> >
> > "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
> news:<MZ8b8.186$Ot2.67120_at_sccrnsc01>...
> > > For 1 use bind variables.
> > > 2. What version of Oracle?
> > > 3. Have you analyzed the tables and indexes?
> > > 4. For the 2nd query what is the execution plan? (clearly it is using
> more
> > > IO)
> > > Jim
> > >
> > > "Andy Rigby" <andy_at_softap.co.uk> wrote in message
> > > news:56c32d01.0202150444.1d5ae2f_at_posting.google.com...
> > > > I have a table of approx 50,000 rows. My Visual C/ADO app generates
> > > > large numbers of requests of this type - see below (the app requires
> > > > 'pagefulls' of 12 rows so I use one of the standard 'top n' tricks)
:
> > > >
> > > > SELECT *
> > > > FROM
> > > > (SELECT * FROM TBL19 WHERE LRN>130345 ORDER BY LRN)
> > > > WHERE ROWNUM<=12
> > > >
> > > > The timings and I/O for this as reported by TRACE is excellent, i.e:
> > > >
> > > >
> > > > call count cpu elapsed disk query current
> > > > rows
> > > > ------- ------ -------- ---------- ---------- ---------- ----------
> > > > ----------
> > > > Parse 1 0.00 0.00 0 0 0
> > > > 0
> > > > Execute 1 0.00 0.00 0 0 0
> > > > 0
> > > > Fetch 2 0.00 0.00 0 5 0
> > > > 12
> > > > ------- ------ -------- ---------- ---------- ---------- ----------
> > > > ----------
> > > > total 4 0.00 0.00 0 5 0
> > > > 12
> > > >
> > > > Misses in library cache during parse: 1
> > > > Optimizer goal: CHOOSE
> > > > Parsing user id: 25
> > > >
> > > > Rows Row Source Operation
> > > > ------- ---------------------------------------------------
> > > > 12 COUNT STOPKEY
> > > > 12 VIEW
> > > > 12 TABLE ACCESS BY INDEX ROWID TBL19
> > > > 12 INDEX RANGE SCAN (object id 4148)
> > > >
> > > >
> > >
>
> > > ****
> > > >
> > > >
> > > > However, if my app wants to organise its 'pages' by another column,
> > > > the SELECT is more complex, and the timings are not good at all:
> > > >
> > > >
> > > > SELECT *
> > > > FROM
> > > > (SELECT * FROM TBL19 WHERE (ffcode > 'RLACKWFJ') OR (ffcode =
> > > > 'RLACKWFJ' AND (LRN>148124)) ORDER BY ffcode,LRN) WHERE
> > > > ROWNUM<=12
> > > >
> > > >
> > > > call count cpu elapsed disk query current
> > > > rows
> > > > ------- ------ -------- ---------- ---------- ---------- ----------
> > > > ----------
> > > > Parse 1 0.00 0.00 0 0 0
> > > > 0
> > > > Execute 1 0.00 0.00 0 0 0
> > > > 0
> > > > Fetch 2 0.04 0.27 238 449 5
> > > > 12
> > > > ------- ------ -------- ---------- ---------- ---------- ----------
> > > > ----------
> > > > total 4 0.04 0.27 238 449 5
> > > > 12
> > > >
> > > > Misses in library cache during parse: 1
> > > > Optimizer goal: CHOOSE
> > > > Parsing user id: 25
> > > >
> > > > This results in fairly jumpy movement around the screen in my app.
> > > >
> > > > The "OR" is necessary to resolve duplicates on the FFCODE column, so
> > > > effectively the rows are subsorted by LRN within FFCODE.
> > > >
> > > > I have an index on LRN, also one on FFCODE, and (most important, I
> > > > would have thought) one on FFCODE, LRN. I have analysed all tables
and
> > > > indexes. No improvement. Tried making the indexes unique. No
> > > > improvement.
> > > >
> > > > I have tried using index hints to force any of the above 3 indexes
to
> > > > be used, all resulted in slower timings than with the 'choose'
method.
> > > > Tried using the 'first_rows' hint on the inner and/or outer
> > > > SELECT...no improvement.
> > > >
> > > > I have also tried creating a view consisting of the entire table
> > > > ordered by FFCODE,LRN and then selecting ROWNUM<=12 from that, thus
> > > > avoiding the embedded SELECT - that turns out to be no better in
> > > > performance terms.
> > > >
> > > > I know that Oracle has a lot of work to do here, in that it must do
> > > > the inner SELECT, sort the results and then do the outer SELECT. But
I
> > > > can't see why the first SELECT is so much faster.
> > > >
> > > > Can anyone suggest a better method for this type of SQL statement or
> > > > some other tuning tip?
> > > >
> > > > Andy
>
>
Received on Mon Feb 18 2002 - 14:15:33 CST
