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