To check the database growth:
COLUMN month FORMAT a30 HEADING 'Month'
COLUMN growth FORMAT 999,999,999,999,999 HEADING 'Growth (bytes)'
SELECT
TO_CHAR(creation_time, 'RRRR-MM') month
, SUM(bytes) growth
FROM sys.v_$datafile
GROUP BY TO_CHAR(creation_time, 'RRRR-MM')
ORDER BY TO_CHAR(creation_time, 'RRRR-MM');
To count total number of sessions active:
select username,count(1) num_sessions from v$session group by Username order by 2 desc;
To check the total number of active sessions:
COLUMN username FORMAT A12
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
COLUMN OSUSER FORMAT A10
COLUMN MODULE FORMAT A40
SELECT s.sid,
s.serial#,
p.spid,
NVL(s.username, '(oracle)') AS username,
s.osuser,
s.status,
s.module,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time,
s.machine,
s.terminal
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
and s.status='ACTIVE'
--and s.username='FKHALID'
--and s.type<>'BACKGROUND'
ORDER BY s.username, s.osuser;
To get the sql text of all queries running under a user:
set linesize 1000
column osuser format a15
column status format a8
column user format a15
SELECT a.SID, a.serial#, a.username, a.osuser,a.status,
TO_CHAR (a.logon_time, 'dd-mm-yy hh24:mi:ss') Logon_Time, substr(b.sql_text,1,180) SQL_STATEMENT
FROM v$session a, v$sql b
WHERE a.sql_address = b.address AND a.username = UPPER ('&user')
and a.status='ACTIVE'
/
To get the Operating system ID of a user:
SELECT s.sid||','||s.serial# SIDs, s.osuser, s.status, s.schemaname
FROM v$session s, v$process p
WHERE s.paddr = p.addr AND p.spid = '&osid'
/
To get the metadata of a object:
SELECT DBMS_METADATA.get_ddl
(UPPER('&OBJECT_TYPE'),UPPER('&OBJECT_NAME'),UPPER('&OWNER')) from dual;
To get the list of all tables which should be placed in memory for logical reads:
drop table keep_tables purge;
create table keep_tables as
select
o.owner owner,
o.object_name object_name,
o.subobject_name subobject_name,
o.object_type object_type,
count(distinct file# || block#) num_blocks
from
dba_objects o,
v$bh bh
where
o.data_object_id = bh.objd
and
o.owner not in ('SYS','SYSTEM')
and
bh.status != 'free'
group by
o.owner,
o.object_name,
o.subobject_name,
o.object_type
order by
count(distinct file# || block#) desc
;
select
'alter '||s.segment_type||' '||t1.owner||'.'||s.segment_name||' storage (buffer_pool keep);'
from
keep_tables t1,
dba_segments s
where
s.segment_name = t1.object_name
and
s.owner = t1.owner
and
s.segment_type = t1.object_type
and
nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
and
buffer_pool <> 'KEEP'
and
object_type in ('TABLE','INDEX')
group by
s.segment_type,
t1.owner,
s.segment_name
having
(sum(num_blocks)/greatest(sum(blocks), .001))*100 > 80;
--Finally getting the list of objects in keep_pool
col OBJECT_NAME format a30
SELECT o.object_name, COUNT(1) number_of_blocks
FROM DBA_OBJECTS o, V$BH bh, dba_segments s
WHERE o.data_object_id = bh.objd
AND o.owner=s.owner and o.object_name=s.segment_name
AND s.buffer_pool='KEEP'
GROUP BY o.object_name
ORDER BY count(1) desc;
To get the list of all tables currently locked:
SELECT session_id SID, owner, SUBSTR (object_name, 1, 32) "OName",SUBSTR (oracle_username, 1, 12) "User",
object_type Object, process,
DECODE (locked_mode,
2, 'RS',
3, 'RE',
4, 'TS',
5, 'TE',
6, 'Exc',
'No Lock'
) "Mode"
FROM v$locked_object v, dba_objects d
WHERE v.object_id = d.object_id
ORDER BY oracle_username, object_name;
To get the list of all locks held on a object:
select * from V$ACCESS where upper(object)=upper('&OBJECT_NAME');
To get the list of rows locked:
select do.object_name,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ) as rowid_locked
from v$session s, dba_objects do
where sid in
(select s2.sid
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2)
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;
To kill user sessions:
SELECT 'alter system kill session ' || '''' || SID || ',' || serial# || ''''
|| ';'
FROM v$session
WHERE username = upper('&user');
To check a table size:
col segment_name format a30
select SEGMENT_name,partition_name,bytes/1024/1024/1024 "GB" from dba_segments
where owner=UPPER('&OWNER')
and segment_type like 'TABLE%' and segment_name =UPPER('&SEGMENT_NAME')
order by 2
;
To check the temporary tablespace usage:
column sid_serial format A12 heading "Sid-Serial"
column tablespace format a15 heading 'TBS Name'
column spid format 9,999 heading 'Unix ID'
column segblk# format 999,999,999 heading 'Block ID'
column size_mb format 999,990 heading "MBytes Used"
column username format a10 heading "DB User"
column osuser format a20 heading "OS User"
column program format a35
select s.sid ||','|| s.serial# sid_serial,c.SPID spid, s.status,
s.username, s.osuser, s.program, su.tablespace, round(((su.blocks * p.value)/(1024*1024))) size_mb
from v$session s, v$sort_usage su, v$process c, v$parameter p where p.name='db_block_size'
and s.saddr = su.session_addr and s.paddr = c.addr
order by 8 desc;
To check the undo tablespace usage:
col NAME format a15
col TABLESPACE_NAME format a10
col Programme format a20
col machine format a30
SELECT
a.usn,
e.sid,
e.serial#,
e.username,
e.program,
e.machine,
e.osuser,
a.name,
b.status,
c.tablespace_name,
d.addr
FROM
v$rollname a,
v$rollstat b,
dba_rollback_segs c,
v$transaction d,
v$session e
WHERE
a.usn=b.usn AND
a.name=c.segment_name AND
a.usn=d.xidusn AND
d.addr=e.taddr;
To check the database cache hit percentage:
SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'
/
To get the SGA statistics:
DECLARE
libcac number(10,2);
rowcac number(10,2);
bufcac number(10,2);
redlog number(10,2);
spsize number;
blkbuf number;
logbuf number;
BEGIN
select value into redlog from v$sysstat
where name = 'redo log space requests';
select 100*(sum(pins)-sum(reloads))/sum(pins) into libcac from v$librarycache;
select 100*(sum(gets)-sum(getmisses))/sum(gets) into rowcac from v$rowcache;
select 100*(cur.value + con.value - phys.value)/(cur.value + con.value) into bufcac
from v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
where cur.statistic# = ncu.statistic#
and ncu.name = 'db block gets'
and con.statistic# = nco.statistic#
and nco.name = 'consistent gets'
and phys.statistic# = nph.statistic#
and nph.name = 'physical reads';
select value into spsize from v$parameter where name = 'shared_pool_size';
select value into blkbuf from v$parameter where name = 'db_block_buffers';
select value into logbuf from v$parameter where name = 'log_buffer';
dbms_output.put_line(' SGA CACHE STATISTICS');
dbms_output.put_line(' *************************************');
dbms_output.put_line(' SQL Cache Hit rate = '||libcac);
dbms_output.put_line(' Dict Cache Hit rate = '||rowcac);
dbms_output.put_line(' Buffer Cache Hit rate = '||bufcac);
dbms_output.put_line(' Redo Log space requests = '||redlog);
dbms_output.put_line('');
dbms_output.put_line(' INIT.ORA SETTING');
dbms_output.put_line(' *************************************');
dbms_output.put_line(' Shared Pool Size = '||spsize||' Bytes');
dbms_output.put_line(' DB Block Buffer = '||blkbuf||' Blocks');
dbms_output.put_line(' Log Buffer = '||logbuf||' Bytes');
dbms_output.put_line('');
dbms_output.put_line('');
dbms_output.put_line(' *************************************');
if
libcac < 99 then dbms_output.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
END IF;
if
rowcac < 85 then dbms_output.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
END IF;
if
bufcac < 90 then dbms_output.put_line('*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.');
END IF;
if
redlog > 100 then dbms_output.put_line('*** HINT: Log Buffer value is rather low!');
END IF;
END;
/
To check the waits stats:
column object_name format a20
column owner format a20
column object_type format a20
column sample_time format a27
column sql_text format a50
set ver off
prompt Histogram of Buffer Busy Wait
select WAIT_TIME_MILLI, WAIT_COUNT, round((WAIT_COUNT/tot) * 100, 2) pct
from v$event_histogram,
(select sum(WAIT_COUNT) tot
from v$event_histogram
where event = 'buffer busy waits'
)
where event = 'buffer busy waits'
/
prompt top 5 buffer busy waits
select sql_text, seconds_wait, waits, p1 file#, p2 block#
from v$sql ,
( select * from
(select p1 , p2 , count(*) waits, sum(time_waited)/1000000 seconds_wait, sql_id
from V$ACTIVE_SESSION_HISTORY
where event= 'buffer busy waits'
group by p1, p2, sql_id
order by 4 desc)
where rownum < 6
) a
where a.sql_id = v$sql.sql_id(+)
/
column file# new_value fv
column block# new_value bv
Prompt top waited on block information
select OBJECT_NAME, OBJECT_TYPE, OWNER,
seconds_wait, waits, p1 file#, p2 block#
from dba_objects ,
( select * from
(select p1 , p2 , count(*) waits, sum(time_waited)/1000000 seconds_wait,
CURRENT_OBJ#
from V$ACTIVE_SESSION_HISTORY
where event= 'buffer busy waits'
group by p1, p2, CURRENT_OBJ#
order by 4 desc)
where rownum < 2
) a
where current_obj# = object_id
/
prompt Times of Break Down Of top waited on Block
select sample_time, TIME_WAITED/1000000 seconds_wait, sql_text
from V$ACTIVE_SESSION_HISTORY outer left join v$sql using(sql_id)
where event= 'buffer busy waits'
and CURRENT_FILE# = nvl(to_number('&fv'), -1)
and CURRENT_BLOCK# = nvl(to_number('&bv'), -1)
order by 1
/
set ver on
To analyze a table:
begin
dbms_stats.gather_table_stats(OWNNAME=>'&SCHEMA_NAME',tabname=>'&TABLE_NAME',cascade=>TRUE,degree=>16,method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO,estimate_percent=>&ESTIMATE_PERCENTAGE,granularity=>'ALL');
end;
/
--or
begin
dbms_stats.gather_table_stats(OWNNAME=>'&SCHEMA_NAME',tabname=>'&TABLE_NAME',cascade=>TRUE,degree=>16,method_opt=>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY',estimate_percent=>&ESTIMATE_PERCENTAGE,granularity=>'ALL');
end;
/
To check if any datapump job is being executed:
SELECT
owner_name
,job_name
,operation
,job_mode
,state
,degree
,attached_sessions
FROM dba_datapump_jobs;
To get the constraints list:
#set linesize 1000
#set verify off
#set pagesize 40
#break on owner on table_name on constraint_name on r_constraint_name
column owner format a10
column r_owner format a10
column column_name format a15
column tt noprint
column position heading P format 9
column table_name format a20
column r_table_name format a15
column constraint_name format a15
column r_constraint_name format a15
select
a.tt,
a.owner,
b.table_name,
a.constraint_name,
b.column_name,
b.position,
a.r_constraint_name,
c.column_name,
c.position,
c.table_name r_table_name,
a.r_owner
from
(select
owner,
constraint_name,
r_constraint_name,
r_owner,1 tt
from
dba_constraints
where
owner=upper('&&owner')
and table_name=upper('&&table_name')
and constraint_type!='C'
union
select
owner,
constraint_name,
r_constraint_name,
r_owner,2
from
dba_constraints
where
(r_constraint_name,r_owner) in
(select
constraint_name,
owner
from
dba_constraints
where
owner=upper('&owner')
and table_name=upper('&table_name'))
) a,
dba_cons_columns b,
dba_cons_columns c
where
b.constraint_name=a.constraint_name
and b.owner=a.owner
and c.constraint_name=a.r_constraint_name
and c.owner=a.r_owner
and b.position=c.position
order by 1,2,3,4,5
/
set verify on
clear columns
clear breaks
undef owner
undef table_name
To get the tablespaces sizes:
set linesize 128 echo off feedback on heading on verify off pagesize 35
col megs_alloc format 999,999,999 heading "MB ALLOC"
col megs_free format 999,999,999 heading "MB FREE"
col megs_used format 999,999,999 heading "MB USED"
col pct_free format 999 heading "% FREE"
col pct_used format 999 heading "% USED"
break on report skip 1
compute sum label "TOTAL" of megs_alloc on report
compute sum of megs_free on report
compute sum of megs_used on report
select c.tablespace_name,
round(a.bytes/1048576,2) megs_alloc,
round(b.bytes/1048576,2) megs_free,
round((a.bytes-b.bytes)/1048576,2) megs_used,
round(b.bytes/a.bytes * 100,2) pct_free,
round((a.bytes-b.bytes)/a.bytes,2) * 100 pct_used
from (select tablespace_name,
sum(a.bytes) bytes,
min(a.bytes) minbytes,
max(a.bytes) maxbytes
from sys.dba_data_files a
group by tablespace_name) a,
(select a.tablespace_name,
nvl(sum(b.bytes),0) bytes
from sys.dba_data_files a,
sys.dba_free_space b
where a.tablespace_name = b.tablespace_name (+)
and a.file_id = b.file_id (+)
group by a.tablespace_name) b,
sys.dba_tablespaces c
where a.tablespace_name = b.tablespace_name(+)
and a.tablespace_name = c.tablespace_name
union
select tablespace_name, round((sum(bytes_used) + sum(bytes_free)) / 1048576,2) meg_alloc,
round(sum(bytes_free) / 1048576,2) megs_free,
round(sum(bytes_used) / 1048576,2) megs_used,
round(sum(bytes_free) / (sum(bytes_used) + sum(bytes_free)) * 100,2) pct_free,
round(sum(bytes_used) / (sum(bytes_used) + sum(bytes_free)) * 100,2) pct_used
from v$temp_space_header
group by tablespace_name
order by tablespace_name;
1 comment:
Very Informative...
Keep sharing...
Thanks
--
Mirza
Post a Comment