Home » RDBMS Server » Performance Tuning » Enable 10053 Trace information (Oracle 10g, Win XP)
Enable 10053 Trace information [message #412812] Sun, 12 July 2009 08:37 Go to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

I read that 10053 event collects internal CBO calculations and decisions for a query.

How do we enable/activate it ?

I used,

alter session set events '10053 trace name context forever,level 1'


Could you please let me know where do I get information about 10053 (With example ofcourse).

Where do I find the Trace Information ? Please brief me.

Regards,
Ashoka BL
Re: Enable 10053 Trace information [message #412815 is a reply to message #412812] Sun, 12 July 2009 08:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.lmgtfy.com/?q=oracle+10053

Regards
Michel
Re: Enable 10053 Trace information [message #412816 is a reply to message #412812] Sun, 12 July 2009 08:43 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

I did Googled and got some information,Which was not informative ( I haven't searched too much !!)

I thought posting here would be a help
Re: Enable 10053 Trace information [message #412818 is a reply to message #412816] Sun, 12 July 2009 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can buy Jonathan Lewis' book.

Regards
Michel
Re: Enable 10053 Trace information [message #412819 is a reply to message #412816] Sun, 12 July 2009 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Metalink
note 225598.1, "How to Obtain Tracing of Optimizer Computations (EVENT 10053)"
note 338137.1, "CASE STUDY: Analyzing 10053 Trace Files"
and so on

Regards
Michel
Re: Enable 10053 Trace information [message #412820 is a reply to message #412816] Sun, 12 July 2009 08:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Wolfgang Breitling, A Look Under the Hood of CBO: the Event 10053

Regards
Michel

[Updated on: Sun, 12 July 2009 08:53]

Report message to a moderator

Re: Enable 10053 Trace information [message #412822 is a reply to message #412812] Sun, 12 July 2009 10:11 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

I have one Indexing Doubt (sounds very basic..But i am really curious)

I have a table,

SQL> create table tt1(col1 number,col2 number);

Table created.


Inserted data,

SQL> declare
  2  i number;
  3  begin
  4  for i in 1..1000000
  5  Loop
  6  insert into tt1 values(dbms_random.value,dbms_random.value);
   9  end loop;
 10  end;
/


Created Index on Col1 and Col2

Now If execute a following SQL Statement with Col1 in Order by Clause the index is not used,

SQL> explain plan for select col1 from tt1 order by col1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   975K|    12M|  7768 |
|   1 |  SORT ORDER BY     |      |   975K|    12M|  7768 |
|   2 |   TABLE ACCESS FULL| TT1  |   975K|    12M|  1449 |
-----------------------------------------------------------




Is it the thing that the index will be used only when we use the indexed column in WHERE Clause ??

IF used in Order By Clause the index is used...

SQL> explain plan for select col2 from tt1 where col1=1233455;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

----------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |    26 |     2 |
|   1 |  INDEX RANGE SCAN| I_TT1 |     1 |    26 |     2 |
----------------------------------------------------------



I know it sounds basic, But explain me please.

Re: Enable 10053 Trace information [message #412823 is a reply to message #412822] Sun, 12 July 2009 10:33 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now that you know it, activate a 10053 trace then you will see why the optimizer chooses this plan.

Regards
Michel
Previous Topic: Redo Size doesn't increase more than 260MB
Next Topic: To Tune or To use Hints (merged)
Goto Forum:
  


Current Time: Sat Dec 10 14:59:10 CST 2016

Total time taken to generate the page: 0.09862 seconds