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

For the same SQL statement, if different optimizer goals are used in different environments, there will be different execution plans

This requires a separate cursor (that is, sub-cursors) to distinguish the different running behavior in each case. [@ more@]

Oracle uses a line in v$sqlarea to hold the SQL of the same statement, and uses the value of the field version_count to indicate the number of subcursors

Number, and a separate row is reserved for each subcursor in the v$sql. Two tables can be accessed through the fields address and hash_value

Correspond to each other. For example, the following example:

SQL > select address,hash_value,sql_text,version_count

2 from v$sqlarea where sql_text like 'select value$ from sys.props%'

ADDRESS HASH_VALUE SQL_TEXT VERSION_COUNT

--

97F1C9C4 563046721 select value$ from sys.props$ where name =: 1 3

SQL > select address,hash_value,child_address,child_number,sql_text

2 from v$sql where sql_text like 'select value$ from sys.props%'

ADDRESS HASH_VALUE CHILD_ADDRESS CHILD_NUMBER SQL_TEXT

--

97F1C9C4 563046721 97F1C448 0 select value$ from sys.props$ where name =: 1

97F1C9C4 563046721 97EAD3B0 1 select value$ from sys.props$ where name =: 1

97F1C9C4 563046721 97E7EEF0 2 select value$ from sys.props$ where name =: 1

If you want to find out the execution plan of this SQL statement, you can also find it in v$sql_plan through the fields address, hash_value, and child_number

The execution plan of different child cursors is distinguished in v$sql_plan.

SQL > select address,child_number,lpad ('', 2*level) | | operation | | decode (id,0,' cost=' | | position) op

2 options,object_name

3 from v$sql_plan t where address='97F1C9C4' and hash_value=563046721

ADDRESS CHILD_NUMBER OP OPTIONS OBJECT_NAME

--

97F1C9C4 2 SELECT STATEMENT cost=0

97F1C9C4 2 TABLE ACCESS FULL PROPS$

97F1C9C4 1 SELECT STATEMENT cost=0

97F1C9C4 1 TABLE ACCESS FULL PROPS$

97F1C9C4 0 SELECT STATEMENT cost=0

97F1C9C4 0 TABLE ACCESS FULL PROPS$

There are no statistics on SQL statements in the view v$sqltext, but v$sqltext saves sql statements in multiple lines, while v$sqlarea can only

Save the first 1000 bytes of the sql statement, so if the sql statement is larger than 1000 bytes, go to v$sqltext to see the complete statement with its words

The segment PIECE represents the line order of each sql.

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.