Select * from cat;
2)To generate a KEEP for each package currently in the shared pool:
SELECT
'EXECUTE DBMS_SHARED_POOL.KEEP('''||name||''');'
FROM v$db_object_cache
WHERE type='PACKAGE';
SELECT DISTINCT
'EXECUTE DBMS_SHARED_POOL.KEEP('''||name||''');'
FROM user_source
WHERE type='PACKAGE';
SELECT DISTINCT
'EXECUTE DBMS_SHARED_POOL.KEEP('''||object_name||''');'
FROM user_objects
WHERE object_type='PACKAGE';
3) To match a user trace file to the user session that generated it.
SELECT s.username, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND p.background is null;
4)To know which tables have rows in them that are
either chained or migrat ed.
select table_name, chain_cnt
from dba_tables
where owner = ’SCOTT’
and chain_cnt !=0;
5)Show all tablespaces used by a user
SELECT s.username, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND p.background is null;
4)To know which tables have rows in them that are
either chained or migrat ed.
select table_name, chain_cnt
from dba_tables
where owner = ’SCOTT’
and chain_cnt !=0;
5)Show all tablespaces used by a user
select table
space_name, ceil(sum(bytes) / 1024 / 1024) "MB"
from dba_extents
where owner like '&user_id'
group by tablespace_name
order by tablespace_name;
6) Show table name, number of rows, block, empty blocks, avg row length
select table_name, num_rows, blocks, empty_blocks, avg_row_len
from dba_tables where
owner = 'SCOTT';
7)To see if any space is being wasted in the index
SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100
“Wasted Space”
FROM index_stats
WHERE name = ‘EMPLOYEE_LAST_NAME_IDX’;
8)To calculate the Data Dictionary Cache hit ratio
SELECT 1 - (SUM(getmisses)/SUM(gets))
"Data Dictionary Hit Ratio"
FROM v$rowcache;
9)To determine the current size of the Shared Pool
SELECT pool, sum(bytes) "SIZE"
FROM v$sgastat
WHERE pool = ’shared pool’
GROUP BY pool;
10)Calculating the Database Buffer Cache hit ratio using
V$SYSSTAT
11)To calculate hit ratios for each of the individual
Buffer Pools
SELECT name "Buffer Pool",
1-(physical_reads / (db_block_gets + consistent_gets))
"Buffer Pool Hit Ratio" FROM v$buffer_pool_statistics
ORDER BY name;
12)To determine which tables are cached
SELECT owner, table_name
FROM dba_tables
WHERE LTRIM(cache) = ’Y’;
13)To calculate a Redo Log Buffer Retry Ratio
SELECT retries.value/entries.value
"Redo Log Buffer Retry Ratio"
FROM v$sysstat retries, v$sysstat entries
WHERE retries.name='redo buffer allocation retries'
AND entries.name='redo entries';
14)Checkpoint activity shown in V$SYSTEM_EVENT
select event,total_waits,average_wait from v$system_event
where event like '%check%' or event like '%log file switch%';
15)Checkpoint activity shown in V$SYSSTAT
select name,value from v$sysstat where name
like '%background checkpoint%';
16) Redo Log activity shown in V$SYSTEM_EVENT
select EVENT , TOTAL_WAITS ,AVERAGE_WAIT
from v$system_event
where event='log file parallel write'
or event='log file switch completion';
17)V$LOCK to Monitor Lock Contention
SELECT s.username,
DECODE(l.type,'TM','TABLE LOCK','TX','ROW LOCK', NULL) "LOCK LEVEL",
o.owner, o.object_name, o.object_type
FROM v$session s, v$lock l, dba_objects o
WHERE s.sid = l.sid
AND o.object_id = l.id1
AND s.username IS NOT NULL;
18) Identify the application user that is blocking other users
SELECT s.username
FROM dba_blockers db, v$session s
WHERE db.holding_session = s.sid;
No comments:
Post a Comment