| |
 |
|
Oracle Concepts by Burleson
Consulting |
Explore the Oracle Library
Cache
This exercise is
not a graded, but the techniques that you learn from this exercise
will be very useful in completing the assignment for this tutorial.
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 Oracle 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
- Sorts from v$sql
- Operation from
v$sql_plan
- Object_name from
v$sql_plan
- 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_HA
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:
SORTS OPERATION
OBJECT_NAME SQL_TEXT
------ ----------------- ---------------
-------------------------------
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 order#
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 order#
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 order#
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 order#
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 order#
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 order#
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 order#
7 rows selected.
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
