|
The goal of this exercise is to create a diagram
that shows the data relationships for the v$
views that relate to the parsing and execution
of Oracle SQL.
The views are named v$sql and v$sql_plan, and
these views are used to view execution
information for SQL statements inside the
database SGA region. The v$sql view describes
the characteristics of each SQL statement, and
the v$sql_plan view shows execution plan data
for the SQL statement.
Step 1 - Begin by entering SQL*Plus as the pubs
user and perform a describe command on these
views to see their data columns:
SQL> spool sqltabs.lst
SQL> desc v$sql
SQL> desc v$sql_plan
SQL> spool off
SQL > host notepad sqltabs.lst
Step 2 - Now that you have the view
descriptions, see if you can infer view columns
that are used to join the views together.
Step 3 – Write a query that display the
following data
1. Sorts from v$sql
2. Operation from v$sql_plan
3. Object_name from v$sql_plan
4. Sql_text from v$sql
Your output should display the SQL statements in
descending order of the number of sorts
performed by the SQL.
You should only display the rows with the
highest number of sorts. You can get this
threshold by entering:
Select max(sorts) from v$sql;
You need not be worried about understand this
output for now since we will be covering the
meaning of these data columns in a later
tutorial.
Answer:
Step 1
These two views contain an address column that
can be used to join the views together:
v$sql
Name Null? Type
-----------------------------------------
-------- -------------------
SQL_TEXT VARCHAR2(1000)
SHARABLE_MEM NUMBER
PERSISTENT_MEM NUMBER
RUNTIME_MEM NUMBER
SORTS NUMBER
LOADED_VERSIONS NUMBER
OPEN_VERSIONS NUMBER
USERS_OPENING NUMBER
EXECUTIONS NUMBER
USERS_EXECUTING NUMBER
LOADS NUMBER
FIRST_LOAD_TIME VARCHAR2(19)
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
BUFFER_GETS NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
OPTIMIZER_MODE VARCHAR2(10)
OPTIMIZER_COST NUMBER
PARSING_USER_ID NUMBER
PARSING_SCHEMA_ID NUMBER
KEPT_VERSIONS NUMBER
ADDRESS RAW(4)
TYPE_CHK_HEAP RAW(4)
HASH_VALUE NUMBER
PLAN_HASH_VALUE NUMBER
CHILD_NUMBER NUMBER
TUTORIAL VARCHAR2(64)
TUTORIAL_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
SERIALIZABLE_ABORTS NUMBER
OUTLINE_CATEGORY VARCHAR2(64)
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
OUTLINE_SID NUMBER
CHILD_ADDRESS RAW(4)
SQLTYPE NUMBER
REMOTE VARCHAR2(1)
OBJECT_STATUS VARCHAR2(19)
LITERAL_HASH_VALUE NUMBER
LAST_LOAD_TIME VARCHAR2(19)
IS_OBSOLETE VARCHAR2(1)
v$sql_plan
Name Null? Type
-----------------------------------------
-------- -------------------
ADDRESS RAW(4)
HASH_VALUE NUMBER
CHILD_NUMBER NUMBER
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(30)
OBJECT_NODE VARCHAR2(10)
OBJECT# NUMBER
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(64)
OPTIMIZER VARCHAR2(20)
ID NUMBER
PARENT_ID NUMBER
DEPTH NUMBER
POSITION NUMBER
COST NUMBER
CARDINALITY NUMBER
BYTES NUMBER
OTHER_TAG VARCHAR2(35)
PARTITION_START VARCHAR2(5)
PARTITION_STOP VARCHAR2(5)
PARTITION_ID NUMBER
OTHER VARCHAR2(4000)
DISTRIBUTION VARCHAR2(20)
CPU_COST NUMBER
IO_COST NUMBER
TEMP_SPACE NUMBER
Step 2
The address column is used to join the views.
Step 3
Your output should look something like this:
876 SELECT STATEMENT select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#,
d_owner#, nvl(property,0),subname from
dependency$,obj$ where d_obj#=:1 and p_obj#=obj#(+)
order by
876 INDEX I_OBJ1 select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#,
d_owner#, nvl(property,0),subname from
dependency$,obj$ where d_obj#=:1 and p_obj#=obj#(+)
order by
876 TABLE ACCESS OBJ$ select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#,
d_owner#, nvl(property,0),subname from
dependency$,obj$ where d_obj#=:1 and p_obj#=obj#(+)
order by
876 NESTED LOOPS select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#,
d_owner#, nvl(property,0),subname from
dependency$,obj$ where d_obj#=:1 and p_obj#=obj#(+)
order by
876 TABLE ACCESS DEPENDENCY$ select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#,
d_owner#, nvl(property,0),subname from
dependency$,obj$ where d_obj#=:1 and p_obj#=obj#(+)
order by
876 INDEX I_DEPENDENCY1 select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#,
d_owner#, nvl(property,0),subname from
dependency$,obj$ where d_obj#=:1 and p_obj#=obj#(+)
order by
876 SORT select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#,
d_owner#, nvl(property,0),subname from
dependency$,obj$ where d_obj#=:1 and p_obj#=obj#(+)
order by
|
