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...