Oradebug

Jika kita ingin menganalisa performance dan eksekusi plan dari SQL atau PL/SQL yang kita jalankan kita bisa menggunakan ORADEBUG utility untuk mendebug statement SQL yang kita jalankan .

SQL> ORADEBUG SETMYPID
Statement processed.
SQL> ORADEBUG UNLIMIT
Statement processed.
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 8
Statement processed.
SQL> SELECT ... /* jalankan SQL staement yang ingin dianalisa . . . tunggu beberapa menit */
SQL> ORADEBUG TRACEFILE_NAME   /* Perintah ini untuk mengetahui nama tracfile yang di generate oleh ORADEBUG */
/u01/app/admin/ORCL/udmp/ORCL_ora_24953.trc
#Disable trace ORADEBUG
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF
Statement processed.

#Setelah itu kita bisa format file trace dengan menggunakan utility tkprof
#Note : Untuk menggunakan oradebug harus login sebagai sys atau user yang punya role sysdba

contoh diatas hanyalah salah satu contoh fungsi dari utility oradebug
untuk melihat comand-comand apa saya yang bisa di pakai bisa di lihat di helpnya sbb :
SQL> oradebug help
HELP [command] Describe one or all commands
SETMYPID Debug current process
SETOSPID <ospid> Set OS pid of process to debug
SETORAPID <orapid> ['force'] Set Oracle pid of process to debug
DUMP <dump_name> <lvl> [addr] Invoke named dump DUMPSGA [bytes]
Dump fixed SGA
DUMPLIST Print a list of available dumps
EVENT <text> Set trace event in process
SESSION_EVENT <text> Set trace event in session
DUMPVAR <p|s|uga> <name> [level] Print/dump a fixed PGA/SGA/UGA variableSETVAR
<p|s|uga> <name> <value> Modify a fixed PGA/SGA/UGA variable
PEEK <addr> <len> [level] Print/Dump memory
POKE <addr> <len> <value> Modify memory
WAKEUP <orapid> Wake up Oracle process
SUSPEND Suspend execution
RESUME Resume execution
FLUSH Flush pending writes to trace file
CLOSE_TRACE Close trace file
TRACEFILE_NAME Get name of trace file
LKDEBUG Invoke global enqueue service debugger
NSDBX Invoke CGS name-service debugger
-G <Inst-List | def | all> Parallel oradebug command prefix
-R <Inst-List | def | all> Parallel oradebug prefix (return outputSETINST
<instance# .. | all> Set instance list in double quotes
SGATOFILE <SGA dump dir> Dump SGA to file; dirname in double
quotesDMPCOWSGA <SGA dump dir> Dump & map SGA as COW; dirname in double
quotes
MAPCOWSGA <SGA dump dir> Map SGA as COW; dirname in double quotes
HANGANALYZE [level] [syslevel] Analyze system hang
FFBEGIN Flash Freeze the Instance
FFDEREGISTER FF deregister instance from cluster
FFTERMINST Call exit and terminate instance
FFRESUMEINST Resume the flash frozen instance
FFSTATUS Flash freeze status of instance
SKDSTTPCS <ifname> <ofname> Helps translate PCs to names
WATCH <address> <len> <self|exist|all|target> Watch a region of memoryDELETE
<local|global|target> watchpoint <id> Delete a watchpoint
SHOW <local|global|target> watchpoints Show watchpoints
CORE Dump core without crashing process
IPC Dump ipc information
UNLIMIT Unlimit the size of the trace file
PROCSTAT Dump process statistics
CALL <func> [arg1] ... [argn] Invoke function with arguments
SQL>

Referensi : Oradebug Documentation