| 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
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)
> > >
> > >
> >
 |
 |