Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

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:
 


 

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks