DBA common SQL statements (1) - overview information

Check database essential information

select * from v$version;
select name ,open_mode,log_mode from v$database;
select instance_number,instance_name ,status from gv$instance;
show parameter cpu_count
show parameter block_size
select group#,thread#,members,bytes/1024/1024 from gv$log;
show sga
select count(*) from v$controlfile
select count(*) from v$tempfile;
select count(*) from v$datafile;

Check the tablespace data file information

col tablespace_name for a30
select 
tablespace_name,
sum(bytes)/1024/1024 
from dba_temp_files group by 
tablespace_name;

Checklist space

SELECT
TABLESPACE_NAME,
EXTENT_MANAGEMENT,
ALLOCATION_TYPE,
SEGMENT_SACE_MANAGEMENT
FROM DBA_TABLESPACES;

Check data file status

select count(*),status from v$datafile group by status;

Check table space usage

select
f.tablespace_name,
a.total,
f.free,
(a.total-f.free)/1024 "used SIZE(G)",
round((f.free/a.total)*100) "% Free"
from
(
select 
tablespace_name,
sum(bytes/(1024*1024)) total
from dba_data_files group by tablespace_name) a,
(
select 
tablespace_name,
round(sum(bytes/(1024*1024))) free
from dba_free_space
group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "% Free"

Query temporary segment usage

COL username FORMAT a10;
COL segtype FORMAT a10;
SELECT 
username,
segtype,
extents "Extents Allocated",
blocks "Blocks Allocated"
FROM v$tempseg_usage;

View temporary tablespace size

select 
tablespace_name,
file_name,bytes/1024/1024 "file_size(M)",
autoextensible
from dba_temp_files;

select 
status,
enabled,
name,
bytes/1024/1024 file_size
from v$tempfile;

See how temporary table spaces are used

SELECT 
temp_used.tablespace_name,
total - used as "Free",
total as "Total",
round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used
FROM GV_$TEMP_SPACE_HEADER
GROUP BY tablespace_name) temp_used,
(
SELECT 
tablespace_name,
SUM(bytes) / 1024 / 1024 total
FROM dba_temp_files
GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name;

Find those that consume more temporary table space sql

Select 
se.username,
se.sid,
su.extents,
su.blocks * to_number(rtrim(p.value)) as Space,
tablespace,
segtype,
sql_text
from v$sort_usage su, v$parameter p, v$session se, v$sql s
where p.name = 'db_block_size'
and su.session_addr = se.saddr
and s.hash_value = su.sqlhash
and s.address = su.sqladdr
order by se.username, se.sid

Check the current usage size of temporary table space and the current usage size of temporary table space sql sentence

select 
sess.SID,
segtype,
blocks * 8 / 1000 "MB",
sql_text
from v$sort_usage sort, v$session sess, v$sql sql
where sort.SESSION_ADDR = sess.SADDR
and sql.ADDRESS = sess.SQL_ADDRESS
order by blocks desc;

View data file information , If there are many files, you can sort according to the required fields Output top 10

col datafile for a60
SELECT 
fs.phyrds "Reads",
fs.phywrts "Writes",
fs.avgiotim "Average I/O Time",
df.name "Datafile"
FROM v$datafile df, v$filestat fs
WHERE df.file# = fs.file#;

View all data files I/O situation

phyrds The number of times I read for physics is very Reads, phywrts The number of times written for physics is extremely Writes, phyblkrd The number of reads for the physical block is br, phyblkwrt The number of times written for physics is bw. readtime The total time spent on physics reading is RTime, writetim The total time spent on physics is WTime. RTime and WTime These two values are only available in the parameter timed_statistics Parameter is true Only when effective .

COL ts FORMAT a10 HEADING "Tablespace";
COL reads FORMAT 999990;
COL writes FORMAT 999990;
COL br FORMAT 999990 HEADING "BlksRead";
COL bw FORMAT 999990 HEADING "BlksWrite";
COL rtime FORMAT 999990;
COL wtime FORMAT 999990;
SELECT 
ts.name AS ts,
fs.phyrds "Reads",
fs.phywrts "Writes",
fs.phyblkrd AS br,
fs.phyblkwrt AS bw,
fs.readtim "RTime",
fs.writetim "WTime"
FROM v$tablespace ts, v$datafile df, v$filestat fs
WHERE ts.ts# = df.ts# AND df.file# = fs.file#
UNION
SELECT 
ts.name AS ts,
ts.phyrds "Reads",
ts.phywrts "Writes",
ts.phyblkrd AS br,
ts.phyblkwrt AS bw,
ts.readtim "RTime",
ts.writetim "WTime"
FROM v$tablespace ts, v$tempfile tf, v$tempstat ts
WHERE ts.ts# = tf.ts# AND tf.file# = ts.file# ORDER BY 1;

obtain top 10 heat segment

set linesize 180
col object_name for a40
select * from
(select
ob.owner, ob.object_name, sum(b.tch) Touchs
from x$bh b , dba_objects ob
where b.obj = ob.data_object_id
and b.ts# > 0
group by ob.owner, ob.object_name
order by sum(tch) desc)
where rownum <=10;

Judge who reads the most physics object

select * from (select owner,object_name,value from v$segment_statistics where
statistic_name='physical reads' order by value desc) where rownum<=10;

View hotspot data files ( Judging from the reading time of a single block )

col FILE_NAME for a60
set linesize 180
SELECT t.file_name,
t.tablespace_name,
round(s.singleblkrdtim/s.singleblkrds, 2) AS CS,
s.READTIM,
s.WRITETIM
FROM v$filestat s, dba_data_files t
WHERE s.file# = t.file_id and s.singleblkrds <>0 and rownum<=10 order by cs desc;

Estimate tablespace size

select a.tablespace_name,
round(a.s,2) "CURRENT_TOTAL(MB)" ,
round((a.s - f.s),2) "USED(MB)" ,
f.s "FREE(MB)" ,
round(f.s / a.s * 100, 2) "FREE%" ,
g.autoextensible,
round(a.ms,2) "MAX_TOTAL(MB)"
from ( select d.tablespace_name,
sum (bytes / 1024 / 1024) s,
sum (decode(maxbytes, 0, bytes, maxbytes) / 1024 / 1024) ms
from dba_data_files d
group by d.tablespace_name) a,
( select f.tablespace_name, sum (f.bytes / 1024 / 1024) s
from dba_free_space f
group by f.tablespace_name) f,
( select distinct tablespace_name, autoextensible
from DBA_DATA_FILES
where autoextensible = 'YES'
union
select distinct tablespace_name, autoextensible
from DBA_DATA_FILES
where autoextensible = 'NO'
and tablespace_name not in
( select distinct tablespace_name
from DBA_DATA_FILES
where autoextensible = 'YES' )) g
where a.tablespace_name = f.tablespace_name
and g.tablespace_name = f.tablespace_name order by "FREE%" ;

Accurately calculate the table space size , Consume system resources , Use with caution

SELECT F.TABLESPACE_NAME,
A.ALL_TOTAL " Total space " ,
A.ALL_USED " Total space used " ,
A.ALL_TOTAL - A.ALL_USED " Total remaining space " ,
(A.ALL_TOTAL - A.ALL_USED) / A.ALL_TOTAL * 100 " Total remaining proportion " ,
A.TOTAL " The current size " ,
U.USED " Current space in use " ,
F. FREE " Current remaining space " ,
(U.USED / A.TOTAL) * 100 " Current usage ratio " ,
(F. FREE / A.TOTAL) * 100 " Current remaining proportion "
FROM ( SELECT TABLESPACE_NAME,
SUM (BYTES / (1024 * 1024 * 1024)) TOTAL,
SUM (DECODE(AUTOEXTENSIBLE, 'YES' , MAXBYTES, BYTES) /
(1024 * 1024 * 1024)) ALL_TOTAL,
SUM (USER_BYTES) / (1024 * 1024 * 1024) ALL_USED
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
( SELECT TABLESPACE_NAME, SUM (BYTES / (1024 * 1024 * 1024)) USED
FROM DBA_EXTENTS
GROUP BY TABLESPACE_NAME) U,
( SELECT TABLESPACE_NAME, SUM (BYTES / (1024 * 1024 * 1024)) FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE A.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
AND A.TABLESPACE_NAME = U.TABLESPACE_NAME(+)
ORDER BY (A.ALL_TOTAL - A.ALL_USED) / A.ALL_TOTAL,F. FREE / A.TOTAL ASC ;

Check log switching frequency

select sequence#,to_char(first_time,'yyyymmdd_hh24:mi:ss')
firsttime,round((first_time-lag(first_time) over(order by first_time))*24*60,2) minutes from
v$log_history where first_time > sysdate - 1 order by first_time ,minutes;

Check lgwr i/o performance (time_waited/total_waits: Means average lgwr Write completion time if >1 Indicates write is too slow )

select total_waits,time_waited,average_wait,time_waited/total_waits as avg from
v$system_event where event = 'log file parallel write';

Inquire about redo block size

select max(lebsz) from x$kccle;

see user commit frequency

select to_number(value,99999999999) from v$sysstat where name='user commits';

View system running time

select (sysdate - startup_time)*24*60*60 as seconds from v$instance

Calculate how many transactions are processed on average redo block

select 
value 
from v$sysstat where name = 'redo blocks written';

select 
a.redoblocks/b.trancount
from (select value redoblocks from v$sysstat where 
name='redo blocks written') a ,(select value trancount from v$sysstat where name='user commits') b

Calculate how many logs are generated every day

SELECT 
TO_CHAR (TRUNC (COMPLETION_TIME), 'yyyy-mm-dd') " date ",
SUM (blocks * BLOCK_SIZE) / 1024 / 1024 / 1024 " Log volume (G)"
FROM V$ARCHIVED_LOG
WHERE dest_id = 1
GROUP BY TRUNC (COMPLETION_TIME)
ORDER BY TRUNC (COMPLETION_TIME) DESC;