Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Oracle Concepts - SQL Area Contents

Oracle Tips by Burleson Consulting

Example output from the script in Source 11 is shown in Listing 16. In the example report no one user is really hogging the SQL area. If you have a particular user that is hogging SQL areas, the script in Source 12 will show you what SQL areas they have and what is in them. This report on the actual SQL area contents can then be used to help teach the user how to better construct reusable SQL statements.


Date: 01/28/00                                                   Page:   1     
Time: 10:06 AM              Users SQL Area Memory Use            MAULT         
                                 dw database                                    
                      Shared   Persistent      Runtime      Used          Mem  
User                   Bytes        Bytes        Bytes     Areas          Sum  
--------------- ------------ ------------ ------------ --------- ------------  
JSMITH           408,211,332   12,876,752   58,737,832     13814  479,825,916  
SYS                7,458,619       86,912      350,088      2791    7,895,619  
PRECISE           45,392,274    1,155,440   12,562,016       322   59,109,730  
DWPROC             6,710,324      239,128    1,194,792       205    8,144,244  
DSSUSER            4,985,220      174,304      742,136        97    5,901,660  
NETSPO             5,907,293       86,032      657,384        51    6,650,709  
GCMATCH            2,016,353       43,872      360,680        32    2,420,905  
DCHUN              3,558,356       52,112      651,112        27    4,261,580  
DWSYBASE             519,083       20,904      135,672        21      675,659  
SMANN              1,040,273       18,368      114,648        12    1,173,289  
MRCHDXD            1,228,587       18,656      131,352         9    1,378,595  
MAULT                360,497        7,432       70,640         7      438,569  
DATAWHSE             114,783        5,336       26,720         6      146,839  
PATROL               218,605        5,232       35,480         6      259,317  
SYSTEM               256,822        6,000       41,280         6      304,102  
BKUEHNE               33,458        2,400        4,536         2       40,394  
WPEREZ               243,786        4,560       31,824         2      280,170  
TTIERNEY              41,518        2,160        4,320         2       47,998  
CWOOD                 61,856        2,952        6,960         2       71,768  
MRCHAEM               24,744        2,312        6,912         2       33,968  
JHUGHES               97,382        1,936        9,856         2      109,174  
BUYRMRA               59,182        1,144        6,200         1       66,526  
MRCHPWR               72,784        1,144        8,248         1       82,176  
SSCOTT                80,585        1,032        9,664         1       91,281  
TMANCEOR              21,570        1,536        2,384         1       25,490  
SHOLETZ               76,901        1,112       13,160         1       91,173  
MRCHPHP               57,445        1,624        7,680         1       66,749  
INVCMTB              116,306        3,648       32,368         1      152,322  
DEMERY                59,205        1,752        6,760         1       67,717   DBSNMP                14,416          816        5,840         1       21,072  
                ------------ ------------ ------------ --------- ------------  
sum              489,039,559   14,826,608   75,968,544     17427  579,834,711  

30 rows selected.

Listing 16. Example Output From Source 11

In the example output we see that JSMITH user holds the most SQL areas and our SNMP user, DBSNMP holds the least. Usually the application owner will hold the largest section of memory in a well designed system, followed by ad-hoc users using properly designed SQL. In a situation where users aren't using properly designed SQL statements the ad-hoc users will usually have the largest number of SQL areas and show the most memory usage as is shown in this example. Again, the script in Source 12 shows the actual in memory SQL areas for a specific user. Listng 17 shows the example output from a report run against GRAPHICS_USER in my test database using the script in Source 12.

rem FUNCTION: Generate a report of SQL Area Memory Usage
rem           showing SQL Text and memory catagories
rem sqlmem.sql
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
BREAK ON users
COMPUTE SUM OF persistent_mem ON USERS
username users, sql_text, Executions, loads, users_executing,
sharable_mem, persistent_mem
sys.v_$sqlarea a, dba_users b
a.parsing_user_id = b.user_id
AND b.username LIKE UPPER('%&user_name%')
PAUSE Press enter to continue

Source 12: Example Script To Show Active SQL Areas For a User

Date: 11/18/98                                                                                       Page:   1
Time: 04:19 PM                                    Users SQL Area Memory Us                           SYSTEM        
                                                        ORTEST1 database

