| 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: BAD PERF. for single row access with unique index
You might like to get an idea of how much analysis Oracle is doing in the parse stage by setting the following event before parsing a brand new statement:
alter session set events '10053 trace name context forever, level 2';
I am surprised that most of your hints had no effect, but the following hint really looks as if it ought to have trimmed the parse time.
>+ using hint /*+ index(message iu_message_1) */
You might also have a check in v$session_wait after running the query to see if there are significant numbers of wait recorded, e.g. for library cache latches.
-- Jonathan Lewis Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk kerbiquet_at_hotmail.com wrote in message <8jpp4n$8rm$1_at_nnrp1.deja.com>...Received on Mon Jul 03 2000 - 00:00:00 CDT
>Again, Thank you very much for your help,
>
>I done those tests on the table MESSAGE (3 million rows, around 10
>columns) for the simple statement (select single row using unique index
>in sqlplus):
>
>1) 144 partitions on the table, only on local index on the table
>(unique index).
>total time ~= parsing time ~= 0.06
>
>2) 144 partitions on the table, five local indexes on the table.
>After each index creation, I analyze the table (compute statistics) and
>I get a longer parsing time up to 1.44 for 5 indexes for the simple
>statement.
>
>3) I drop some partitions, now there are only 90 partitions on the
>table and still the five local indexes.
>I analyze the table (compute statistics) and I get a lower but still
>too long parsing time =~ 0.50 for the simple statement.
>
>Here is the plan with partition detail:
>
>Query Plan
>---------------------------------------------------
>SELECT STATEMENT [CHOOSE] Cost=1
> SORT AGGREGATE
> INDEX UNIQUE SCAN IU_MESSAGE_1 [ANALYZED] Cost=1 Card=1 Bytes=28
>Pt id: 2 Pt start: NUMBER(86) Pt stop: NUMBER(86)
>
>Parsing time are similar if :
>+ optimizer_mode=choose, there are compute statistics on the table and
>indexes
>+ optimizer_mode=rule, there are compute statistics on the table and
>indexes
>+ optimizer_mode=choose, there are NO statistics on the table and
>indexes
>+ using hint /*+ index(message iu_message_1) */
>+ using hint /* ORDERED */
>
>So it looks like long parsing time is not used by CBO to find best plan
>but to access the data dictionary.
>
>Using utlestat.sql I check that GET_MISS = 0 (<< GET_REQS) for
>dictionary cache access and I do not have recursive calls when
>executing the statement:
>SQL>alter system flush shared_pool
>SQL>select * from mesfich
> 2 where sgtqs = 'AG32'
> 3 and drefmes=to_date('16/05/2000','DD/MM/YYYY')
> 4* and nuqmes=900
>
>Statistics
>----------------------------------------------------------
> 310 recursive calls
> 1 db block gets
> 75 consistent gets
> 12 physical reads
> 0 redo size
> 1413 bytes sent via SQL*Net to client
> 425 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 3 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>Parsing time =0.75
>
>I run the statement with a different value for the WHERE clause :
>
>SQL>select * from mesfich
> 2 where sgtqs = 'AG32'
> 3 and drefmes=to_date('16/05/2000','DD/MM/YYYY')
> 4* and nuqmes=908
>
>Statistics
>----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 3 consistent gets
> 0 physical reads
> 0 redo size
> 1414 bytes sent via SQL*Net to client
> 425 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>Parsing time=0.45
>I have no disk/query for parsing in tkprof report
>
>call count cpu elapsed disk query
>current rows
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>Parse 1 0.38 0.44 0 0
>0 0
>Execute 1 0.00 0.01 0 0
>0 0
>Fetch 2 0.00 0.00 0 3
>0 1
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>total 4 0.38 0.45 0 3
>0 1
>
>Misses in library cache during parse: 1
>Optimizer goal: CHOOSE
>Parsing user id: 19 (OPS$CAA)
>
>Rows Execution Plan
>------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 0 TABLE ACCESS GOAL: ANALYZED (BY LOCAL INDEX ROWID) OF
> 'MESFICH'
> 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'IU_MESFICH_1'
>(UNIQUE)
>
>I run the same statement with the same value for the WHERE clause :
>
>SQL>select * from mesfich
> 2 where sgtqs = 'AG32'
> 3 and drefmes=to_date('16/05/2000','DD/MM/YYYY')
> 4* and nuqmes=908
>
>Statistics
>----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 3 consistent gets
> 0 physical reads
> 0 redo size
> 1414 bytes sent via SQL*Net to client
> 425 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>Parsing time~=0
>I have no disk/query for parsing in tkprof report
>
>==> So I decrease the parsing time by dropping partitions to 0.50 for
>this simple statement but for more complex, parsing time is still >
>1.00 and more than 95% of the execution time. An alternative would be
>to change application code in order to use bind variables to limit
>parsing count but this is costly.
>By the way, I can not understand Oracle taking so long time to check
>the data dictionary for this table, 80 partitions, 5 indexes...
>
>
>
> In article <962317124.8857.0.nnrp-10.9e984b29_at_news.demon.co.uk>,
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>>
>> You will note that almost all
>> of the CPU and elapsed time
>> for the MESSAGE access is
>> spent in parsing - Oracle comes
>> to the correct conclusion about
>> how to access the table, but
>> takes a long time getting there.
>>
>> How many partitions in the table,
>> and how fast does the query operate
>> if you repeat it exactly ?
>>
>> It is more helpful, particularly with
>> partitioned tables, to run the statement
>> through EXPLAIN PLAN. You can use
>> utlxpls.sql script to generate an output
>> (or the one on my website) to show the
>> partition selection details which are not
>> visible in the output from tkprof.
>>
>> --
>>
>> Jonathan Lewis
>> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
 |
 |