聽
鏂囩珷鍑鸿嚜: http://blog.sina.com.cn/s/blog_53a1165e0100nisg.html
聽
v$sql鍜?span xml:lang="EN-US">v$sqlarea瑙嗗浘锛?
v$sqlarea鍜?/span>v$sql涓や釜瑙嗗浘鐨勪笉鍚屼箣澶勫湪浜庯紝v$sql涓?负姣忎竴鏉?span xml:lang="EN-US">SQL淇濈暀涓?涓?潯鐩?紝鑰?span xml:lang="EN-US">v$sqlarea涓?牴鎹?span xml:lang="EN-US">sql_text杩涜?group by锛岄?氳繃version_count璁＄畻瀛愭寚閽堢殑涓?暟銆備笅闈㈠?杩欎釜闂??杩涜?涓?鐐瑰欢浼告帰璁ㄣ??
棣栧厛浠嬬粛涓?涓?/span>v$sql瑙嗗浘锛?strong>v$sql瑙嗗浘鍒椾妇浜嗗叡浜?span xml:lang="EN-US">SQL鍖猴紙Shared SQL Area锛変腑鐨?span xml:lang="EN-US">SQL缁熻?淇℃伅锛岃繖涓??鍥句腑鐨勪俊鎭?湭缁忓垎缁勶紝姣忎釜SQL鎸囬拡閮藉寘鍚?竴鏉＄嫭绔嬬殑璁板綍銆傝繖涓??鍥剧殑涓昏?瀛楁?濡備笅锛?/span>
聽
Column | Datatype | Descrption |
SQL_TEXT | VARCHAR2(1000) | 褰撳墠SQL鎸囬拡鐨勫墠1000涓?瓧绗︼紙涔熷氨鏄??杩欓噷璁板綍鐨?span xml:lang="EN-US">SQL鏄?笉瀹屾暣鐨勶級 |
EXECUTIONS | NUMBER | 鎵ц?娆℃暟 |
DISK_READS | NUMBER | 杩欎釜瀛愭寚閽?span xml:lang="EN-US">Disk Read鐨勬?鏁?/span> |
BUFFER_GETS | NUMBER | 杩欎釜瀛愭寚閽堢殑Buffer Gets鏁伴噺 |
OPTIMIZER_MODE | VARCHAR2(10) | SQL鎵ц?鐨勪紭鍖栧櫒妯″紡 |
OPTIMIZER_COST | NUMBER | SQL鎵ц?鎴愭湰 |
HASH_VALUE | NUMBER | 鍦?span xml:lang="EN-US">Library Cache涓?埗鎸囬拡鐨?span xml:lang="EN-US">Hash Value鍊?/span> |
聽
鐢ㄥ墠鏂囧簲鐢ㄧ殑渚嬪瓙杩涜?杩涗竴姝ヨ?鏄庯紝鍋囧畾鏁版嵁搴撲腑瀛樺湪涓?涓?敤鎴?span xml:lang="EN-US">TQ锛岀敤鎴蜂笅瀛樺湪涓?寮?span xml:lang="EN-US">EMP琛?紙浠ヤ笅娴嬭瘯鏉ヨ嚜Oracle 10gR2鏁版嵁搴撶幆澧冿級锛?/span>
[email protected]> create table emp as select * from scott.emp;
Table created.
[email protected]> set autotrace on
[email protected]> select count(*) from emp;
聽 COUNT(*)
----------
聽聽聽聽聽聽聽 14
Execution Plan
----------------------
Plan hash value: 2083865914
-------------------------------
| Id聽 | Operation聽聽聽聽聽聽聽聽聽 | Name | Rows聽 | Cost (%CPU)| Time聽聽聽聽 |
-------------------------------
|聽聽 0 | SELECT STATEMENT聽聽 |聽聽聽聽聽 |聽聽聽聽 1 |聽聽聽聽 3聽聽 (0)| 00:00:01 |
|聽聽 1 |聽 SORT AGGREGATE聽聽聽 |聽聽聽聽聽 |聽聽聽聽 1 |聽聽聽聽聽聽聽聽聽聽聽 |聽聽聽聽聽聽聽聽聽 |
|聽聽 2 |聽聽 TABLE ACCESS FULL| EMP聽 |聽聽聽 14 |聽聽聽聽 3聽聽 (0)| 00:00:01 |
-------------------------------
Note
-----
聽聽 - dynamic sampling used for this statement
Statistics
----------------------
聽聽聽聽聽聽聽聽聽 5聽 recursive calls
聽聽聽聽聽聽聽聽聽 0聽 db block gets
聽聽聽聽聽聽聽聽聽 7聽 consistent gets
聽聽聽聽聽聽聽聽聽 5聽 physical reads
聽聽聽聽聽聽聽聽聽 0聽 redo size
聽聽聽聽聽聽聽 411聽 bytes sent via SQL*Net to client
聽聽聽聽聽聽聽 385聽 bytes received via SQL*Net from client
聽聽聽聽聽聽聽聽聽 2聽 SQL*Net roundtrips to/from client
聽聽聽聽聽聽聽聽聽 0聽 sorts (memory)
聽聽聽聽聽聽聽聽聽 0聽 sorts (disk)
聽聽聽聽聽聽聽聽聽 1聽 rows processed
杩欎釜鏌ヨ?鐨勭粺璁′俊鎭?樉绀猴紝鎵ц?浜?strong>5涓?墿鐞嗚?锛?strong>7涓?span xml:lang="EN-US">Consistent Gets锛屾潵鐪嬩竴涓?/span>v$sql涓??褰曠殑缁熻?鏁版嵁锛?/span>
[email protected]> select sql_text,executions,disk_reads,optimizer_mode,buffer_gets,hash_value
聽 2聽 from v$sql where sql_text='select count(*) from emp';
SQL_TEXT聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 EXECUTIONS DISK_READS OPTIMIZER_ BUFFER_GETS HASH_VALUE
------------------------------ ---------- ---------- ---------- ----------- ----------
select count(*) from emp聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 1聽聽聽聽聽聽聽聽聽 5 ALL_ROWS聽聽聽聽聽聽聽聽聽聽聽聽 7 2295140356
璁板綍鐨勪俊鎭?拰AUTOTRACE鏄剧ず鐨勪俊鎭?畬鍏ㄤ竴鑷淬?傚湪绗?竴娆℃墽琛屾椂锛?strong>杩欎釜SQL鐨?span xml:lang="EN-US">HASH_VALUE琚??绠楀嚭鏉ヤ负2295140356锛屽苟涓旈殢涔嬶紝杩欎釜SQL鐨勭埗鎸囬拡锛?span xml:lang="EN-US">Parent Cursor锛夊湪鍐呭瓨涓??鍒涘缓锛屼竴涓?瓙鎸囬拡鍚屾椂鍒涘缓銆?strong>鐖舵寚閽堝彲浠ヨ?璁や负鏄?span xml:lang="EN-US">Hash Value鐨勭浉鍏充俊鎭?紝瀛愭寚閽堝彲浠ヨ?璁や负鏄?span xml:lang="EN-US">SQL鐨勫厓鏁版嵁銆?
聽
鍐嶆?鎵ц?杩欎釜鏌ヨ?锛岀粺璁′俊鎭?腑鐨勭墿鐞嗚?锛?span xml:lang="EN-US">DISK_READS锛変笉鍐嶅?鍔狅紝鍥犱负鏁版嵁宸茬粡鍦?span xml:lang="EN-US">Buffer涓?瓨鍦?紝鑰?span xml:lang="EN-US">BUFFER_GETS缁х画澧炲姞銆?strong>鎵ц?娆℃暟涔熷彉涓?span xml:lang="EN-US">2娆?/span>锛?/span>
[email protected]> select count(*) from emp;
聽 COUNT(*)
----------
聽聽聽聽聽聽聽 14
Execution Plan
----------------------
Plan hash value: 2083865914
-------------------------------
| Id聽 | Operation聽聽聽聽聽聽聽聽聽 | Name | Rows聽 | Cost (%CPU)| Time聽聽聽聽 |
-------------------------------
|聽聽 0 | SELECT STATEMENT聽聽 |聽聽聽聽聽 |聽聽聽聽 1 |聽聽聽聽 3聽聽 (0)| 00:00:01 |
|聽聽 1 |聽 SORT AGGREGATE聽聽聽 |聽聽聽聽聽 |聽聽聽聽 1 |聽聽聽聽聽聽聽聽聽聽聽 |聽聽聽聽聽聽聽聽聽 |
|聽聽 2 |聽聽 TABLE ACCESS FULL| EMP聽 |聽聽聽 14 |聽聽聽聽 3聽聽 (0)| 00:00:01 |
-------------------------------
Note
-----
聽聽 - dynamic sampling used for this statement
Statistics
----------------------
聽聽聽聽聽聽聽聽聽 0聽 recursive calls
聽聽聽聽聽聽聽聽聽 0聽 db block gets
聽聽聽聽聽聽聽聽聽 3聽 consistent gets
聽聽聽聽聽聽聽聽聽 0聽 physical reads
聽聽聽聽聽聽聽聽聽 0聽 redo size
聽聽聽聽聽聽聽 411聽 bytes sent via SQL*Net to client
聽聽聽聽聽聽聽 385聽 bytes received via SQL*Net from client
聽聽聽聽聽聽聽聽聽 2聽 SQL*Net roundtrips to/from client
聽聽聽聽聽聽聽聽聽 0聽 sorts (memory)
聽聽聽聽聽聽聽聽聽 0聽 sorts (disk)
聽聽聽聽聽聽聽聽聽 1聽 rows processed
[email protected]> select sql_text,executions,disk_reads,optimizer_mode,buffer_gets,hash_value
聽 2聽 from v$sql where sql_text='select count(*) from emp';聽聽
聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽
SQL_TEXT聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 EXECUTIONS DISK_READS OPTIMIZER_ BUFFER_GETS HASH_VALUE
------------------------------ ---------- ---------- ---------- ----------- ----------
select count(*) from emp聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 2聽聽聽聽聽聽聽聽聽 5 ALL_ROWS聽聽聽聽聽聽聽聽聽聽聽 10 2295140356
聽
v$sqlarea瑙嗗浘涔熸槸闈炲父閲嶈?鐨勪竴涓??鍥撅紝鍦?span xml:lang="EN-US">Oracle 9iR2鐨勬枃妗ｄ腑锛?span xml:lang="EN-US">Oracle杩欐牱瀹氫箟杩欎釜瑙嗗浘锛?strong>v$sqlarea鍒楀嚭浜嗗叡浜?span xml:lang="EN-US">SQL鍖猴紙Shared SQL Area锛変腑鐨?span xml:lang="EN-US">SQL缁熻?淇℃伅锛岃繖浜?span xml:lang="EN-US">SQL鎸夌収SQL鏂囨湰鐨勪笉鍚?/span>锛屾瘡鏉′細璁板綍涓?琛岀粺璁℃暟鎹?/span>銆?strong>娉ㄦ剰杩欓噷鎵?璇寸殑鏄?span xml:lang="EN-US">鈥?/span>鎸夌収SQL鏂囨湰鈥?/span>鏉ヨ繘琛屽尯鍒嗭紝涔熷氨鏄??杩欎釜瑙嗗浘鐨勪俊鎭?彲浠ョ湅浣滄槸鏍规嵁SQL_TEXT杩涜?鐨勪竴娆℃眹鎬荤粺璁?/strong>銆?
聽
v$sqlarea瑙嗗浘鐨勪富瑕佸瓧娈靛?涓嬶細
Column | Datatype | Description |
SQL_TEXT | VARCHAR2(1000) | 褰撳墠鎸囬拡鐨勫墠1000涓?瓧绗?/span> |
VERSION_COUNT | NUMBER | Cache涓?繖涓?埗鎸囬拡涓嬪瓨鍦ㄧ殑瀛愭寚閽堢殑鏁伴噺 |
EXECUTIONS | NUMBER | 鎬荤殑鎵ц?娆℃暟锛屽寘鍚?墍鏈夊瓙鎸囬拡鎵ц?娆℃暟鐨勬眹鎬?/span> |
DISK_READS | NUMBER | 鎵?鏈夊瓙鎸囬拡鐨?span xml:lang="EN-US">Disk Reads鎬诲拰 |
BUFFER_GETS | NUMBER | 鎵?鏈夊瓙鎸囬拡鐨?span xml:lang="EN-US">Buffer Gets鎬诲拰 |
OPTIMIZER_MODE | VARCHAR2(10) | SQL鎵ц?鐨勪紭鍖栧櫒妯?/span> |
HASH_VALUE | NUMBER | 鐖舵寚閽堢殑Hash Value |
聽
閫氳繃鍓嶆枃鍙?互鐭ラ亾锛屾枃鏈?浉鍚岀殑SQL璇?彞锛屽湪鏁版嵁搴撲腑鐨勬剰涔夊彲鑳藉畬鍏ㄤ笉鍚屻?傛瘮濡傛暟鎹?簱涓?瓨鍦ㄤ袱涓?敤鎴?strong>TQ鍜?strong>DBTAN锛屼袱涓?敤鎴峰悇鎷ユ湁涓?寮犳暟鎹?〃EMP銆?
閭ｄ箞褰撲袱涓?敤鎴峰彂鍑轰竴涓?煡璇?span xml:lang="EN-US">select count(*) from emp鏃讹紝杩欎釜鏌ヨ?璁块棶鐨勫?璞★紝杩斿洖鐨勭粨鏋滃彲鑳藉畬鍏ㄤ笉鍚岋紝TQ鐨勬煡璇㈣?闂?殑鏄?span xml:lang="EN-US">TQ.EMP琛?紝鑰?span xml:lang="EN-US">DBTAN鐢ㄦ埛璁块棶鐨勫垯鏄?span xml:lang="EN-US">DBTAN.EMP琛ㄣ?備絾鏄?崟浠?span xml:lang="EN-US">SQL_TEXT涓婃潵璇达紝杩欎袱涓?span xml:lang="EN-US">SQL娌℃湁浠讳綍鍖哄埆銆?
缁х画鍓嶉潰鐨勬祴璇曪紝鍐嶆潵绠?鍗曠湅涓?涓嬩互涓嬬殑杈撳嚭锛?/span>
[email protected]> create table emp as select * from scott.emp where rownum <9;
Table created.
[email protected]> set autotrace on
[email protected]> select count(*) from emp;
聽 COUNT(*)
----------
聽聽聽聽聽聽聽聽 8
Execution Plan
----------------------
Plan hash value: 2083865914
-------------------------------
| Id聽 | Operation聽聽聽聽聽聽聽聽聽 | Name | Rows聽 | Cost (%CPU)| Time聽聽聽聽 |
-------------------------------
|聽聽 0 | SELECT STATEMENT聽聽 |聽聽聽聽聽 |聽聽聽聽 1 |聽聽聽聽 3聽聽 (0)| 00:00:01 |
|聽聽 1 |聽 SORT AGGREGATE聽聽聽 |聽聽聽聽聽 |聽聽聽聽 1 |聽聽聽聽聽聽聽聽聽聽聽 |聽聽聽聽聽聽聽聽聽 |
|聽聽 2 |聽聽 TABLE ACCESS FULL| EMP聽 |聽聽聽聽 8 |聽聽聽聽 3聽聽 (0)| 00:00:01 |
-------------------------------
Note
-----
聽聽 - dynamic sampling used for this statement
Statistics
----------------------
聽聽聽聽聽聽聽聽聽 5聽 recursive calls
聽聽聽聽聽聽聽聽聽 0聽 db block gets
聽聽聽聽聽聽聽聽聽 7聽 consistent gets
聽聽聽聽聽聽聽聽聽 5聽 physical reads
聽聽聽聽聽聽聽聽聽 0聽 redo size
聽聽聽聽聽聽聽 411聽 bytes sent via SQL*Net to client
聽聽聽聽聽聽聽 385聽 bytes received via SQL*Net from client
聽聽聽聽聽聽聽聽聽 2聽 SQL*Net roundtrips to/from client
聽聽聽聽聽聽聽聽聽 0聽 sorts (memory)
聽聽聽聽聽聽聽聽聽 0聽 sorts (disk)
聽聽聽聽聽聽聽聽聽 1聽 rows processed
鐜板湪v$sql涓?簲璇ユ湁浜嗕袱鏉″畬鍏ㄤ竴鏍风殑SQL锛屼絾鏄?悇鑷?煡璇㈢殑鐗╃悊瀵硅薄鍗存槸鎴?劧涓嶅悓锛?/span>
[email protected]> select sql_text,executions,disk_reads,optimizer_mode,buffer_gets,hash_value
聽 2聽 from v$sql where sql_text='select count(*) from emp';
SQL_TEXT聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 EXECUTIONS DISK_READS OPTIMIZER_ BUFFER_GETS HASH_VALUE
------------------------------ ---------- ---------- ---------- ----------- ----------
select count(*) from emp聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 3聽聽聽聽聽聽聽聽聽 5 ALL_ROWS聽聽聽聽聽聽聽聽聽聽聽 13 2295140356
select count(*) from emp聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 1聽聽聽聽聽聽聽聽聽 5 ALL_ROWS聽聽聽聽聽聽聽聽聽聽聽聽 7 2295140356
鐜板湪鍐嶆潵鏌ヨ?v$sqlarea瑙嗗浘,灏卞彲浠ョ湅鍒拌繖涓や釜瑙嗗浘鐨勪笉鍚岋細
[email protected]> select sql_text,executions,disk_reads,buffer_gets,hash_value,version_count
聽 2聽 from v$sqlarea where sql_text='select count(*) from emp';
SQL_TEXT聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 EXECUTIONS DISK_READS BUFFER_GETS HASH_VALUE VERSION_COUNT
------------------------------ ---------- ---------- ----------- ---------- -------------
select count(*) from emp聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 4聽聽聽聽聽聽聽聽 10聽聽聽聽聽聽聽聽聽 20 2295140356聽聽聽聽聽聽聽聽聽聽聽聽 2
鍦ㄨ繖涓??鍥句腑锛?span xml:lang="EN-US">Oracle灏?span xml:lang="EN-US">v$sql涓?殑sql_text鐩稿悓鐨?span xml:lang="EN-US">2涓?瓙鎸囬拡鍚堝苟璧锋潵锛屾墽琛屾?鏁扮瓑淇℃伅涔熼兘杩涜?浜嗙疮璁★紝version_count涔熸樉绀轰负2锛岃繖灏辨槸v$sqlarea鐨勮仛鍚堜綔鐢ㄣ??
聽
聽