Relax Time! and Work... again

I was on vacation at Phu Kradung (wiki). That's a great location...and great time. No Oracle, No Internet... that made me Enjoy...

It's time to work and online again...

I thought... if i want to find what oracle process make more CPU... and trace ...it!

Maybe I should begin with OS process (unix/linux command), check any OS process make more percentage CPU.

$ ps -e -o pcpu,user,pid,args | sort -k 1 | grep oracle | tail

.

.

.

3.2 oracle 32657 oracle[ORACLE_SID](LOCAL=NO)

3.9 oracle 3834 ora_j000_[ORACLE_SID]

15.3 oracle 16463 oracle[ORACLE_SID](LOCAL=NO)

Or

$ top

.

.

.

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

16463 oracle 16 0 7824m 1.6g 1.6g S 34 10.2 1:06.41 oracle

28150 oracle -2 0 7834m 6.6g 6.6g S 9 42.4 3914:51 oracle

I knew os process... and I want to trace it, So sqlplus command with "oradebug" trace 10046 event on OS process can help! (Example: oradebug trace oracle process)

$ sqlplus / as sysdba

SQL> oradebug setospid 16463

SQL> oradebug TRACEFILE_NAME

$ORACLE_BASE/admin/[ORACLE_SID]/udump/[ORACLE_SID]_ora_16463.trcc

SQL> oradebug unlimit

SQL> oradebug event 10046 trace name context forever, level 12

I should find trace file.

$ cd $ORACLE_BASE

$ cd admin/[ORACLE_SID]/udump

$ ls *16463*

[ORACLE_SID]_ora_16463.trc

and then use tkprof command-line .

$ tkprof [ORACLE_SID]_ora_16463.trc /tmp/file.out sys=no

After that I can investigate a problem on /tmp/file.out file, Check SQL statement and resolve...

Example:

$ less /tmp/file.out

.

.

.

SELECT COUNT(*)

FROM

"DTABLE" "A1" WHERE "A1"."A"=:1 AND "A1"."DSTART"<=TO_DATE(:2,

'dd/mm/yyyy hh24:mi:ss') AND "A1"."DSTOP">=TO_DATE(:3,'dd/mm/yyyy

hh24:mi:ss')

call count cpu elapsed disk query current rows

------------- ----------------------------------------------------------

Parse 2 0.00 0.00 0 0 0 0

Execute 2 0.00 0.00 0 0 0 0

Fetch 4 1.85 3.41 1 93080 0 2

------- ------ -------- --------------------------------------------------

total 8 1.86 3.41 1 93080 0 2

.

.

.

Oh! I forgo: disable 10046 event

$ sqlplus / as sysdba

SQL> oradebug setospid 16463

SQL> oradebug event 10046 trace name context off

That's easy to check and trace...

determine hit ratio from tkprof output

when, trace file was executed by tkprof command-line. 

How can find "Hit Ratio" from tkprof output?

Hit Ratio =

Logical Reads - Physical Reads     or       (Query + Current) - Disk 

    ------------------------------                        ------------------------        

           Logical Reads                                      (Query + Current)

1 -  Physical Reads/  Logical Reads  or 1 - Sum(Disk) /(Sum(Query) + Sum(Current))

disk: This indicates the number of blocks read from disk. Generally you want to see blocks being read from the buffer cache rather than disk. 

query : This column is incremented if a buffer is read in Consistent mode. A Consistent mode buffer is one that has been generated to give a consistent read snapshot for a long running transaction. The buffer actually contains this status in its header. 

current: This column is incremented if a buffer found in the buffer cache that is new enough for the current transaction and is in current mode (and it is not a CR buffer). This applies to buffers that have been read in to the cache as well as buffers that already exist in the cache in current mode. 

Look at  =>

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        2      0.00       0.02          0         36          0           0

Execute   2      0.00       0.00       [P]0       [C]0       [D]0        0

Fetch   108      0.00       0.00       [P]0     [C]138       [D]0    1579 

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total      112      0.00       0.03          0        174          0        1579

Hit Ratio = ?

Logical Read = Consistent Get + DB Block Gets

Logical Read = query + current

Logical Read = Sum [C] + Sum [D]

Logical Read =  [0 + 138] + [0 + 0]

Logical Read =  138

Hit Ratio = 1 - (Physical Reads / Logical Reads)

Hit Ratio = 1 - (Sum[P] / Logical Reads)

Hit Ratio = 1 - (0 /  138)

Hit Ratio = 1 = 100%

call     count       cpu    elapsed       disk      query    current       rows

------- ------  -------- ---------- ---------- ---------- ----------  ---------

Parse        2      0.02       0.02          0          0          0            0

Execute      2    239.39    1003.16     [P]274981  [C]3792129     [D]534        242

Fetch            0      0.00       0.00         [P]0                 [C]0          [D]0          0

------- ------  -------- ---------- ---------- ---------- ----------  ---------

total        4    239.41    1003.18     274981    3792129        534        242

Hit Ratio = ?

Logical Read = Consistent Get + DB Block Gets

Logical Read = query + current

Logical Read = Sum [C] + Sum [D]

Logical Read =  [3792129 + 0] + [534 + 0]

Logical Read =  3792663

Hit Ratio = 1 - (Physical Reads / Logical Reads)

Hit Ratio = 1 - (Sum[P] / Logical Reads)

Hit Ratio = 1 - ([0 + 274981] /  3792663)

Hit Ratio = 0.92 = 92%

If hit ratio is lower than 99%,So should check how many extents on each of objects.