compressing indexes has nothing to do with weather the index is new or not.
It eliminates repeated values so the index takes up less space, hence you
get more keys in a block retrieval thus making indexes more efficient.
Jim
"Andy Rigby" <andy_at_softap.co.uk> wrote in message
news:56c32d01.0202200254.42898cad_at_posting.google.com...
> 1. tried compressing indexes - no improvement (indexes were quite
> newly created anyway)
> 2. OPTIMIZER_INDEX_CHACHING - no obvious improvement
> 3. OPTIMIZER_INDEX_COST_ADJ - tried 50% - slower - tried 200% - slower
>
> I'm at a bit of a dead end now!
>
>
> Andy
>
> "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 Wed Feb 20 2002 - 08:16:36 CST
