See which SQL statements are consuming resources: select * from (select SQL_TEXT, EXECUTIONS, DISK_READS, BUFFER_GETS from v $ sqlarea order by BUFFER_GETS desc) where rownum <= 10; see table space usage: SQL> col tablespace_name format a15SQL> select a. TABLESPACE_NAME, A.BYTES / 1024/1024 TOTALBYTES, SUM (B.BYTES) / 1024/1024 FreeBytes, Round (100-SUM (NVL (B.bytes, 0)) / (a.bytes) * 100, 2) | | '%' used_percentfrom dba_data_files a, dba_free_space bwhere a.file_id = B.file_idgroup by a.tablespace_name, a.bytesorder by a.tablespace_name; 2 3 4 5 6
View Lock: SELECT DECODE (Request, 0, 'Lock Holder', 'Waitinger') AS TYPE, SID AS SESSIONID, ID1, ID2, LMODE, Request, Type from V $ LOCK WHERE (ID1, ID2, TYPE) In (SELECT ID1, ID2, TYPE FROM V $ LOCK Where Request> 0) Order by ID1, Request
HP system kernel parameters Significance: SHMMAX Meaning: This setting does not decide how much physical memory exactly the Oracle database or operating system, only determines the number of memory available. This setting does not affect the kernel resources of the operating system. Setting method: 0.5 * Physical memory example: SHMINFO_SHMMAX = 10485760 shmmin meaning: Shared memory minimum size. Setting method: Usually set to 1. Example: set shmsys: shminfo_shmmin = 1: SHMMNI meaning: The maximum number of memory segments in the system. Example: set shmsys: shminfo_shmmni = 100 shmseg Meaning: The number of maximum shared memory segments per user process can be used. Example: set shmsys: shminfo_shmseg = 20: semmni meaning: The maximum number of Semaphore Identifierer in the system. Setting method: Set the value of this variable to all Oracle's instances of all Oracle's instances of the processes to add 10. Example: Set Semsys: seminfo_semmni = 100 SemMns Emnity: The maximum number of EMAPHORES in the system. Setting method: This value can be calculated in the following manner: the sum of the value of the processes of each Oracle instance, the sum of the value (remove the largest processes parameter) the number of the maximum of the processes × 2 10 × Oracle instance. Example: Set Semsys: seminfo_semmns = 200 Semmsl: Meaning: The maximum number of Semaphore in a set. Setting method: Set the value of the largest processes in INITSID.ORA for 10 all Oracle instances. Examples: Set semsys: seminfo_semmsl = -200 Performance Testing: select SQL_TEXT, DISK_READS, executions, buffer_gets from v $ sqlarea order by disk_reads descselect * from v $ nls_parametersselect * from v $ resource_limitselect * from v $ parameter
Can obtain usage (PCT Used) and Debris (FSFI) Col TableSpace Format A24Select Df.Tables, DF.BYTES / (1024 * 1024) "Total Size", SUM (1024 * 1024) "Free size", Round (SUM (fs.bytes) * 100 / df.bytes "PCT FREE", Round ((DF.BYTES-SUM (FS.BYTES)) * 100 / DF.BYTES "PCT Used ", ROUND (100 * SQRT (Max (fs.bytes) / sum (fs.bytes)) * (1 / SQRT (SQRT (count (fs.bytes))))), 2) fsfifrom dba_free_space fs, (Select TableSpace_name , sum (bytes) bytes from dba_data_files group by tablespace_name) df where fs.tablespace_name = df.tablespace_namegroup by df.tablespace_name, df.bytesoracle find the lock of standard document $ ORACLE_HOME / rdbms / admin / utllockt.sql
Find a process of running Idselect B.spid from V $ Session A, V $ Process B, DBA_JOBS_RUNNING C Where A.SID = C.SID and A.Paddr = B.Addrselect B.SPID from V $ Session A, V $ Process B, DBA_JOBS_RUNNING C Where A.SID = C.SID and A.PADDR = B.Addr and C.Job = ??
SELECT A.SID FROM V $ Session A, V $ Process B where b.spid = 25924 and a.paddr = B.Addr
col sql_text format a50; select sql_text from v $ sqltext, v $ sessionwhere v $ sqltext.hash_value = v $ session.prev_hash_valueand v $ sqltext.address = v $ session.sql_addressand v $ session.sid = (select a.sid from v $ session a, v $ process b where b.spid = 25924 and a.paddr = b.addr)
Get long transaction objects: SELECT TARGET, Count (*) from v $ session_longops group by target order by count (*) desc; get object occupation Space Select Owner, segment_name, bytes from dba_segments Order bytes desc
Export script col text format a500set heading offspool test.outselect text from user_source; spool off
- View all sessions, including session ID, user name, status, client machine name, running program, and login time Prompt Show Info of Sessioncol UserName Format A10; Col Machine Format A10; Colim Format A10; Col in c A10; SID, Serial #, Username, Status, Machine, Terminal, Program, Logon_time from v $ session; (Field CTIME, Unit Second), - If the waiting time is longer, you need to check if the session is blocked by other sessions, the field request is not 0 indicates that the session is blocked while waiting for other session release locks, - field Request 0 indicates that the session has been locked, and Block does not indicate that the session blocks other sessions. Prompt Show Info of Lockselect Sid, Type, ID1, ID2, LMODE, REQUEST, CTIME, Block from V $ LOCK WHERE TYPE IN ('TM', 'TX');
- View Locked Objects PROMPT Show Object Be LockedCol "Loked Object" Format A20; Col "OS User" Format A20; Col "Oracle User" Format A20; Col "OS Process ID" Format A20; SELECT B. object_name "loked object", a.session_id "session id", a.oracle_username "oracle user", a.os_user_name "os user", a.process "os process id" from v $ locked_object a, dba_objects b where a.object_id = B.Object_id;
- Query lock it comes to SQL statements prompt show sqltext issued by locked objectcol sql_text format a50; select sql_text from v $ sqltext, v $ sessionwhere v $ sqltext.hash_value = v $ session.prev_hash_valueand v $ sqltext.address = v $ session . SQL_Addressand V $ session.sidin (SELECT DISTINCT SID FROM V $ LOCK WHERE CTIME> 0 and TYPE IN ('TM', 'TX'));
- Check the implementation of the SQL statement - Most Viewed SQL buffer pool physical read statements prompt show disk_read infoselect * from (select sql_text, disk_reads / executions "Avg Disk_Reads", executions from V $ SQLArea where executions> 0 order by disk_reads / Executions desc) where rownum <= 20;
- the most logical view the SQL statement read buffer pool prompt show buffer_gets infoselect * from (select sql_text, buffer_gets / executions "Avg Disk_Reads", executions from V $ SQLArea t where executions> 0 order by buffer_gets / executions desc) where rownum < = 20; NOTE: 163424.1
Get hotspot chip object Select Child # "cchild", addr "saddr", gets "sgets", Misses "smisses", Sleeps "ssleeps" from v $ latch_children where name = 'cache buffers chains'Rder BY 4, 1, 2, 3;
Get hotspot block object Select / * rule * / e.WNER || '.' || E.SEGMENT_NAME segment_name, e.extent_id extent #, x.dbablk - E.block_id 1 block #, x.tch, l. Child # from sys.v $ latch_children l, sys.x $ bh x, sys.dba_extents ewhere x.hladdr = 'above statement query addr' E.File_ID = X.file # and x.hladdr = L.Addr and X.dbablk Between E.block_id and e.block_id e.blocks -1 Order by x.tch desc;
Get hotspot block object SELECT / * USE_HASH (BH) * / de.owner || '.' || de.segment_name segment_name, de.segment_type segment_type, de.dbablk - de.block_id 1 block #, Bh.tchfrom sys.x $ BH BH, SYS.DBA_EXTENTS DEWHERE De.file_id = BH.FILE # and bh.dbablk between de.block_id and de.block_id de.blocks -1 and bh.tch> 10ORDER BY BH .tch
Get hotspot block object Select / * ordered * / de.owner || '.' || de.segment_name segment_name, de.segment_type segment_type, de.extent_id extent #, bh.dbablk - de.block_id 1 block #, BH .lru_flag, bh.tch, lc.child # from (select max (sleeps) maxsleeps from v $ latch_children where name = 'cache buffers chains') max_sleeps, v $ latch_children ls sys.x $ bh bh, sys.dba_extents dewhere lc .name = 'cache buffers chains' and lc.sleeps> (0.8 * maxsleeps) and bh.hladdr = lc.addr and de.file_id = Bh.file # and bh.dbablk between de.block_id and de.block_id de. Blocks -1order by Bh.TCH Solving Oracle Tools: My NLS_LANG is originally simplified chinese_china.zHS16GBK to be changed to American_America.zHS16GBK, you should modify home0 or homen in NLS_LANG in the registry, NLS_LANG under Oracle is useless.
Implementation: Select * from (SELECT A. *, ROWNUM R from T_UserInfo a WHERE ROWNUM <= 6000 ORDER BY RegisterTime) Where r> = 3000
View User Trace file Select D.Value || '/' || (instance (I.instance, Chr (0)) || '_ora_' || P.SPID || '.trc' trace_file_name from (SELECT P .SPID from sys.v $ mystat m, sys.v $ session s, sys.v $ process p where m.statistic # = 1 and s.SID = m.sid and p.addr = s.paddr) p, Select T.Instance from sys.v $ thread t, sys.v $ parameter v where v.name = 'thread' and (v.value = 0 or t.thread # = to_number (v.value))) i, Select Value from sys.v $ parameter where name = 'user_dump_dest') D /
Tracking operation (in a materialized view refresh Example) conn / as sysdba@ORACLE_HOME/rdbms/admin/dbmssupp.sqlGRANT execute ON dbms_support TO hw; CREATE PUBLIC SYNONYM dbms_support FOR dbms_support; conn hw / hwexec dbms_support.start_trace (waits => TRUE , binds => true); exec dbms_refresh.refresh ('"hw". "g101"); exec dbms_support.stop_trace;
TkProf $ Oracle_Base / Admin /
Track Other Process Codes DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION (SID => 42, Serial # => 1215, Parnam => 'Timed_statistics', Bval => True)
DBMS_SYSTEM.SET_INT_PARAM_IN_SESSION (SID => 42, Serial # => 1215, Parnam => 'max_dump_file_size', intVal => 2147483647)
DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION (SID => 42, Serial # => 1215, Parnam => 'TIMED_STATISTICS', BVAL => false)
