Monitor and instrument long runing query

With this query (compatible Oracle RAC) using the v$session_longops view, you can view any SQL statement that executes for more than 6 seconds. select s.inst_id, SQL.SQL_TEXT as “OPERATION”, to_char(START_TIME, ‘dd/mm/yyyy hh24:mi:ss’) Start_Time, to_char(LAST_UPDATE_TIME, ‘dd/mm/yyyy hh24:mi:ss’) Last_Update_Time, round(TIME_REMAINING/60,1) as “MINUTES_REMAINING”, round((SOFAR/TOTALWORK) * 100,2) as PCT_DONE from gv$session s, gv$sqlarea sql, gv$session_longops op where s.sid=op.sid and s.sql_id […]

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 […]

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) — # — ################################################ […]

Display Binds variables

— ############################################################ — # Creator: Vincent Fenoll — # Created: 2011/03/10 — # Name: Display Binds variables — ############################################################ — # — # Compatible: Oracle 10g 11g — # — ############################################################ — # — # How to display values of binds variables — # — ############################################################ set line 150 pagesize 80 col name for a50 […]