Features:
1. Basic information display of the database (Building time, mode, etc.)
2. Database Structure Information - Database Map (Control File, Redo Log File, DataFile, Rollback Segment)
3. Database All Status Information
4. Database level DB Block Buffer Hit Ratio
5. SESSION level DB Block Buffer Hit Ratio
6. Log Switch situation in the previous day
7. Redo Log Space Demand Inquiry
8. Log Buffer performance inquiry
9. Log file switch performance inquiry
10. Check the incomplete Checkpoint
11. Library Cache performance query
12. Dictionary Cache performance query
13. Find the SQL statement of the most resource
14. SESSION CPU usage
15. Sort_area_size performance query
16. Sequence_Cache_ENTRIES performance query
17. Query of Chained Row
18. Rollback Segment Contention Check
19. Table space fragment check
20. Latch Contention Check
21. TABLESPACE Dosage Check
22. Data file I / O check
23. Fragmentation of the table and index
24. Table HWM Check
Usage method example:
Currently, as a DBA, there are many tools to manage, maintain and compatible databases. This is just some scripts I usually collect, which can be used as a small tool program that DBA portable.
When there is no other visual tool, it can be used to make some basic diagnostics for the database.
1. Open SQLPlus, Connect System
2. @a: /check_db.sql (A: / is the path to this file)
3. After execution, the results are stored in C: /Local.txt
4. Local.txt not only has instructions for each data, but also introduces some methods to solve related problems for reference.
Check_db.sql content:
Set echo off
Spool c: /local.txt
TTILE OFF
Break on Today
Column Today Noprint New_Value XDate
SELECT SUBSTR (to_char (sysdate, 'FMMONTH DD, YYYY HH: MI: SS P.m.'), 1, 35) Today
From dual
/
Column name noprint new_value xdbname
SELECT NAME FROM V $ DATABASE
/
Set heading on
Set feedback off
Set linesize 250
Set PageSize 200
Rem ############################################################# ###################################################
REM **** Check_db_v2.1: Performance Tuning ****
Rem ############################################################# ###################################################
PROMPT ******************************************************************* ************************************** Prompt Database CHECK INFORMATION
PROMPT ******************************************************************* *****************************
TTITLE LEFT "Database:" xdbname "(AS OF:" xdate ")" SKIP 2
SELECT NAME, CREATED, LOG_MODE FROM V $ DATABASE
/
Prompt
PROMPT ******************************************************************* *****************************
TTILE OFF
Prompt
PROMPT
PROMPT 0.0 Database Map
PROMPT
Prompt
TTILE LEFT "*** Database Map - Control File ***"
Column "Control File" Format A40
Select Status, Name "Control File" from V $ ControlFile
/
TTILE OFF
TTITLE LEFT "*** Database Map - Redolog file ***"
Column "log file" Format A40
Select F.member "Log File", L.Group #, L.THREAD #, L.BYTES, L.STATUS FROM V $ LOG L, V $ logfile f
WHERE L.Group # = f.group #
/
TTILE OFF
TTILE LEFT "*** Database Map - Data file ***" Column file_name format A40
SELECT FILE_NAME, TABLESPACE_NAME, BYTES, Blocks, Status from DBA_DATA_FILES ORDER BY TABLESPACE_NAME, BYTES DESC
/
TTILE OFF
TTITLE LEFT "*** Database Map - rollback seg ***"
Select N.Name "Rollback Seg Name", R.EXTENTS, R.RSSIZE, R.OPTSIZE, HWMSIZE, STATUS
From V $ ROLLSTAT R, V $ ROLLNAME N
WHERE R.USN = N.USN
/
TTILE OFF
PROMPT
PROMPT 1.0 Database Statistic
PROMPT
Set heading on
Set Termout on
TTITLE LEFT "*** Database:" xdbname ", Database Statistic (as of:" xdate ") ***" SKIP 1
Column "Statistic Name" Format A55
Column Value Format 9, 999, 999, 999, 999, 990
Select n.statistic #, n.name "statistic name", S.Value
From V $ STATNAME N, V $ sysstat s
Where n.statistic # = s.statistic #
And value> 0
Order by Value DESC
/
TTILE OFF
PROMPT PROMPT 2.0 DB Block Buffer - Hit Ratio (Database Wise)
PROMPT
Set heading on
Set Termout on
Column "Physical Reads" Format 9, 999, 999, 999, 999
Column "Consistent Gets" Format 9, 999, 999, 999, 999
Column "DB Block Gets" Format 9, 999, 999, 999, 999
Column "Hit Ratio" Format 999.99
TTILE LEFT "*** Database:" xdbname ", DB Block Buffers Hit Ratio (as of:" xdate ") ***" SKIP 1-
Left "percent = ((100 * (1- (Physical Reads / (Consistent Gets DB Block Gets))" SKIP 2
Select Pr.Value "Physical Reads",
Cg.Value "consistent gets",
Bg.Value "DB Block Gets",
Round ((1- (Pr. Value / (Bg.Value cg.value)) * 100, 2) "Hit Ratio"
From V $ sysstat pr, v $ sysstat bg, v $ sysstat cg
Where pr.name = 'Physical Reads'
And bg.name = 'DB Block gets'
And cg.name = 'consistent gets'
/
Prompt <<<<<<<<<<<<<<<<<< Note: >>>>>>>>>>>>>>>> PROMPT 2.0 Investigation IFE PERCENT IS Less Than 70%, Increase DB_BLOCK_BUFFERS
Prompt <<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
TTILE OFF
Prompt
Prompt
PROMPT
Prompt 2.1 DB Block Buffer - Hit Ratio (Session Wise)
PROMPT
Clear Breaks
Clear Computes
Break on report
Compute Sum of consistent_gets on report
Compute Sum of Block_gets on report
Compute sum of physical_reads on report
Column "Hit Ratio%" Format 999.99
Column UserName Format A10
TTITLE LEFT "*** Database:" xdbname ", Hit Ratio for User sessions (as of:" xdate ") ***" SKIP 1
Select Username,
Osuser,
CONSISTENT_GETS,
BLOCK_GETS,
Physical_reads,
100 * (consistent_gets block_gets - physical_reads) / (consistent_gets block_gets "Hit Ratio%"
From v $ session, V $ sess_io
Where v $ session.sid = V $ sess_io.sid
AND (consistent_gets block_gets> 0
And username is not null
Order by Username, "Hit Ratio%";
Prompt
Prompt <<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Prompt 2.0 / 2.1 Investigation
Prompt if you have 20 or more users and batch users Cause Less Than 50%
Prompt Logical Reads With your Database, you Should Aim for a hit ratio
Prompt of Between 94% ~ 97%.
Prompt if you have fewer Than 20 Uses, The Sharing of Data Among Users Depends
Prompt Heavily on The Application, SO you Should Aim for a hit ratio in the 89% ~ 94%
Prompt <<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Prompt
Prompt