User           Text                                                         Executions  Loads Used By  Bytes  Bytes   
-------------- ------------------------------------------------------------ ---------- ------ ------- ------ -----
GRAPHICS_DBA    BEGIN (:1, :2, :3, :4); END;                         2121      1      0  10251  
                alter session set nls_language= 'AMERICAN' nls_territory=             7      1      0   3975  
                'AMERICA' nls_currency= '$' nls_iso_currency=
                nls_numeric_characters= '.,' nls_calENDar= 'GREGORIAN'         
                nls_date_format= 'DD-MON-YY' nls_date_language=
                nls_sort= 'BINARY'                                   
                BEGIN :1 := dbms_lob.getLength (:2); END;                             6      1      0   9290  
                SELECT TO_CHAR(image_seq.nextval) FROM dual                           6      1      0   6532  
                SELECT graphic_blob FROM internal_graphics WHERE                      2      1      0   5863    468   
                SELECT RPAD(TO_CHAR(graphic_id),5)||':                                1      1      0   7101    472   
                '||RPAD(graphic_desc,30)||' : '||RPAD(graphic_type,10) FROM                
                internal_graphics ORDER BY graphic_id                                                                              
                SELECT graphic_blob FROM internal_graphics WHERE                      1      1      0   6099    468   
                SELECT graphic_blob FROM internal_graphics WHERE                      1      1      0   6079    468   
                SELECT graphic_blob FROM internal_graphics WHERE                      1      1      0   6074    468   
                SELECT graphic_blob FROM internal_graphics WHERE                      1      1      0   5962    468   
***************                                                                                        ------ ----
sum                                                                                                     67226 4640

Listing 17: Report Output Example For a Users SQL Area

One warning about the script in Source 12, the report it generates can run to several hundred pages for a user with a large number of SQL areas. What things should you watch for in a user's SQL areas?

* Watch for the non-use of bind variables, bind variable usage is shown by the inclusion of variables such as ":1" or ":B" in the SQL text.

* Watch for repetitive nearly identical SQL statements, notice the last several lines in the report in listing 17 for example.

* Watch for repetitive use of identical fixed SELECT statements, perhaps they can be moved into views.

* Watch for statements that are reloaded frequently

Notice that in the example report in Listing 17 the first four statements use bind variables, and, consequently are reusable. Non-bind usage means hard coded values such as 'Missing' or '10' are used. Notice that for most of the rest of the statements in the report no bind variables are used even though many of the SQL statements are nearly identical. This is one of the leading causes of shared pool misuse and results in useful SQL being drown in tons of non-reusable garbage SQL. Frequently reload of statements is an indication that the shared pool may be too small. Consider if the object showing reloads should be pinned into the SGA.

The problem with non-reusable SQL is that it must still be looked at by any new SQL inserted into the pool (actually its hash value is scanned). While a hash value scan may seem a small cost item, if your shared pool contains tens of thousands of SQL areas this can be a performance bottleneck. How can we determine, without running the report in Source 12 for each of possibly hundreds of users, if we have garbage SQL in the shared pool?

The script in Source 13 shows a view that provides details on individual user's SQL area reuse. The view can be tailored to your environment if the limit on reuse (currently set at  1) is too restrictive. For example, in a recent tuning assignment resetting the value to 12 resulting in nearly 70 percent of the SQL being rejected as garbage SQL, in DSS or data warehouse systems where rollups are performed by the month, bi-monthly or weekly, values of 12, 24 or 52 might be advisable. Source 14 shows a report script that uses the view created in Source 13.

REM View to sort SQL into GOOD and GARBAGE
  b.username users,
  SUM(a.sharable_mem+a.persistent_mem) Garbage,
  TO_NUMBER(null) good
   sys.v_$sqlarea a, dba_users b
  (a.parsing_user_id = b.user_id and a.executions<=1)
GROUP BY b.username
  b.username users,
  TO_NUMBER(null) garbage,
  SUM(c.sharable_mem+c.persistent_mem) Good
   dba_users b, sys.v_$sqlarea c
  (b.user_id=c.parsing_user_id and c.executions>1)
GROUP BY b.username;

Source 13: Example Script to Create the SQL_GARBAGE View

Note high lighted area in code, alter this to make the view more restrictive.

column garbage format a14 heading 'Non-Shared SQL'
column good format a14 heading 'Shared SQL'
column good_percent format a14 heading 'Percent Shared'
column users format a14 heading Users
column nopr noprint
set feedback off
@title80 'Shared Pool Utilization'
spool rep_out\&db\sql_garbage
select 1 nopr,
a.users users,
to_char(a.garbage,'9,999,999,999') garbage,
to_char(b.good,'9,999,999,999') good,
to_char((b.good/(b.good+a.garbage))*100,'9,999,999.999') good_percent
from sql_garbage a, sql_garbage b
where a.users=b.users
and a.garbage is not null and b.good is not null
select 2 nopr,
'-------------' users,'--------------' garbage,'--------------' good,
'--------------' good_percent from dual
select 3 nopr,
to_char(count(a.users)) users,
to_char(sum(a.garbage),'9,999,999,999') garbage,
to_char(sum(b.good),'9,999,999,999') good,
to_char(((sum(b.good)/(sum(b.good)+sum(a.garbage)))*100),'9,999,999.999') good_percent
from sql_garbage a, sql_garbage b
where a.users=b.users
and a.garbage is not null and b.good is not null
order by 1,3 desc
spool off

Source 14: Example Report Script For SQL Reuse Statistics


This is an excerpt from the eBook "Oracle DBA made Simple".

For more details on Oracle database administration, see the "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam.  It?s only $19.95 when you buy it directly from the publisher here.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.