Maximum allowed connections to database
SELECT name, value
FROM v$parameter
WHERE name = 'sessions'
Active connections
ACTIVE connections are those currently executing some type of SQL.
SELECT username, status, machine, COUNT(1)
FROM v$session
GROUP BY username, status, machine
ORDER BY 1, 2, 3
SQL queries currently being executed
SELECT a.username, b.address, b.hash_value, b.child_number, b.plan_hash_value, b.sql_text
FROM v$session a, v$sql b
WHERE a.sql_address = b.address
SQL locks
-- ----------------------------------------------------------------------
-- ----- 1. See if somebody is locked
-- ----------------------------------------------------------------------
SELECT SUBSTR(a.username,1,10) username, a.sid, c.sql_text
FROM v$session a, v$sqltext c
WHERE a.lockwait IS NOT NULL
AND a.sql_address = c.address
-- ----------------------------------------------------------------------
-- ----- 2. See who is locking the same object (get User_Session_Id from SQL statement above)
-- ----------------------------------------------------------------------
SELECT SUBSTR(a.username,1,10) username, a.serial#, c.object_id, c.os_user_name, SUBSTR(a.machine,1,20) machine
FROM v$session a, v$lock b, v$locked_object c
WHERE a.sid = b.sid
AND c.session_id <> &User_Session_Id
AND c.object_id = b.id1
AND c.object_id IN (SELECT d.id1 FROM v$lock d WHERE sid = &User_Session_Id)
AND a.sid = c.session_id
ORDER BY a.username
Performance tuning - Buffer cache hit ratio
If the hit ratio is below 90%, then it would be advisable to increase DB_BLOCK_BUFFERS until you obtain a hit ration above 90%.
SELECT ROUND(((1-(SUM(DECODE(name,'physical reads', value,0))/(SUM(DECODE(name, 'db block gets', value,0)) +
(SUM(DECODE(name, 'consistent gets', value, 0))))))*100),2) || '%' "Buffer Cache Hit Ratio"
FROM v$sysstat
Performance tuning - Redo log space
The value of "redo log space requests" should be near 0. If this value increments consistently, processes have had to wait for space in the buffer. This may be caused by the log buffer being too small, or it could be caused by the checkpointing or log switching. The is no danger in making it larger.
SELECT name, value
FROM v$sysstat
WHERE name = 'redo log space requests'
Performance tuning - Waits
The waits should be closer to 0. Increase number of rollback segments.
SELECT *
FROM v$waitstat
SELECT name, waits
FROM v$rollstat s, v$rollname n
WHERE s.usn = n.usn
SELECT s.sid, s.serial#, s.username, r.name "ROLLBACK"
FROM v$session s, v$transaction t, v$rollname r
WHERE r.name IN (SELECT segment_name FROM dba_rollback_segs)
AND s.taddr = r.addr
AND t.xidusn = r.usn
ORDER BY rollback
Performance tuning - Log buffer
Consider increasing LOG_BUFFER.
SELECT name, value
FROM v$sysstat
WHERE name = 'redo log space requests';
Performance tuning - DB block buffer
The performance benefit you see is the reduced physical I/O from index scans, since you can grab more of the index in a single I/O. Increasing DB_BLOCK_SIZE may have negative effects in an OLTP environment with fixed SGA size. Check the database buffer cache hit ratio first. You want it to be above 90%. If it is below 90%, then increase DB_BLOCK_BUFFER.
SELECT ROUND(((1-(SUM(DECODE(name,'physical reads', value,0))/(SUM(DECODE(name, 'db block gets', value,0)) +
(SUM(DECODE(name, 'consistent gets', value, 0))))))*100),2) || '%' "Buffer Cache Hit Ratio"
FROM v$sysstat
SELECT name, (physical_reads / (consistent_gets + db_block_gets ) ) "HIT_RATIO"
FROM v$buffer_pool_statistics
WHERE (consistent_gets + db_block_gets) != 0
def p = SUM(DECODE(statistic#,39,value,0))
def l = SUM(DECODE(statistic#,37,value,38,value,0))
def h = (1-(&p/&l))*100
SELECT &p physical, &l logical, &h "hit rate",
DECODE(sign(&h-70),-1,'increase db_block_buffer', DECODE(sign(&h-95),1,'decrease db_block_buffer','okay')) comments
FROM v$sysstat
def g = SUM(gets)
def m = SUM(getmisses)
def mi = &m/&g*100
SELECT &g gets,&m getmisses, &mi "miss rate",
DECODE(sign(&mi-10),1,'increase shared_pool_size','okay') comments
FROM v$rowcache
Physical reads/writes on datafiles
SELECT name, phyrds, phywrts
FROM v$datafile df, v$filestat fs
WHERE df.file# = fs.file#
ORDER BY phyrds, phywrts
|
.com