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 […]
Category: Tuning
Latch contention
Library cache latch contention is typically caused by NOT using bind variables. It is due to excessive parsing of statements. Cache buffers chain latches contention is typically caused by concurrent access to a very “hot” block and the most common type of such a hot block is an index root or branch block (since any […]
SQL Informations
The Oracle database administrator knows the pid of an Oracle resource-intensive process. Now we want to gather some informations on the SQL Statement (SQL_ID, username, program, terminal…) before running an explain plan. SELECT ‘USERNAME : ‘ || s.username || CHR (10) || ‘SCHEMA : ‘ || s.schemaname || CHR (10) || ‘OSUSER : ‘ || […]
I/O Bottlenecks
Our Oracle databases in Montreal are sometimes experiencing performance problems and the Oracle DBA wants to know if there are I/O Bottlenecks. If working with a zone (a virtual operating system abstraction), first you will have to find the disk: $ zpool status pool: rpool state: ONLINE scan: none requested config: NAME STATE READ […]
CPU Issues
The DBAs want to know if there are performance issues with cpu in an Oracle database for example: First: $ ps -e -o pcpu,pid,user,tty,args | sort -n -k 1 -r | head %CPU PID USER TT COMMAND 55.3 934757 oracle ? oracleGdefgP (LOCAL=NO) 10.1 935480 oracle ? oracleGdefgP (LOCAL=NO) 0.1 935247 oracle ? oracleGdefgP (LOCAL=NO) […]