|
|
|
Oracle: 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
pubs 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$')
|
|
|
|
|
|
