Some common scripts in Oracle [Applicable DBA]

xiaoxiao2021-03-06  100

Waiting for the monitoring case

Select Event, SUM (Decode (Wait_Time, 0, 0, 1)) "Prev", SUM (Decode (Wait_time, 0, 1, 0)) "Curr", count (*) "Tot" from v $ session_wait group by Event ORDER BY 4;

2. Strive to roll

Select Name, Waits, Gets, Waits / Gets "Ratio" from V $ ROLLSTAT A, V $ ROLLNAME B Where A.usn = B.USN;

3. Monitor the I / O ratio of the table space

Select Df.TablesPace_Name Name, DF.File_name "F.Phyrds Pyr, F.Phyblkrd PBR, F.PhyWRTS PYW, F. PHYBLKWRT PBW from V $ Filestat F, DBA_DATA_FILES DF Where F.File # = DF.FILE_ID ORDER By DF.TABLESPACE_NAME;

4. Monitor the I / O ratio of the file system

Select Substr (A.FILE #, 1, 2) "#", Substr (A.Name, 1, 30) "Name", A.STATUS, A.BYTES, B.PHYRDS, B.PHYWRTS from V $ DataFile A , V $ filestat b where a.file # = B.file #;

5. Find all the indexes under a user

select user_indexes.table_name, user_indexes.index_name, uniqueness, column_name from user_ind_columns, user_indexes where user_ind_columns.index_name = user_indexes.index_name and user_ind_columns.table_name = user_indexes.table_name order by user_indexes.table_type, user_indexes.table_name, user_indexes.index_name, column_position;

6. Monitor the hit rate of SGA

Select A.Value B.Value "Logical_Reads", C.Value "Phys_Reads", Round (100 * (A.Value B.Value) -c.value) / (A.Value B.Value) " Buffer Hit Ratio "from V $ sysstat a, v $ sysstat b, v $ sysstat c where a.statistic # = 38 and b.statistic # = 39 and c.statistic # = 40;

7. Monitor the hit rate of the Dictionary buffer in SGA

Select Parameter, Gets, GetMisses, getMisses / (Gets GetMisses) * 100 "Miss Ratio", (1- (SUM (getMisses) / (SUM (Gets) SUM (GetMisses))) * 100 "Hit Ratio" from V $ ROWCACHE WHERE GETS GETMISSES <> 0 Group by parameter, gets, getmisses; 8. Monitoring the hit rate of the CCA in SGA, should be less than 1%

Select SUM (Pins) "Total Pins", SUM (Reloads) "Total Reloads", SUM (RELOADS) / SUM (PINS) * 100 Libcache from V $ librarycache

Select SUM (PINHITS-RELOADS) / SUM (PINS) "Hit Radio", SUM (RELOADS) / SUM (PINS) "Reload Percent" from v $ librarycache

9. Show all the categories and size of all database objects

select count (name) num_instances, type, sum (source_size) source_size, sum (parsed_size) parsed_size, sum (code_size) code_size, sum (error_size) error_size, sum (source_size) sum (parsed_size) sum (code_size) sum ( ERROR_SIZE) SIZE_REQUIRED from DBA_OBJECT_SIZE GROUP BY TYPE ORDER BY 2;

10. Monitor the hit rate of the SGA medium-red log buffer, should be less than 1%

SELECT name, gets, misses, immediate_gets, immediate_misses, Decode (gets, 0,0, misses / gets * 100) ratio1, Decode (immediate_gets immediate_misses, 0,0, immediate_misses / (immediate_gets immediate_misses) * 100) ratio2 FROM v $ latch where name in ('redo allocation', 'redo copy');

11. Monitor memory and hard disk sorting ratio, it is best to make it less than .10, increase sort_area_size

Select Name, Value from V $ Sysstat WHERE Name in ('sorts (a ");

12. Monitor the current database Who is running what SQL statement

Select OSuser, Username, SQL_Text from V $ Session A, V $ SQLText B Where A.SQL_Address = B.Address Order by Address, Piece

13. Monitor Dictionary Buffer

SELECT (SUM (Pins - Reloads)) / SUM (Pins "LIB Cache" from v $ librarycache; select (sum (gets - getmisses - usage - fixed) / sum (gets) "Row Cache" from v $ rowcache; Select SUM (Pins) "Executions", SUM (RELOADS) "Cache Misses While Executing" from V $ librarycache; the latter except for the former, this ratio is less than 1%, close to 0% is good.

Select Sum (Gets) "Dictionary Gets", SUM (GetMisses) "Dictionary Cache Get Misses" from v $ ROWCACHE

14. Looking for Oracle character sets

Select * from sys.props $ where name = 'nls_characterset';

15. Monitor MTS

Select Busy / (Busy IDle) "Shared Servers Busy" from V $ dispatcher;

When this value is greater than 0.5, the parameters need to be increased.

Select SUM (WAIT) / SUM (Totalq) "Dispatcher Waits" from v $ queue where type = 'dispatcher'; select count (*) from v $ dispatcher; select servers_highwater from v $ mts;

When Servers_Highwater is close to MTS_MAX_SERVERS, the parameters need to increase

16. Degradation

SELECT TABLESPACE_NAME, Count (TableSpace_name) from dba_free_space group by tablespace_name haVing count (TABLESPACE_NAME)> 10;

Alter TableSpace Name Coalesce; Alter Table Name Deallocate Unused;

create or replace view ts_blocks_v as select tablespace_name, block_id, bytes, blocks, 'free space' segment_name from dba_free_space union all select tablespace_name, block_id, bytes, blocks, segment_name from dba_extents;

SELECT * from TS_BLOCKS_V;

SELECT TABLESPACE_NAME, SUM (BYTES), Max (Bytes), Count (Block_ID) from DBA_Free_Space Group by TableSpace_name;

View the high degree of fragmentation

SELECT segment_name table_name, COUNT (*) extents FROM dba_segments WHERE owner NOT IN ( 'SYS', 'SYSTEM') GROUP BY segment_name HAVING COUNT (*) = (SELECT MAX (COUNT (*)) FROM dba_segments GROUP BY segment_name);

17. Table, indexed storage check

select segment_name, sum (bytes), count (*) ext_quan from dba_extents where tablespace_name = '& tablespace_name' and segment_type = 'TABLE' group by tablespace_name, segment_name; select segment_name, count (*) from dba_extents where segment_type = 'INDEX' and owner = '& Owner' group by segment_name;

18, find more users with CPU session

12 is CPU Used by this session

Select A.SID, SPID, STATUS, SUBSTR (A.Program, 1,40) PROG, A.TERMINAL, OSUSER, VALUE / 60/100 VALUE FROM VALUE / 60/100 VALUE FROM VALUE / 60/100 VALUE FROM V $ SESSION A, V $ Process B, V $ SESSSTAT C Where C .statistic # = 12 and c.SID = a.sid and a.paddr = b.addr Order by value desc;

Table 19. The remaining space set linesize 1000select a.tablespace_name, free_space / (1024 * 1024), all_space / (1024 * 1024), free_space * 100 / all_space from (select tablespace_name, sum (bytes) all_space from dba_data_files group by tablespace_name) a (select TableSpace_name, SUM (Bytes) free_space from dba_free_spacegroup by tablespace_name) bwhere a.tablespace_name = b.tablespace_name

29. The progress of IMPORT SELECT SUBSTR (SQL_Text, INTO "'), 30) Table_name, Rows_Processed, Round ((sysdate- to_date) * 24 * 60,1) minutes, trunc (rows_processed / ((sysdate-to_date (first_load_time, 'yyyy-mm-dd hh24: mi: ss')) * 24 * 60)) rows_per_minute from sys.v_ $ sqlarea where sql_text like 'INSERT% INTO "%' and common_type = 2 and open_versions> 0;

转载请注明原文地址:https://www.9cbs.com/read-125083.html

New Post(0)