Ora Internals - Performance related scripts

SCRIPTS FOR PERFORMANCE TUNING

dtrace script to trace Oracle processes

This is a dtrace script to trace Oracle processes, especially, background processes. Quite useful in debugging I/O performance issues. Of course, works in Solaris 10 and needs root privileges.

We can also see if LGWR is bumped off the CPU voluntarily or involuntarily with this script.

But, details may be overwhelming for the faint of heart :-)

View trace_syscall_preempt_size.sh

heapdump_freelists.sh

Print freelist information parsing shared pool heapdump

View heapdump_freelists.sh

shared_pool_free_lists_9i.sql

Prints shared pool free lists at subheap level. For 8i and before, refer www.ixora.com.au.

View shared_poo_free_lists_9i.sql.txt

heapdump_dissect.sh

Heapdump level 2 dissection. Aggregation at various levels.

View heapdump_dissect.ksh

library_cache_locks_pins.sql

Useful in debugging library cache locks and pins waits. Accesses x$ tables and so only for single instance.

View library_cache_locks_pins.sql

ashw_top20.sql

Prints wait events and sql_ids using ASH warehouse tables.

View ashw_top20.sql

ash_top20.sql

Prints statistics about top sessions, top sql_ids, top events etc for the past N minutes. As an added advantage, also prints concurrent manager details.

View ash_top20.sql

ash_ses_top20.sql

Prints detail about top sql_id, top events etc in the past N minutes for a specific session. Works in RAC too.

View ash_ses_top20.sql

ash_lgwr.sql

Prints wait details for a specific named background process. In this example, LGWR is used. Also, prints count of waits.

View ash_lgwr.sql

awr_dbcpu.sql

Prints average DB CPU usage, an approximation of load average using AWR tables. As granular as AWR snaps though. Useful to see if there is any DB CPU usage changes at macro level.

View awr_dbcpu.sql

1 2 next