Thursday, March 24, 2011

Queries : Memory, Free Space, Size, Temp Files, Control Files etc..

Database block size:

select to_number(value) "Block size in bytes"
from   sys.v_$parameter
where  name = 'db_block_size';


Max number of possible extents:

select to_number(value)/16-7 "MaxExtents"
from   sys.v_$parameter
where  name = 'db_block_size';



Min extent size:

select to_number(a.value) * to_number(b.value) / 1024 "Min extent size in K"
from   sys.v_$parameter a, sys.v_$parameter b
where  a.name = 'db_block_size'
and  b.name = 'db_file_multiblock_read_count';

List objects in the SYSTEM tablespace that doesn't belong to SYS or SYSTEM

select * from sys.dba_segments
where owner not in ('PUBLIC', 'SYS', 'SYSTEM')
and tablespace_name = 'SYSTEM';

Reports information about your current database context

select 'User: '|| user || ' on database ' || global_name,
' (term='||USERENV('TERMINAL')||
', audsid='||USERENV('SESSIONID')||')' as MYCONTEXT
from global_name;


Reports free memory available in the SGA

select name,
sgasize/1024/1024 "Allocated (M)",
bytes/1024 "Free (K)",
round(bytes/sgasize*100, 2) "% Free"
from (select sum(bytes) sgasize from sys.v_$sgastat) s, 
sys.v_$sgastat f
where f.name = 'free memory';
 

Measure the Buffer Cache Hit Ratio

Get initial Buffer Hit Ratio reading

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';

******************************

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';


Database users with deadly roles assigned to them.

select grantee, granted_role, admin_option
from sys.dba_role_privs
where granted_role in ('DBA', 'AQ_ADMINISTRATOR_ROLE',
'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
'OEM_MONITOR')
and grantee not in ('SYS', 'SYSTEM', 'OUTLN', 
'AQ_ADMINISTRATOR_ROLE',
'DBA', 'EXP_FULL_DATABASE', 
'IMP_FULL_DATABASE',
'OEM_MONITOR', 
'CTXSYS', 'DBSNMP', 'IFSSYS',
'IFSSYS$CM', 'MDSYS', 'ORDPLUGINS', 
'ORDSYS',
'TIMESERIES_DBA');

Database users with deadly system privilages assigned to them.

select grantee, privilege, admin_option
from sys.dba_sys_privs
where (privilege like '% ANY %'
or privilege in ('BECOME USER', 'UNLIMITED TABLESPACE')
or admin_option = 'YES')
and grantee not in ('SYS', 'SYSTEM', 'OUTLN', 
'AQ_ADMINISTRATOR_ROLE',
'DBA', 'EXP_FULL_DATABASE', 
'IMP_FULL_DATABASE',
'OEM_MONITOR', 'CTXSYS', 
'DBSNMP', 'IFSSYS',
'IFSSYS$CM', 'MDSYS', 
'ORDPLUGINS', 'ORDSYS',
'TIMESERIES_DBA');
 


Allocated, Used & Free space within datafiles

SELECT   SUBSTR (df.NAME, 1, 40) file_name, 
df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;


Show Used/free space by tablespace name

SELECT Total.name "Tablespace Name",
nvl(Free_space, 0) Free_space,
nvl(total_space-Free_space, 0) Used_space,
total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name;


Total Tablespace size


SELECT space.tablespace_name, space.total_space, free.total_free,
ROUND(free.total_free/space.total_space*100) as pct_free,
ROUND((space.total_space-free.total_free),2) as total_used,
ROUND((space.total_space-free.total_free)/space.total_space*100) as pct_used,
free.max_free, next.max_next_extent
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 total_space
FROM dba_data_files
GROUP BY tablespace_name) space,
(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) total_free, ROUND(MAX(bytes)/1024/1024,2) max_free
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name, ROUND(MAX(next_extent)/1024/1024,2) max_next_extent FROM dba_segments
GROUP BY tablespace_name) NEXT
WHERE space.tablespace_name = free.tablespace_name (+)
AND space.tablespace_name = next.tablespace_name (+)
AND (ROUND(free.total_free/space.total_space*100)<> free.max_free)
order by pct_used desc;


Ivestigating undo-tablespace in EBS R12

SELECT version FROM v$timezone_file;

select count(status) from dba_undo_extents where status = 'ACTIVE';

