Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 
     Oracle: SQL in Library Cache

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

 


 

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.