 |
|
Oracle Tips by Burleson |
Exercise 8A: Write a PL/SQL
pinning script
The following exercise is not graded, but it
is highly recommended that you get it working on your database. Your
challenge is to add a WHERE clause condition to the below query to
generate the pinning syntax for all packages with above average
executions. Remember, you must run dbmspool.sql before this exercise
will work:
SQL> connect
system/manager as sysdba;
Connected.
SQL>
@c:\oracle\ora90\rdbms\admin\dbmspool
No errors.
To get a working script to pin above average
usage packages, you need add the following changes to the SQL:
1 – Spool the output to a file
spool
pin_list.sql
2 – Add a non-correlated subquery:
where
executions > (select . . .. )
3 – Run the file that you just created:
@pin_list
As a refresher, here is the code from Theme 1:
select
'execute dbms_shared_pool.keep('''||name||''');'
from
v$db_object_cache
where
type = 'PACKAGE'
and
kept='NO'
order by
executions desc;
When you are finished, the following query should show all of
the displayed packaged as pinned (KEPT=YES):
select
name,
executions,
pinned
from
v$db_object_cache
where
type = 'PACKAGE
and
executions >
(select
avg(executions)
from
v$db_object_cache
where
type = 'PACKAGE')
;
NAME EXECUTIONS PINS KEP
------------------------------ ---------- -------------DBMS_STANDARD
12 0 YES
DBMS_OUTPUT 23 0 YES
ANSWER
set pages
999;
set heading off;
set echo off;
set feedback off;
spool pin_list.sql
select
'execute dbms_shared_pool.keep('''||name||''');'
from
v$db_object_cache
where
type = 'PACKAGE'
and
kept='NO'
and
executions >
(select
avg(executions)
from
v$db_object_cache
where
type = 'PACKAGE'
)
order by
executions desc;
spool off;
@pin_list
spool t.lst
set heading
on
col name format a30
select
name,
executions,
pins,
kept
from
v$db_object_cache
where
type = 'PACKAGE'
and
executions >
(select
avg(executions)
from
v$db_object_cache
where
type = 'PACKAGE')
;
spool off
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.
|