select count(status) from dba_undo_extents where status = 'UNEXPIRED';

select (sum(bytes)/(1024*1024)) "SIZE in MB",tablespace_name from dba_free_space group by tablespace_name;



Last Analyzed

select max(last_analyzed) from dba_tables;


Listing Memory Used By All Sessions

select se.sid,n.name,
max(se.value) maxmem
from v$sesstat se,
v$statname n
where n.statistic# = se.statistic#
and n.name in ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
group by n.name,se.sid
order by 3  ;


INDEX :-

select segment_name, owner, extents, max_extents
from dba_segments
where segment_type = 'INDEX' and
(extents +1) >= max_extents;  

 

SESSION WAITS

SELECT NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM v$session_wait sw,
v$session s
WHERE s.sid = sw.sid
ORDER BY sw.seconds_in_wait DESC; 


Find type of objects available in a tablespace

select ds.segment_name,do.object_type,do.status,ds.tablespace_name from dba_segments ds,dba_objects do where ds.tablespace_name='TOOLS';

Temp tablespace usage information

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

To count no. of segments in each t.s.

select tablespace_name,count(*) from dba_segments group by tablespace_name;

Database growth per month for last year

select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024 "Growth in Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month');
 
 
 
To select the username and the process status


select a.requested_start_date,a.last_update_date,a.status_code,b.user_name
from fnd_concurrent_requests a,fnd_user b 
where a.requested_by = b.user_id and a.request_id = 677224

select a.requested_start_date,a.last_update_date,a.status_code,b.user_name
,a.argument_text 
from fnd_concurrent_requests a,fnd_user b 
where a.requested_by = b.user_id and a.request_id = 677224
 
 
To select the username,process,status,Terminal name using SID

select a.status,p.spid, a.sid, a.serial#, a.username, a.terminal,a.osuser
,c.Consistent_Gets, c.Block_Gets, c.Physical_Reads,
(100*(c.Consistent_Gets+c.Block_Gets-c.Physical_Reads)/
(c.Consistent_Gets+c.Block_Gets)) HitRatio, c.Physical_Reads, b.sql_text
from v$session a, v$sqlarea b, V$SESS_IO c,v$process p
where a.sql_hash_value = b.hash_value
and a.SID = c.SID
and p.addr = a.paddr
and (c.Consistent_Gets+c.Block_Gets)>0
and a.Username is not null
Order By a.status asc, c.Consistent_Gets desc , c.Physical_Reads desc;
To see the currently updated archive log files
SQL>select name from v$archived_log where trunc(completion_time) >= trunc(sysdate)-5;



To find the BDUMP,UDUMP directory

select value from v$parameter where name = 'background_dump_dest';
select value from v$parameter where name = 'user_dump_dest';
select value from v$parameter 
where name in ('background_dump_dest','user_dump_dest', 'log_archive_dest');
 
Prompt Showing what sql statement is doing
 
select a.sid, a.value session_cpu, c.physical_reads,
c.consistent_gets,d.event,d.seconds_in_wait
from v$sesstat a,v$statname b, v$sess_io c, v$session_wait d
where a.sid= 14
and b.name = 'CPU used by this session'
and a.statistic# = b.statistic#
and a.sid=c.sid
and a.sid=d.sid;
 
 Check all active processes, the latest SQL, and the SQL hit ratio

select a.status, a.sid, a.serial#, a.username, a.terminal,
a.osuser, c.Consistent_Gets, c.Block_Gets, c.Physical_Reads,
(100*(c.Consistent_Gets+c.Block_Gets-c.Physical_Reads)/
(c.Consistent_Gets+c.Block_Gets)) HitRatio, 
c.Physical_Reads, b.sql_text
from v$session a, v$sqlarea b, V$SESS_IO c
where a.sql_hash_value = b.hash_value
and a.SID = c.SID
and (c.Consistent_Gets+c.Block_Gets)>0
and a.Username is not null
and a.status = 'ACTIVE'
Order By a.status asc, c.Consistent_Gets desc , c.Physical_Reads desc;
Monitoring Oracle processes

select p.spid "Thread ID", b.name "Background Process", s.username "User Name",
s.osuser "OS User", s.status "STATUS", s.sid "Session ID",
s.serial# "Serial No.",
s.program "OS Program"
from v$process p, v$bgprocess b, v$session s
where s.paddr = p.adr and b.paddr(+) = p.addr
order by s.status,1;
  
 
Query to find the ATG rollup Patch level in your application (11i).

