as.sql

cdb_as.sql

AS is short for Active SQL. This script shows all active SQL statements on the current instance as shown by V$SESSION. Note that you may need to execute it several times to get an idea of what’s happening on a system, as fast statements may not be “caught” by this quick-and-dirty approach.

calibrate_io.sql

This script provides a simple wrapper for the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure. The procedure must be run before Oracle will allow you to enable Auto DOP on versions prior to Oracle 12cR1.

check_px.sql

This script contains a simple query of V$PX_PROCESS_SYSSTAT to show how many parallel server processes are currently in use.

comp_ratio.sql

This is a simple script that computes a compression ratio based on an input value (the original table size).

create_display_raw.sql

This script creates the display_raw() function in the ENKITEC schema, which translates raw-data-type values into various other data-types (originally written by Greg Rahn).

dba_tables.sql

This is a simple script to query DBA_TABLES. It shows the number of rows, number of blocks, and default degree of parallelism.

cdb_tables.sql

This is a simple script to query CDB_TABLES. It shows the number of rows, number of blocks, and default degree of parallelism for the entire CDB or limited to a specific PDB.

display_raw.sql

This is a simple script to translate a raw value into a specified data-type format such as NUMBER or VARCHAR2. It depends on the display_raw() function created by the create_display_raw.sql script.

dplan.sql

This script shows the actual execution plan for a SQL statement in the shared pool. This is a very simple script that prompts for a SQL_ID and CHILD_NO and then calls dbms_xplan.display_cursor.

dump_block.sql

This script dumps a data block to a trace file using ALTER SYSTEM DUMP DATAFILE. It prompts for fileno and blockno.

esfc_keep_tables.sql

This script displays objects that have the CELL_FLASH_CACHE attribute set to KEEP. Modifying the storage clause should not be necessary since Exadata 11.2.3.3; this script helps you identify the segments in need for a change.

flush_pool.sql

This script uses ALTER SYSTEM FLUSH SHARED_POOL to flush all SQL statements from the shared pool. Use this script with great care in production as it can cause a (hard) parse storm.

flush_sql.sql

This script uses DBMS_SHARED_POOL.PURGE to flush a single SQL statement from the shared pool. It only works with 10.2.0.4 and later.

fs.sql

This script allows you to search through V$SQL using a bit of SQL text or a SQL_ID. (FS is short for Find SQL.) The script reports some statistical information such as average elapsed time and average LIOs.

fsx.sql

FSX stands for Find SQL eXadata. This script searches the shared pool (V$SQL) based on the SQL statement text or a specific SQL_ID and reports whether statements were offloaded or not and, if offloaded, what percentage of I/O was saved. Note that there are several alternate versions of this script used in the book (fsx2.sql, fsx3.sql, and fsx4.sql). These versions reduce the width of the output to something more easily printed in the limits imposed by the printed book format.

fsxo.sql

This script is similar to the fsx.sql script but lists only those statements that have been offloaded. It can be used in conjunction with the offload_percent.sql script to drill into the individual statements contributing to its calculated offload percentage.

gather_table_stats.sql

This is a simple script to gather table statistics using the DBMS_STATS.GATHER_TABLE_STATS procedure.

get_compression_ratio.sql

get_compression_ratio_12c.sql

This script is a wrapper for the built in compression advisor functionality (DBMS_COMPRESSION.GET_COMPRESSION_RATIO). It prompts for a table name and a compression type and then estimates the expected compression ratio by actually compressing a subset of the table’s rows.

get_compression_type.sql

This script provides a wrapper for the DBMS_COMPRESSION.GET_COMPRESSION_TYPE procedure. It can be used to identify the actual compression type used for a specific row. It prompts for a table name and a ROWID and returns the actual compression type for that row as opposed to the compression type assigned to the table.

mystat.sql

This is a simple script for querying V$MYSTATS, not to be confused with Adrian Billington’s mystats script described later.

mystats.sql

Extensively covered in Chapter 11 Adrian Billington’s myststs.sql allows you to capture the change in session statistics during the execution of a SQL statement. Download from www.oracle-developer.net

old_rowid.sql

This script creates the old_rowid() function. The old_rowid() function accepts a rowid and returns the fileno, blockno, and rowno (the old rowid format).

obj_by_hex.sql

This script translates an object_id in hex format into an object name. The hex value is contained in block dumps.

offload_percent.sql

This script can be used to provide a quick check on whether statements are being offloaded or not on Exadata platforms. It allows all statements over a minimum time or a minimum number of LIOs to be evaluated and calculates a percentage of statements that have been offloaded.

parms.sql

This script displays database parameters and their current values. Includes a switch to show or suppress display of hidden parameters. Requires you to log in as SYSDBA.

parmsd.sql

This script displays database parameters and their descriptions. Includes a switch to show or suppress display of hidden parameters. Requires a login as SYSDBA.

part_size2.sql

This script shows the sizes of partitions as reported by DBA_SEGMENTS.

pool_mem.sql

This script provides a simple query against V$SGASTAT, showing memory assigned to various “pools.”

queued_sql.sql

This simple script queries V$SQL_MONITOR for statements that are queued by the parallel statement queuing feature.

report_sql_monitor.sql

This is a script to call DBMS_SQLTUNE.REPORT_SQL_MONITOR.

si.sql

This script displays the current value for the statistic Cell Physical IO Bytes Saved by Storage Index from V$MYSTATS. It provides a quick way to check storage index usage.

snapper.sql

This is far and away the most robust script used in the book. It is really more like a monitoring program that can report on an extremely wide range of information about active sessions. The script and documentation can be found on Tanel Poder’s blog http://blog.tanelpoder.com

ss_off.sql

This script turns off Smart Scans via alter session (that is, it sets CELL_OFFLOAD_PROCESSING=FALSE).

ss_on.sql

This script turns on Smart Scans via alter session (that is, it sets CELL_OFFLOAD_PROCESSING=TRUE).

table_size.sql

This script shows sizes of objects as reported by DBA_SEGMENTS. There is another version (table_size2.sql) that is basically the same script with a reduced number of output columns.

valid_events.sql

This script displays a list of wait events that match a text string. Requires you to connect as SYSDBA.

whoami.sql

whoami_12c.sql

This script displays current session information, including SID, Serial#, Previous SQL Hash Value, and OS Shadow Process ID. The 12c version also reports the thread ID in case you operate with threaded_execution = true.