Oracle Performance Survival Guide - Scripts


Here you can obtain the scripts and examples from Oracle Performance Survival Guide by Guy Harrison.

An archive containing the full set of scripts can be downloaded here.  Individual files can be downloaded below.

The archive contains both utility scriptsand all of the SQL files that I used to create various test loads while writing the book.  The utility scripts are described in the index.html file contained within the archive.  These scripts provide reports on various aspects of database health and performance.

The rest of the SQL files are provided as is, and without descriptions.  You might find them useful if you are trying to replicate my workloads.

Some of the scripts will only work if you install some packages, views and permissions.  In particular, the OPSG_PKG package provides an ability to generate short term delta and rate reports against the time model and wait interface.  See installation, below, for instructions.

I hope you find these scripts - and my book - useful.  If you have any problems please contact me at guy.a.harrison@gmail.com.  

Regards,

Guy Harrison


Installation 


Many scripts can be run without any installation providing that the user has access to V$ views.  However, a few require specialized views and that the OPSG_PKG be installed.  To install the package:

1. Open a command prompt within the "scripts" directory
2. execute either install_opsg.bat (Windows) or install_opsg.sh (Linux/Unix)
3. Respond to the prompts

Here is an example session:

C:\tmp\opsg\install>install_opsg

C:\tmp\opsg\install>sqlplus /nolog @install_opsg

SQL*Plus: Release 11.1.0.7.0 - Production on Thu Sep 10 10:55:02 2009

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Enter password for the (new) OPSG user:opsg
Enter SYS password:
Enter TNSNAMES entry:g11r2ga

The script creates a user OPSG which has privileges to run all of the scripts and which has appropriate permissions.  You can modify the script if you want to install a different user.   You do need the SYS password to install the user.

Extending the SH schema


I found the data volumes in Oracle's SH schema too low to illustrate some of the SQL tuning principles.  The script extend_sh_schema.sql in the scripts directory will add about 2 million rows to the SALES and COSTS tables.   These updates are applied directly to the SH schema.  



Listing of individual utility scripts

