| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: How to speed up this query
May this has to do with the order by clause. I believe the optimizer figures
that it has to do order by in anycase, so it may as well as do it first and
save the index lookups.
You may want to try Optimizer hint /*+ FIRST_ROWS */ so it knows that you only want a few rows - like this
SELECT /*+ FIRST_ROWS */
Ashish
"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
news:8dOc8.25996$UT6.53489_at_rwcrnsc52.ops.asp.att.net...
> 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)
> > > > > >
> > > > > >
> > > > >
> > >
>
 |
 |