Scripts

Here is a listing of the diagnostic scripts we used in the book. The entire set is contained in this zip file: Expert_Oracle_Exadata_Scripts.zip

Script Name Description
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 whats happening on a system as fast statements may not be “caught” by the this quick and dirty approach.
calibrate_io.sql This script is a 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.
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 that 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. Shows number of rows, number of blocks and default degree of parallelism.
desc.sql This script produces output similar to the SQL*Plus DESCRIBE command.
display_raw.sql This ia 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_hit_ratio.sql Useless script for calculating an incorrect Edxadata Smart Flash Cache hit ratio.
esfc_keep_tables.sql This script displays objects that have the CELL_FLASH_CACHE attribute set to KEEP.
flush_pool.sql This script uses ALTER SYSTEM FLUSH SHARED_POOL to flush all SQL statements from the shared pool.
flush_sql.sql This script uses DBMS_SHARED_POOL.PURGE to flush a single SQL statetement 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 LIO’s.
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 % of I/O was saved. Note that there are several alternate versions of this script used in the book (fsx2.sql, fsx3.sql, 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 can be used to identify long running statements and report on whether they have been offloaded or not. It can be used in conjunction with the offload_percent.sql script to drill into the individual statements contributing to it’s calcualted 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 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 identifiy 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.
mystats.sql This is a simple script for querying V$MYSTATS.
old_rowid.sql This is a simple script for querying V$MYSTATS. This script creates the old_rowid() function. The old_rowid() function accepts a rowid and returns the fileno, blockno and rowno (in the old rowid format).
obj_by_hex.sql 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 Displays database parameters and their current values. Includes a switch to show or suppress display of hidden parameters.
parmsd.sql Displays database parameters and their descriptions. Includes a switch to show or suppress display of hidden parameters.
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 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. Documentation can be found on Tanel’s blog.
ss_off.sql Turns off Smart Scans via alter session (i.e. sets CELL_OFFLOAD_PROCESSING=FALSE).
ss_on.sql Turns on Smart Scans via alter session (i.e. 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 Displays a list of wait events that match a text string.
whoami.sql This script displays current session information including SID, Serial#, Previous SQL Hash Value and OS Shadow Process ID.

19 Comments to “Scripts”

  1. jithesh says:

    My favourite one is esfc_hit_ratio.sql.. LOL

  2. Wojciech says:

    Hi,

    are there any scripts that create SKEW* tables?

  3. li.f says:

    i want to download the scripts, thanks a lot!!

  4. Vijay Manthena says:

    Kerry,

    Can you please include the scripts for test table creation such as skew, skew3 etc.

    Also please make the other scripts mentioned in the listings such as burn_cpu.sql available in the zip file coz it will help us to resimiluate all the examples.

    Regards
    Vijay

  5. yingfeng says:

    whoami is missing in the zip file.

  6. osborne says:

    snapper should be in the zip now,

    By the way:

    Snapper is maintained by Tanel and the most current version can be found here:

    http://files.e2sn.com/scripts/snapper.sql

    More information about snapper can be found here:

    http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper

  7. wilhelm saba says:

    Where is the scrip table_stats.sql used on book ?

  8. wilhelm saba says:

    HI

    Where is the table_stats.sql script used on book ?

  9. Muru says:

    Dear Authors,

    It’s great book for Exadata. It’s my plesure to reading it. Thanks for your detailed explantion. There are few of scripts are missing in your zip. can you pls upload them ?. i need snapper.sql ( chap 11) , oswextract.sh (chap 12) .

    Thanks
    Muru

  10. peter says:

    I just downloaded the zip to look at snapper.sql, it is not in the zip.

  11. Manoranjan Dash says:

    snapper.sql is missing from the zip file

  12. jjqing says:

    Found following scripts missing in the zip file, thanks!

    desc.sql
    old_rowid.sql
    snapper.sql
    whoami.sql

  13. Tzur Emzari says:

    Hi

    @snapper is missing from the zip file.

    Could pls upload the script?

  14. Basil says:

    report_sql_monitor is missing from zip file

    1. osborne says:

      The zip file has been updated with the report_sql_monitor.sql script.

  15. Ravi says:

    Found following scripts missing in the zip file(Expert_Oracle_Exadata_Scripts.zip).

    desc.sql
    fsxo.sql
    gather_table_stats.sql
    obj_by_hex.sql
    offload_percent.sql
    ss_off.sql
    ss_on.sql
    table_size.sql
    valid_events.sql

    1. admin says:

      Some of these were missing and some were there (I think). I have added the missing files and re=uploaded the zip file. Please let us know if you find any trouble extracting them.

  16. Vishal Gupta says:

    get_compression_type.sql scripts is missing from the zip file.

    1. admin says:

      The zip file has been updated with get_compression_type.sql.

Leave your Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Blue Taste Theme created by Jabox