Scripts & Questions

This page is both my page for all the links to useful script AND for any questions you want to post regarding topics you find on my blog.  Always remember:

  1.  I’m not Oracle Support.  I can’t interfere with a solution that’s being worked on with these great folks in the support team.
  2. I’m not licensing or sales.  Trust me-  you don’t want to ask me deep licensing questions or costs.  I know nothing in regards to either… 🙂

So go for it, post a questions at the bottom of the page and Discus will email and let me know!

New Stuff

Apple Scripts

OmniFocus Weekly Status Report

Delphix Scripting Resources

Delphix GitHub Links

Delphix How To Videos

Scripts

This is a collection of scripts that I’ve found useful over the years.  Please feel free to utilize any you would like and hopefully they will serve others as well as they have served me.  I have tried to ensure that if an author of the script is known, it is shown in the script.  You will see some that I’ve had for so long, I have no idea where the original source is from or even if it is in the original form, (vs. me having personalized it as I’ve gone along…) I have a tendency to utilize very generic, simple scripts and edit them to the needs of the specific situation. If you have a request for one of the ones here, just let me know!

AWR Warehouse Scripts, (NEW!!)

AWRW Top IO Waits

AWRW Top CPU

AWRW Analyze SQLID Data

AWRW Multiple Hash Plans for SQLID

CPU History by DBID

High Level AWR Data by DBID and Instance

Resource Allocation Scripts
mem_chk.sql
mem_alloc.sql

Misc. Scripts

Dynamically Collecting Modified Object Stats
Check Rman Async
Check Mview Lag
Check DB Object Cache Info
Check for Data Dictionary Corruption
Set Sequence Cache
User Privilege Report
Check Ora Patch
DB Size
Datafile Space
Dynamic Index Monitoring Script
Check Index Monitoring
User Last Login
Locate Historical SQL_ID Info

RMAN Hot Backup Script for Easy Restores

RAC Schtuff
Global Cache Blocks Lost
Global Cache Corrupt Blocks

Performance
Long Operations
Library Cache Stats
Data Dictionary Hit
Duplicate SQL Search
Check Redo Size
Check Full Scans
Check Execs in Memory
Latch Check
Enqueue Waits
The Mutex Step Thru
Max Cursors
Redo Waits
AAS Expensive
AAS SQL Hist Stats
AAS High Var
AAS Exact
AAS Per Hour
AWR Per Minute
ASH Per Minute
Waits Aggregated

 

 

 

 

Troubleshooting
What’s in Undo
Find UNDO Blocks by SQL_ID
Dynamic Kill Sessions Script
Find Program from SQL_ID(or Hash)

Exadata

Check Optimal Object Scans
Exadata Optimal Reads
ASM Cell Node Check

ASM
ASM Disk Info
View ASM Files

SQL Server
Check Process SQL
SQL Tuning Script by DB
Check Memory
Move SQL Server Logins
Query Waits
SQL Performance Stats
Dump the Stack Trace Data

ASH Queries, (NEW!)

Whats in the Ash Buffer
ASH CPU by User
ASH CPU Time
ASH IO Waits
ASH Per Minute
ASH SQL_ID CPU Use
ASH SQL_ID Waits
ASH Wait for CPU
Tyler Muth ASH Resource Query

Script to email HTML AWR Report

AWR Streamlined Repository

I’ve been asked a couple times for this, so searched it out and added it here.  This is the DDL for a repository table to store data remotely/locally for the high level hourly data requests based off some wonderful queries from Karl Arao that we would email back and forth, discussing how we could make it into something *more*… 🙂

AWR DDL by DB_ID

Hourly Report of AWR Data, identified by DB_ID, can then be used to report or fill into DDL created in table above.

Shell Script to load data

Explain Plans/Execution Plans

Get Explain Plans

Valuable ASH URLS

›Karl Arao: http://karlarao.wordpress.com

›Tyler Muth: http://tylermuth.wordpress.com/

›Kyle Hailey, John Beresniewicz, Graham Wood:  http://ashmasters.com/

 

Questions and Recommendations for Blog Posts

Print Friendly
December 11th, 2009 by Kellyn Pot'Vin
  • Pingback: Redgate Webinar, “ASH and AWR Performance Data” | DBA Kevlar()

  • Pingback: Performance Tuning with ASH and AWR Data – All Things Oracle()

  • Pingback: AWR Warehouse Webinar from ODTUG | DBA Kevlar()

  • Pingback: AWR Warehouse, Status | DBA Kevlar()

  • Pingback: AWR Warehouse, Status - Oracle - Oracle - Toad World()

  • luis

    it’s great, thank u

  • Kumarek

    is there any way to check “the query volume” on a table in production at specific interval(Say 9pm to 10pm)?

  • DBAkevlar

    You can look at IO, Buffer Busy Waits, etc. on a specific object for a given hour interval through snapshots, but I’m not sure what your term, “query volume” would signify. It’s not a term that is used for a specific wait event, etc.?

  • Foued Grayâa

    Very kind and very nice to share such scripts as well as those articles. Grateful for that.
    Foued

  • rajeshwar

    I am running into temp space errors when trying to run a sql query. How can I estimate how much temp space would I need for a query that is currently running?

  • DBAkevlar

    There are a couple different ways to do this, but I would start with ensuring the stats are up to date for the objects involved and then create an explain plan for the statement, then
    SET LINESIZE 130
    SET PAGESIZE 0
    SELECT * FROM table(DBMS_XPLAN.DISPLAY);
    Inspect the temp usage displayed for the explain plan.

    If the statement has already run, you can look at the data in the AWR:
    SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(”));
    Thanks,
    Kellyn

  • rajeshwar

    Thank you. I will try this and let you know, am not sure if I have privileges to query the dbms_xplan.Btw, am a developer and very much interested in learning about performance tuning, oem etc. What’s a good book you would recommend if I’ve to understand looking at oem and understanding the awr reports etc, understanding explain plans etc?

  • vinay singh

    Is there any way to capture the dbsnmp “account lock” errors in oem 12c and create corrective action for that ..We have a big environment and a lot of time databases show as down only because of this .we cannot keep the same password forever due to the policies.

  • Facebook
  • Google+
  • LinkedIn
  • Twitter