Hints

Hints for Optimization Approaches and Goals ALL_ROWS The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption). FIRST_ROWS The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource […]

Wait time percentage

How the Oracle DBA can find the Wait time percentage of the total DB time during the last hour. DBA Oracle Compatibility: Oracle 11g Creator: Cyrille MODIANO – Montreal SELECT metric_name, ROUND(AVG(value),1) « Average Wait time % » FROM v$sysmetric_history WHERE metric_name = ‘Database Wait Time Ratio’ GROUP BY metric_name;

Better stats job

Oracle script for the Oracle DBA. A better stats job in 10g than the native job. — ################################################ — # Creator: Cyrille MODIANO (DBA Oracle in Montreal) — # Created: 2013/02/06 — # Name: create better gather stats job — ################################################ — # — # Compatible: 10g — # — ################################################ –disable the automatic statistics […]

Resource intensive queries

— ################################################ — # Creator: Vincent Fenoll, Oracle DBA in Montreal (Quebec) — # Created: 2011/12/23 — # Name: Resource intensive queries — ################################################ — # — # Compatible: Oracle 10g 11g — # — ################################################ — # — # List SQL statements, duration > xx seconds — # (here 30s) — # — ################################################ […]

Buffer cache advisor

— ################################################ — # Creator: Vincent Fenoll — # Created: 2011/07/27 — # Name: db_cache_advisor.sql — ################################################ — # — # Compatible: Oracle 9i 10g 11g — # — ################################################ — # — # Display database buffer cache advisor: useful — # to know the good size of the buffer cache — # — ################################################ […]