Sqlscripts

From Oramosswiki

A series of scripts that I commonly use - feel free to use them as you see fit.

They are, of course, subject to the limitations that they are supplied "as is" without any warranty, expressed or otherwise. Essentially you use them at your own risk and it would be sensible that you check that they function in a manner you require, on a non production system, before you use them in anger.

Contents 1 Performance 1.1 IO 1.2 Memory 1.3 Locks 1.4 Compression 1.5 Tuning Queries 1.6 Tuning Sessions 1.7 Dictionary Statistics 1.8 System Performance 1.9 TEMP usage 2 Checks 2.1 Partition Exchange Loading 2.2 Physical Design 2.3 Storage 2.4 Physical Design 3 SQL Generators 3.1 Security

Performance

IO

• File IO in last 24 hours - from AWR
• Get the Volume IO stats for the last 24 hours from AWR
• Get the Database IO stats for the last 24 hours from AWR
• Get the Volume IO stats by day since AWR has been recording them
• Get the Database IO stats by day since AWR has been recording them
• Get AWR IO Stats

Memory

• SQL Workarea Histogram
• SGA Target Advice
• PGA Target Advice
• DB Cache Advice

Locks

• Get lock and locked object information

Compression

• Get the ordering of columns for a table, which results in the maximum compression

Tuning Queries

• Gets the progress of a long running query
• Get memory areas combined with execution plan steps from a SID
• Get exeuction plan for a SQL statement in the AWR repository
• Get OS PID from SID
• Get SQL join filter information (for Bloom filters)

Tuning Sessions

• Get session waits from SID (including PX slaves)
• Get memory areas combined with execution plan steps from a SID

Dictionary Statistics

• Get Cost Based Optimizer statistics for a given object

System Performance

• Get the number of blocks per physical read from normal Datafliles
• Get Event Histograms

TEMP usage

• Get the number of blocks per physical read from TEMP
• Get the temp space usage by tablespace
• Get the temp space usage by session

Checks

Partition Exchange Loading

• Test a source and target table for the ability to do a partition exchange load

Physical Design

• Identify indexes where the compression attribute differs between table and sub/partition levels

Storage

• Get tablespace space usage metrics

Physical Design

• Get partitioning and subpartitioning rationale for all tables

SQL Generators

Security

• Build table grants