connect as apps:

SELECT (bug_number),
decode((bug_number),
'3438354','ATG_PF.H',
'4017300','ATG_PF.H RUP 1',
'4125550','ATG_PF.H RUP 2',
'4334965','ATG_PF.H RUP 3',
'4676589','ATG_PF.H RUP 4',
'5473858','ATG_PF.H RUP 5',
'5903765','ATG_PF.H RUP 6',
'6241631','ATG_PF.H RUP 7')
FROM ad_bugs
WHERE bug_number IN
('3384350',
'3438354',
'4017300',
'4125550',
'4334965',
'4676589',
'5473858',
'5903765',
'6241631')
order by bug_number desc;  


To find RUP Level in R12 : -


select bug_number, creation_date
from ad_bugs
where bug_number in ('6022657','6266113','6728000')
ORDER BY 2;


To Check Maintenance Family Pack Level

select patch_level from fnd_product_installations
where application_id = 275;


Query to find the Apps Version : -

select release_name from fnd_product_groups;


Which FND_USER is locking that table

The column named "module" will tell you the name of the Form Function
or the Concurrent Program Short name which has aquired a lock onto that table.

SELECT c.owner ,c.object_name,c.object_type,
       fu.user_name locking_fnd_user_name
      ,fl.start_time locking_fnd_user_login_time,
      vs.module,vs.machine ,vs.osuser
      ,vlocked.oracle_username,vs.sid,vp.pid
      ,vp.spid AS os_process,vs.serial# ,vs.status ,vs.saddr
      ,vs.audsid ,vs.process

FROM fnd_logins fl ,fnd_user fu ,v$locked_object vlocked
    ,v$process vp ,v$session vs,dba_objects c

WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'
AND nvl(vs.status ,'XX') != 'KILLED';


Security related database initialization parameters and password file users.

select name || '=' || value "PARAMTER"
from   sys.v_$parameter
where  name in ('remote_login_passwordfile', 'remote_os_authent',
                'os_authent_prefix', 'dblink_encrypt_login',
                'audit_trail', 'transaction_auditing');

List security related profile information

select profile, resource_name, limit 
from dba_profiles
where resource_name like '%PASSWORD%'
or resource_name like '%LOGIN%';
 
 

To find space used by a database user

Query to find space used by a database user. Following query can be used to know the space used by the logged in user in MBs:

SELECT sum(bytes)/1024/1024 user_size FROM user_segments;

Query to find the space occupied by all the users in a database. This requires access to dba_segments table:

SELECT owner, sum(bytes)/1024/1024 total_size FROM dba_segments
GROUP BY owner ORDER BY total_size DESC;

Total space occupied by all users:

SELECT sum(bytes)/1024/1024 total_size FROM dba_segments;

To find Database Size

The database mainly comprises of datafiles, temp files and redo log files.
The biggest portion of a database’s size comes from the datafiles.

To find out how many megabytes are allocated to all datafiles:
SELECT sum(bytes)/1024/1024 data_size FROM dba_data_files;

To get the size of all TEMP files:
SELECT nvl(sum(bytes),0)/1024/1024 temp_size FROM dba_temp_files;

To get the size of the on-line redo-logs:
SELECT sum(bytes)/1024/1024 redo_size FROM sys.v_$log;

Finally, summing up the three above, total database size can be found:
SELECT (dsize.data_size + tsize.temp_size + rsize.redo_size)/1024/1024 "total_size"
FROM (SELECT sum(bytes) data_size
FROM dba_data_files ) dsize,
(SELECT nvl(sum(bytes),0) temp_size
FROM dba_temp_files ) tsize,
(SELECT sum(bytes) redo_size
FROM sys.v_$log ) rsize;
 

To find free space in temporary tablesapce:

SELECT tablespace_name,SUM(bytes_used),SUM(bytes_free) FROM V$temp_space_header GROUP BY tablespace_name;

To find tablespace free space in a database

SELECT a.tablespace_name, a.file_name, a.bytes allocated_bytes,
b.free_bytes
FROM dba_data_files a,
(SELECT file_id, SUM(bytes) free_bytes
FROM dba_free_space b GROUP BY file_id) b
WHERE a.file_id=b.file_id
and a.tablespace_name='SYSTEM'
ORDER BY a.tablespace_name;


