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 […]
Category: Resources contention
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) […]
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;