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. |
My favourite one is esfc_hit_ratio.sql.. LOL
Hi,
are there any scripts that create SKEW* tables?
i want to download the scripts, thanks a lot!!
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
whoami is missing in the zip file.
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
Where is the scrip table_stats.sql used on book ?
HI
Where is the table_stats.sql script used on book ?
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
I just downloaded the zip to look at snapper.sql, it is not in the zip.
snapper.sql is missing from the zip file
Found following scripts missing in the zip file, thanks!
desc.sql
old_rowid.sql
snapper.sql
whoami.sql
Hi
@snapper is missing from the zip file.
Could pls upload the script?
report_sql_monitor is missing from zip file
The zip file has been updated with the report_sql_monitor.sql script.
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
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.
get_compression_type.sql scripts is missing from the zip file.
The zip file has been updated with get_compression_type.sql.