DBA routine maintenance SQL sorting

Database overview information check
# Check basic database 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;

View data file information
# Check the spatial 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_space
_ Management from dba_tablespaces;
# Check the data file status
Select count (*), status from V $ datafile group by status;
# Check the 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
Tablespace_name),
(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"
/
# Querying the usage of a temporary segment
Col username format A10;
Col segtype format A10;
Select username, segtype, extents "extents allocated"
, Blocks "blocks allocated"
From v $ tempseg_usage;
# View the data file information. If there are many files, you can sort and output the top 10 files according to the required fields.

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 the I/O status of all data files. If there are too many files, rewrite them to top 10 select * (order by XX DESC) Where
Rownum <= 10. Here, phyrds is the number of physical reads is extremely reads, phywrts is the number of physical writes is extremely writes, phyblkrd is the number of physical block reads is Br, phyblkwrt is the number of physical writes is BW. Readtime is the total time spent on physical reading, and writetim is the total time spent on physical writing, that is, wtime. These two values are valid only when the timed_statistics parameter is true.
Col ts format A10 heading "tablespace ";
Colreads 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 bwjava get the specified time
, 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 hot 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
# Determining the objects with the most physical reads

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 (determined by the time when a single block is read)

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
View redo
# 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: indicates the average lgwr write completion time. If the value is greater than 1, the 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 ';
# Query redo block size

Select max (lebsz) from x $ kccle;
# View User commit count
Select to_number (value, 99999999999) from V $ sysstat where name = 'user commits ';
# Viewing system running time
Select (sysdate-startup_time) * 24*60*60 as seconds from V $ instance
# Calculate the number of user submissions per second
Select user_commit times/system running time from dual;
# Calculate the average number of redo blocks processed by each transaction
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
SGA, PGA, hit rate
# SGA, PGA, hit rate
# Check SGA
Show SGA
Select * from V $ SGA;
# Viewing the buffer cache settings
Select size_for_estimate, estd_physical_read_factor,
To_char (estd_physical_reads, 99999999999999999999999) as "estd_physical_reads" from
V $ db_cache_advice where name = 'default ';
Col pool format A10;
Select (select round (value/1024/1024, 0) from V $ Parameter
Where name = 'db _ cache_size ') "Current cache (MB )"
, Name "pool", size_for_estimate "projected cache (MB )"
, Round (100-estd_physical_read_factor, 0) "cache hit ratio %"
From v $ db_cache_advice
Where block_size = (select value from V $ Parameter
Where name = 'db _ block_size ')
Order by 3;
# View PGA
Show parameter PGA
# Viewing cache pools
Show parameter Cache
# View the defalut pool hit rate in the buffer cache
Select name, 1-(physical_reads)/(consistent_gets + db_block_gets)
From v $ buffer_pool_statistics;
# Check Shared Pool
Show parameter shared
# Check the library cache in the Shared Pool
Select namespace, pinhitratio from V $ librarycache;
# Check the overall hit rate (library cache)
Select sum (pinhits)/sum (PINs) from V $ librarycache;
Select sum (PINs) "hits ",
Sum (reloads) "Misses ",
Sum (PINs)/(sum (PINs) + sum (reloads) "hits ratio"
From v $ librarycache;
# Check shared pool free space
Select * from V $ sgastat
Where name = 'free memory'
And pool = 'shared pool ';
# Each sub-shared pool is protected by a separate shared pool latch. Check their hit rate. The Shared Pool latch is used to recycle the allocated latch in the Shared Pool space.
Col name format A15
Select ADDR, name, gets, misses, 1-Misses/gets from V $ latch_children where name = 'shared pooled ';
# Use v $ shared_pool_advice to calculate the response time and unit of S for different shared pool sizes
Select 'shared pool 'component,
Shared_pool_size_for_estimate estd_sp_size,
Estd_lc_time_saved_factor parse_time_factor,
Case
When current_parse_time_elapsed_s + adjustment_s <0 then
0
Else
Current_parse_time_elapsed_s + adjustment_s
End response_time
From (select shared_pool_size_for_estimate,
Shared_pool_size_factor,
Estd_lc_time_saved_factor,
A. estd_lc_time_saved,
E. Value/100 current_parse_time_elapsed_s,
C. estd_lc_time_saved-A. estd_lc_time_saved adjustment_s from V $ shared_pool_advice,
(Select * from V $ sysstat where name = 'parse time elapsed ') e,
(Select estd_lc_time_saved from V $ shared_pool_advice
Where shared_pool_size_factor = 1) C)
/
# View the number of chunks of various types in the Shared Pool
Select ksmchcls class, count (ksmchcls) num, sum (ksmchsiz) siz,
To_char (sum (ksmchsiz)/count (ksmchcls)/1024), '000000') | 'K' "AVG
Size"
From x $ ksmsp group by ksmchcls;
# Check whether any wait events related to database buffering exist
Select Sid, seq #, event, P1, p1raw, P2, p2raw, P3, p3raw, State from V $ session_wait where event like
'Library % ';
# Query various pools in SGA
Col name format A32;
Select pool, name, bytes from V $ sgastat
Where pool is null
Or pool! = 'Shared pool 'or (pool = 'shared pool'
And (name in ('dictionary cache', 'enqueue ', 'library
Cache ', 'parameters ',
'Processs', 'session', 'free memory ')))
Order by pool DESC nulls first, name;
Select * from V $ sgainfo;
# View the status of the reserved pool using shard_pool
Select request_misses, request_failures, free_space
From v $ shared_pool_reserved;
Oracle allocates memory to keep these blocks from the shared pool. The default size of the reserved shared pool is 5% of the Shared Pool (_ shared_pool_reserved_pct 5 control). The value of Oracle creation is 10%. The size is changed by shared_pool_reserved_size. It is allocated from the shared pool, not directly from the SGA. It is the reserved part of the shared pool, it is used to store large blocks # When the memory in the shared pool is greater than _ shared_pool_reserved_min_alloc, It will be placed in the Shared Pool retention pool. The retention pool maintains a separate freelist, LRU, and does not store recreatable chunks in, the release of a common shared pool has nothing to do with the shared pool retention pool.
# Set shared_pool_reserved_size
#1. if the system has a ora-04031, it is found that the request memory is greater than _ shared_pool_reserved_min_alloc (default 10gr2 4400), and V $ shared_pool_reserved has a large number of request_misses (and you can see last) it indicates that the shared_pool_reserved_size is too small and the request that requires a large memory fails. Therefore, you need to increase the shared_pool_reserved_size.
#2. If the ora-04031 request memory appears in 4100-4400 and causes the Shared Pool LRU merge, aging out of memory, you can reduce _ shared_pool_reserved_min_alloc to let this part of memory into the shared reserved pool, correspondingly increase the shared_pool_reserv
#3. judging from V $ shared_pool_reserved, if request_failures> 0 (ora-04031 exists) and last_failure_size (last request memory size)> _ memory indicates that the shared reserved pool lacks continuous memory, you can increase the memory, reduce _ shared_pool_reserved_min_alloc by placing fewer objects and adding shared_pool_size
# If the reverse request_failures> 0 (ora-04031 occurred) and last_failure_size (last requested memory size) <_ shared_pool_reserved_min_alloc, it indicates the lack of continuous memory in the shared pool, you can add and reduce _ shared_pool_reserved_min_alloc to add more objects to reduce the sharedpool pressure and appropriately increase shared_pool_size and shared_pool_reserved_size.
# The query is still stored in the library cache. the maximum number of resolutions and the maximum number of executed SQL statements (resolution * execution)
Col SQL _text format a38;
Select * from (
Select parse_cils * executions "product", parse_cils
"Parses"
, Executions "execs", SQL _text from V $ sqlarea order by 1 DESC)
Where rownum <= 10;
# View PGA
Show parameters area_size
# View PGA
Select * from V $ pgastat;
# View database cache or keep objects
Col table_name format A16
Col index_name format A16
Select table_name as "table", null, buffer_pool, cache from
User_tables
Where buffer_pool! = 'Default' or trim (cache) = 'y'
Union
Select table_name, index_name, null, buffer_pool from
User_indexes
Where buffer_pool! = 'Default'
Order by 1, 2 nulls first;