반응형
v$sqlarea 뷰
공유 풀 영역을 검사할 때 v$sqlarea나 v$sqltext을 사용 【예제】SQL> conn system/manager connected SQL> select ename,sal from scott.emp where sal >=3000; ENAME SAL ---------- ---------- SCOTT 3000 KING 5000 FORD 3000 SQL> select ename,sal from scott.emp where sal >=3000; ENAME SAL ---------- ---------- SCOTT 3000 KING 5000 FORD 3000 SQL> select sql_text,version_count,loads,invalidations,parse_calls,sorts 2 from v$sqlarea 3 where sql_text not like '%$%' 4 and command_type in (2,3,6,7) 5 and upper(sql_text) like '%SCOTT.EMP%' 6 order by sql_text; SQL_TEXT -------------------------------------------------------------------------------- VERSION_COUNT LOADS INVALIDATIONS PARSE_CALLS SORTS ------------- ---------- ------------- ----------- ---------- select ename,sal from scott.emp where sal>=3000 1 1 0 2 0 SQL> select sql_text,version_count,loads,invalidations,parse_calls,sorts 2 from v$sqlarea 3 where sql_text not like '%$%' 4 and command_type in (2,3,6,7) 5 and upper(sql_text) like '%SCOTT.EMP%' 6 order by sql_text; SQL_TEXT -------------------------------------------------------------------------------- VERSION_COUNT LOADS INVALIDATIONS PARSE_CALLS SORTS ------------- ---------- ------------- ----------- ---------- select ename,sal from scott.emp where sal>=3000 1 1 0 3 0 SQL>
SQL> select executions, disk_reads, buffer_gets from v$sqlarea; EXECUTIONS DISK_READS BUFFER_GETS ---------- ---------- ----------- 9 0 9 1 2 47 2 11 94 0 0 0 3 4 56 0 0 0 0 0 0 9 8 73 1 0 4 737 0 2211 1 2 15 EXECUTIONS DISK_READS BUFFER_GETS ---------- ---------- ----------- 1 2 15 1 1 15 8 104 471 1 0 0 1 0 0 1 0 0 0 0 0 1 0 0 1 2 55 2 0 0 11 0 0 EXECUTIONS DISK_READS BUFFER_GETS ---------- ---------- ----------- 0 0 0 7 0 14 9 0 27 9 0 45 4 2 55 12 3 24 737 2 2212 3 0 9 1 0 3 30 2 92 1 0 3 EXECUTIONS DISK_READS BUFFER_GETS ---------- ---------- ----------- 6 8 44 3 1 6 2 0 6 4 2 30 6 5 43 4 2 42 1 0 3 1 0 3 10 8 56 4 75 316 1 0 966 EXECUTIONS DISK_READS BUFFER_GETS ---------- ---------- ----------- 0 0 0 9 10 130 4 1 28 0 0 0 118 7 478 55 26 294 1 0 2 1 0 2 0 0 0 1 0 3 1 0 17 EXECUTIONS DISK_READS BUFFER_GETS ---------- ---------- ----------- 0 0 0 77 10 573 1 39 582 192 5 1070 1 0 2 43 1 43 0 0 0 26 1 129 17 3 60 1 4 15 1 0 0 EXECUTIONS DISK_READS BUFFER_GETS ---------- ---------- ----------- 3 9 35 1 0 3 1 3 25 26 0 88 26 0 121 15 1 39 1 1 37 4567 0 4567 2 3 69 9 0 45 1 1 33 EXECUTIONS DISK_READS BUFFER_GETS ---------- ---------- ----------- 3 3 28 0 0 0 4 0 12 76 13 1368 175 3 525 26 1 112 1 69 2397 26 0 112 0 0 0 1 4 43 1 117 2816 EXECUTIONS DISK_READS BUFFER_GETS ---------- ---------- ----------- 76 0 76 77 1 585 1 4 81 1 0 46 0 0 0 12 15 120 12 14 84 12 59 192 9 1 43 1 0 23 12 10 86 EXECUTIONS DISK_READS BUFFER_GETS ---------- ---------- ----------- 0 0 0 26 1 121 44 1 245 9 6 142 0 0 0 39 8 90 8 12 118 37 7 74 2 0 6 3 1 3 55 29 557 EXECUTIONS DISK_READS BUFFER_GETS ---------- ---------- ----------- 8 0 26 3 0 6 7 0 3514 25 0 50 194 13 582 26 1 119 16214 1 113754 1 6 55 1 6 58 1 7 55 1 6 55 EXECUTIONS DISK_READS BUFFER_GETS ---------- ---------- ----------- 9 11 72 31 3 62 1 6 55 1 6 55 1 6 55 1 7 58 7 11 283 1 6 55 1 1 27 3 0 3 0 0 0 EXECUTIONS DISK_READS BUFFER_GETS ---------- ---------- ----------- 4566 0 9132 1 2 37 1 12 80 1 0 7 1 12 112 1 0 7 1 0 7 1 0 7 9 0 27 7 4 69 1 11 211 EXECUTIONS DISK_READS BUFFER_GETS ---------- ---------- ----------- 1 0 7 1 0 7 1 0 7 1 0 7 1 0 7 0 0 0 7 490 3514 0 0 0 1 368 1317 1 0 7 1 0 7 EXECUTIONS DISK_READS BUFFER_GETS ---------- ---------- ----------- 1 0 7 1 0 7 1 0 7 1 0 7 1 0 7 1 0 7 190 36 552 1 0 7 0 1 92 0 1 82 0 1 92 EXECUTIONS DISK_READS BUFFER_GETS ---------- ---------- ----------- 0 1 91 13 1 65 0 1 94 0 4 93 0 2 84 0 1 84 737 10 905 737 2 2299 737 1 2286 0 1 82 116 12 361 EXECUTIONS DISK_READS BUFFER_GETS ---------- ---------- ----------- 737 143 310752 737 13 855 121 3 408 737 13 867 737 3 849 737 2 861 4 2 30 9 1 54 1 32 674 12 4 62 4 0 107 EXECUTIONS DISK_READS BUFFER_GETS ---------- ---------- ----------- 3 2 57 118 24 688 189 rows selected. SQL> 【예제】 SQL> select substr(sql_text,1,40) "SQL", count(*), 2 sum(executions) "총실행회수" 3 from v$sqlarea 4 where sql_text like '%emp%' 5 group by substr(sql_text, 1,40) 6 having count(*) >0 7 order by 2; SQL COUNT(*) 총실행회수 --------------------------------------------- ---------- ---------- select substr(sql_text,1,40) "SQL", coun 1 5 select count(*) from emp 1 1 select ename from emp where ename=:test 1 2 INSERT INTO wrh$_sql_plan sp (sn 1 1 select t.ts#,t.file#,t.block#,nvl(t.bobj 1 20013 select obj#, dataobj#, part#, hiboundlen 1 144 6 rows selected. SQL> 【예제】 SQL> show user USER is "SYS" SQL> desc v$sqlarea Name Null? Type ----------------------------------------- -------- ---------------------------- SQL_TEXT VARCHAR2(1000) SQL_FULLTEXT CLOB SQL_ID VARCHAR2(13) SHARABLE_MEM NUMBER PERSISTENT_MEM NUMBER RUNTIME_MEM NUMBER SORTS NUMBER VERSION_COUNT NUMBER LOADED_VERSIONS NUMBER OPEN_VERSIONS NUMBER USERS_OPENING NUMBER FETCHES NUMBER EXECUTIONS NUMBER PX_SERVERS_EXECUTIONS NUMBER END_OF_FETCH_COUNT NUMBER USERS_EXECUTING NUMBER LOADS NUMBER FIRST_LOAD_TIME VARCHAR2(38) INVALIDATIONS NUMBER PARSE_CALLS NUMBER DISK_READS NUMBER DIRECT_WRITES NUMBER BUFFER_GETS NUMBER APPLICATION_WAIT_TIME NUMBER CONCURRENCY_WAIT_TIME NUMBER CLUSTER_WAIT_TIME NUMBER USER_IO_WAIT_TIME NUMBER PLSQL_EXEC_TIME NUMBER JAVA_EXEC_TIME NUMBER ROWS_PROCESSED NUMBER COMMAND_TYPE NUMBER OPTIMIZER_MODE VARCHAR2(10) OPTIMIZER_COST NUMBER OPTIMIZER_ENV RAW(2000) OPTIMIZER_ENV_HASH_VALUE NUMBER PARSING_USER_ID NUMBER PARSING_SCHEMA_ID NUMBER PARSING_SCHEMA_NAME VARCHAR2(30) KEPT_VERSIONS NUMBER ADDRESS RAW(8) HASH_VALUE NUMBER OLD_HASH_VALUE NUMBER PLAN_HASH_VALUE NUMBER MODULE VARCHAR2(64) MODULE_HASH NUMBER ACTION VARCHAR2(64) ACTION_HASH NUMBER SERIALIZABLE_ABORTS NUMBER OUTLINE_CATEGORY VARCHAR2(64) CPU_TIME NUMBER ELAPSED_TIME NUMBER OUTLINE_SID VARCHAR2(40) LAST_ACTIVE_CHILD_ADDRESS RAW(8) REMOTE VARCHAR2(1) OBJECT_STATUS VARCHAR2(19) LITERAL_HASH_VALUE NUMBER LAST_LOAD_TIME DATE IS_OBSOLETE VARCHAR2(1) IS_BIND_SENSITIVE VARCHAR2(1) IS_BIND_AWARE VARCHAR2(1) IS_SHAREABLE VARCHAR2(1) CHILD_LATCH NUMBER SQL_PROFILE VARCHAR2(64) SQL_PATCH VARCHAR2(30) SQL_PLAN_BASELINE VARCHAR2(30) PROGRAM_ID NUMBER PROGRAM_LINE# NUMBER EXACT_MATCHING_SIGNATURE NUMBER FORCE_MATCHING_SIGNATURE NUMBER LAST_ACTIVE_TIME DATE BIND_DATA RAW(2000) TYPECHECK_MEM NUMBER SQL> select count(*) from v$sqlarea; COUNT(*) ---------- 1469 SQL>
