ä¸ãéè¿PL/SQL Devå·¥å
·
1ãç´æ¥File->New->Explain Plan Windowï¼å¨çªå£ä¸æ§è¡sqlå¯ä»¥æ¥ç计åç»æãå
¶ä¸ï¼Cost表示cpuçæ¶èï¼åä½ä¸ºn%ï¼Cardinality表示æ§è¡çè¡æ°ï¼çä»·Rowsã
2ãå
æ§è¡ EXPLAIN PLAN FOR select * from tableA where paraA=1ï¼å select * from table(DBMS_XPLAN.DISPLAY)便å¯ä»¥çå°oracleçæ§è¡è®¡åäºï¼çå°çç»æå1ä¸çä¸æ ·ï¼æ以使ç¨å·¥å
·çæ¶åæ¨è使ç¨1æ¹æ³ã
注æï¼PL/SQL Devå·¥å
·çCommand windowä¸ä¸æ¯æset autotrance onçå½ä»¤ãè¿æ使ç¨å·¥å
·æ¹æ³æ¥ç计åçå°çä¿¡æ¯ä¸å
¨ï¼æäºæ¶åæ们éè¦sqlplusçæ¯æã
äºãéè¿sqlplus
1ï¼æç®åçåæ³
Sql> set autotrace on
Sql> select * from dual;
ããæ§è¡å®è¯å¥åï¼ä¼æ¾ç¤ºexplain plan ä¸ ç»è®¡ä¿¡æ¯ã
ããè¿ä¸ªè¯å¥çä¼ç¹å°±æ¯å®ç缺ç¹ï¼è¿æ ·å¨ç¨è¯¥æ¹æ³æ¥çæ§è¡æ¶é´è¾é¿çsqlè¯å¥æ¶ï¼éè¦çå¾
该è¯å¥æ§è¡æååï¼æè¿åæ§è¡è®¡åï¼ä½¿ä¼åçå¨æ大大å¢é¿ãå¦æä¸æ³æ§è¡è¯å¥èåªæ¯æ³å¾å°æ§è¡è®¡åå¯ä»¥éç¨ï¼
Sql> set autotrace traceonly
è¿æ ·ï¼å°±åªä¼ååºæ§è¡è®¡åï¼èä¸ä¼çæ£çæ§è¡è¯å¥ï¼å¤§å¤§åå°äºä¼åæ¶é´ãè½ç¶ä¹ååºäºç»è®¡ä¿¡æ¯ï¼ä½æ¯å 为没ææ§è¡è¯å¥ï¼æ以该ç»è®¡ä¿¡æ¯æ²¡æç¨å¤ï¼å¦ææ§è¡è¯¥è¯å¥æ¶éå°é误ï¼è§£å³æ¹æ³ä¸ºï¼
(1ï¼å¨è¦åæçç¨æ·ä¸ï¼
Sqlplus > @ ?
dbmsadminutlxplan.sql
(2) ç¨sysç¨æ·ç»é
Sqlplus > @ ?sqlplusadminplustrce.sql
Sqlplus > grant plustrace to user_name;
- - user_nameæ¯ä¸é¢æ说çåæç¨æ·
ã2ï¼ç¨explain planå½ä»¤
(1) sqlplus > explain plan for select * from testdb.myuser
(2) sqlplus > select * from table(dbms_xplan.display);
ããä¸é¢è¿2ç§æ¹æ³åªè½ä¸ºå¨æ¬ä¼è¯ä¸æ£å¨è¿è¡çè¯å¥äº§çæ§è¡è®¡åï¼å³æ们éè¦å·²ç»ç¥éäºåªæ¡è¯å¥è¿è¡çæçå¾å·®ï¼æ们æ¯æç®çåªå¯¹è¿æ¡SQLè¯å¥å»ä¼åãå
¶å®ï¼å¨å¾å¤æ
åµä¸ï¼æ们åªä¼å¬ä¸ä¸ªå®¢æ·æ±æ¨è¯´ç°å¨ç³»ç»è¿è¡å¾æ
¢ï¼èæ们ä¸ç¥éæ¯åªä¸ªSQLå¼èµ·çãæ¤æ¶æ许å¤ç°æçè¯å¥å¯ä»¥æ¾åºèè´¹èµæºæ¯è¾å¤çè¯å¥ï¼å¦ï¼
SELECT ADDRESS, substr(SQL_TEXT,1,20) Text, buffer_gets, executions,
buffer_gets/executions AVG FROM v$sqlarea
WHERE executions>0 AND buffer_gets > 100000 ORDER BY 5;
ADDRESS TEXT BUFFER_GETS EXECUTIONS AVG
-------- ---------------------------------------- ----------- ---------- ------------------------------------------------------------
66D83D64 select t.name, (sel 421531 60104 7.01336017
66D9E8AC select t.schema, t.n 1141739 2732 417.913250
66B82BCC select s.synonym_nam 441261 6 73543.5
ããä»è对æ¾åºçè¯å¥è¿è¡è¿ä¸æ¥ä¼åãå½ç¶æ们è¿å¯ä»¥ä¸ºä¸ä¸ªæ£å¨è¿è¡çä¼è¯ä¸è¿è¡çææSQLè¯å¥çææ§è¡è®¡åï¼è¿éè¦å¯¹è¯¥ä¼è¯è¿è¡è·è¸ªï¼äº§çtraceæ件ï¼ç¶å对该æ件ç¨tkprofç¨åºæ ¼å¼åä¸ä¸ï¼è¿ç§å¾å°æ§è¡è®¡åçæ¹å¼å¾æç¨ï¼å 为å®å
å«å
¶å®é¢å¤ä¿¡æ¯ï¼å¦SQLè¯å¥æ§è¡çæ¯ä¸ªé¶æ®µ(å¦ParseãExecuteãFetch)åå«èè´¹çå个èµæºæ
åµ(å¦CPUãDISKãelapsedç)ã
3ãå¯ç¨SQL_TRACEè·è¸ªææåå°è¿ç¨æ´»å¨ï¼
å
¨å±åæ°è®¾ç½®ï¼ .OracleHome/admin/SID/pfileä¸æå®: SQL_TRACE = true (10g)
å½åsessionä¸è®¾ç½®ï¼
SQL> alter session set SQL_TRACE=true;
SQL> select * from dual;
SQL> alter session set SQL_TRACE=false;
对å
¶ä»ç¨æ·è¿è¡è·è¸ªè®¾ç½®ï¼
SQL> select sid,serial#,username from v$session where username='XXX';
SID SERIAL# USERNAME
------ ---------- ------------------
127 31923 A
128 54521 B
å¼å¯è·è¸ª:SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,true);
å
³éè·è¸ª:SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,false);
ç¶å使ç¨oracleèªå¸¦çtkprofå½ä»¤è¡å·¥å
·æ ¼å¼åè·è¸ªæ件ã
4ã使ç¨10046äºä»¶è¿è¡æ¥è¯¢ï¼
10046äºä»¶çº§å«ï¼
Lv1 - å¯ç¨æ åçSQL_TRACEåè½,çä»·äºSQL_TRACE
Lv4 - Level 1 + ç»å®å¼(bind values)
Lv8 - Level 1 + çå¾
äºä»¶è·è¸ª
Lv12 - Level 1 + Level 4 + Level 8
å
¨å±è®¾å®ï¼
OracleHome/admin/SID/pfileä¸æå®: EVENT="10046 trace name context forever,level 12"
å½åsession设å®ï¼
å¼å¯:SQL> alter session set events '10046 trace name context forever, level 8';
å
³é:SQL> alter session set events '10046 trace name context off';
对å
¶ä»ç¨æ·è¿è¡è®¾ç½®ï¼
SQL> select sid,serial#,username from v$session where username='XXX';
SID SERIAL# USERNAME
------ ---------- ------------------
127 31923 A
SQL> exec dbms_system.set_ev(127,31923,10046,8,'A');
5ã使ç¨tkprofæ ¼å¼åè·è¸ªæä»¶ï¼ (æ ¹æ®ä¸é¢SQLè¯å¥å¾å°çæ件é½ä¸åå¨è¯¥ç®å½ä¸,éé·å,æµæå...)
ä¸è¬ï¼ä¸æ¬¡è·è¸ªå¯ä»¥å为以ä¸å æ¥ï¼
1ãçå®éè¦è·è¸ªçç®æ èå´ï¼å¹¶ä½¿ç¨éå½çå½ä»¤å¯ç¨æéè·è¸ªã
2ãç»è¿ä¸æ®µæ¶é´åï¼åæ¢è·è¸ªãæ¤æ¶åºè¯¥äº§çäºä¸ä¸ªè·è¸ªç»ææ件ã
3ãæ¾å°è·è¸ªæ件ï¼å¹¶å¯¹å
¶è¿è¡æ ¼å¼åï¼ç¶åé
读æåæã
--使ç¨ä¸ä¸SQLæ¾å°å½åsessionçè·è¸ªæ件ï¼
SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_namefrom( select p.spid from v$mystat m,v$session s, v$process pwhere m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,( select t.instance from v$thread t,v$parameter vwhere v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,( select value from v$parameter where name = 'user_dump_dest' ) d;-- å
¶å®ç¨æ·ç session SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_name from ( select p.spid from v$session s, v$process p where s.sid= '27' and s. SERIAL#= '30' and p.addr = s.paddr) p, ( select t.instance from v$thread t,v$parameter v where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i, ( select value from v$parameter where name = 'user_dump_dest' ) d;
--æ¥æ¾å使ç¨tkprofå½ä»¤,å°TRACEæä»¶æ ¼å¼ä¸ºå°Dççexplain_format.txtæ件ä¸
SQL> $tkprof d:/oracle/admin/FZLGFM/udump/fzlgfm_ora_3468.trc d:/explain_format.txt
æ件å
容大è´å¦ä¸(çä¸å¤ªæ....æµæå.....天å....ç¥å.....è¿å æ¶å°±æäº/////////////)
TKPROF: Release 9.2.0.1.0 - Production on ææäº 4æ 20 13:59:20 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: d:/oracle/admin/FZLGFM/udump/fzlgfm_ora_3468.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call********************************************************************************
alter session set events '10046 trace name context forever, level 8'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: SYS
温馨提示:答案为网友推荐,仅供参考