Several oracle script records

-- ***************** View the tablespace size *****************


Select upper (F. TABLESPACE_NAME) "tablespace name", D. TOT_GROOTTE_MB "tablespace size (M )",
D. TOT_GROOTTE_MB-F. TOTAL_BYTES "used space (M )",
TO_CHAR (ROUND (D. TOT_GROOTTE_MB-F. TOTAL_BYTES)/D. TOT_GROOTTE_MB * 990), '2014. 99') | '%' "usage ratio ",
F. TOTAL_BYTES "Idle space (M )",
F. MAX_BYTES "maximum block (M)" FROM (SELECT TABLESPACE_NAME, ROUND (SUM (BYTES)/(1024*1024), 2) TOTAL_BYTES,
ROUND (MAX (BYTES)/(1024*1024), 2) MAX_BYTES from sys. DBA_FREE_SPACE group by TABLESPACE_NAME) F,
(Select dd. TABLESPACE_NAME, ROUND (SUM (DD. BYTES)/(1024*1024), 2) TOT_GROOTTE_MB
From sys. DBA_DATA_FILES dd group by dd. TABLESPACE_NAME) D
Where d. TABLESPACE_NAME = F. TABLESPACE_NAME order by 1;

-- If you do not want to use the awr or ash view, there is actually one of the most convenient ways to give you an SQL, which is also the most commonly used SQL in my work, basically
-- 90% awr and ash can be killed in seconds
Select SQL _id, event, count (*) from v $ active_session_history
Where sample_time> sysdate-10/1440 -- queries the sqlid and wait event of the most occupied time within 10 minutes
Group by SQL _id, event
Order by count (*) desc