Tablespaces that have less then 90% free space

select tbs.tablespace_name,
tot.bytes/(1024*1024) "Total Space in MB",
round(tot.bytes/(1024*1024)- sum(nvl(fre.bytes,0))/(1024*1024),2) "Used in MB",
round(sum(nvl(fre.bytes,0))/(1024*1024),2) "Free in MB",
round((1-sum(nvl(fre.bytes,0))/tot.bytes)*100,2) Pct,
decode(
greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90),
90, '', '*'
) Pct_warn
from dba_free_space fre,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) tot,
dba_tablespaces tbs
where tot.tablespace_name = tbs.tablespace_name
and fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/(1024*1024), tot.bytes
order by 5 desc, 1 ;
 


Problem tablespaces

SELECT space.tablespace_name, space.total_space, free.total_free,
ROUND(free.total_free/space.total_space*100) as pct_free,
ROUND((space.total_space-free.total_free),2) as total_used,
ROUND((space.total_space-free.total_free)/space.total_space*100) as pct_used,
free.max_free, next.max_next_extent
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 total_space
FROM dba_data_files
GROUP BY tablespace_name) space,
(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) total_free, ROUND(MAX(bytes)/1024/1024,2) max_free
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name, ROUND(MAX(next_extent)/1024/1024,2) max_next_extent FROM dba_segments
GROUP BY tablespace_name) NEXT
WHERE space.tablespace_name = free.tablespace_name (+)
AND space.tablespace_name = next.tablespace_name (+)
AND (ROUND(free.total_free/space.total_space*100)<> free.max_free)
order by pct_used desc ;


Number of Disk Sorts vs Memory Sorts 

select 'INIT.ORA sort_area_size: 'value
from v$parameter
where name like 'sort_area_size' ;

select a.name, value
from v$statname a, v$sysstat
where a.statistic# = v$sysstat.statistic#
and a.name in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)') ;



Jobs Currently running in the local database.

select
djr.sid sess,
djr.job jid,
dj.log_user subu,
dj.priv_user secd,
dj.what proc,
to_char(djr.last_date,'MM/DD') lsd,
substr(djr.last_sec,1,5) lst,
to_char(djr.this_date,'MM/DD') nrd,
substr(djr.this_sec,1,5) nrt,
djr.failures fail
from
sys.dba_jobs dj,
sys.dba_jobs_running djr
where
djr.job = dj.job;


Jobs that have been submitted to run in the local database job queue
select
job jid,
log_user subu,
priv_user secd,
what proc,
to_char(last_date,'MM/DD') lsd,
substr(last_sec,1,5) lst,
to_char(next_date,'MM/DD') nrd,
substr(next_sec,1,5) nrt,
failures fail,
decode(broken,'Y','N','Y') ok
from
sys.dba_jobs  ;

To find Table size in a database


select sum(BYTES/1024/1024) as TOTAL_GIG from user_segments where
SEGMENT_NAME = 'TABLE_NAME';

Note: Need to execute as owner of the table. (OR)
select sum(BYTES/1024/1024) as TOTAL_GIG from dba_segments where SEGMENT_NAME='FND_TS_MIG_CMDS';


=== Temp Usage ===

select sum(blocks)/1024*8 "Size in MB" FROM v$sort_usage;
 
SELECT ss.sid, sum(st.blocks)/1024*8 
FROM v$sort_usage st, v$session ss 
where ss.saddr=st.session_addr 
group by ss.sid 
having sum(st.blocks)/1024*8 > 100 order by 2;


SELECT  /*+ RULE */ s.username, s.osuser, s.sid, 
NVL(s.machine,'N/A'), 
NVL(s.module,'N/A'), NVL(s.action,'N/A'), 
NVL(s.program,'N/A'), 
s.status ||' for '||LPAD(((last_call_et/60)-mod((last_call_et/60),60))/60,2,'0') ||':'||LPAD(ROUND(mod((last_call_et/60),60)),2,'0') ||' Hr',
u.tablespace, u.contents, u.extents, 
round((u.blocks*8)/1024),s.sql_address, s.sql_hash_value
FROM    v$session s, v$sort_usage u
WHERE   s.saddr    = u.session_addr
AND     u.contents = 'TEMPORARY'
AND     s.audsid != USERENV('sessionid')
AND    (u.blocks*8)/1024 >= 1000
 
