| Enable 10053 Trace information [message #412812] |
Sun, 12 July 2009 08:37  |
|
|
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 #412822 is a reply to message #412812] |
Sun, 12 July 2009 10:11  |
|
|
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
