 |
|
Oracle shared pool v$sqlarea
|
Chapter 2 Inside the
Oracle Shared Pool
rem
rem FUNCTION: Generate a report of SQL Area Memory Usage
rem showing SQL Text and memory catagories
rem
rem sqlmem.sql
rem
column sql_text format a60 heading Text word_wrapped
column sharable_mem heading Shared|Bytes
column
persistent_mem heading Persistent|Bytes
column loads heading Loads
column users format a15 heading "User"
column executions heading "Executions"
column users_executing heading "Used By"
start title132 "Users SQL Area Memory Use"
spool rep_out\&db\sqlmem
set long 2000 pages 59 lines 132
break on users
compute sum of sharable_mem on users
compute sum of persistent_mem on users
compute sum of runtime_mem on users
select username users, sql_text, Executions, loads, users_executing,
sharable_mem, persistent_mem
from sys.v_$sqlarea a, dba_users b
SEE CODE DEPOT FOR FULL SCRIPT
and b.username like upper('%&user_name%')
order by 3 desc,1;
spool off
pause Press
enter to continue
clear columns
clear
computes
clear breaks
set pages 22
lines 80
ttitle off
If you see duplicate hash
values, a fast way to find the duplicate hash values
is to do a self-join, filtering out the duplicate hash values. This
may sound easy, but remember, the v$ tables have no rowids, so the
classic methods cannot be used. Another column will have to be found
that is different when the hash_value column in v$sqlarea is the
same. Consider the select that follows:
select
a.hash_value
from
v$sqlarea a,
v$sqlarea b
and
a.FIRST_LOAD_TIME != b.FIRST_LOAD_TIME
SEE CODE DEPOT FOR FULL SCRIPT
This select should indicate the problem hash
values. It is then a simple matter to go back to the v$sqlarea table and isolate the actual text. Long statements require special
care to make sure that bind variables are used to prevent this
hashing problem. Another remedy for long statements is to use views
to store values at an intermediate state, thus reducing the size of
the variable portion of the SQL.
Use bind variables, PL/SQL (procedures or
functions), and views to reduce the size of large SQL statements in
order to prevent hashing problems.
Disk IO and the Shared
Pool
The Pcode versions of all current SQL commands
that haven’t been aged out of the shared pool are contained in the
shared SQL area. Numerous statistics are available via the v$sqlarea DPT. The text of SQL statements in the shared pool
can be retrieved (at least the first tens of bytes) from the
v$sqltext DPT.
The report below displays the SQL statements with
the greatest amount of disk reads (these will probably be the ones
you will want to review and tune).
sqldrd.sql
DEFINE access_level = 1000 (NUMBER)
COLUMN parsing_user_id FORMAT 9999999 HEADING ‘User Id’
COLUMN executions FORMAT 9999 HEADING ‘Exec’
COLUMN sorts FORMAT 99999 HEADING ‘Sorts’
COLUMN command_type FORMAT 99999 HEADING ‘CmdT’
COLUMN disk_reads FORMAT 999,999,999 HEADING ‘Block Reads’
COLUMN sql_text FORMAT a40 HEADING ‘Statement’
WORD_WRAPPED
SET LINES 130 VERIFY OFF FEEDBACK OFF
ttitle 'SQL Statements With High Reads’
SPOOL sqldrd.lis
SELECT
parsing_user_id,
executions,
sorts,
command_type,
disk_reads,
sql_text
FROM
v$sqlarea
SEE CODE DEPOT FOR FULL SCRIPT
ORDER BY
disk_reads;
SPOOL OFF
SET LINES 80 VERIFY ON FEEDBACK ON
The following is sample output from this
script.
User
Id Exec Sorts CmdT Block Reads Statement
---- ---- ----- ---- ----------- ----------------------------
0 403 0 3 11 select f.file#,
f.block#,
f.ts#,f.length from
fet$ f, ts$ t where
t.ts#=f.ts#
and
t.dflextpct=0
0 11 0 3 11 select order#,columns,types
from access$ where d_obj#=:1
0 12 0 3 12 select /*+
index(idl_ub1$
i_idl_ub11)+*/piece#,length
,piece
from idl_ub1$ where
obj#=:1
and part=:2 and
version=:3 order by piece#
5 34 0 3 13 SELECT NAME,VALUE FROM
V$SYSSTAT WHERE NAME = 'db
block gets'
0 12 0 3 14 select /*+ index(idl_ub2$
i_idl_ub21) +*/piece#,length,
piece from idl_ub2$ where
obj#=:1 and part=:2 and version
=:3 order by piece#
0 17 0 3 27 select file#, block#, ts# from
seg$ where type# = 3
0 1 1 3 79 select distinct d.p_obj#,
d.p_timestamp
from sys.dependency$ d, obj$
o where d.p_obj#>=:1
and d.d_obj#=o.obj# and o.status!=5
5 34 0 47 90 DECLARE job BINARY_INTEGER : :job;
next_date DATE := :mydate;
broken BOOLEAN := FALSE;
BEGIN hitratio; :mydate :=
next_date;
IF broken THEN :b
The Oracle script collection contains more than 600 working Oracle scripts. You can download them immediately at this link:
|
