How to generate Trace & TKProf analysis ?

Autotrace :

- Go to sqlplus and login as scott/tiger.

Issue the following command at the sqlplus prompt :

sql> set autotrace on

set autotrace on; -- Results, Explain Plan, Statistics

set autotrace traceonly explain; -- Explain Plan

set autotrace traceonly explain statistics; -- Explain Plan, Statistics

Now if you run the following query you will see the subsequent output from trace.

sql> select max(sal) from emp;

MAX(SAL)
----------
5000


Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 56 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

Statistics
----------------------------------------------------------
232 recursive calls
0 db block gets
49 consistent gets
0 physical reads
0 redo size
411 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

TKPROF

However, TKPROF provides more information than trace. To get the tkprof information, follow these steps :

a)

exec dbms_monitor.session_trace_enable( waits=>true );

OR
sql> alter session set sql_trace=true

sql> alter system set timed_statistics=true;

Log out of sqlplus.

Note : Issues these grants to scott, if you are not able to set the above.

grant alter session to scott;

grant alter system to scott;

b) cd \oracle\product\10.2.0\admin\[Oracle SID]\udump

Note : Replace with your SID from tnsnames.ora.

c)In Linux :

$ ls -t | head -1

The ouput of this command would be a file with an extension .trc.

Eg : test_ora_2996.trc

d) Now, tkprof the trace file into a temporary text file so that you can see.

$ tkprof test_ora_2996.trc tmp.txt explain=scott/tiger

e) $ vi tmp.txt

Look for the tkprof output for the sql you have fired.

ps : To quit from vi, just issue :q

In the text file, look for : select max(sal) from emp

select max(sal)
from
emp


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 7 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 (SCOTT)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=61 us)
14 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=54 us)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 SORT (AGGREGATE)
14 TABLE ACCESS MODE: ANALYZED (FULL) OF 'EMP' (TABLE)

f) If you don't want the tmp.txt anymore,

$ rm tmp.txt

Note : You need to use the 10046 trace event to get waits in the trace file.

sql> alter session set events '10046 trace name context forever, level 12'

instead of sql_trace=true;

That's it.

References :

Auto Trace

Explain Plan

TKProf Explanation

Oracle Analytics / Analytic Function: How to avoid “referencing the table twice in queries having aggregate functions”.

Database : Oracle 10g
Schema : Scott/Tiger
Table : emp

Analytic Functions syntax :
Function(arg1,..., argn) OVER ( [PARTITION BY <...>] [ORDER BY <....>] [] )

We come across this very often in our development world.

We need to apply an aggregate function on a column and then get another column based on that aggregate.

Let me give an example.

Say "We need to find out the Names of those employees who earn the max salary for each department".

Immediately, we jump in and write a query similar to this:

select *
from emp
where sal in (select max(e.sal) sal from emp e group by e.deptno);

Even though the table is referenced twice, this query runs very fast on the "emp" table, coz the table is very small.

But, what if your table is having millions and millions of records?

So, now the questions is "how to avoid referencing the table twice?”

Once again Analytic functions rock and roll !!

Here we go:

select * from (
select e.* ,max(sal) over (partition by deptno) max_sal
from emp e)e
where sal = max_sal;

Long Live "Tom Kyte".

Good Luck,
r-a-v-i

Oracle 10g : Connect by prior

Database : Oracle 10g
Schema : Scott/Tiger

If you have a hiearchial table, the following generic query presents the data in a hierarchical form.

column graph format a10;

select rpad( '*', 2*level, '*' ) graph, level, t.*
from t
connect by prior p_node = c_node;

p_node = parent node
c_node = child node

If you want to get the hierarchy of a particular node, use "starts with".

Eg :

column graph format a10
select rpad( '*', 2*level, '*' ) graph, level, t.*
from t
start with c_node = '&node_number'
connect by prior p_node = c_node;

Let's take a look at the standard emp table on scott/tiger schema.

To see the full hierarchy in the table (employee -> Manager relationship), fire the following query :

select rpad( '*', 2*level, '*' ) graph, level, e.*
from emp e
connect by prior e.mgr = e.empno
order by deptno;

Let's start with an employee in a department and see what happens.

select rpad( '*', 2*level, '*' ) graph, level, e.*
from emp e
start with e.empno = 7934
connect by prior e.mgr = e.empno
order by deptno;

Long live "Tom kyte".

Good Luck,
r-a-v-i

Using "case ....end" in order by

Schema : scott/tiger
Version : Oracle 10g

Say your manager stops by and asks you to produce a result like this, from the standard "emp" table :

DEPTNO ENAME JOB
10 CLARK MANAGER
10 MILLER CLERK
10 KING PRESIDENT
20 JONES MANAGER
20 SCOTT ANALYST
20 FORD ANALYST
20 SMITH CLERK
20 ADAMS CLERK
30 BLAKE MANAGER
30 JAMES CLERK
30 WARD SALESMAN
30 ALLEN SALESMAN
30 TURNER SALESMAN
30 MARTIN SALESMAN

So, if you notice, for each department the manager records shows up first and then the rest of the records in the department are sorted by Job.

So, how do we get this result ?

Once again, Analytics rock and roll !!

Here we go :

select e.deptno, e.ename, e.job
from scott.emp e
order by deptno,
case
when job = 'MANAGER' then
0
else
1
end;

This is how this query works :

Looking at the JOB column, there are jobs before and after 'MANAGER'. Eg : 'ANALYST' comes before 'MANAGER' and 'SALAESMAN' comes after the 'MANAGER'.
So, even if order by "asc" or "desc" we won't achieve the result. The magic is using "case ...end" in order by.
The statement : "case when job='MANAGER' then 0 else 1 end" says : if the job is 'MANAGER' then order by it otherwise, order by the job column.
So, within each partition(or group) the 'MANAGER' records becomes the first record.

Long live "Tom Kyte".

Good Luck !!
r-a-v-i