Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  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

  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_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

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.