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

Create a Custom SQL Report

The purpose of this assignment is to create a customized SQL query from the data dictionary to display information about SQL statements inside the library cache.

As we know from the exercise, the v$sql, v$process and v$sql_plan views all contain a common address column that can be used to join the tables together with SQL.

Step 2- Connect to SQL*Plus as pubs/pubs and run queries q1.sql through q5.sql against the sample database to load-up the library cache with SQL statements.

Step 2 - Run the following query to display all non-system SQL in the library cache.

select
   sql_text
from
   v$sql      sq,
   v$sql_plan se
where
   sq.address = se.address
and
   object_owner not in ('SYS','SYSTEM','AURORA$JIS$UTILITY$')
;

Paste this script into a file on your PC, open an MS-DOS window, use the “cd” command to go that directory, and then enter SQL*Plus with the following command:

sqlplus system/manager@mydatabase

Now you can ruin the program using the “@” command.  For example, if I called the code sql_text.sql, we can execute the script by entering:

SQL> @sql_text

Step 3 – Now, enhance the above code snippet to include the following columns from the v$sql view:

First_load_time – The date and time that the SQL first appeared in the library cache

Sorts – The number of sorts that Oracle has invoked by executing this SQL statement

Parse_calls – The number of times that this SQL statement has been parsed.  If the statement is reentrant, you will see multiple parse calls for the SQL statement

Rows_processed – The total number of data rows returned for the sum of all invocations of this SQL statement.

You can use the “host notepad sql_text.sql” command directly from inside SQL*Plus to edit your code.

Your output will look something like this:

FIRST_LOAD_TIME            SORTS  PARSE_CALLS EXECUTIONS ROWS_PROCESSED
------------------- ------------- ----------- ---------- ---
SQL_TEXT                                                                        
----------------------------------------                                       
2002-05-18/14:14:44             0           1          1             10        
select * from store                                       

2002-05-18/14:14:16             0           1          1             19        
select * from book                                                  

2002-05-18/14:14:32             0           1          1             10
select * from author                                                            

Once you get your script running, copy it into an e-mail and send it to your instructor.

ANSWER KEY:

select
   first_load_time   ,
   invalidations     ,
   parse_calls       ,
   executions        ,
   rows_processed    ,
   sql_text
from
   v$sql      sq,
   v$sql_plan se
where
   sq.address = se.address
and
   object_owner not in ('SYS','SYSTEM','AURORA$JIS$UTILITY$')


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.