Shulou(Shulou.com)06/01 Report--

I believe many inexperienced people don't know what to do about it. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

V$sqltext has complete sql statements in memory (SQL is split into multiple lines of storage), while the other two views are partial sql statements.

However, the storage in v$sqltext is relatively simple, and there is no statistical information about the statement, such as the number of executions and so on.

The fields of v$sqltext are as follows:

ADDRESS RAW (4 | 8) Used with HASH_VALUE to uniquely identify a cached cursor

HASH_VALUE NUMBER Used with ADDRESS to uniquely identify a cached cursor

SQL_ID VARCHAR2 (13) SQL identifier of a cached cursor

COMMAND_TYPE NUMBER Code for the type of SQL statement (SELECT, INSERT, and so on)

PIECE NUMBER Number used to order the pieces of SQL text

SQL_TEXT VARCHAR2 (64) A column containing one piece of the SQL text

Piece represents the sequential numbering of SQL statements after fragmentation. For example, the values of three lines are 0 and 1, and then concatenated sequentially is a completed SQL statement.

Sql_text represents part of the sharded sql statement. Note that its length is only 64 bytes. The fields HASH_VALUE and address together uniquely mark a sql.

V$sql: stores specific SQL and execution plan related information.

V$sqlarea: stores the first 1000 bytes of SQL statements and some related statistics, such as cumulative execution times, logical reads, physical reads, etc.

V$SQLAREA has different field definitions in Oracle9i, Oracle10.1 and Oracle10.2 versions, and there will be more sql_fulltext fields in Oracle10.2.

Is a CLOB field.

In fact, v$sqlarea can be thought of as the information of v$sql after group by based on sqltext and so on.

-

1. You can check the definition of these views through the following statement, and you can see that the source tables of v$sql and v$sqlarea are relatively close.

SELECT view_definition FROM v$fixed_view_definition WHERE view_name='GV$SQL'

SELECT view_definition FROM v$fixed_view_definition WHERE view_name='GV$SQLAREA'

SELECT view_definition FROM v$fixed_view_definition WHERE view_name='GV$SQLTEXT'

-- definition of V$SQL in Oracle10.2.0.4:

Select inst_id,kglnaobj,kglfnobj,kglobt03, kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16

Kglobt08+kglobt11, kglobt10, kglobt01, decode (kglobhs6,0,0,1), decode (kglhdlmd,0,0,1), kglhdlkc, kglobt04, kglobt05

Kglobt48, kglobt35, kglobpc6, kglhdldc, substr (to_char (kglnatim,'YYYY-MM-DD/HH24:MI:SS'), 1mai 19), kglhdivc, kglobt12

Kglobt13, kglobwdw, kglobt14, kglobwap, kglobwcc, kglobwcl, kglobwui, kglobt42, kglobt43, kglobt15, kglobt02

Decode (kglobt32, 0, 'NONE', 1,' ALL_ROWS', 2, 'FIRST_ROWS', 3,' RULE', 4, 'CHOOSE'

'UNKNOWN'), kglobtn0, kglobcce, kglobcceh, kglobt17, kglobt18, kglobts4, kglhdkmk, kglhdpar, kglobtp0, kglnahsh, kglobt46

Kglobt30, kglobt09, kglobts5, kglobt48, kglobts0, kglobt19, kglobts1, kglobt20, kglobt21, kglobts2, kglobt06, kglobt07

Decode (kglobt28, 0, to_number (NULL), kglobt28), kglhdadr, kglobt29, decode (bitand (kglobt00,64), 64, 'Yee,' N')

Decode (kglobsta, 1, 'VALID', 2,' VALID_AUTH_ERROR',3, 'VALID_COMPILE_ERROR', 4

'VALID_UNAUTH', 5,' INVALID_UNAUTH', 6, 'INVALID'), kglobt31, substr (to_char (kglobtt0,'YYYY-MM-DD/HH24:MI:SS'), 1Magne 19)

Decode (kglobt33, 1, 'Yee,' N'), kglhdclt, kglobts3, kglobt44, kglobt45, kglobt47, kglobt49, kglobcla, kglobcbca, kglobt22

From x$kglcursor_child

-- definition of V$SQLAREA in Oracle10.2.0.4:

Select inst_id, kglnaobj, kglfnobj, kglobt03, kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6, kglobt08+kglobt11

Kglobt10, kglobt01, kglobccc, kglobclc, kglhdlmd, kglhdlkc, kglobt04, kglobt05, kglobt48, kglobt35, kglobpc6, kglhdldc

Substr (to_char (kglnatim,'YYYY-MM-DD/HH24:MI:SS'), 1mai 19), kglhdivc, kglobt12, kglobt13, kglobwdw, kglobt14, kglobwap

Kglobwcc, kglobwcl, kglobwui, kglobt42, kglobt43, kglobt15, kglobt02, decode (kglobt32, 0, 'NONE',1,' ALL_ROWS', 2, 'FIRST_ROWS'

3, 'RULE', 4,' CHOOSE', 'UNKNOWN'), kglobtn0, kglobcce, kglobcceh, kglobt17, kglobt18, kglobts4, kglhdkmk, kglhdpar, kglnahsh

Kglobt46, kglobt30, kglobts0, kglobt19, kglobts1, kglobt20, kglobt21, kglobts2, kglobt06, kglobt07

Decode (kglobt28, 0, NULL, kglobt28), kglhdadr, decode (bitand (kglobt00,64), 64, 'Yee,' N')

Decode (kglobsta,1, 'VALID', 2,' VALID_AUTH_ERROR',3, 'VALID_COMPILE_ERROR', 4,' VALID_UNAUTH', 5, 'INVALID_UNAUTH',6,' INVALID')

Kglobt31, kglobtt0, decode (kglobt33, 1, 'Yee,' N'), kglhdclt, kglobts3, kglobt44, kglobt45, kglobt47, kglobt49, kglobcla

Kglobcbca, kglobt22

From x$kglcursor_child_sqlid

Where kglobt02! = 0

-- definition of V$SQLTEXT in Oracle10.2.0.4:

Select inst_id,kglhdadr, kglnahsh, kglnasqlid, kgloboct, piece, name

From x$kglna

Where kgloboct! = 0

2. The difference and connection between v$sql and v$sqlarea:

A, v$sqlarea is equivalent to pressing INST_ID, KGLNAOBJ, KGLHDPAR, KGLNAHSH, KGLNATIM, GLOBTS0,GLOBT19, KGLOBTS1, KGLOBT20,DECODE (KGLOBT33, 1, 'Yee,' N')

KGLHDCLT these columns are from the group by of v$sql, that is, each line of v$sql represents a version of each sql statement, while v$sqlarea stores a summary of different version of the same statement.

There is only one line for the same statement, but version_count records the number of times.

B. It is recommended to use v$sql in actual tuning, which is relatively faster than v$sqlarea and does not cause contention for share pool latch.

3. Because v$sql and v$sqlarea store statistical information, they are mostly used in tuning, but their sql is incomplete. If you want to get a complete sql, you have to use v$sqltext.

After reading the above, have you mastered what is the difference between vSecretsqlMagneVidsqlareaGradusqltext? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.