ORDER   BY 1,2,3,4,5 Desc;


=== High Redo ===

SELECT s.inst_id,s.sid, s.serial#, s.username, 
s.program, i.block_changes
FROM gv$session s, gv$sess_io i
WHERE s.sid = i.sid
AND i.block_changes > 10000000
ORDER BY 6 desc, 1, 2, 3, 4;


=== Rollback Used ===
                                 
SELECT rn.name, ROUND(rs.rssize/1024/1024),
s.username, s.osuser, s.sid, NVL(s.machine,'N/A'),
NVL(s.module,'N/A'), NVL(s.action,'N/A'), 
NVL(s.program,'N/A'),
s.sql_address, s.sql_hash_value, p.spid,
s.status ||' for '||LPAD(((last_call_et/60)-mod((last_call_et/60),60))/60,2,'0') ||':'||LPAD(ROUND(mod((last_call_et/60),60)),2,'0') ||' Hr' ,
round(ceil((t.used_ublk*8)/1024),1)
FROM   v$rollname rn, v$rollstat rs,
v$session s, v$transaction t, v$process p
WHERE  rn.usn = rs.usn
AND    round((t.used_ublk*8)/1024) >= 1000
AND    rs.usn = t.xidusn
AND    s.sid = p.pid (+)
AND    s.taddr = t.addr
ORDER  BY 2 desc, s.sid ,s.status;


=== Roll back segement Information ====

select tablespace_name, status segment_status, 
count(extent_id) "Extent Count", sum(blocks) "Total Blocks",
sum(blocks)*8/(1024*1024) "Total Space in GB" 
from dba_undo_extents 
where tablespace_name like '%UNDO%'
group by tablespace_name, status;


=== Shared Pool Usage ===

SELECT INST_ID,ROUND(bytes/1024/1024, 2)||' MB' 
FROM gv$sgastat  
WHERE name='free memory' AND pool='shared pool';


=== Archive Generation for last 5 hours ===

SELECT TO_CHAR(first_time, 'DD-MM-YY') AS Day,
TO_CHAR(first_time, 'HH24') AS Hour,COUNT(*)  
FROM v$log_history 
WHERE TO_CHAR(first_time, 'DD-MM-YY') = TO_CHAR(sysdate, 'DD-MM-YY') 
AND  TO_CHAR(first_time, 'HH24') >= TO_CHAR(sysdate, 'HH24') - 5 
GROUP BY  TO_CHAR(first_time, 'DD-MM-YY'), 
TO_CHAR(first_time, 'HH24') ORDER BY 2;


=== High Memory ===

select s.inst_id, s.sid, name, 
round(value/1024/1024) Mem_in_mb, p.*
from gv$sesstat s, v$statname n, gv$session p
where n.statistic# = s.statistic#
and s.inst_id = p.inst_id
and s.sid = p.sid
and name like '%pga memory%'
and value > 1*1024*1024*512; 


=== Performance ===

select sql_hash_value, count(*) 
from v$session 
where event like 'enq%' 
group by sql_hash_value;


select sql_hash_value,username,osuser, count(*) 
from v$session 
where event like 'enq%' 
and SQL_HASH_VALUE='&event' 
group by sql_hash_value,username,osuser;


select sql_text 
from v$sqlarea 
where hash_value = '&hash_value';


select s1.sid,FETCHES,ROWS_PROCESSED 
from v$sql s,v$session s1 
where s.HASH_VALUE=s1.SQL_HASH_VALUE 
and s1.sid=4885;


select s.sid,s.serial#,s.status,s.username,s.module,
s.osuser,x.event 
from v$session s,v$session_wait x 
where s.paddr in ( select addr 
from v$process 
where spid in (2340,23869,13827,18261,14880,2381))
and x.sid=s.sid;



select s.sid,s.serial#,s.status,s.username,
s.module,s.osuser,x.event 
from v$session s,v$session_wait x
where x.sid=s.sid and x.event like '&event_name';



=== Other Script to update the daily report ===

select status,count(1) from gv$session group by status; 

select count(1) from dba_tables where logging='NO';

select distinct status,count(1) from dba_indexes group by status;

select count(1) from dba_objects where status='INVALID';






Regards,
Sukhwinder Singh

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.