10053 Parser

Last time i wrote about SYS_DL_CURSOR hint to find out if i can make use of it, but i realized that i rely many times on trace files, mainly 10053 tracefile, so i decided to write a simple parser (in Python) to help me. It’s very simple and for now i will not share source code with you until i have a “good” and readeable version of the code 🙂

 

10053Parser has 2 features for now:

1 – Hints

2 – Explain plans 

 

Feature #1 – Hints

 

10053Parser will (eventually) parse contents to find out how many DML/DDL statements were executed and which hints were used on statements showing the output in a good/fashion way:

 
[oracle@localhost trace]$ ./trace_10053.py --hints testSID_ora_14131_MESSI.trc
Report Hints for [testSID_ora_14131_MESSI.trc] ...
Hint | Used | Error | Level | SQL FULL TEXT
----------------------------------------------------------------------------------------------

N/A | N/A | N/A | N/A | create table t1_abc as select 1 as N1 from dual
SYS_DL_CURSOR () | 0 | 0 | 1 | select /*+ full(a) SYS_DL_CURSOR */ N1 from t1_abc a
FULL ("A") | 1 | 0 | 3 | select /*+ full(a) SYS_DL_CURSOR */ N1 from t1_abc a
INDEX ("A") | 1 | 0 | 3 | select /*+ index(a) */ N1 from t1_abc a

 

 

As you can see, output is easy to read and you have all information regarding hints, even if the statements uses multiple hints.

 

 

Feature #2 – Explain Plans

 

Instead of trying to remember explain plans for every statement, 10053Parser allows you to output the list of explain plans and corresponding statement:

 

[oracle@localhost trace]$ ./trace_10053.py --explain testSID_ora_14131_MESSI.trc
Report Explain for [testSID_ora_14131_MESSI.trc] ...
SQL: create table t1_abc as select 1 as N1 from dual

------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------+-----------------------------------+
| 0 | CREATE TABLE STATEMENT | | | | 3 | |
| 1 | LOAD AS SELECT | | | | | |
| 2 | FAST DUAL | | 1 | | 2 | 00:00:01 |
------------------------------------------+-----------------------------------+

SQL: select /*+ full(a) SYS_DL_CURSOR */ N1 from t1_abc a

-------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 2 | |
| 1 | TABLE ACCESS FULL | T1_ABC | 1 | 13 | 2 | 00:00:01 |
-------------------------------------+-----------------------------------+

SQL: select /*+ index(a) */ N1 from t1_abc a

-------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 2 | |
| 1 | TABLE ACCESS FULL | T1_ABC | 1 | 13 | 2 | 00:00:01 |
-------------------------------------+-----------------------------------+

 

As soon i have the courage i will release source code 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s