Chapter 3: Tools of the Trade
p 41topsql1.sql Top 10 cached sql statements by elapsed time
p 58loginTrigger.sql Example of a login trigger that activates SQL trace
p 70topWaits.sql Non-idle wait times sorted by time waited
p 73timeModelSimple.sql Time model unioned with wait data to show waits combined with CPU timings
p 55CurrentSessionTraceStatus.sql Show the full name and path of the trace file for the current session
Chapter 5: Indexing and Clustering
p 122monitoringOn.sql Turn on monitoring for all indexes
p 122vobject.sql Show usage statistics for indexes
p 133checkPlanCache.sql Report on indexes that are not found in any cached execution plan
Chapter 6: Application Design and Implementation
p 157force_matching.sql Identify SQLs that are identical other than for literal values (Force matching candidates)
Chapter 7: Optimizing the Optimizer
p 194ses_optimizer.sql Show optimizer parameters in effect for the curren session
p 198disableStats.sqlDisable automatic statistics collection
Chapter 11: Sorting Grouping and Set Operations
p 332sql_workarea.sql Show statistics onsort and hash workareas (from V$SQL_WORKAREA)
Chapter 12: Using and Tuning PL/SQL
p 355plsqltime_sys.sql Query to reveal the overhead of PLSQL within the database
p 356cachedPlsql.sql Show statements in the cache with PLSQL component and show pct of time spent in PLSQL
p 357queryProfiler.sql Report on data held in the PLSQL_PROFILER tables
p 360hrpof_qry.sql Query agains the DBMSHP (hierarchical profiler) tables
Chapter 13: Parallel SQL
p 414px_session.sql Show real time view of current parallel executions
p 413tqstat2.sql Example of using v$pq_tqstat to reveal PQO workload distribution
p 422rac_pqo2.sql Example of using v$pq_tqstat to show inster-instance parallel in RAC
Chapter 15: Lock Contention
p 460lock_type.sql Show definition of all lock codes
p 461v_my_locks.sqlView definition that will show all locks held by the current user
p 467lock_delta_qry.sql Show lock waits compared to other waits and CPU over a short time period
p 466lock_wait_events.sql Show lock waits compared to other waits and CPU
p 468ash_locks.sql Show lock wait information from Active Session History (ASH)
p 468awr_locks.sql Show lock wait information from Active Workload Repository (AWR)
p 470locking_sql.sql Show SQLs with the highest lock waits
p 471segment_stats.sql Show segments with the highest lock waits
p 472application_module_wait.sql Show SQLs for a particular module with lock waits
p 472session_lock_waits.sql Show sessions with a specific USERNAME and their lock waits
p 476blockers.sql Simple blocking locks script
p 477lock_tree.sql Lock tree built up from V$SESSION
p 477show_session_waits.sql Blocking row level locks at the session level
p 478wait_chains.sql Lock tree built up from V$wait_chains
Chapter 16: Latch and Mutex contention
p 494latch_waits.sql Latch/mutex waits compared to other non-idle waits and to CPU
p 496latch_qry.sql "Latch statistics - gets
p 497ash_latch.sql Latch statistics from Active Session History (ASH)
p 495latch_delta_qry.sql Latch/mutex waits over a short duration compared to other waits
p 497latching_sql.sql SQLs with the highest concurrency waits (possible latch/mutex-related)
p 500force_matching.sql SQLs not using bind variables - possibly causing library cache mutex contention
p 499libcache.sql Library cache statistics
p 503cbc_config.sql "Number of Cache Buffers Chains latches & number of buffers covered
p 504cbc_blocks.sql Blocks with the highest touch counts and latches involved
p 505rowcache_latches.sql Rowcache latch statistics
p 510latch_class.sql Adjusting spin count for an individual latch class
p 511latch_class_qry.sql Query to show latch class configuration
Chapter 17: Shared Memory Contention
p 519iostat_file.sql Status of ansynchronous IO
p 521flash_size.sql Size of the flashback log buffer
p 526waitstat.sql Buffer busy waits by buffer type
p 526busy_segments.sql Buffer busy waits by segment
Chapter 18: Buffer Cache Tuning
p 538temporary_direct.sql Direct path IO and buffered IO
p 540buffer_pool_objects.sql Segments cached in the buffer pools
p 541hit_rate.sql """hit rates"" for direct and cached IOs "
p 542hit_rate_delta.sql """hit rates"" calculated over a time interval "
p 544sql_miss_rates.sql logical and physical IO for specific SQLs
p 546buffer_pool_stats.sql Buffer pool IO statistics
p 547db_cache_advice1.sql Query on V$DB_CACHE_ADVICE
p 548db_cache_hist.sql DB cache advise shown as a histogram
p 552sga_dynamic_components.sql Query on V$SGA_DYNAMIC_COMPONENTS
p 551sga_resize_ops.sql Query on V$SGA_RESIZE_OPS
Chapter 19: Optimizing PGA Memory
p 562pga_parameters.sql PGA parameters and configuration from v$pgastat
p 566direct_temp_waits.sql temporary direct path IO compared to CPU and other non-idle waits
p 565top_pga.sql Top consumers of PGA memory
p 567direct_io_delta_view_qry.sql Direct path temp IO over a time interval
p 570sql_workarea.sql SQL workarea statistics
p 571pga_target_advice.sql PGA target advice report
p 572pga_advice_hist.sql PGA target advice histogram
Chapter 20: Other Memory Management Topics
p 578io_waits.sql IO wait breakdown
p 581io_time_delta_view_qry.sql IO wait breakdown over a time period
p 582direct_path_trace_stats.plPerl script to calculate average direct path IO time from a trace file
p 583pga_target_time.sql PGA target converted to elapsed times
p 584overall_memory_advice.sql Combined (PGA+SGA) memory advice report for 10g
p 586overall_memory11g.sql Combined (PGA+SGA) memory advice report for 11g
p 590memory_dynamic_components.sql V$MEMORY_DYNAMIC_COMPONENTS report
p 590memory_resize_ops.sql V$MEMORY_RESIZE_OPS report
p 591memory_target_advice.sql Memory target advice report
p 593kmgsbsmemadv.sql Query against X$KMSGSBSMEMADV (basis of V$MEMORY_TARGET_ADVICE)
p 594memory_parms.sql Report on memory related parameters
p 599result_cache_statistics.sql Result set cache statistics
p 600rscache_hit_rate.sql Result set cache efficiency
p 600sql_cache_stats.sql Result set cache statistics for SQL statements
p 601rc_dependencies.sql Result set cache dependencies
p 605shared_pool_advice.sql Shared pool advisory
Chapter 21: Disk IO Tuning fundamentals
p 617io_waits.sql IO waits compared to other waits and CPU
p 618io_waits_delta.sql IO waits reported for an interval
p 619iostat_file.sql Summary report from v$iostat_file
p 620iostat_function.sql Summary report of v$iostat_function
p 622filestat.sql Summary report from v$filestat
p 622filemetric.sql Short term IO statistics from v$filemetric
p 624calibrate_io.sql PL/SQL reoutine to calibrate IO
p 623file_histogram.sql IO service time histogram
p 625dba_rsrc_io_calibrate.sql Query IO calibration data
p 634lgwr_size.sql Size of an average redo log IO
p 636log_file_waits.sql Report of redo log related waits
p 637log_history.sql Log switch rates from v$log_history
Chapter 22: Advanced IO techniques
p 645diskgroup_performance.sql ASM diskgroup IO throughput and service time
p 646asm_disk_performance.sql ASM disk-level throughput and service time
p 647asm_operations.sql ASM rebalance operations in progress
p 647asm_files.sql ASM file level IO statistics
p 654asm_templates.sql List all ASM templates
Chapter 23: Optimizing RAC
p 669top_level_waits.sql Break down of top level WAITCLASS waits
p 670cluster_waits.sql Break out of cluster waits compared to other categories
p 671rac_waits_delta.sql RAC waits over an interval of time
p 673avg_latency.sql Global cache latency report
p 674latency_delta.sql Global cache latency over a time period
p 675ksxpia.sql Private interconnect IP address
p 677gc_blocks_lost.sql Lost blocks report
p 681lms_latency.sql LMS latency breakdown
p 682flush_time.sql redo log flush frequency and wait times
p 684balance.sql Cluster balance report
p 685rac_balance_delta.sql Cluster balance over a time period
p 689service_stats.sql Report on service workload by instance
p 693gc_miss_rate.sql "Global cache ""miss rate"" by instance "
p 694top_gc_segments.sql Segments with the highest Global Cache activity