Author: Andrew Fraser

Oracle AwrExtr cron to export backup AWR and ASH data

Oracle supplies the awrextr and awrload scripts to backup and copy AWR and ASH data by datapump. Those are designed to work interactively, prompting for user input. Script below will run awrextr from cron, without the interactive user input. #!/bin/bash

Posted in Uncategorized

Oracle Foreign Key Constraints with missing indexes

Thomas Gutzmann from Tom Kyte script to identify foreign key constraints with missing indexes works in only one schema. I had the problem of having two tightly integrated schemas with referential constraints going back and forth. The below version of

Posted in Uncategorized

Display Oracle SQL Developer XMLTYPE

Do you see annoying (XMLTYPE) in Oracle SQL Developer output? Wrap the column in the xmlType.getClobVal function to see the actual data. WITH q AS ( SELECT XMLTYPE ( ” ) AS col1 FROM DUAL ) SELECT q.col1 , XMLTYPE.GETCLOBVAL

Posted in XML

TKProf by ASH

Oracle session trace + tkprof is still the best tool for performance diagnosis. But: Need access to database server user_dump_dest directory. Has to be switched on in advance Has a performance impact, especially if tracing multiple sessions or tracing for

Posted in Performance tuning, Scripts

What to do if a database performance issue is reported

A high level overview. Expectation should be for all OLTP operations to take less than 1 second, and all batch processing jobs and background scheduled report jobs to take less than 1 hour each. This is deliverable on even large

Posted in Performance tuning

Rman list most recent backups

List most recent rman database backups on target database sqlplus with: set pages 9999 lines 132 ALTER SESSION SET nls_date_format = ‘Dy DD-Mon-YYYY HH24:MI:SS’ ; SELECT b.checkpoint_time , b.incremental_level , COUNT(*) FROM v$backup_datafile b JOIN v$datafile f ON f.file# =

Posted in Rman

Oracle hint cursor_sharing_exact using sql patch

Bind variable peeking is still a cause of plan instability, even after 11gR1’s introduction of adaptive cursor sharing. In my case adaptive cursor sharing wasn’t helpful for a daily batch job, because the sql was aged out of cache by

Posted in Performance tuning

Oracle select earliest and latest rows using keep group or first_value analytics

There are two ways of selecting the earliest and latest rows for each customer. 1) Keep group: SELECT t1.customer_ref , MAX ( t1.order_amount ) KEEP ( DENSE_RANK FIRST ORDER BY t1.order_date ASC ) AS earliest_order_amount , MAX ( t1.order_amount )

Posted in Uncategorized

Windows PowerShell run script on all SQL Servers recovery mode

Micrsoft SQL Server databases should be kept in full recovery mode (the equivalent of Oracle’s archivelog mode) except for the master, tempdb, ReportServerTempDB databases. To check all databases in a SQL Server instance, run the below script in a SQL

Posted in SQL server, Uncategorized

Oracle Full Text simple example for like % grep wildcard

The equivalent of the grep command in oracle is like: SELECT * FROM dba_source s WHERE LOWER ( s.text ) LIKE ‘%within group%’ ; That works fast enough for querying dba_source, which is a small dataset. But for even medium

Posted in Performance tuning