Track SQL statement execution and related knowledge development in Oracle

<pre name= "code" class= "SQL" >select * from V$sqlarea;

SELECT * from V$sqlarea where first_load_time> ' 2010-11-27/09:30:00 ';

This method query results each record displays a query statement, and can only query Sql_text less than 1000 characters, the redundant will be truncated.
Improve:

SELECT * from V$sqlarea where first_load_time> ' 2010-11-27/09:30:00 ' and  sql_text like ' insert% ';  You can further filter

If you want to display the extra 1000 with Fulltext.

Knowledge Development:

1, V$sqltext

This view includes the full text of the SQL statement in the Shared pool, and an SQL statement may be divided into multiple blocks to be saved in multiple records.
Note: V$sqlarea only includes the first 1000 characters.

Common columns in the V$sqltext
L HASH_VALUE:SQL The hash value of the statement
L ADDRESS:SQL Statement in the SGA address
L Sql_text:sql text.
L piece:sql number of statement blocks

Connection columns in 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: Known hash_value:3111103299, querying SQL statements:

SELECT * from V$sqltextwhere hashvalue= ' 3111103299 ' ORDER by piece

2, V$sqlarea

This view keeps track of the shared cursor in all shared pool, with one column for each SQL statement in the shared pool. This view is important in analyzing the use of SQL statement resources.

Information columns in the V$sqlarea

L HASH_VALUE:SQL The hash value of the statement.
L ADDRESS:SQL The address of the statement in the SGA.
These two columns are used to identify SQL statements, and sometimes two different statements may have the same hash value. At this point, the SQL statement must be confirmed with address together.
L PARSING_USER_ID: the user who resolves the first cursor for the statement
L Version_count: Number of statement cursor
L Kept_versions:
L Total shared memory used by Sharable_mem:cursor
L total number of resident memory used by Persistent_mem:cursor
L RUNTIME_MEM:CURSOR The total number of run-time memory used.
The text of the Sql_text:sql statement (maximum only the first 1000 characters of the statement can be saved).
L Module,action: Information when the session parses the first cursor when using Dbms_application_info
Other common columns in the V$sqlarea
L Sorts: Order number of statements
L Cpu_time: CPU time when statements are parsed and executed
L Elapsed_time: The shared time when statements are parsed and executed
L Parse_calls: Number of parsing calls (soft, hard) of statements
L Executions: Number of executions of statements
L Invalidations: The number of cursor failures in a statement
L LOADS: Number of statements loaded (loaded)
L Rows_processed: The total number of columns returned by the statement

Connection columns in the 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:
A. View the SQL that consumes the most resources:

SELECT hash_value, executions, buffer_gets, Disk_reads, Parse_callsfrom v$sqlareawhere buffer_gets > 10000000 OR disk_ Reads > 1000000ORDER by buffer_gets + * Disk_reads DESC;

B, view the resource consumption of an SQL statement:

SELECT Hash_value, Buffer_gets, disk_reads, executions, parse_callsfrom v$sqlareawhere Hash_value = 228801498 and Address = Hextoraw (' cbd8e4b0 ');

C, find the first 10 poor performance SQL statements:

SELECT * FROM (select Parsing_user_id,executions,sorts,command_type,disk_reads,sql_text from V$sqlarea;  SELECT * FROM (select Parsing_user_id,executions,sorts,command_type,disk_reads,sql_text to V$sqlarea ORDER by Disk_ Reads DESC) where rownum<10;

Executions indicates how many times the same SQL statement was executed, sorts represents the number of sorts, and disk_reads represents the amount of physical reads.

3, V$sql

A statement can map multiple cursor, because the cursor that the object refers to can have different users (such as Example 1). If there is more than one cursor (child cursor) present, the V$sqlarea provides the collection information for all cursors.

Example 1: Here are the following child cursor

User A:select * from tbluser b:select * from TBL

People think that these two statements are not the same ah, there may be many people will say is the same, but I tell you not necessarily, then why?
This tbla seems to be the same, but not necessarily oh, one is a user, one is a B user, then their execution plan analysis code differences may be big oh, change the wording of people understand:

SELECT * FROM A.tblselect * from B.TBL

On individual cursor, v$sql can be used. The view contains cursor-level data. Used when attempting to locate a session or user to parse the cursor.
The Plan_hash_value column stores the cursor execution plan as a numeric representation. Can be used to compare execution plans. Plan_hash_value lets you easily identify whether two execution plans are the same without having to compare them in one row.

The column descriptions in V$sql:
L SQL_TEXT:SQL the first 1000 characters of a text
L Sharable_mem: The amount of shared memory occupied (in bytes)
L PERSISTENT_MEM: Fixed memory size during lifetime in bytes
L RUNTIME_MEM: Fixed memory size during the execution period
L Sorts: number of completed sorts
L Loaded_versions: Shows whether the context heap is loaded, 1 is 0 no
L Open_versions: Shows if the child cursor is locked, 1 is 0 no
L users_opening: Number of users executing statements
L FETCHES:SQL the number of fetch for the statement.
L Executions: number of executions since it was loaded into the cache library
L users_executing: Number of users executing statements
L LOADS: Number of times an object has been loaded
L First_load_time: Initial loading time
L Invalidations: Invalid number of times
L Parse_calls: Parse call count
L Disk_reads: Read disk Count
L Buffer_gets: Number of Read buffers
l rows_processed: Resolves the total number of columns returned by the SQL statement
L Command_type: Command type Code
The optimizer model of the L-optimizer_mode:sql statement
L Optimizer_cost: The cost of this query given by the optimizer
L PARSING_USER_ID: The first resolved user ID
L PARSING_SCHEMA_ID: The first resolved plan ID
L Kept_versions: Indicates whether the current child cursor is marked as resident memory using the Dbms_shared_pool package
L Address: Current cursor parent handle addresses
L Type_chk_heap: Current heap type check description
L Hash_value: Hash value of parent statement in Cache library
L Plan_hash_value: A numerical representation of the execution plan.
L Child_number: Number of child cursors
L MODULE: The first parsing of this statement is done by calling Dbms_application_info. The module name of the Set_module setting.
L ACTION: The first parse of this statement is by calling Dbms_application_info. Set_action the name of the action set.
L Serializable_aborts: Transaction failed to serialize number of times
L Outline_category: If OUTLINE is applied during the interpretation of the cursor, then this column will show OUTLINE categories, otherwise the columns are empty
L Cpu_time: CPU Usage time (units, milliseconds) for parse/execute/Get
L Elapsed_time: Elapsed time (units, milliseconds) for parsing/execution/acquisition
L Outline_sid:outline Session ID
L Child_address: Child cursor Address
L SqlType: Indicates the SQL language version used by the current statement
L Remote: Indicates if the cursor is a remote image (y/n)
L Object_status: Object state (VALID or INVALID)
L Is_obsolete: Indicates whether the cursor is discarded when the number of child cursors is too large (y/n)

Original address:
Http://hi.baidu.com/clebean/item/73297be5da8cba0e8d3ea8e7
Http://blog.sina.com.cn/s/blog_8019d3c10100rte3.html

Track SQL statement execution and related knowledge development in Oracle