Tuning Scripts

--檢視所有使用者下的monitor資訊 SELECT u.name owner, io.name index_name, t.name table_name, decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring, decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used, ou.start_monitoring start_monitoring, ou.end_monitoring end_monitoring FROM sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou WHERE i.obj# = ou.obj# AND io.obj# = ou.obj# AND t.obj# = i.bo# AND u.user# = io.owner#; --檢視長時間執行會話的指令碼 SELECT L.sid ssid, substr(OPNAME,1,15) opname, target, trunc((sofar/totalwork)*100) pct, to_char(60*sofar*8192/(24*60*(last_update_time - start_time))/1024/1024/60, '9999.0') Rate, elapsed_seconds/60 es, time_remaining/60 tr,PROGRAM,MACHINE FROM v$session_longops L,V$SESSION S WHERE time_remaining > 0 AND L.SID=S.SID ORDER BY start_time / --date轉為redo dump time的指令碼 SET ECHO off REM NAME: TFSTM2RD.SQL REM USAGE:"@path/tfstm2rd" REM ------------------------------------------------------------------------ REM REQUIREMENTS: REM None. REM ------------------------------------------------------------------------ REM AUTHOR: REM Anonymous REM Copyright 1996, Orqacle Corporation REM ------------------------------------------------------------------------ REM PURPOSE: REM Converts a standard date into redo dump time format. REM ------------------------------------------------------------------------ REM EXAMPLE: REM Enter day (DD/MM/YYYY) ? 08/07/1996 REM Enter time (HH24:MI:SS) ? 12:05:05 REM REM REDO_YEAR REDO_MONTH REDO_DAY REDO_HOUR REDO_MIN REDO_SEC REM --------- ---------- -------- --------- -------- -------- REM 1996 7 8 12 5 5 REM REM EDO_TIME REM ---------- REM 273845105 REM REM ------------------------------------------------------------------------ REM DISCLAIMER: REM This script is provided for educational purposes only. It is NOT REM supported by Oracle World Wide Technical Support. REM The script has been tested and appears to work as intended. REM You should always run new scripts on a test instance initially. REM ------------------------------------------------------------------------ REM Main text of script follows: undefine redo_day undefine redo_hhmiss accept redo_day prompt "Enter day (DD/MM/YYYY) ? " accept redo_hhmiss prompt "Enter time (HH24:MI:SS) ? " column redo_year new_value redo_year format 9999 column redo_month new_value redo_month format 9999 column redo_day new_value redo_day format 9999 column redo_hour new_value redo_hour format 9999 column redo_min new_value redo_min format 9999 column redo_sec new_value redo_sec format 9999 column redo_time new_value redo_time set verify off SELECT to_number(to_char(to_date('&redo_day &redo_hhmiss', 'DD/MM/YYYY HH24:MI:SS'),'YYYY')) redo_year, to_number(to_char(to_date('&redo_day &redo_hhmiss', 'DD/MM/YYYY HH24:MI:SS'),'MM')) redo_month, to_number(to_char(to_date('&redo_day &redo_hhmiss', 'DD/MM/YYYY HH24:MI:SS'),'DD')) redo_day, to_number(to_char(to_date('&redo_day &redo_hhmiss', 'DD/MM/YYYY HH24:MI:SS'),'HH24')) redo_hour, to_number(to_char(to_date('&redo_day &redo_hhmiss', 'DD/MM/YYYY HH24:MI:SS'),'MI')) redo_min, to_number(to_char(to_date('&redo_day &redo_hhmiss', 'DD/MM/YYYY HH24:MI:SS'),'SS')) redo_sec FROM dual; SELECT ( ( ( ( ( ( &redo_year - 1988 ) ) * 12 + ( &redo_month - 1 ) ) * 31 + ( &redo_day - 1 ) ) * 24 + ( &redo_hour ) ) * 60 + ( &redo_min ) ) * 60 + ( &redo_sec ) redo_time FROM dual; --redo dump time 轉為date的指令碼 SET ECHO off REM NAME: TFSRD2TM.SQL REM USAGE:"@path/tfsrd2tm" REM ------------------------------------------------------------------------ REM REQUIREMENTS: REM None. REM ------------------------------------------------------------------------ REM AUTHOR: REM Anonymous REM Copyright 1996, Oracle Corporation REM ------------------------------------------------------------------------ REM PURPOSE: REM Will convert redo dump time into a readable date. REM REM ------------------------------------------------------------------------ REM EXAMPLE: REM Enter redo time ? 273845105 REM REM REDO_YEAR REDO_MONTH REDO_DAY REDO_HOUR REDO_MIN REDO_SEC REM --------- ---------- -------- --------- -------- -------- REM 1996 7 8 12 5 5 REM REM ------------------------------------------------------------------------ REM DISCLAIMER: REM This script is provided for educational purposes only. It is NOT REM supported by Oracle World Wide Technical Support. REM The script has been tested and appears to work as intended. REM You should always run new scripts on a test instance initially. REM ------------------------------------------------------------------------ REM Main text of script follows: undefine redo_time accept redo_time prompt "Enter redo time ? " column redo_year new_value redo_year format 9999 column redo_month new_value redo_month format 9999 column redo_day new_value redo_day format 9999 column redo_hour new_value redo_hour format 9999 column redo_min new_value redo_min format 9999 column redo_sec new_value redo_sec format 9999 set verify off SELECT trunc(trunc(trunc(trunc(trunc(&redo_time/60)/60)/24)/31)/12)+1988 redo_year, mod(trunc(trunc(trunc(trunc(&redo_time/60)/60)/24)/31),12)+1 redo_month, mod(trunc(trunc(trunc(&redo_time/60)/60)/24),31)+1 redo_day, mod(trunc(trunc(&redo_time/60)/60),24) redo_hour, mod(trunc(&redo_time/60),60) redo_min, mod(&redo_time,60) redo_sec FROM dual; ----鎖解析指令碼1 set linesize 200 feedback off heading on column sid format 999 column res heading 'Resource Type' format a20 column id1 format 9999999 column id2 format 9999999 column lmode heading 'Lock Held' format a14 column request heading 'Lock Req.' format a14 column serial# format 99999 column username format a10 column terminal heading Term format a6 column tab format a10 column owner format a8 SELECT l.sid,s.serial#,s.username,s.terminal, decode(l.type,'RW','RW - Row Wait Enqueue', 'TM','TM - DML Enqueue', 'TX','TX - Trans Enqueue', 'UL','UL - User',l.type||'System') res, substr(t.name,1,10) tab,u.name owner, l.id1,l.id2, decode(l.lmode,1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Shr Row Excl', 6,'Exclusive',null) lmode, decode(l.request,1,'No Lock', 2,'Row Share', 3,'Row Excl', 4,'Share', 5,'Shr Row Excl', 6,'Exclusive',null) request FROM v$lock l, v$session s, sys.user$ u,sys.obj$ t WHERE l.sid = s.sid AND s.type != 'BACKGROUND' AND t.obj# = l.id1 AND u.user# = t.owner# / --鎖解析指令碼2 set lines 200 set pagesize 66 spool locks.lis break on Kill on sid on username on terminal column Kill heading 'Kill String' format a13 column res heading 'Resource Type' format 999 column id1 format 9999990 column id2 format 9999990 column locking heading 'Lock Held/Lock Requested' format a40 column lmode heading '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 a30 heading "Table Name" column owner format a9 column LAddr heading "ID1 - ID2" format a18 column Lockt heading "Lock Type" format a40 column command format a25 column sid format 990 rem L.SID||','||S.SERIAL# Kill, SELECT nvl(S.USERNAME,'Internal') username, L.SID, nvl(S.TERMINAL,'None') terminal, decode(command, 0,'None',decode(l.id2,0,U1.NAME||'.'||substr(T1.NAME,1,20),'None')) tab, decode(command, 0,'BACKGROUND', 1,'Create Table', 2,'INSERT', 3,'SELECT', 4,'CREATE CLUSTER', 5,'ALTER CLUSTER', 6,'UPDATE', 7,'DELETE', 8,'DROP', 9,'CREATE INDEX', 10,'DROP INDEX', 11,'ALTER INDEX', 12,'DROP TABLE', 13,'---', 14,'---', 15,'ALTER TABLE', 16,'---', 17,'GRANT', 18,'REVOKE', 19,'CREATE SYNONYM', 20,'DROP SYNONYM', 21,'CREATE VIEW', 22,'DROP VIEW', 23,'---', 24,'---', 25,'---', 26,'LOCK TABLE', 27,'NO OPERATION', 28,'RENAME', 29,'COMMENT', 30,'AUDIT', 31,'NOAUDIT', 32,'CREATE EXTERNAL DATABASE', 33,'DROP EXTERNAL DATABASE', 34,'CREATE DATABASE', 35,'ALTER DATABASE', 36,'CREATE ROLLBACK SEGMENT', 37,'ALTER ROLLBACK SEGMENT', 38,'DROP ROLLBACK SEGMENT', 39,'CREATE TABLESPACE', 40,'ALTER TABLESPACE', 41,'DROP TABLESPACE', 42,'ALTER SESSION', 43,'ALTER USER', 44,'COMMIT', 45,'ROLLBACK', 46,'SAVEPOINT', 47,'PL/SQL EXECUTE', 48,'SET TRANSACTION', 49,'ALTER SYSTEM SWITCH LOG', 50,'EXPLAIN', 51,'CREATE USER', 52,'CREATE ROLE', 53,'DROP USER', 54,'DROP ROLE', 55,'SET ROLE', 56,'CREATE SCHEMA', 57,'CREATE CONTROL FILE', 58,'ALTER TRACING', 59,'CREATE TRIGGER', 60,'ALTER TRIGGER', 61,'DROP TRIGGER', 62,'ANALYZE TABLE', 63,'ANALYZE INDEX', 64,'ANALYZE CLUSTER', 65,'CREATE PROFILE', 66,'DROP PROFILE', 67,'ALTER PROFILE', 68,'DROP PROCEDURE', 70,'ALTER RESOURCE COST', 71,'CREATE SNAPSHOT LOG', 72,'ALTER SNAPSHOT LOG', 73,'DROP SNAPSHOT LOG', 74,'CREATE SNAPSHOT', 75,'ALTER SNAPSHOT', 76,'DROP SNAPSHOT', 84,'-', 85,'TRUNCATE TABLE', 86,'TRUNCATE CLUSTER', 87,'-', 88,'ALTER VIEW', 89,'-', 90,'-', 91,'CREATE FUNCTION', 92,'ALTER FUNCTION', 93,'DROP FUNCTION', 94,'CREATE PACKAGE', 95,'ALTER PACKAGE', 96,'DROP PACKAGE', 97,'CREATE PACKAGE BODY', 98,'ALTER PACKAGE BODY', 99,'DROP PACKAGE BODY', command||' - ???') COMMAND, decode(L.LMODE,1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exclusive','NONE') lmode, decode(L.REQUEST,1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exclusive','NONE') request, l.id1||'-'||l.id2 Laddr, l.type||' - '|| decode(l.type, 'BL','Buffer hash table instance lock', 'CF','Cross-instance function invocation instance lock', 'CI','Control file schema global enqueue lock', 'CS','Control file schema global enqueue lock', 'DF','Data file instance lock', 'DM','Mount/startup db primary/secondary instance lock', 'DR','Distributed recovery process lock', 'DX','Distributed transaction entry lock', 'FI','SGA open-file information lock', 'FS','File set lock', 'IR','Instance recovery serialization global enqueue lock', 'IV','Library cache invalidation instance lock', 'MB','Master buffer hash table instance lock', 'MM','Mount definition gloabal enqueue lock', 'MR','Media recovery lock', 'RE','USE_ROW_ENQUEUE enforcement lock', 'RT','Redo thread global enqueue lock', 'RW','Row wait enqueue lock', 'SC','System commit number instance lock', 'SH','System commit number high water mark enqueue lock', 'SN','Sequence number instance lock', 'SQ','Sequence number enqueue lock', 'ST','Space transaction enqueue lock', 'SV','Sequence number value lock', 'TA','Generic enqueue lock', 'TD','DDL enqueue lock', 'TE','Extend-segment enqueue lock', 'TM','DML enqueue lock', 'TT','Temporary table enqueue lock', 'TX','Transaction enqueue lock', 'UL','User supplied lock', 'UN','User name lock', 'WL','Being-written redo log instance lock', 'WS','Write-atomic-log-switch global enqueue lock', 'TS',decode(l.id2,0,'Temporary segment enqueue lock (ID2=0)', 'New block allocation enqueue lock (ID2=1)'), 'LA','Library cache lock instance lock (A=namespace)', 'LB','Library cache lock instance lock (B=namespace)', 'LC','Library cache lock instance lock (C=namespace)', 'LD','Library cache lock instance lock (D=namespace)', 'LE','Library cache lock instance lock (E=namespace)', 'LF','Library cache lock instance lock (F=namespace)', 'LG','Library cache lock instance lock (G=namespace)', 'LH','Library cache lock instance lock (H=namespace)', 'LI','Library cache lock instance lock (I=namespace)', 'LJ','Library cache lock instance lock (J=namespace)', 'LK','Library cache lock instance lock (K=namespace)', 'LL','Library cache lock instance lock (L=namespace)', 'LM','Library cache lock instance lock (M=namespace)', 'LN','Library cache lock instance lock (N=namespace)', 'LO','Library cache lock instance lock (O=namespace)', 'LP','Library cache lock instance lock (P=namespace)', 'LS','Log start/log switch enqueue lock', 'PA','Library cache pin instance lock (A=namespace)', 'PB','Library cache pin instance lock (B=namespace)', 'PC','Library cache pin instance lock (C=namespace)', 'PD','Library cache pin instance lock (D=namespace)', 'PE','Library cache pin instance lock (E=namespace)', 'PF','Library cache pin instance lock (F=namespace)', 'PG','Library cache pin instance lock (G=namespace)', 'PH','Library cache pin instance lock (H=namespace)', 'PI','Library cache pin instance lock (I=namespace)', 'PJ','Library cache pin instance lock (J=namespace)', 'PL','Library cache pin instance lock (K=namespace)', 'PK','Library cache pin instance lock (L=namespace)', 'PM','Library cache pin instance lock (M=namespace)', 'PN','Library cache pin instance lock (N=namespace)', 'PO','Library cache pin instance lock (O=namespace)', 'PP','Library cache pin instance lock (P=namespace)', 'PQ','Library cache pin instance lock (Q=namespace)', 'PR','Library cache pin instance lock (R=namespace)', 'PS','Library cache pin instance lock (S=namespace)', 'PT','Library cache pin instance lock (T=namespace)', 'PU','Library cache pin instance lock (U=namespace)', 'PV','Library cache pin instance lock (V=namespace)', 'PW','Library cache pin instance lock (W=namespace)', 'PX','Library cache pin instance lock (X=namespace)', 'PY','Library cache pin instance lock (Y=namespace)', 'PZ','Library cache pin instance lock (Z=namespace)', 'QA','Row cache instance lock (A=cache)', 'QB','Row cache instance lock (B=cache)', 'QC','Row cache instance lock (C=cache)', 'QD','Row cache instance lock (D=cache)', 'QE','Row cache instance lock (E=cache)', 'QF','Row cache instance lock (F=cache)', 'QG','Row cache instance lock (G=cache)', 'QH','Row cache instance lock (H=cache)', 'QI','Row cache instance lock (I=cache)', 'QJ','Row cache instance lock (J=cache)', 'QL','Row cache instance lock (K=cache)', 'QK','Row cache instance lock (L=cache)', 'QM','Row cache instance lock (M=cache)', 'QN','Row cache instance lock (N=cache)', 'QO','Row cache instance lock (O=cache)', 'QP','Row cache instance lock (P=cache)', 'QQ','Row cache instance lock (Q=cache)', 'QR','Row cache instance lock (R=cache)', 'QS','Row cache instance lock (S=cache)', 'QT','Row cache instance lock (T=cache)', 'QU','Row cache instance lock (U=cache)', 'QV','Row cache instance lock (V=cache)', 'QW','Row cache instance lock (W=cache)', 'QX','Row cache instance lock (X=cache)', 'QY','Row cache instance lock (Y=cache)', 'QZ','Row cache instance lock (Z=cache)', 'TO','DDL operations on temporary tbl', 'DL','Direct Loader Index Creation', 'IN','Instance Number', 'IS','Instance State', 'KK','Redo Log Kick', 'PF','Password File', 'PI','Parallel Slaves', 'PR','Process Startup', 'PS','Parallel Slave Synchronization', 'TS','Temporary Segment (also TableSpace)', 'RO','Multiple object resue', '????') Lockt 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,1) AND U1.USER# = T1.OWNER# AND S.TYPE != 'BACKGROUND' ORDER BY 1,2,5 / ------------------------------------------------ --profile使用的部分指令碼 ------------------------------------------------ ----profiler執行PL/SQL物件的指令碼 declare err number; begin err:=DBMS_PROFILER.START_PROFILER (to_char(sysdate,'dd-Mon-YYYY hh:mi:ss')); --此處執行要測試的PL/SQL儲存過程或者程式 err:=DBMS_PROFILER.STOP_PROFILER ; end; / --查詢run id column RUN_COMMENT format a40 truncate; Select runid, run_date, RUN_COMMENT FROM plsql_profiler_runs ORDER BY runid; ---查詢分析結果 column unit_name format a15 truncate; column occured format 999999 ; column line# format 99999 ; column tot_time format 999.999999 ; SELECT p.unit_name, p.occured, p.tot_time, p.line# line, substr(s.text, 1,75) text FROM (SELECT u.unit_name, d.TOTAL_OCCUR occured, (d.TOTAL_TIME/1000000000) tot_time, d.line# FROM plsql_profiler_units u, plsql_profiler_data d WHERE d.RUNID=u.runid and d.UNIT_NUMBER = u.unit_number AND d.TOTAL_OCCUR >0 AND u.runid= &RUN_ID) p, user_source s WHERE p.unit_name = s.name(+) and p.line# = s.line (+) ORDER BY p.unit_name, p.line#; --測試PL/SQL 型別效能的例子 create or replace procedure testplsql is anumber number ; ainteger integer; anumber_10 number(10); apls_integer pls_integer; abinary BINARY_integer; err number; begin anumber:=0; loop anumber:=anumber+1; ainteger:=ainteger+1; anumber_10:=anumber_10+1; apls_integer:=apls_integer+1; abinary:=abinary+1; exit when anumber>1500 ; end loop; end; / ----測試集合型別效能的例子 CREATE OR REPLACE TYPE big_emp_ename_t AS TABLE OF varchar2(10); / CREATE OR REPLACE TYPE big_emp_ename_v AS VARRAY(200002) of varchar2(10); / CREATE OR REPLACE PROCEDURE coll_test IS TYPE big_emp_ename_i IS TABLE OF varchar2(10) INDEX BY BINARY_INTEGER; big_emp_cache_t big_emp_ename_t; big_emp_cache_v big_emp_ename_v; big_emp_cache_i big_emp_ename_i; CURSOR big_emp_curs IS SELECT rownum, ename FROM big_emp; cnt number; ename varchar2(10); p pls_integer; BEGIN big_emp_cache_t:=big_emp_ename_t(' '); --to initilaize big_emp_cache_v:=big_emp_ename_v(' '); --to initilaize FOR be_r IN big_emp_curs LOOP big_emp_cache_t(be_r.rownum):=be_r.ename; big_emp_cache_t.EXTEND; big_emp_cache_v(be_r.rownum):=be_r.ename; big_emp_cache_v.EXTEND; big_emp_cache_i(be_r.rownum):=be_r.ename; ename:='DUMMY'; END LOOP; SELECT COUNT(*) INTO cnt FROM big_emp WHERE ename='SCOTT'; SELECT COUNT(*) cache_t INTO cnt FROM TABLE(CAST( big_emp_cache_t AS big_emp_ename_t)) be_c WHERE be_c.column_value='SCOTT'; SELECT COUNT(*) cache_v INTO cnt FROM TABLE(CAST( big_emp_cache_v AS big_emp_ename_v)) be_c WHERE be_c.column_value='SCOTT'; ename:= big_emp_cache_t(100000); FOR i in 1..1000 LOOP p:=100000+i; ename:= big_emp_cache_t(p); ename:= big_emp_cache_v(p); ename:= big_emp_cache_i(p); END LOOP; ename:='DUMMY'; END coll_test; / ----查詢熱點塊的指令碼 Select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',4, '2K SUBCACHE', 5,'4K SUBCACHE',6,'8K SUBCACHE',7, '16K SUBCACHE',8,'32K SUBCACHE','UNKNOWN') subcache, bh.object_name object_name,bh.blocks,tch from x$kcbwds ds, x$kcbwbpd pd, (SELECT /*+ use_hash(x) */ set_ds, o.name object_name, count(*) BLOCKS,sum(tch) tch FROM obj$ o, x$bh x WHERE o.dataobj# = x.obj AND x.state !=0 and o.owner# !=0 GROUP BY set_ds,o.name) bh WHERE ds.set_id >= pd.bp_lo_sid AND ds.set_id 2000 ORDER BY subcache,object_name; ----檢查cursor的指令碼 SELECT 'session_cached_cursors' parameter, lpad(value, 5) value, decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage FROM ( SELECT max(s.value) used FROM v$statname n, v$sesstat s WHERE n.name = 'session cursor cache count' and s.statistic# = n.statistic# ), ( SELECT value FROM v$parameter WHERE name = 'session_cached_cursors' ) UNION ALL SELECT 'open_cursors', lpad(value, 5), to_char(100 * used / value, '990') || '%' FROM ( select max(sum(s.value)) used from v$statname n, v$sesstat s WHERE n.name in ('opened cursors current', 'session cursor cache count') and s.statistic# = n.statistic# GROUP BY s.sid ), ( SELECT value FROM v$parameter WHERE name = 'open_cursors' ) / ---查詢隱含引數 set line 132 col "session value" format a40 truncate col "parameter" format a40 truncate SELECT a.ksppinm "Parameter", a.ksppdesc "Description", b.ksppstvl "Session Value" c.ksppstvl "Instance Value" FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm = lower('&1'); ----閂鎖分析 SELECT name, 'Child '||child#, gets, misses, sleeps FROM v$latch_children WHERE addr='&P1' UNION SELECT name, null, gets, misses, sleeps FROM v$latch WHERE addr='&P1'; SELECT latch#, name, gets, misses, sleeps FROM v$latch WHERE sleeps>0 ORDER BY sleeps DESC; SELECT addr, latch#, gets, misses, sleeps FROM v$latch_children WHERE sleeps>0 and latch# = &LATCH_NUMBER ORDER BY sleeps ; --ora-1591處理的相關指令碼 SELECT KTUXEUSN, KTUXESLT, KTUXESQN, KTUXESTA Status, KTUXECFL Flags FROM x$ktuxe WHERE ktuxesta!='INACTIVE' AND ktuxeusn=&usn; SELECT local_tran_id, global_tran_fmt, global_Oracle_id, global_foreign_id, state, status, heuristic_dflt, session_vector, reco_vector, global_commit# FROM pending_trans$;[@more@]