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
![]() |
![]() |