| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Oracle 8i server vs Oracle 8i EE
Perhaps Jonathan Lewis' book talk about it? I'm not sure, and my people keep
stealing it from me, so I can't check. The book is listed under --- books! ---
on our website along with Gaja's.
Here's some documentation about 10053. I've also attached a sample trace file resulting from setting this event...
Article-ID: <Note:21161.1> Alias: EVENT:10053 Circulation: ** Available to Customers on OCIS ** Folder: server.Rdbms.Internals Topic: ** Events Modified-Date: 27-FEB-1996 14:15:10 Document-Type: FAQ Attachments: 1 See <Note:21161.1.AttachList>
Error: ORA 10053
Text: dump optimizer stats (kke and kko)
Explanation:
This is NOT an error but is a special EVENT code.
It should *NOT* be used unless explicitly requested by RD support.
It is not advisable to use in releases prior to 7.0.16
(but then it is not advisable to use CBO in any release before
7.1.3)
Event 10053 gives cost information for the different paths
in a query - it produces a LOT of output.
Usage:
alter session set events '10053 trace name context forever';
NB1: Remember to get the object_ids for the indexes:
select i.table_name, o.object_name , o.object_id
from user_objects o,
user_indexes i
where o.object_type= 'INDEX'
and i.table_name in ('<table_name>')
and i.index_name = o.object_name;
NB2:
The Cost information is only produced when the statement is PARSED
so if you have already run the statement then it is already in the
shared pool and does not need parsing, therefore no output!
It may also be necessary to set SQL_TRACE to true on some versions
or no output is produced.
With no parsing the output will be along the lines of:
QUERY
select * from dual
rather than the output in the example below.
Workaround:
Modify the statement so that it reparses by capitalising etc. e.g.
Select * from dual;
as opposed to
select * from dual;
As a last resort you could:
alter system flush shared_pool;
but this is not such a good idea on a production system!
Example output:
QUERY
select * from dual
BASE STATISTICAL INFORMATION
-- Table stats
Table: DUAL
Alias: DUAL
Cardinality: 593.00
Number of Blocks: 1.00
Table Scan Cost: 1.00
Avg Row Length: 3.00
-- Column stats
Table: DUAL
Column: DUMMY
Column #: 1.00
NDV: 18.00
High: 0.00
Low: 0.00
-- Index stats
Table: DUAL
Cardinality
Table: DUAL
Original Cdn: 593
Computed Cdn: 593
OPTIMIZER STATISTICS AND COMPUTATIONS
Join order[1]:DUAL
Alias: EVENT:10053
Circulation: ** Available to Customers on OCIS **
Folder: server.Rdbms.Internals
Topic: ** Events
Modified-Date: 27-FEB-1996 14:15:10
Document-Type: FAQ
Attachments: 1 See <Note:21161.1.AttachList>
Error: ORA 10053
Text: dump optimizer stats (kke and kko)
Explanation:
This is NOT an error but is a special EVENT code.
It should *NOT* be used unless explicitly requested by RD support.
It is not advisable to use in releases prior to 7.0.16
(but then it is not advisable to use CBO in any release before
7.1.3)
Event 10053 gives cost information for the different paths
in a query - it produces a LOT of output.
Usage:
alter session set events '10053 trace name context forever';
NB1: Remember to get the object_ids for the indexes:
select i.table_name, o.object_name , o.object_id
from user_objects o,
user_indexes i
where o.object_type= 'INDEX'
and i.table_name in ('<table_name>')
and i.index_name = o.object_name;
NB2:
The Cost information is only produced when the statement is PARSED
so if you have already run the statement then it is already in the
shared pool and does not need parsing, therefore no output!
It may also be necessary to set SQL_TRACE to true on some versions
or no output is produced.
With no parsing the output will be along the lines of:
QUERY
select * from dual
rather than the output in the example below.
Workaround:
Modify the statement so that it reparses by capitalising etc. e.g.
Select * from dual;
as opposed to
select * from dual;
As a last resort you could:
alter system flush shared_pool;
but this is not such a good idea on a production system!
Example output:
QUERY
select * from dual
BASE STATISTICAL INFORMATION
-- Table stats
Table: DUAL
Alias: DUAL
Cardinality: 593.00
Number of Blocks: 1.00
Table Scan Cost: 1.00
Avg Row Length: 3.00
-- Column stats
Table: DUAL
Column: DUMMY
Column #: 1.00
NDV: 18.00
High: 0.00
Low: 0.00
-- Index stats
Table: DUAL
Cardinality
Table: DUAL
Original Cdn: 593
Computed Cdn: 593
OPTIMIZER STATISTICS AND COMPUTATIONS
Join order[1]:DUAL
===========================================================
Greg Moore wrote:
> > A trace event which will show you what the cost-based optimizer considered
> > before choosing a certain strategy. Way cool, but very internal in its
> format
>
> Do you know of a good source for learning generally about setting events and
> in particular, event 10053? It must be in the documentation, but my search
> didn't turn up anything. And perhaps there is a book or paper that does a
> good job with this topic?
>
> - Greg
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Greg Moore
> INET: sqlgreg_at_pacbell.net
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
-- Venlig hilsen Mogens Nørgaard Technical Director Miracle A/S, Denmark Web: http://MiracleAS.dk Mobile: +45 2527 7100Received on Thu Jun 14 2001 - 00:27:32 CDT
 |
 |