These sqlplus scripts are for Oracle Database performance optimization & troubleshooting. Most of the scripts should be runnable directly from SQL Developer too.
To get an idea of how I set up my client environment and use some of my main Oracle-related scripts, check out the videos here:
Also you can follow me in Twitter as I'll post any noteworthy updates there:
| NAME | DESCRIPTION | USAGE |
|---|---|---|
| ash_wait_chains.sql | Display ASH wait chains (multi-session wait signature, a | @ash/ash_wait_chains <grouping_cols> <filters> <from_time> <to_time> |
| session waiting for another session etc.) | @ash/ash_wait_chains username||'-'||program2 "wait_class='Application'" sysdate-1/24 sysdate | |
| asqlmon.sql | Report SQL-monitoring-style drill-down into where in an | @ash/asqlmon <sql_id> <child#> <from_time> <to_time> |
| execution plan the execution time is spent (ASH based) | @ash/asqlmon 7q729nhdgtsqq 0 sysdate-1/24 sysdate | |
| @ash/asqlmon 7q729nhdgtsqq % sysdate-1 sysdate | ||
| aw.sql | Display last minute database activity | @aw <filter_expression> |
| @aw 1=1 | ||
| awr_sqlid.sql | Display SQL text from AWR | @awr/awr_sqlid <sql_id> |
| @awr/awr_sqlid 7q729nhdgtsqq | ||
| awr_sqlstats.sql | Display SQL statistics from AWR | @awr/awr_sqlstats <sql_id> <plan_hash_value> <from_time> <to_time> |
| @awr/awr_sqlstats 0sh0fn7r21020 1541789278 sysdate-7 sysdate | ||
| @awr/awr_sqlstats 0sh0fn7r21020 % sysdate-7 sysdate | ||
| awr_sqlstats_per_exec.sql | Display SQL statistics per execution from AWR | @awr/awr_sqlstats_per_exec <sql_id> <plan_hash_value> <from_time> <to_time> |
| @awr/awr_sqlstats_per_exec 0sh0fn7r21020 1541789278 sysdate-7 sysdate | ||
| @awr/awr_sqlstats_per_exec 0sh0fn7r21020 % sysdate-7 sysdate | ||
| awr_sqlstats_unstable.sql | Display unstable SQL execution plans from AWR | @awr/awr_sqlstats_unstable <group_by_expr1> <group_by_expr2> <from_time> <to_time> |
| @awr/awr_sqlstats_unstable force_matching_signature plan_hash_value sysdate-7 sysdate | ||
| bg.sql | Display background processes | @bg <process_name|process_description> |
| @bg dbw | ||
| @bg writer | ||
| @bg % | ||
| bhobjects.sql | Display top objects in buffer cache | @bhobjects |
| bhobjects2.sql | Display buffer cache statistics | @bhobjects2 |
| cancel.sql | Generate commands for canceling selected SQL | @cancel <filter_expression> |
| @cancel sid=150 | ||
| @cancel username='SYSTEM' | ||
| @cancel "username='APP' and program like 'sqlplus%'" | ||
| col.sql | Display column | @col <column_name> |
| @col open_mode | ||
| colusage.sql | Display column usage | @colusage [<owner>.]<table_name> |
| @colusage soe.orders | ||
| @colusage soe.% | ||
| create_sql_baseline.sql | Create SQL Plan Baseline from an existing "good" cursor | @create_sql_baseline <good_sqlid> <good_plan_hash_value> <to_bad_sqlid> |
| @create_sql_baseline g5tuxh82pk3qf 2966233522 d7khnbh6c9qas | ||
| create_sql_patch.sql | Create SQL patch | @create_sql_patch <sql_id> <hint> |
| @create_sql_patch g4pkmrqrgxg3b GATHER_PLAN_STATISTICS | ||
| @create_sql_patch b9dmj0ahu6xgc 'NO_INDEX_SS(@"SEL$26CA4453" "STORE_SALES"@"SEL$1")' | ||
| d.sql | Display data dictionary views and x$ tables | @d <object_name> |
| @d sql | ||
| @d % | ||
| dash_wait_chains.sql | Display ASH (based on DBA_HIST) wait chains (multi-session | @ash/dash_wait_chains <grouping_cols> <filters> <from_time> <to_time> |
| wait signature, a session waiting for another session etc.) | @ash/dash_wait_chains username||'-'||program2 "wait_class='Application'" sysdate-1/24 sysdate | |
| dasqlmon.sql | Report SQL-monitoring-style drill-down into where in an | @ash/dasqlmon <sqlid> <plan_hash_value> <from_time> <to_time> |
| execution plan the execution time is spent (AWR based) | @ash/dasqlmon 7q729nhdgtsqq 0 "timestamp'2019-10-07 07:00:00'" "timestamp'2019-10-07 07:00:00'" | |
| @ash/dasqlmon 7q729nhdgtsqq % sysdate-1 sysdate | ||
| date.sql | Display current date | @date |
| @d sql | ||
| @d % | ||
| ddl.sql | Extracts DDL statements for specified objects | @ddl [<owner>.]<object_name> |
| @ddl sys.dba_users | ||
| @ddl sys.%tab% | ||
| desc.sql | Describe object | @desc <object_name> |
| @desc dba_tables | ||
| devent_hist.sql | Display a histogram of the number of waits from AWR | @ash/devent_hist.sql <event> <filter_expression> <from_time> <to_time> |
| (milliseconds) | @ash/devent_hist.sql log_file 1=1 sysdate-1/24 sysdate | |
| @ash/devent_hist.sql log.file|db.file "wait_class='User I/O' AND session_type='FOREGROUND'" sysdate-1/24 sysda | ||
| te | ||
| df.sql | Display tablespace usage (GB) | @df |
| dfm.sql | Display tablespace usage (MB) | @dfm |
| dirs.sql | Display database directories | @dirs |
| drop_sql_baseline.sql | Drop SQL Plan Baseline | @drop_sql_baseline <sql_handle> (get sql_handle from DBMS_XPLAN notes or DBA_SQL_PLAN_BASELINES) |
| @drop_sql_baseline SQL_52cb74b7097edbbd | ||
| drop_sql_patch.sql | Drop SQL patch | @drop_sql_patch <patch_name> |
| @drop_sql_patch SQL_PATCH_g4pkmrqrgxg3b | ||
| ev.sql | Set session event | @ev <event> <level> |
| @ev 10046 12 | ||
| event_hist.sql | Display a histogram of the number of waits from ASH | @ash/event_hist.sql <event> <filter_expression> <from_time> <to_time> |
| (milliseconds) | @ash/event_hist.sql log.file 1=1 sysdate-1/24 sysdate | |
| @ash/event_hist.sql log.file|db.file "wait_class='User I/O' AND session_type='FOREGROUND'" sysdate-1/24 sysdat | ||
| e | ||
| event_hist_micro.sql | Display a histogram of the number of waits from ASH | @ash/event_hist_micro <event> <filter_expression> <from_time> <to_time> |
| (microseconds) | @ash/event_hist_micro log.file 1=1 sysdate-1/24 sysdate | |
| @ash/event_hist_micro log.file|db.file "wait_class='User I/O' AND session_type='FOREGROUND'" sysdate-1/24 sysd | ||
| ate | ||
| evh.sql | Display a histogram of the number of waits | @evh <event> |
| @evh log.file | ||
| @evh log.file|db.file | ||
| evo.sql | Disable session event | @evo <event> |
| @evo 10046 | ||
| fix.sql | Display fix controls description | @fix <bugno|description|optimizer_feature_enable|sql_feature> |
| @fix 13836796 | ||
| @fix adaptive | ||
| grp.sql | Group function wrapper | @grp <column_name> <table_name> |
| @grp owner dba_tables | ||
| @grp owner,object_type dba_objects | ||
| hash.sql | Display the hash value, sql_id, and child number of the last | @hash |
| SQL in session | ||
| help.sql | Display TPT script help | @help <search_expression> |
| @help explain | ||
| @help lock|latch.*hold | ||
| @help ^ind.*sql|^tab.*sql | ||
| hint.sql | Display all available hints | @hint <name> |
| @hint full | ||
| hintclass.sql | Display all available hints with hint class info | @hintclass <hint_name> |
| @hintclass merge | ||
| hintfeature.sql | Display all available hints with SQL feature info | @hintfeature <feature_name> |
| @hintfeature transformation | ||
| hinth.sql | Display hint hierarchy | @hinth <hint_name> |
| @hinth merge | ||
| ind.sql | Display indexes | @ind [<owner>.]<index_name|table_name> |
| @ind orders | ||
| @ind soe.ord_customer_ix | ||
| @ind soe.% | ||
| indf.sql | Display function-based index expressions | @indf [<owner>.]<index_name|table_name> |
| @indf orders | ||
| @indf soe.ord_customer_ix | ||
| @indf soe.% | ||
| kill.sql | Generate command to for killing user session | @kill <filter_expression> |
| @kill sid=284 | ||
| @kill username='SYSTEM' | ||
| @kill "username='APP' AND program LIKE 'sqlplus%'" | ||
| latchprof.sql | Profile top latch holders (V$ version) | @latchprof <grouping_columns> <sid> <latch_name> <samples> |
| @latchprof name,sqlid 123 % 10000 | ||
| @latchprof sid,name,sqlid % "shared pool" 10000 | ||
| latchprofx.sql | Profile top latch holders eXtended (X$ version) | @latchprofx <grouping_columns> <sid> <latch_name> <samples> |
| @latchprofx sid,name 123 % 10000 | ||
| @latchprofx sid,name,timemodel,hmode,func % "shared pool" 10000 | ||
| lock.sql | Display current locks | @lock <filter_expression> |
| @lock 1=1 | ||
| @lock type='TM' | ||
| log.sql | Display redo log layout | @log |
| long.sql | Display session long operations | @long <filter_expression> |
| @long 1=1 | ||
| @long username='SOE' | ||
| ls.sql | Display tablespace | @ls <tablespace_name> |
| @ls system | ||
| @ls % | ||
| lt.sql | Display lock type info | @lt <lock_name> |
| @lt TM | ||
| mem.sql | Display information about the dynamic SGA components | @mem |
| memres.sql | Display information about the last completed memory resize | @memres |
| operations | ||
| nls.sql | Display NLS parameters at session level | @nls |
| nonshared.sql | Display reasons for non-shared child cursors from | @nonshared <sql_id> |
| v$shared_cursor | @nonshared 7q729nhdgtsqq | |
| o.sql | Display database object based on object owner and name | @o [<owner>.]<object_name> |
| @o sys.dba_users | ||
| @o %.%files | ||
| oda.sql | Display oradebug doc event action | @oda <action> |
| @oddc latch | ||
| @oddc . | ||
| oddc.sql | Display oradebug doc component | @oddc <component> |
| @oddc optimizer | ||
| @oddc . | ||
| oerr.sql | Display Oracle error decription | @oerr <error_number> |
| @oerr 7445 | ||
| oi.sql | Display invalid objects | @oi |
| oid.sql | Display database objects based on object id | @oid <object_id> |
| @oid 10 | ||
| @oid 10,20 | ||
| otherxml.sql | Display outline hints from library cache | @otherxml <sql_id> <child#> |
| @otherxml 1fbwxvngasv1f 1 | ||
| p.sql | Display parameter name and value | @p <parameter_name> |
| @pd optimizer | ||
| partkeys.sql | Display table partition keys | @partkeys [<owner>.]<table_name> |
| @partkeys soe.orders | ||
| @partkeys soe.% | ||
| pd.sql | Display parameter name, description and value | @pd <parameter_description> |
| @pd optimizer | ||
| pga.sql | Display PGA memory usage statistics | @pga |
| pmem.sql | Display process memory usage | @pmem <spid> |
| @pmem 1000 | ||
| proc.sql | Display functions and procedures | @proc <object_name> <procedure_name> |
| @proc dbms_stats table | ||
| @proc dbms_stats % | ||
| procid.sql | Display functions and procedures | @procid <object_id> <subprogram_id> |
| @procid 13615 84 | ||
| pv.sql | Display parameters based on the current value | @pv <value> |
| @pv MANUAL | ||
| pvalid.sql | Display valid parameter values | @pvalid <parameter_name> |
| @pvalid optimizer | ||
| rowid.sql | Display file, block, row numbers from rowid | @rowid <rowid> |
| @rowid AAAR51AAMAAAACGAAB | ||
| s.sql | Display current session wait and SQL_ID info (10g+) | @s <sid> |
| @s 52,110,225 | ||
| @s "select sid from v$session where username = 'XYZ'" | ||
| @s &mysid | ||
| sdr.sql | Control direct read in serial (_serial_direct_read) | @sdr <TRUE|FALSE> |
| se.sql | Display session events | @se <sid> |
| @se 10 | ||
| sed.sql | Display wait events description | @sed <event> |
| @sed log_file | ||
| @sed "enq: TX" | ||
| seg.sql | Display segment information | @seg [<owner>.]<segment_name> |
| @seg soe.customers | ||
| @seg soe.% | ||
| segcached.sql | Display number of buffered blocks of a segment | @segcached [<owner>.]<object_name> |
| @segcached soe.orders | ||
| @segcached soe.% | ||
| seq.sql | Display sequence information | @seq [<owner>.]<sequence_name> |
| @seq sys.jobseq | ||
| @seq %.jobseq | ||
| ses.sql | Display session statistics for given sessions, filter by | @ses <sid> <statname> |
| statistic name | @ses 10 % | |
| @ses 10 parse | ||
| @ses 10,11,12 redo | ||
| @ses "select sid from v$session where username = 'APPS'" parse | ||
| ses2.sql | Display session statistics for given sessions, filter by | @ses2 <sid> <statname> |
| statistic name and show only stats with value > 0 | @ses2 10 % | |
| @ses2 10 parse | ||
| @ses2 10,11,12 redo | ||
| @ses2 "select sid from v$ses2sion where username = 'APPS'" parse | ||
| settings.sql | Display AWR configuration | @awr/settings |
| sga.sql | Display instance memory usage breakdown from | @sga |
| v$memory_dynamic_components | ||
| sgai.sql | Display instance memory usage breakdown from v$sgainfo | @sgai |
| sgares.sql | Display information about the last completed SGA resize | @sgares |
| operations from v$sga_resize_ops | ||
| sgastat.sql | Display detailed information on the SGA from v$sgastat | @sgastat <name|pool> |
| @sgastat % | ||
| @sgastat result | ||
| sgastatx.sql | Display shared pool stats by sub-pool from X$KSMSS | @sgastatx <statistic_name> |
| @sgastatx "free memory" | ||
| @sgastatx cursor | ||
| sl.sql | Set statistics level | @sl <statistics_level> |
| @sl all | ||
| smem.sql | Display process memory usage | @smem <sid> |
| @smem 1000 | ||
| sqlbinds.sql | Display captured SQL bind variable values | @sqlbinds <sql_id> <child_number> |
| @sqlbinds 2swu3tn1ujzq7 0 | ||
| @sqlbinds 2swu3tn1ujzq7 % | ||
| sqlf.sql | Display full sql text from memory | @sqlf <sql_id> |
| @sqlf 7q729nhdgtsqq | ||
| sqlfn.sql | Display SQL functions | @sqlf <name> |
| @sqlfn date | ||
| sqlid.sql | Display SQL: text, child cursors and execution statistics | @sqlid <sql_id> <child_number> |
| @sqlid 7q729nhdgtsqq 0 | ||
| @sqlid 7q729nhdgtsqq % | ||
| sqlmon.sql | Run SQL Monitor report | @sqlmon <sid> |
| @sqlmon 1019 | ||
| syn.sql | Display synonym information | @syn [<owner>.]<synonym_name> |
| @syn system.tab | ||
| @syn system.% | ||
| sys.sql | Display system statistics | @sys <statistic_name> |
| @sys redo | ||
| @sys 'redo write' | ||
| t.sql | Display default trace file | @t |
| tab.sql | Display table information | @tab [<owner>.]<table_name> |
| @tab soe.orders | ||
| @tab soe.% | ||
| tabhist.sql | Display column histograms | @tabhist [<owner>.]<table_name> <column_name> |
| @tabhist soe.orders order_mode | ||
| @tabhist soe.orders % | ||
| tabpart.sql | Display table partitions | @tabpart [<owner>.]<table_name> |
| @tabpart soe.orders | ||
| @tabpart soe.% | ||
| tabsubpart | Display table subpartitions | @tabsubpart [<owner>.]<table_name> |
| @tabsubpart soe.orders | ||
| @tabsubpart soe.% | ||
| ti.sql | Force new trace file | @ti |
| tlc.sql | Display top-level call names | @tlc <call_name> |
| @tlc commit | ||
| topseg.sql | Display top space users per tablespace | @topseg <tablespace_name> |
| @topseg soe | ||
| @topseg % | ||
| topsegstat.sql | Display information about top segment-level statistics | @topsegstat <statistic_name> |
| @topsegstat reads | ||
| @topsegstat % | ||
| trace.sql | Enable tracing | @trace <filter_expression> |
| @trace sid=123 | ||
| @trace username='SOE' | ||
| traceme.sql | Enable tracing for the current session | @traceme |
| traceoff.sql | Disable tracing | @traceoff <filter_expression> |
| @traceoff sid=123 | ||
| @traceoff username='SOE' | ||
| trans.sql | Display active transactions | @trans |
| trig.sql | Display trigger information | @trig [<owner>.]<trigger_name> |
| @trig sys.delete_entries | ||
| @trig sys.% | ||
| ts.sql | Display tablespaces | @ts <tablespace_name> |
| @ts soe | ||
| @ts % | ||
| uds.sql | Display undo statistics | @uds |
| us.sql | Display database usernames from dba_users | @us <username> |
| @us username | ||
| usid.sql | Display user sessoin and process information | @usid <sid> |
| @us 1234 | ||
| uu.sql | Display user sessions | @uu <username> |
| @uu % | ||
| @uu username | ||
| @uu %username% | ||
| wrka.sql | Display PGA and TEMP usage | @wrka <fileter_expression> |
| @wrka 1=1 | ||
| @wrka sid=1000 | ||
| wrkasum.sql | Display summary of SQL workareas groupbed by opertion type | @wrkasum <filter_expression> |
| (PGA and TEMP) | @wrkasum sql_id='7q729nhdgtsqq' | |
| x.sql | Display SQL execution plan for the last SQL statement | @x |
| xa.sql | Display SQL execution plan for the last SQL statement - | @xa |
| alias | ||
| xall.sql | Display SQL execution plan for the last SQL statement - | @xall |
| advanced | ||
| xawr.sql | Display SQL execution plan from AWR | @xawr <sql_id> <plan_hash_value> |
| @xawr 0sh0fn7r21020 1541789278 | ||
| @xawr 0sh0fn7r21020 % | ||
| xb.sql | Explain a SQL statements execution plan with execution | @xb |
| profile directly from library cache - for the last SQL | ||
| executed in current session | ||
| xbi.sql | Explain a SQL statements execution plan with execution | @xbi <sql_id> <sql_child_number> |
| profile directly from library cache - look up by SQL ID | @xbi a5ks9fhw2v9s1 0 | |
| xi.sql | Display SQL execution plan from library cache | @xi <sql_id> <child#> |
| @xi 7q729nhdgtsqq 0 | ||
| @xi 7q729nhdgtsqq % | ||
| xia.sql | Display SQL execution plan from library cache: ADVANCED | @xia <sql_id> |
| @xia 7q729nhdgtsqq | ||
| xp.sql | Run DBMS_SQLTUNE.REPORT_SQL_MONITOR (text mode) for session | @xp <session_id> |
| @xp 47 | ||
| xplto.sql | Display execution plan operations | @xplto <name> |
| @xplto full | ||
| xprof.sql | Run DBMS_SQLTUNE.REPORT_SQL_MONITOR for session | @xprof <report_level> <type> <sql_id|session_id> <sql_id|sid> |