From http://blog.9cbs.net/cn_chenfeng/archive/2004/09/17/108119.aspx
View the name and size of the table space:
SQL> SELECT T.TABLESPACE_NAME, ROUND (SUM (Bytes / (1024 * 1024)), 0) TS_SIZE
From DBA_TABLESPACES T, DBA_DATA_FILES D where t.tablespace_name = d.tablespace_name group by t.tablespace_name;
View the name and size of the physical file of the tablespace:
SQL> SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, ROUND (Bytes / (1024 * 1024), 0) Total_Space from DBA_DATA_FILES ORDER BY TABLESPACE_NAME
View the name and size of the roll:
SQL> Select segment_name, tablespace_name, r.status,
(Initial_extent / 1024) InitialeXtent, (Next_EXTENT / 1024) Nextextent,
Max_EXTENTS, V.Curext Curextent from DBA_ROLLBACK_SEGS R, V $ ROLLSTAT V
WHERE R.SEGMENT_ID = V.USN ( ) ORDER BY Segment_Name;
How to check what something in a rollover or run or what SQL statement is being executed:
SQL> SELECT D.SQL_Text, A.Name from V $ ROLLNAME A, V $ TRANSACTION B, V $ Session C, V $ SQLText D Where A.usn = B.XIDUSN and B.Addr = C.TADDR and C.SQL_Address = D.Address and C.SQL_Hash_Value = D.hash_Value and A.usn = 1;
(Note: Which one you want to see, just write USN =?
View the control file:
SQL> SELECT * FROM V $ ControlFile;
View log files:
SQL> COL MEMBER FORMAT A50
SQL> SELECT * FROM V $ logfile;
How to view the current SQL * Plus user's SID and Serial #:
SQL> SELECT SID, Serial #, Status from v $ session where audsid = uchesserenv ('sessionID');
How to view the character set of the current database:
SQL> SELECT Userenv ('Language') from Dual;
SQL> SELECT Userenv ('lang') from dual;
How to determine what SQL optimization method currently is currently using:
Use the Explain Plan to generate an Explain Plan, check the value of the POSITION column of id = 0 in Plan_Table.
SQL> SELECT DECODE (nVL (position, -1), - 1, 'RBO', 1, 'CBO') from plan_table where id = 0;
How to view the current latest SCN number:
SQL> SELECT MAX (KTuxescnw * Power (2,32) KTUXESCNB) from W $ KTUXE;
Find scripts for trace files in Oracle:
SQL> SELECT U_DUMP.VALUE || '/' || instance.value || '_ora_' || V $ process.spid || NVL2 (V $ process.traceid, '_' || V $ process.traceid, null ) || '.trc' "trace" from V $ Parameter u_dump cross Join V $ PRAMETER INSTANCE CROSS JOIN V $ Process.addr = V $ session.paddr where u_dump.name = 'user_dump_dest 'and
Instance.name = 'instance_name' and v $ session.audsid = sys_context ('useerenv', 'sessionID');
SQL> SELECT D.VALUE || '/ ORA_' || P.SPID || '.trc' trace_file_namefrom (SELECT P.SPID from Sys.v_ $ MyStat M, Sys.V_ $ Session S, SYS.V_ $ PROCESS P Where m.statistic # = 1 ands.SID = m.sid and p.addr = s.paddr) p, (Select Value from sys.v_ $ parameter where name = 'user_dump_dest') d;
How to view the client login IP address:
SQL> SELECT SYS_CONText ('useerenv', 'ip_address') from dual;
How to create a trigger for tracking client IP addresses in the production database:
SQL> Create or Replace Trigger ON_LOGON_TRIGGER
After Logon on Database
Begin
DBMS_Application_info.set_client_info (sys_context ('useerenv', 'ip_address'));
END;
REM records the trigger of login information
Create Or Replace Trigger Logon_History
After Logon on Database --when (user = 'wacos') --only for user 'wacos'
Begin
INSERT INTO session_history
Select Username, SID, Serial #, Audsid, OSuser, Action, Sysdate, Null, Sys_CONTEXT ('Userenv', 'IP_Address'), Terminal, Machine, Program
From v $ session where audsid = uchesernv ('sessionid');
END;
Query the current date:
SQL> SELECT TO_CHAR (Sysdate, 'YYYY-MM-DD, HH24: MI: SS') from DUAL
View all tablespaces corresponding to the data file name:
SQL> SELECT DISTINCT FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE from DBA_DATA_FILES
View the usage of the table space:
SQL> SELECT SUM (BYTES) / (1024 * 1024) As Free_Space, TableSpace_nameFrom DBA_FREE_SPACE GROUP BY TABLESPACE_NAME
SQL> SELECT A.TABLESPACE_NAME, A.BYTES TOTAL, B.BYTES USED, C.BYTES Free,
(B.Bytes * 100) /a.bytes "% used", (C.BYTES * 100) /A.BYTES "% free"
From sys.sm $ TS_AVAIL A, SYS.SM $ TS_USED B, SYS.SM $ TS_FREE C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME and ANTABLESPACE_NAME = C.TABLESPACE_NAME Download Adobe Reader
column tablespace_name format a18; column Sum_M format a12; column Used_M format a12; column Free_M format a12; column pto_M format 9.99; select s.tablespace_name, ceil (sum (s.bytes / 1024/1024)) || 'M' Sum_M, ceil (sum (s.UsedSpace / 1024/1024)) || 'M' Used_M, ceil (sum (s.FreeSpace / 1024/1024)) || 'M' Free_M, sum (s.UsedSpace) / sum (s .betes) PTUSED
From (Select B.File_ID, B.Tablespace_Name, B.bytes, (B.bytes-Sum (NVL (A.bytes, 0))) UsedSpace, SUM (NVL (A.BYTES, 0)) FreeSpace, (SUM NVL (A.BYTES, 0)) / (B.bytes)) * 100 FreePercentratio from sys.dba_free_space a, sys.dba_data_files b where A.File_ID ( ) = B.file_id group by b.file_id, b.tablespace_name, B.bytes Order By B.TABLESPACE_NAME) S Group by S.Tablespace_name Order By Sum (S.Freespace) / SUM (S.BYTES) DESC;
View the HWM of the data file (you can resize minimum space) and file header size:
SELECT v1.file_name, v1.file_id, num1 totle_space, num3 free_space, num1-num3 "USED_SPACE (HWM)", nvl (num2,0) data_space, num1-num3-nvl (num2,0) file_headFROM (SELECT file_name, file_id, SUM (bytes) num1 FROM Dba_Data_Files GROUP BY file_name, file_id) v1, (SELECT file_id, SUM (bytes) num2 FROM dba_extents GROUP BY file_id) v2, (SELECT file_id, SUM (bYTES) num3 FROM DBA_FREE_SPACE GROUP BY file_id) v3WHERE v1. File_id = v2.file_id ( ) and v1.file_id = v3.file_id ( );
Data file size and head size:
SELECT v1.file_name, v1.file_id, num1 totle_space, num3 free_space, num1-num3 Used_space, nvl (num2,0) data_space, num1-num3-nvl (num2,0) file_head FROM (SELECT file_name, file_id, SUM (bytes) num1 FROM Dba_Data_Files GROUP BY file_name, file_id) v1, (SELECT file_id, SUM (bytes) num2 FROM dba_extents GROUP BY file_id) v2, (SELECT file_id, SUM (bYTES) num3 FROM DBA_FREE_SPACE GROUP BY file_id) v3 WHERE v1.file_id = v2 .file_id ( ) and v1.file_id = v3.file_id ( ); (running the above query, we can ask the following information: Totle_pace: The total size of the data file, byte is the size of the data file: the data file is left. Bytes are in units, using spaces, the data files, the byte is unit_space: the data file is spaced in the data file, that is, the data space, byte unit file_head: This data file header occupies, bytes unit)
Check of various tablespace growth in the database:
SQL> SELECT A.TABLESPACE_NAME, (1- (a.total) /b.total) * 100 USED_PERCENT
From (select tablespace_name, sum (bytes) total from dba_free_space group by tablespace_name) A, (select tablespace_name, sum (bytes) total from dba_data_files group by tablespace_name) B where A.tablespace_name = B.tablespace_name;
SQL> SELECT UPPER (F.TABLESPACE_NAME) "Table Space Name", D. Tot_Grootte_MB "Table Space Size (M)", D. Tot_Grootte_MB - F. Total_Bytes "has been used with space (M)", to_CHAR (Round) TOT_GROOTTE_MB - F. TOTAL_BYTES) / D. Tot_Grootte_MB * 100, 2), '990.99') "User, 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 from sys.dba_data_files dd group by dd.tablespace_name) D where d.tablespace_name = f.tablespace_name
ORDER BY 4 DESC;
View each table space occupation disk condition: SQL> col TableSpace_name Format A20; SQL> SELECT B.FILE_ID File_ID, B.TABLESPACE_NAME TABLESPACE_NAME, B.BYTES BYTES, (B.BYTES-SUM (NVL (NVL (A.Bytes, 0)))) Used, SUM (NVL (A.bytes, 0)) Free, SUM (NVL (A.BYTES, 0)) / (B.bytes) * 100 Percent from DBA_FREE_SPACE A, DBA_DATA_FILES B Where A.FILE_ID = B.File_ID Group By B.TABLESPACE_NAME, B.FILE_ID, B.BYTES ORDER BY B.FILE_ID; Database Objects Next Extension and Table Space FREE Extributes Check:
SQL> SELECT A.TABLE_NAME, A.NEXT_EXTENT, A.TABLESPACE_NAME
From all_tables a, (Select TableSpace_name, max (bytes) AS BIG_CHUNK
From DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F Where f.tablespace_name = a.tablespace_name and a.next_extent> f.big_chunk
Union Select a.index_name, a.next_extent, a.tablespace_name
From all_indexes a, (Select TableSpace_name, max (bytes) AS BIG_CHUNK
From DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F Where f.tablespace_name = a.tablespace_name and a.next_extent> f.big_chunk;
Get the highest SQL statement of Disk Read:
SQL> SELECT SQL_TEXT FROM (SELECT * FROM V $ SQLAREA Order by Disk_reads)
WHERE ROWNUM <= 5;
Find the top ten performance SQL
Select * from (SELECT PARSING_USER_ID EXECUTIONS, SORTS, Command_Type, Disk_reads, SQL_Text from V $ SQLAREA Order by Disk_reads Desc) Where Rownum <10;
5 system waiting events for the most waiting time:
SQL> SELECT * FROM (SELECT * from V $ system_event where event not limited "sql% 'order by total_waits desc) Where rownum <= 5;
View sessions of the current waiting event:
Col UserName Format A10
Set line 120
Col Event Format A30
Select SE.SID, S.USERNAME, SE.EVENT, SE.TOTAL_WAITS, SE.TIME_WAITED, SE.AVERAGE_WAIT
From v $ session s, v $ session_event se Where S.Username is not null and se.SID = S.SID
And s.status = 'Active' and se.event not like '% SQL * Net%'; SELECT SID, Event, P1, P2, P3, WAIT_TIME, SECONDS_IN_WAIT, State from V $ Session_Wait Where Event Not Like '% Message% 'and Event not Like' SQL * Net% 'and Event Not Like'% Timer% 'and Event! =' Wakeup Time Manager '
Find the current wait event related to the connected session:
Select sw.SID, S.Username, SW.Event, sw.wait_time, sw.state, sw.seconds_in_wait sec_in_wait
From v $ session s, v $ session_wait sw where s.usename is not null and sw.sid = s.sid
And sw.event not like '% SQL * Net%' Order by sw.wait_time desc;
Oracle's check: check:
SQL> SELECT Segment_Name, Owner, TableSpace_name, Initial_EXTENT, NEXT_EXTENT, DBA_ROLLBACK_SEGS.STATUS from DBA_ROLLBACK_SEGS, V $ dataFile Where File_ID = file #;
Oracle rollback segment extension information check:
COL Name Format A10
Set Linesize 140
Select Substr (Name, 1,40) Name, Extents, Rssize, Optsize, Aveactive, Extends, Wraps, Shrinks, HWMSIZE
From V $ ROLLNAME RN, V $ ROLLSTAT RS WHERE (RN.USN = rs.usn);
EXTENTS: Quantity in the rollback segment.
RSSIZE: The size of the rollback segment in bytes.
OPTSIZE: The value set for the Optimal parameter.
AveActive: The size of the average space that is released from bytes in bytes from the rollback segment is deleted.
Extends: The number of times the system is incremented by the rollover section.
Shrinks: The number of times the system is removed from the rollback segment (ie rollback segment shrinkage). When the rollback segment is removed, the system may eliminate one or more panels from this rollback segment.
HWMSIZE: The upper limit of the segment size, that is, the maximum size that has been reached back to the segment.
(If the average size of the rollback segment is close to Optimal, then the value of Optimal is set correctly. If the number of dynamic growth of the rollback segment is high or the number of shrinkage is high, then it is necessary to improve the value of Optimal)
View the use of the rollback segment, which user is using the resource of the rollback segment:
Select S.username, U.NAME from V $ Transaction T, V $ ROLLSTAT R, V $ ROLLNAME U, V $ session s where s.taddr = t.addr andt.xidusn = r.usn and r.usn = u. USN ORDER by S.Username;
How to check one Shared_Server is busy with:
Select a.username, A. Machine, A.Program, A.SID, A. Serial #, A.STATUS, C.PIECE, C.SQL_TEXTFROM V $ SESSION A, V $ Process B, V $ SQLText Cwhere B.spid = 13161 and b.addr = a.paddrand a.sql_address = c.address ( ) Order by c.piece; Database Sharing Bool Performance Check:
Select Namespace, Gets, Gethitratio, Pins, Pinhitratio, Reloads,
Invalidations from V $ LibraryCache Where Namespace in
('Sqlarea', 'Table / Procedure', 'Body', 'Trigger');
Check data overload ratio:
Select SUM (Reloads) / SUM (Pins) * 100 "Reload Ratio" from
V $ librarycache;
Check the hit rate of the data dictionary:
SELECT 1-SUM (GETMISSES) / SUM (Gets) "Data Dictionary Hit
Ratio "from V $ rowcache;
(For library cache, gethitratio and pinhitratio should be greater than 90%, for data overload ratio, RELOAD RATIO should be less than 1%, for the hit rate of data dictionary, Data Dictionary Hit Ratio should be greater than 85%)
Check the surplus of shared memory:
SELECT REQUEST_MISSES, Request_Failures from V $ Shared_pool_reserve;
(Request_Misses and Request_Failures should be close to 0) for the surplus of shared memory.
Data cache performance check:
SELECT 1-P.Value / (B.Value C.Value "DB Buffer Cache Hit
Ratio "from V $ SYSSTAT P, V $ SYSSTAT B, V $ sysstat c where
P.Name = 'Physical Reads' and B.Name = 'DB Block Gets' and
C.Name = 'Consistent Gets';
Check if buffer pool hit_ratio
SELECT NAME, (Physical_Reads / (DB_BLOCK_GETS CONSISTENT_GETS))
"Miss_hit_ratio" from v $ buffer_pool_statistics where (db_block_gets consistent_gets)> 0;
(Normal time DB Buffer Cache Hit Ratio should be greater than 90%, and the buffer pool miss_hit_ratio should be less than 10% when normal.
Database backlink segment performance check:
Check Ratio Execution
Select SUM (Waits) * 100 / Sum (Gets) "Ratio", SUM (Waits)
"Waits", SUM (Gets) "Gets" from V $ ROLLSTAT D;
Check count / value execution:
Select Class, Count From V $ WaitStat Where Class Like '% UNDO%'
Select Value from V $ sysstat where name = 'consistent gets';
(The value of the VALUE is removed)
Check if_wait execution: SELECT EVENT, TOTAL_WAITS, TIME_WAITED, AVERAGE_WAIT from V $ SYSTEM_EVENT
WHERE EVENT LIKE '% UNDO%';
Check if RBS Header Get Ratio is executed:
Select N.Name, S.USN, S.WRAPS, DECODE (S.Waits, 0, 1, 1- S.WAITS / S.GETS) "RBS
Header Get Ratio "from V $ ROLLSTAT S, V $ ROLLNAME N where s.usn = n.usn;
(Normal time Ratio should be less than 1%, count / value should be less than 0.01%, Average_wait is preferably 0, the smaller the value, the better, the RBS Header Get Ratio should be greater than 95%)
Kill the script:
Select A.SID, B.SPID, A.Serial #, a.Lockwait, A.username, A. OSuser, a.logon_time, a.last_call_et / 3600 last_HOUR, A.STATUS, 'ORAKILL' || SID || ' '|| SPID HOST_COMMAND,' ALTER System Kill Session '' '|| A.SID ||', '|| A.Serial # ||' '' 'SQL_COMMANDFROM V $ Session A, V $ Process B Where A.paddr = B.Addr and Sid> 6;
View the performance of the sort segment:
SQL> SELECT NAME, VALUE FROM V $ SYSSTAT WHERE NAME IN ('Sorts (AMORY)', 'Sorts (Disk);
7. View the database library object:
Select Owner, Object_Type, Status, Count (*) Count # from all_Objects group by Owner, Object_type, status
8, check the version of the database:
SELECT * from V $ Version;
9. View the creation date and archive of the database:
SELECT CREATED, LOG_MODE, LOG_MODE FROM V $ DATABASE
10, capture SQL running for a long time:
Column UserName Format A12
Column Opname Format A16
Column Progress Format A8
select username, sid, opname, round (sofar * 100 / totalwork, 0) || '%' as progress, time_remaining, sql_text from v $ session_longops, v $ sql where time_remaining <> 0 and sql_address = address hash_value and sql_hash_value =;
11. View the parameters of the data sheet:
SELECT partition_name, high_value, high_value_length, tablespace_name, pct_free, pct_used, ini_trans, max_trans, initial_extent, next_extent, min_extent, max_extent, pct_increase, FREELISTS, freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size, last_analyzed From dba_tab_partitions - where table_name =: tname and table_owner =: townerRDer by partition_position; 12, check-out business:
Select * from v $ locked_Object;
Select * from V $ Transaction;
13. Find what the object is used for Object:
Select P.SPID, S.SID, S.Serial # Serial_Num, S.Username User_Name, A.TYPE Object_Type, S.OSuser OS_User_Name, A.OWNER, A. Object Object_name, Decode (Sign (48 - Command), 1, TO_CHAR (Command), 'Action Code #' || to_CHAR (Command)) Action, P.Program Oracle_Process, S.Terminal Terminal, S.Program Program, S.Status Session_Status from V $ Session S, V $ Access A, V $ process p where s.paddr = p.addr and s.type = 'user' and a.sid = s.SID and a.Object = 'Subscriber_attr'Rder by S.Username, S. OSUSER
14, check back and roll:
SQL> COL Name Format A10
SQL> SET LINESIZE 100
SQL> select rownum, sys.dba_rollback_segs.segment_name Name, v $ rollstat.extents Extents, v $ rollstat.rssize Size_in_Bytes, v $ rollstat.xacts XActs, v $ rollstat.gets Gets, v $ rollstat.waits Waits, v $ rollstat .writes Writes, sys.dba_rollback_segs.status status from v $ rollstat, sys.dba_rollback_segs, v $ rollname where v $ rollname.name ( ) = sys.dba_rollback_segs.segment_name and v $ rollstat.usn ( ) = v $ rollname .usn order by rownum
15, the resource process (TOP session):
Select S.Schemaname Schema_name, Decode (Sign (48 - Command), 1, To_Char (Command), 'Action Code #' || to_char (command)) Action, Status Session_Status, S. OS_User_name, S.SID, P. SPID, S. Serial # Serial_Num, NVL (S.Username, '[Oracle Process]) USER_NAME, S.TERMINAL TERMINAL, S.PROGRAM Program, St.Value Criteria_Value from V $ SESSSTAT ST, V $ Session S, V $ Process p where st.sid = s.sid and st.statistic # = to_number ('38 ') and (' all '=' all 'or s.status =' all ') and p.addr = s.paddr Order by St.Value DESC, P.SPID ASC, S.USERNAME ASC, S. OSUSER ASC; find the corresponding statement according to PID:
SELECT A.USERNAME,
A. Machine, A.Program, A.SID, A.Serial #, A.STATUS, C.PIECE, C.SQL_Text
From v $ sessions a, v $ process b, v $ sqltext c where b.spid = SPID
And B.Addr = a.paddr and a.sql_address = c.address ( ) Order by C.Piece;
Looking for a process of Oracle according to SID:
SQL> SELECT Pro.SPID from V $ Session SES, V $ Process Pro Where Ss.SID = 21 and SES.PADDR = Pro.Addr;
Monitor the current database who is running what SQL statement: SQL> SELECT OSUSER, UserName, SQL_Text from V $ Session A, V $ SQLText B where a.sql_address = B.Address Order By Address, Piece
How to view a user in the database, what SQL statement is running
SQL> SELECT SQL_TEXT FROM V $ SQLText T, V $ session s where t.address = s.sql_address
And T.hash_Value = s.sql_hash_value and s.machine = 'xxxxx' or usrname = 'wacos';
How to detect the SQL statement that the reception is being issued:
SQL> SELECT User_name, SQL_Text from V $ Open_CURSOR WHERE SID IN (SELECT SID, Serial # from v $ session where status = 'Active'));
Query the current SQL statement:
SQL> SELECT Program, SQL_Address from V $ Session WHERE PADDR in (SELECT ADDR
From v $ process where spid = 3556);
Program SQL_Address
------------------------------------------------ -------------- SQLPLUS @ CTC20 (TNS V1-V3) 000000038FCB1A90
SQL> SELECT SQL_TEXT FROM V $ SQLAREA WHERE Address = '000000038FCB1A90'
Find the SQL statement corresponding to the process consumed the highest CPU:
Set line 240
SET VERIFY OFF
Column Sid Format 999
Column Pid Format 999
Column S_ # Format 999
Column UserName Format A9 Heading "ORA User"
COLUMN Program Format A29
Column SQL Format A60
Column Osname Format A9 Heading "OS User"
Select P.PID PID, S.SID SID, P.SPID SPID, S.USERNAME Username,
S.OSuser osname, p.serial # s _ #, p.Terminal, P.Program Program,
P. Background, S.Status, Rtrim (Substr (A.SQL_Text, 1, 80)) SQL
From V $ Process P, V $ Session S, v $ sqlarea a where p.addr = s.paddr
And S.SQL_Address = a.address ( ) and p.spid Like '% & 1%;
Enter Value for 1: PID (here you enter PIDs with the highest process of CPU)
Set Termout Off
Spool maxcpu.txt
SELECT ' || S.Username Username,
RTRIM (a.sql_text, chr (10), '')) || ';' From V $ Process P, V $ Session S,
V $ sqlarea a where p.addr = s.paddr and s.sql_address = a.address ( )
And P.spid Like '% && 1%';
Enter Value for 1: PID (here you enter PIDs with the highest process of CPU)
Spool off (this sentence is in the final execution)
Acquisition of 2 SQL statements with the highest CPU rate
Execute: Top, get the PID of the process of the highest CPU usage through TOP.
SQL> select sql_text, spid, v $ session.program, process from v $ sqlarea, v $ session, v $ process where v $ sqlarea.address = v $ session.sql_address and v $ sqlarea.hash_value = v $ session.sql_hash_value
And v $ session.paddr = v $ process.addr and v $ process.spid in (pid);
Col Machine Format A30 Collas Format A40 Set Line 200 SQL> Select Sid, Serial #, Username, OSuser, Machine, Program, Process, To_Char (Logon_Time, 'YYYY / MM / DD HH24: MI: SS') from v $ session where paddr in (select addr from v $ process where spid in ([$ spid])); select sql_text from v $ sqltext_with_newlines where hash_value = (select SQL_HASH_VALUE from v $ session where sid = & sid) order by piece;
16, check the lock (LOCK):
SQL> SELECT / * Rule * / LS.OSUSER OS_USER_NAME, LS.USERNAME USER_NAME, DECODE (Ls.Type, 'RW', 'Row Wait Enqueue Lock ",' TM ',' DML Enqueue Lock ',' TX ', 'Transaction Enqueue Lock', 'UL', 'User Supplied Lock') Lock_Type, O.Object_name Object, Decode (Ls.Lmode, 1, Null, 2, 'Row Share', 3, 'Row Exclusive', 4, ' Share ', 5,' Share Row Exclusive ', 6,' Exclusive ', NULL) LOCK_MODE, O.OWNER, LS.SID, LS.SERIAL # Serial_Num, Ls.ID1, LS.ID2 from sys.dba_objects O, (Select S. Orsuser, S.Username, L. Type, L.Lmode, S.SID, S.Serial #, L.ID1, L.ID2 from V $ Session S, V $ LOCK L Where s.SID = L.SID ) ls where object_id = ls.id1 and o.owner <> sys' order by o.owner, o.object_name;
SQL> Select sys.v_ $ session.osuser, sys.v_ $ session.machine, V $ lock.sid, sys.v_ $ session.serial #, decode (V $ loc.TYPE, 'MR', 'Media Recovery " , 'RT', 'Redo Thread', 'Un', 'User Name', 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL / SQL User Lock', 'DX' , 'Distributed XAction', 'CF', 'Control File', 'IS', 'Instance State', 'FS', 'File Set', 'IR', 'Instance Recovery', 'St', 'Disk Space Transaction ',' TS ',' Temp Segment ',' IV ',' Library Cache Invalida-Tion ',' Ls ',' Log Start Or Switch ',' RW ',' Row Wait ',' SQ ',' Sequence Number ',' TE ',' TEMP TABLE ',' UNKNOWN ') LOCKTYPE, RTRIM (Object_Type) ||' '|| RTRIM (OWNER) ||'. '|| Object_Name Object_name, Decode (LMODE, 0, 'NONE', 1, 'NULL', 2, 'ROW-S', 3, 'ROW-X', 4, 'Share', 5, 'S / ROW-X', 6, 'Exclusive', 'unknown') LockMode, Decode (Request, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-x', 4, 'Share', 5, 'S / row-x', 6, 'Exclusive', 'unknown') RequestMode, CTIME, BLOCK B from V $ LOCK, All_Objects, Sys.V_ $ Session Where V $ LOCK.SID> 6 and Sys.v_ $ session .sid = V $ lock.sid and v $ lock.id1 = all_obj ECTS.OBJECT_ID;
With the DBA role, see the current database locks to use the following SQL statement: Col Owner for A12 COL Object_name for A16 SELECT B.OWNER, B.Object_name, L.Session_ID, L.LOCKED_MODE ASSION_ID_OBJECT L, DBA_OBJECTS B WHERE B. Object_id = l.object_id; SQL> SELECT T2.USERNAME, T2.SID, T2.SERIAL #, T2.LOGON_TIME FROM V $ locked_Object T1, V $ session t2 where t1.session_id = t2.sid ORDER BY T2.Logon_time ; SQL> SELECT SQL_ADDRESS from V $ sessions where sid =
;
SQL> SELECT * FROM V $ SQLTEXT where address =
;
SQL> Select Command_Type, Piece, SQL_Text from V $ SQLText WHERE Address = (SELECT SQL_ADDRESS FROM V $ Session a where sid = 18);
SQL> SELECT Object_id from v $ locked_Object;
SQL> Select Object_name, Object_Type from DBA_Objects Where Object_ID = ''; if there is a long-term one column, it may be a lock that is not released. We can use the following SQL statement to kill long-term not released abnormal locks: SQL> ALTER SYSTEM KILL Session 'SID, Serial #'; 17, View Wait (WAIT) situation:
SQL> SELECT V $ Waitstat.class, V $ Waitstat.count Count, Sum (V $ sysstat.value) SUM_VALUE FROM V $ WAITSTAT, V $ SYSSTAT WHERE V $ SYSSTAT.NAME IN ('DB Block Gets',' Conistent Gets ') Group by V $ WaitStat.class, V $ WaitStat.count; 18, view SGA situation:
SQL> SELECT NAME, BYTES from Sys.v_ $ SGASTAT ORDER BY Name ASC; 19, view catched object:
SQL> Select Owner, Name, DB_LINK, NAMESPACE, TYPE, Shable_Mem, Loads, Executions, Locks, Pins, Kept from V $ db_Object_cache; 20, view v $ sqlarea:
SQL> SELECT SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS, VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, EXECUTIONS, USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS,
Disk_reads, buffer_gets, rows_processed from v $ sqlarea; 21, view Object Categories:
Select Decode (O.Type #, 1, 'Index', 2, 'Table', 3, 'Cluster', 4, 'View', 5, 'Synynym', 6, 'Sequence', 'Other') Object_Type, Count (*) Quantity from sys.obj $ o Where o.type #> 1 Group by decode (O.Type #, 1, 'INDEX', 2, 'TABLE', 3, 'Cluster', 4, 'View' , 5, 'Synynym', ') Union Select' Column ', Count (*) from Sys.col $ Union Select' DB Link ', Count (*) from ALL_Objects; 22, About Connection Related information: 1) View which users are connected
Select S.OSUSER OS_USER_NAME, DECODE (Sign (48 - Command), 1, TO_CHAR (Command), 'Action Code #' || to_char (command)) Action, P.Program Oracle_Process, Status Session_Status, S.Terminal Terminal, S .program program, s.username user_name, s.fixed_table_sequence activity_meter, '' query, 0 memory, 0 max_memory, 0 cpu_usage, s.sid, s.serial # serial_num from v $ session s, v $ process p where s.paddr = p.addr and s.type = 'user' order by S.Username, S. OSUSER
2) View the resource occupation of the corresponding connection according to V.SID
Select n.name, v.value, n.class, n.statistic # from v $ statname n, v $ sessstat v where v.sid = 18 and v.statistic # = n.statistic # Order by n.class, n .statistic #;
3) View according to SID to see the corresponding connection is running SQL
SELECT / * PUSH_SUBQ * / Command_Type, SQL_Text, Sharable_Mem, Persistent_Mem, Runtime_Mem, Sorts, Version_Count,
loaded_versions, open_versions, users_opening, executions, users_executing, loads, first_load_time, invalidations, parse_calls, disk_reads, buffer_gets, rows_processed, sysdate start_time, sysdate finish_time, '>' || address sql_address, 'N' status from v $ sqlarea where address = ( SELECT SQL_ADDRESS from V $ session where sid = 8);
According pid View sql statement: select sql_text from v $ sqlwhere address in (select sql_address from v $ sessionwhere sid in (select sid from v $ session where paddr in (select addr from v $ process where spid = & pid))); 23, Query table space usage:
SELECT A.TABLESPACE_NAME "Table Space Name",
100-Round ((NVL (B.Bytes_Free, 0) /A.BYTES_ALLOC) * 100, 2) "Occupation (%)",
Round (A.BYTES_ALLOC / 1024/1024, 2) "Capacity (M)",
Round (NVL (B.BYTES_FREE, 0) / 1024/1024, 2) "Idle (M)",
Round ((A.BYTES_ALLOC-NVL (B.BYTES_FREE, 0)) / 1024/1024, 2) "Use (M)",
Largest "Maximum Expansion Section (M)", To_Char (Sysdate, 'YYY-MM-DD HH24: MI: SS') "Sample Time" from (SUM (F.Bytes) Bytes_alloc,
Sum (Decode (F.autoextensible, 'Yes', F.maxbytes, 'NO', F.BYTES) MAXBYTES
From dba_data_files f group by tablespace_name) a,
(Select F.Tablespace_name, SUM (F.BYTES) BYTES_FREE
From dba_free_space f Group by tablespace_name) b,
(Select Round (Max (ff.length) * 16/1024, 2) Largest, Ts.Name TableSpace_name
From sys.FET $ ff, sys.file $ tf, sys.ts $ TS
Where ts.ts # = ff.ts # and ff.file # = tf.relfile # and ts.ts # = tf.ts #
Group by ts.name, tf.blocks) c where a.tablespace_name = B.TABLESPACE_NAME AND ADABLESPACE_NAME = C.TABLESPACE_NAME Download Adobe Reader
SELECT UPPER (F.TABLESPACE_NAME) "Table Space Name", D. Tot_Grootte_MB "Table Space Size (M)", D. Tot_Grootte_MB - F. Total_Bytes "Use Space (M)", TO_CHAR (Round (((D. Tot_Grootte_MB - F. Total_Bytes) / D. Tot_Grootte_MB * 100, 2), '990.99') "Used than", F. Total_bytes "Idle Space (M)", F.max_bytes "Maximum Block (M)" from (SELECT TABLESPACE_NAME, ROM) (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 from sys.dba_data_files dd group by dd.tablespace_name) D where d.tablespace_name = f.Tablespace_name Order by 4 desc; 24, the degree of fragmentation of the query table space:
SQL> SELECT TABLESPACE_NAME, Count (TableSpace_name) from dba_free_space group by tablespace_name haVing count (TABLESPACE_NAME)> 10;
SQL> ALTER TABLESPACE NAME COALESCE;
SQL> ALTER TABLE TABLE_NAME DEAALLOCATE UNUSED
SQL> 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
SQL> SELECT * from TS_BLOCKS_V;
SQL> SELECT TABLESPACE_NAME, SUM (BYTES), Max (Bytes), Count (Block_ID) from DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
SQL> SELECT 'ALTER TABLESPACE' || TableSpace_name || 'coalesce;' from DBA_FREE_SPACE_COALESCED WHERE Percent_EXTENTS_COALESCED <100OR Percent_blocks_coalesced <100;
Since the free space debris is composed of several parts, such as the range, the maximum range size, etc., we can use FSFI - Free Space Fragmentation Index (free space debris index) value: fsfi = 100 * SQRT (max (extent) / SUM (Extents) * 1 / SQRT (SQRT (count (extents)))
rem fsfi value compute rem fsfi.sql column fsfi format 999,99 select tablespace_name, sqrt (max (blocks) / sum (blocks)) * (100 / sqrt (sqrt (count (blocks)))) fsfi from dba_free_space group by tablespace_name ORDER BY 1; Spool Fsfi.rep; / Spool OFF;
It can be seen that the maximum possible value of FSFI is 100 (an ideal single-text table space). As the range increases, the FSFI value slowly decreases, and the FSFI value will fall quickly as the maximum size is reduced.
For example, run the script fsfi.sql in a database, get the following FSFI value: TableSpace_name FSFI ------------------------------------------------------------------------------------------ ----- RBS 74.06 System 100.00 Temp 22.82 Tools 75.79 User_Tools 100.00 YDCX_DATA 47.34 YDCX_IDX 57.19 YDJF_DATA 33.80 YDJF_IDX 75.55 ---- Counting the FSFI value of the database, you can use it as a comparable parameter. In a table space with enough effective free space, and the FSFI value exceeds 30, there is little problem of effective free space. When a space will be close to the parameter, you need to make a fragmentation.
25. What database instances are running:
SELECT INST_NAME FROM V $ ACTIVE_INSTANCES;
26. Take the DBA role to see the current database lock:
Select Object_ID, Session_ID, Locked_Mode from V $ locked_Object;
SELECT T2.USERNAME, T2.SID, T2.SERIAL #, T2.LOGON_TIME FROM V $ LOCKED_OBJECT T1, V $ session t2
WHERE T1.SESSION_ID = T2.SID ORDER BY T2.LOGON_TIME;
27, check if the table is a partition table:
SELECT TABLE_NAME, Partitioned from User_Tables Where Table_name = 'localusage';
Table_name par
---------------------------------------
Localusage Yes
28. View the partition name of the partition table and the corresponding table space name:
SELECT TABLE_NAME, Partition_name, TableSpace_name from user_tab_partitions where table_name like '% usage%';
29, check whether the index is a partition index:
SELECT INDEX_NAME, TABLE_NAME, STATUS, Partitioned from user_indexes where table_name like '% USAGE'
30. If the backed patitioned is YES, execute the following statement to query the type of partition index: select index_name, table_name, locality from user_part_indexes;
31. Dual is an actual existing table in Oracle, any user can read, commonly used in SELECT without a target table.
View the system time:
SELECT TO_CHAR (SYSDATE, 'YY-MM-DD HH24: MI: SS') Shijian from DUAL
32. View the number of extents in the index section:
Select segment_name, count (*) from DBA_EXTENTS
WHERE segment_type = 'index' and owner = 'scott' group by segment_name;
33. View the user index in the system table (used to check the existence of other users in the System tablespace):
SQL> SELECT Count (*) from dba_indexes where tablespace_name = 'system' and Owner Not in ('sys', 'system');
34. View the extension of the index in the WacOS table space:
Select Substr (segment_name, 1, 20) "Segment Name", Bytes, Count (Bytes)
From dba_extents where segment_name in (SELECT INDEX_NAME FROM DBA_INDEXES
WHERE TABLESPACE_NAME = 'wacos') Group by segment_name, bytes Order by segment_name;
35. View the read and write performance of the tablespace data file:
SQL> Select Name, Phyrds, Phywrts, Avgiotim, Miniotim, Maxiowtm, Maxiortm from V $ Filestat, V $ DataFile WHERE V $ FileStat.File # = V $ datafile.file #;
SQL> Select fs.name name, f.phyrds, f.phyblkrd, f.phywrts, f.phyblkwrt, f.readtim, f.writetim
From v $ filestat f, v $ datafile fs where f.file # = fs.file # Order by fs.name;
(Note: If Phyblkrd is close to PHYRDS, there is a table with full table scanning in this table space, these tables need to adjust indexes or optimize SQL statements)
36. Conversion table space is managed by Local:
SQL> EXEC SYS.DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('TBS_TEST');
37, check which user is in use:
Select Username, SID, Serial #, SQL_Address, Machine, Program, TableSpace, SegType, Contents from V $ Session Se, V $ Sort_USAGE SU where se.saddr = su.session_addr;
38. Viewing the Running Session in the 10 IO:
Se.Serial #, pr.spid, se.user, se.status, se.terminal, se.program, se.module, se.sql_address, st.event, st.p1text, si.physical_reads, Si.block_changes from V $ Session Se, V $ session_wait st, v $ sess_io si, v $ process pr bere st.sid = se.sid and st.sid = si.sid and se.paddr = pr.Addr and se. SID> 6 and st.wait_time = 0 and st.event Not Like '% SQL%' Order by Physical_Reads DESC; 39, find the top ten performance poor SQL:
Select * from (SELECT PARSING_USER_ID EXECUTIONS, SORTS, Command_Type, Disk_reads, SQL_Text from V $ SQLAREA Order by Disk_reads Desc) Where Rownum <10;
40. Delete the statement of all tables under the user: select 'drop table' || Table_name || 'Cascade constraints;' from user_tables;
41, view Lock, and kill the session:
Set Linesize 132 PageSize 66
Break on Kill on UserName on Terminal
Column Kill Heading 'Kill String' Format A13
Column res be here resource type 'Format 999
Column id1 Format 9999990
Column ID2 Format 9999990
Column LMODE BEADING 'LOCK HELD' FORMAT A20
Column Request Heading 'Lock Requested' Format A20
Column serial # Format 99999
Column UserName Format A10 Heading "Username"
Column Terminal Heading Term Format A6
Column Tab Format A35 Heading "Table Name"
Column Owner Format A9
Column Address Format A18
SELECT NVL (S.USERNAME, 'Internal') UserName,
NVL (S.Terminal, 'None') Terminal,
L.SID || ',' || s.serial # kill,
U1.NAME || ',' || Substr (T1.NAME, 1, 20) TAB,
Decode (L.Lmode, 1, 'NO LOCK',
2, 'Row Share',
3, 'ROW EXCLUSIVE',
4, 'Share',
5, 'Share Row Exclusive',
6, 'Exclusive', NULL) LMODE,
Decode (L.Request, 1, 'NO LOCK',
2, 'Row Share',
3, 'ROW EXCLUSIVE',
4, 'Share',
5, 'Share Row Exclusive',
6, 'Exclusive', NULL) Request
From V $ LOCK L,
V $ session s,
Sys.user $ u1,
Sys.obj $ T1
WHERE L.SID = S.SID
And t1.obj # = decode (L.ID2, 0, L.ID1, L.ID2)
And u1.user # = T1.OWNER #
And S.Type! = 'Background'
Order by 1, 2, 5;
--alter system kill session ',';
Column UserName Format A15
Column Sid Format 9990 Heading SID
Column Type Format A4
Column Lmode Format 990 Heading 'Held'
Column Request Format 990 Heading 'Req'
Column id1 Format 9999990
Column ID2 Format 9999990
Break ON ID1 SKIP 1 DUP
Spool tfslckwt.lst
Select sn.username,
M.SID,
M.TYPE,
Decode (M.Lmode, 0, 'None',
1, 'Null',
2, 'Row Share',
3, 'ROW EXCL.',
4, 'Share',
5, 'S / ROW EXCL.',
6, 'ExClusive',
LMODE, LTRIM (To_Char (LMODE, '990'))) LMODE,
Decode (M.Request, 0, 'None',
1, 'Null',
2, 'Row Share',
3, 'ROW EXCL.',
4, 'Share',
5, 'S / ROW EXCL.',
6, 'ExClusive',
Request, Ltrim (To_Char (M.Request, '990')))) Request,
M.ID1,
M.ID2
From v $ session SN,
V $ LOCK M
WHERE (sn.sid = m.sid and m.request! = 0)
OR (sn.SID = m.sid and
m.Request = 0 and LMODE! = 4 and
(ID1, ID2) in (Select S.ID1,
S.ID2
From v $ locoo
WHERE REQUEST! = 0 and s.id1 = m.id1 and sid2 = m.id2)
)
Order by ID1, ID2, m.Request;
Spool off
Clear Breaks
42. View all indexes under WacOS tables:
SQL> SELECT 'Analyze Index' || Segment_name || 'Validate Structure;' from DBA_SEGMENTS WHERE TABLESPACE_NAME = 'Wacos'and Segment_Type =' INDEX ';
43. How to identify IO competition and load balancing:
COL file name Format A35
SELECT
DF.NAME file name,
fs.Phyrds Reading,
Fs.Phywrts write time, (fs.readtim / decode (fs.phyrds, 0, -1, fs.phyrds)) read time,
(fs.writetim / decode (fs.phywrts, 0, -1, fs.phywrts) write time
From
V $ datafile df,
V $ filestat fs
WHERE DF.FILE # = fs.file #
Order by DF.NAME
/
File name reading time a number of read times write time
------------------------------------------------------------------------------------------ ---- ---------- ---------- ----------
C: /oracle/oradata/oradb/dr01.dbf 885 883 0 0
C: /oracle/oradata/oradb/indx01.dbf 885 883 0 0
C: /oracle/oradata/oradb/oem_repository.ora 885 883 0 0
C: /oracle/oradata/oradb/rbs01.dbf 925 22306 0 0
C: /oracle/oradata/oradb/system01.dbf 50804 155025 0 0
C: /oracle/oradata/oradb/temp01.dbf 887 894 0 0
C: /oracle/oradata/oradb/tools01.dbf 886 892 0 0
C: /oracle/oradata/oradb/users01.dbf 885 883 0 0
8 lines have been selected.
Where: ORADB is the database name, because the databases in this example have been installed by default, no optimization, adjustment,
So, I have been working on the SYSTEM table space, causing the data files in the SYSTEM table space SYSTEM01.DBF to be read and written.
This also illustrates that try not to do an operation-independent operation with the system table space, and should establish a separate table space to each user.
44. Which sessions are using the rolling segments:
COL Roll Square Format A10
COL SID FORMAT 9990
COL user name Format A10
COL operation procedure Format A80
Col Status Format A6 Trunc
Select R.Name returns the name,
S.SID,
S.Serial #,
S.Username username,
T.STATUS,
T.CR_GET,
T.PHY_IO,
T.USED_UBLK,
T.Noundo,
Substr (S.Program, 1, 78) Operator
From sys.v_ $ session s, sys.v_ $ transaction t, sys.v_ $ rollname r
Where t.addr = s.taddr and t.xidusn = r.USN
Order by t.cr_get, t.phy_io;
45. Checking Who Lock What Object: Set Line 200 Col "O / S-User" Format A10 Col "ORA-User" Format A10 Col "Obj Locked" Format A30 SELECT / * Rule*/s.machine, s. OSUser "O / S-User", S.Username "Ora-User", S.SID "session-id", s.serial # "serial", s.Process "Process-ID", S.Status "status" , L.Name "obj locked", L.Mode_held "Lock Mode" from v $ session s, dba_dml_locks l, v $ process p where l.session_id = s.sid and p.addr = s.paddr / 46. Causes waiting Lock information, such as Lock type, etc .: Col Event Format A30 Set line 160 Col Machine Format A10 Col UserName Format A15 Select B.SID, B. Serial #, B.USERNAME, MACHINE, EVENT, WAIT_TIME, CHR (Bitand (P1 , -16777215) || CHR (Bitand (P1, 16711680) / 65535) "Enqueue Type" from v $ session_wait a, v $ session b Where event not limited 'sql * n%' and Event not Like 'RDBMS % 'and A.SID = B.SID and B.SID> 8 and Event =' Enqueue 'Order by UserName / 47. list of the lock Oracle Objects
Set line 120
Column Object_name Format A32
Column OS_User_Name Format A12
COLUMN ORAUSER FORMAT A12
Column SQL_Text Format A32
Column serial # format 999999
Column Sid Format 99999
SELECT OS_USER_NAME, ORACLE_USERNAME AS ORAUSER, S.SID, O.Object_name,
O.Object_type, s.serial #, a.sql_text
From V $ locked_Object L, DBA_Objects O, V $ Session S, V $ SQLAREA A
Where l.object_id = O.Object_ID
And S.SQL_Address = a.address
And L.Session_ID = S.SID;
SELECT 'ALTER SYSTEM KILL Session' '' || To_Char (S.SID) || ',' || to_CHAR (S.Serial #) || '' ';'
AS "Statement to Kill"
From v $ locked_Object L, DBA_Objects O, V $ Session S
Where l.object_id = O.Object_ID
And L.Session_ID = S.SID; Oracle Database Performance Monitoring SQL
1. Waiting for SQL> SELECT EVENT, SUM (Decode (Wait_Time, 0, 1)) "Prev", SUM (Decode (Wait_Time, 0, 1, 0)) "CURR", count (*) " Tot "from v $ session_wait group by Event ORDER BY 4; 2. Strong Segment Squatting SQL> Select Name, Waits, Gets, Waits / Gets" Ratio "from V $ ROLLSTAT A, V $ ROLLNAME B WHERE A. USN = B.USN; 3. Monitor tablespace I / O ratio SQL> select df.tablespace_name name, df.file_name "file", 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 file system I / O ratio SQL> 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 bwhere a.file # = B.File #; 5. find all indexes in a SQL 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_inde Xes.index_name, column_position; 6. Monitor SGA's hit rate SQL> 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 $ SSSSTAT 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 SQL> SELECT Parameter, Gets, getMisses, getMisses / (Gets getMisses) * 100 "Miss Ratio", (1- (getMisses) / ( SUM (Gets) SUM (GetMisses))))))))))))) * 100 "Hit Ratio" from v $ ROWCACHE WHERE GETS GETMISSES <> 0 group by parameter, gets, getMisses;
8. Monitor the hit rate of the cache in SGA, should be less than 1% SQL> Select SUM (Pins) "Total Pins", SUM (Reloads) "Total Reloads", SUM (RELOADS) / SUM (PINS) * 100 Libcache from v $ librarycache; sql> select sum (pins) "Hit Radio", SUM (RELOADS) / SUM (PINS) "Reload Percent" from v $ librarycache; 9. Show all database objects and size SQL> 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. monitoring SGA redo log buffer hit ratio should be less than 1% SQL> 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, add sort_area_size sql> select name, value from v $ sysstat where name in ('sorts ",' sort S (Disk) '); 12. Monitor the current database Who is running what SQL statement SQL> 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 SQL> SELECT (SUM (Pins - Reloads)) / SUM (Pins "LIB Cache" from v $ librarycache; sql> select (sum (gets - getmisses - usage - fixed) / sum (Gets) "Row Cache" from V $ Rowcache; SQL> 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 0% is good) SQL> Select SUM (Gets) "Dictionary Gets", SUM (GetMisses "Dictionary Cache Get Misses"
From v $ rowcache; 14. Find Oracle Characters SQL> Select * from sys.props $ where name = 'nls_characterset'; 15. Monitor MTS SQL> SELECT Busy / (Busy IDle) "Shared Servers Busy" from v $ dispatcher (This value is greater than 0.5, parameters need to increase) SQL> Select SUM (WAIT) / SUM (Totalq) "Dispatcher Waits" from v $ queue where type = 'dispatcher'; SQL> Select Count (*) from V $ dispatcher; SQL> select servers_highwater from v $ mts; (when servers_highwater close mts_max_servers, parameters to be increased) 16. fragmented SQL> select tablespace_name, count (tablespace_name) from dba_free_space group by tablespace_name having count (tablespace_name)> 10; SQL> alter tablespace name coalesce; SQL> alter table name deallocate unused; SQL> 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; SQL> SELECT TABLESPACE_NAME, SUM (Bytes), Max (Bytes), Count (Block_ID) from DBA_FREE_SPACE GROUP BY TABLESPACE_NAME; View Degraded Table SQ L> 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. tables, indexes of the inspection stored SQL> 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; SQL> select segment_name, count (*) from dba_extents where segment_type = 'INDEX' and owner = '& owner' group by segment_name; 18, find use CPU plurality User Session SQL> SELECT A.SID, SPID, STATUS, SUBSTR (A.Program, 1, 40) PROG, A.TERMINAL, OSUSER, VALUE / 60/100 VALUE FROM V $ Session A, V $ Process B, V $ seestat c where c.statistic # = 12 and c.SID = a.sid and a.paddr = b.addr Order by value desc; (12 is CPU Used by this session)
Table space statistics
A, script description:
This is my most common script that uses it to display the status of all tablespaces in the database, such as the size of the table space, the use of space, the percentage of use, how much the number of free space and the current table space is now.
B, the script original:
Select Upper (f.tablespace_name "Table Space Name",
D. Tot_Grootte_MB "Table Space Size (M)",
D. TOT_GROOTTE_MB - F. TOTAL_BYTES "The space (M) has been used,"
TO_CHAR ((D. Tot_Grootte_MB - F. TOTAL_BYTES) / D. TOT_GROOTTE_MB * 100, 2), '990.99') "Use Comparison",
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 4 DESC;
View unable to extensible segment
A, script description:
Oracle is not extended for a paragraph or index, which is not the remaining space in the table space, but is taken in the largest block in these remaining spaces. Is it comparable to the "next" value of the index, so Sometimes a table space remains in the free space, oracle or the index cannot be expanded when you use it, that is, because this is too much. This script is some of the information that cannot be expanded. B, the script original:
Select segment_name,
Segment_type,
Owner,
A.Tablespace_name "TableSpaceename",
Initial_extent / 1024 "INITAL_EXTENT (K)",
Next_extent / 1024 "next_extent (k)",
PCT_INCREASE,
B.BYTES / 1024 "TableSpace Max Free Space (K)",
B. Sum_Bytes / 1024 "TableSpace Total Free Space (K)"
From DBA_SEGMENTS A,
(Select TableSpace_name, max (bytes) Bytes, SUM (Bytes) SUM_BYTES from DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B
Where a.tablespace_name = b.tablespace_name
And next_extent> B.BYTES
Order BY 4, 3, 1;
Viewing the space used by the segment (table segment, index)
A, script description:
Sometimes you might want to know how much a table or an index take up, this script is to meet your requirements, replace the content in <>.
B, the script original:
SELECT OWNER,
Segment_name,
Sum (Bytes) / 1024/1024
From DBA_SEGMENTS
WHERE OWNER =
And segment_name =
GROUP BY OWNER, Segment_Name
ORDER BY 3 DESC;
View the table lock in the database
A, script description:
The style of this apartment is much, like all kinds, but I think this is the most practical, don't believe you, don't say more, the lock is the content that every DBA must be involved, when you know some Which SESSION is locked by a table, you used this script.
B, the script original:
SELECT A.OWNER,
A.Object_name,
B.xIDUSN,
B.XIDSLOT,
B.XIDSQN,
B.Session_ID,
B. Oracle_Username,
B.OS_USER_NAME,
B.Process,
B.locked_mode,
C. Machine,
C.STATUS,
C. Server,
C.SID,
C. Serial #,
C.Program
From all_Objects a,
V $ locked_Object B,
Sys.gv_ $ session c
Where (a.Object_id = B.Object_ID)
AND (B.Process = C.Process)
- and
ORDER BY 1, 2;
Process stored procedure is locked
A, script description:
In the actual process, you may have to recompile a store procedure, always in the waiting state, and finally will not lock the object. At this time, you can use this script to find the SID of the lock process, you need to pay attention to check V $ access this The view is very slow, and some cloth is concerned. B, the script original:
SELECT * FROM V $ Access
Where owner = ""
