learning v$ view-> v$sqltext&v$sqlarea

最新推荐文章于 2024-03-25 09:31:51 发布

jxzkin

最新推荐文章于 2024-03-25 09:31:51 发布

阅读量764

点赞数

分类专栏： NOTES 文章标签： sql parsing buffer disk join statistics

本文链接：https://blog.csdn.net/jxzkin/article/details/6828483

版权

NOTES 专栏收录该内容

34 篇文章 0 订阅

订阅专栏

V$SQLTEXT

This view contains the complete SQL text for the SQL statements in the shared pool.

Note:

V$SQLAREA only contains only the first 1000 characters.

Useful Columns for V$SQLTEXT

HASH_VALUE: Hash value for the SQL statement
ADDRESS: Address of the SQL statement cursor in SGA
SQL_TEXT: Statement text in 64 character chunks
PIECE: Ordering information for the SQL statement pieces

Join Columns for V$SQLTEXT

Table 24-27 lists the join columns forV$SQLTEXT.

Table 24-27 Join Columns for V$SQLTEXT

Column	View	Joined Column(s)
`HASH_VALUE, ADDRESS`	`V$SQL, V$SESSION`	`HASH_VALUE, ADDRESS`
`HASH_VALUE. ADDRESS`	`V$SESSION`	`SQL_HASH_VALUE. SQL_ADDRESS`

Example 24-30 Finding the SQL Statement for a Hash Value

SELECT sql_text FROM V$SQLTEXT WHERE hash_value = 228801498 ORDER BY piece; SQL_TEXT ---------------------------------------------------------------- select dbsu.primary_flag, i.site_use_code, i.rowid from ra_customers dbc, ra_addresses dbad, ra_site_uses dbsu, ra_customers_ interface i where (((((((i.orig_system_customer_ref=dbc.orig_system_reference and dbad.address_id=dbsu.address_id) and i.site_use_code=dbsu.site_use_code) and dbsu.status='A') and dbad.customer_id=dbc.customer_id) and i.request_id=:b0) and nvl(i.validated_flag,'N')<>'Y') and ((i.primary_site_use_flag='Y' and dbsu.primary_flag='Y') or dbsu.site_use_code in ('STMTS','DUN','LEGAL')))

V$SQLAREA

This view keeps track of all the shared cursors present in the shared pool. It has one row for every SQL statement present in the shared pool. It is an invaluable view for finding the resource usage of a SQL statement.

Information columns in V$SQLAREA

HASH_VALUE: Hash value of the SQL statement
ADDRESS: SGA address for the SQL statement

These two columns are used to identify the SQL statement. Sometimes, two different statements could hash to the same value. In such cases, it is necessary to use the address along with the hash_value.

PARSING_USER_ID: User who parsed the first cursor for the statement
VERSION_COUNT: Number of cursors for the statement
KEPT_VERSIONS: Cursors of the statement pinned using DBMS_SHARED_POOL.KEEP()
SHARABLE_MEMORY: Total shared memory used by the cursor
PERSISTENT_MEMORY: Total persistent memory used by the cursor
RUNTIME_MEMORY: Total runtime memory used by the cursor
SQL_TEXT: Up to first 1000 characters of SQL statement
MODULE, ACTION: Information about the session parsing the first cursor if set using DBMS_APPLICATION_INFO

Other Useful Columns in V$SQLAREA

These columns get incremented with each execution of the statement.

BUFFER_GETS: Number of logical reads for this statement
DISK_READS: Number of physical reads for this statement
SORTS: Number of sorts for this statement
CPU_TIME: CPU time used for parsing and executing this statement
ELAPSED_TIME: Elapsed time for parsing and executing this statement
PARSE_CALLS: Number of parse calls (hard and soft) for this statement
EXECUTIONS: Number of times this statement was executed
INVALIDATIONS: Number of times the cursors for this statement have been invalidated
LOADS: Number of loads (and reloads) for this statement
ROWS_PROCESSED: Total number of rows this statement returns

Join Columns in V$SQLAREA

Table 24-26 lists the join columns for V$SQLAREA.

Table 24-26 Join Columns for V$SQLAREA

Column	View	Joined Column(s)
`HASH_VALUE, ADDRESS`	`V$SESSION`	`SQL_HASH_VALUE, SQL_ADDRESS`
`HASH_VALUE, ADDRESS`	`V$SQLTEXT, V$SQL, V$OPEN_CURSOR`	`HASH_VALUE, ADDRESS`
`SQL_TEXT`	`V$DB_OBJECT_CACHE`	`NAME`

Example 24-28 Finding Resource-intensive SQL

There are several costs you can use:

Total logical I/O (LIO), LIO for each execution
Total physical I/O (PIO), PIO for each execution
PIO/LIO (poor cache hit ratio)

parse_calls, parse_calls for each executions

SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls
  FROM V$SQLAREA
 WHERE buffer_gets > 10000000
    OR disk_reads > 1000000
 ORDER BY buffer_gets + 100*disk_reads DESC;

HASH_VALUE EXECUTIONS BUFFER_GETS DISK_READS PARSE_CALLS
---------- ---------- ----------- ---------- -----------
2676594883        126     7583140    6199113         126
4074144966        126     7264362    6195433          49
 228801498        136   236116544    2371187         136
 360282550       5467    21102603    4476317        2355
1559420740        201     8197831    4537591          39
3213702248   28039654   364516977         44         131
1547710012        865     7579025    3337735         865
3000880481       4481     3676546    2212658        2885
1398193708       4946    73018658    1515257        1418
1052917712    8342025   201246652      38240      327462
 371697988          7    74380777     862611           7
1514306888    3922461    29073852    1223482         268
1848522009          1     1492281    1483635           1
1478599096   28042103   140210513        594         164
 226079402      21473    22121577    1034787        4484
 478652562       4468    21669366    1020370        4438
2054874295      73520   118272694      29987       73520

Note:

If a statement is executing for the first time on the system and responsible for large fraction of the current resource usage, then this statement does not find that statement, because the BUFFER_GETS and DISK_READS statistics do not get updated until the statement finishes execution.

Example 24-29 Finding Resources Used by a SQL Statement

SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls
  FROM V$SQLAREA
 WHERE hash_Value = 228801498
   AND address = hextoraw('CBD8E4B0');

HASH_VALUE BUFFER_GETS DISK_READS EXECUTIONS PARSE_CALLS
---------- ----------- ---------- ---------- -----------
 228801498   236116544    2371187        136         136