-- ****************** Buffer hit rate *****************
SELECT a. VALUE + B. VALUE logical_reads,
C. VALUE phys_reads,
Round (100 * (1-c.value/(a. value + B. value), 4) hit_ratio
FROM v $ sysstat a, v $ sysstat B, v $ sysstat c
WHERE a. NAME = 'db block gets'
AND B. NAME = 'consistent gets'
AND c. NAME = 'physical reads ';

-- ****************** Share pool hit rate *****************

Select sum (pinhits)/sum (pins) * 100 from v $ librarycache;

-- ***************** View the size of the Shared Pool ******************* *
Select name, total, round (total-free, 2) used, round (free, 2) free, round (total-free)/total * 100,2) pctused from
(Select 'sga 'name, (select sum (value/1024/1024) from v $ SGA) total,
(Select sum (bytes/1024/1024) from v $ sgastat where name = 'free memory ') free from dual)
Union
Select name, total, round (used, 2) used, round (total-used, 2) free, round (used/total * 100,2) pctused from (
Select 'pga 'name, (select value/1024/1024 total from v $ pgastat where name = 'aggregate PGA target parameter ') total,
(Select value/1024/1024 used from v $ pgastat where name = 'total PGA allocated') used from dual)
Union
Select name, round (total, 2) total, round (total-free), 2) used, round (free, 2) free, round (total-free) /total * 100,2) pctused from (
Select 'shared pool 'name, (select sum (bytes/1024/1024) from v $ sgastat where pool = 'shared pooled ') total,
(Select bytes/1024/1024 from v $ sgastat where name = 'free memory 'and pool = 'shared pool') free from dual)
Union
Select name, round (total, 2) total, round (total-free, 2) used, round (free, 2) free, round (total-free)/total, 2) pctused from (
Select 'default pool 'name, (select. cnum_repl * (select value from v $ parameter where name = 'db _ block_size ')/1024/1024 total from x $ kcbwds a, v $ buffer_pool p
Where a. set_id = p. LO_SETID and p. name = 'default' and p. block_size = (select value from v $ parameter where name = 'db _ block_size ') total,
(Select a. anum_repl * (select value from v $ parameter where name = 'db _ block_size ')/1024/1024 free from x $ kcbwds a, v $ buffer_pool p
Where. set_id = p. LO_SETID and p. name = 'default' and p. block_size = (select value from v $ parameter where name = 'db _ block_size ') free from dual)
Union
Select name, nvl (round (total, 2), 0) total, nvl (round (total-free, 2), 0) used, nvl (round (free, 2 ), 0) free, nvl (round (total-free)/total, 2), 0) pctused from (
Select 'keep pool 'name, (select. cnum_repl * (select value from v $ parameter where name = 'db _ block_size ')/1024/1024 total from x $ kcbwds a, v $ buffer_pool p
Where a. set_id = p. LO_SETID and p. name = 'keep 'and p. block_size = (select value from v $ parameter where name = 'db _ block_size') total,
(Select a. anum_repl * (select value from v $ parameter where name = 'db _ block_size ')/1024/1024 free from x $ kcbwds a, v $ buffer_pool p
Where. set_id = p. LO_SETID and p. name = 'keep 'and p. block_size = (select value from v $ parameter where name = 'db _ block_size ') free from dual)
Union
Select name, nvl (round (total, 2), 0) total, nvl (round (total-free, 2), 0) used, nvl (round (free, 2 ), 0) free, nvl (round (total-free)/total, 2), 0) pctused from (
Select 'recycle pool 'name, (select. cnum_repl * (select value from v $ parameter where name = 'db _ block_size ')/1024/1024 total from x $ kcbwds a, v $ buffer_pool p
Where a. set_id = p. LO_SETID and p. name = 'recycle' and p. block_size = (select value from v $ parameter where name = 'db _ block_size ') total,
(Select a. anum_repl * (select value from v $ parameter where name = 'db _ block_size ')/1024/1024 free from x $ kcbwds a, v $ buffer_pool p
Where. set_id = p. LO_SETID and p. name = 'recycle' and p. block_size = (select value from v $ parameter where name = 'db _ block_size ') free from dual)

Union
Select name, nvl (round (total, 2), 0) total, nvl (round (total-free, 2), 0) used, nvl (round (free, 2 ), 0) free, nvl (round (total-free)/total, 2), 0) pctused from (
Select 'default 16 K buffer cache' name, (select a. cnum_repl * 16/1024 total from x $ kcbwds a, v $ buffer_pool p
Where a. set_id = p. LO_SETID and p. name = 'default' and p. block_size = 16384) total,
(Select a. anum_repl * 16/1024 free from x $ kcbwds a, v $ buffer_pool p
Where a. set_id = p. LO_SETID and p. name = 'default' and p. block_size = 16384) free from dual)
Union
Select name, nvl (round (total, 2), 0) total, nvl (round (total-free, 2), 0) used, nvl (round (free, 2 ), 0) free, nvl (round (total-free)/total, 2), 0) pctused from (
Select 'default 32 K buffer cache' name, (select a. cnum_repl * 32/1024 total from x $ kcbwds a, v $ buffer_pool p
Where a. set_id = p. LO_SETID and p. name = 'default' and p. block_size = 32768) total,
(Select a. anum_repl * 32/1024 free from x $ kcbwds a, v $ buffer_pool p
Where a. set_id = p. LO_SETID and p. name = 'default' and p. block_size = 32768) free from dual)
Union
Select name, total, total-free used, free, (total-free)/total * 100 pctused from (
Select 'java Pool 'name, (select sum (bytes/1024/1024) total from v $ sgastat where pool = 'java pooled 'group by pool) total,
(Select bytes/1024/1024 free from v $ sgastat where pool = 'java pooled 'and name = 'free memory') free from dual)
Union
Select name, Round (total, 2), round (total-free, 2) used, round (free, 2) free, round (total-free)/total * 100,2) pctused from (
Select 'large Pool 'name, (select sum (bytes/1024/1024) total from v $ sgastat where pool = 'large pool 'group by pool) total,
(Select bytes/1024/1024 free from v $ sgastat where pool = 'large pool 'and name = 'free memory') free from dual)
Order by pctused desc;