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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle Concepts - Example Report Showing SQL Reuse Statistics

Oracle Tips by Burleson Consulting

The report  in Source 14 shows at a glance (well, maybe a long glance for a system with hundreds of users) which users aren't making good use of reusable SQL. An example report output is shown in Listing 18.

Date: 01/28/00                                                   Page:   1
Time: 10:45 AM            Shared Pool Utilization                 MAULT         
                              dw database                                 
                                                                          

Users          Non-Shared SQL Shared SQL     Percent Shared               
-------------- -------------- -------------- --------------               
JSMITH            371,387,006      1,007,366           .271               
NETSPO             10,603,456        659,999          5.860               
DCHUN               6,363,158        151,141          2.320               
DSSUSER             5,363,057        824,865         13.330               
MRCHDXD             4,305,330        600,824         12.246               
DWPROC              2,690,086      4,901,400         64.564                
CWOOD                 946,199        239,604         20.206
TMANCEOR              877,644         93,323          9.611               
GCMATCH               604,369      1,637,788         73.045               
MAULT                 445,566      3,737,984         89.350               
PRECISE               205,564     46,342,150         99.558               
BKUEHNE               154,754         35,858         18.812               
SYS                   146,811      9,420,434         98.465               
SMANN                 102,460      8,523,746         98.812               
MRCHPHP                56,954         59,069         50.911               
MRCHAEM                42,465         65,017         60.491                
-------------  -------------- -------------- --------------               
16                404,553,888     78,358,468         16.226               

Listing 18: Example Report From Showing SQL Reuse Statistics

Notice in Listing 18 that the JSMITH user only shows 0.271% shared SQL use based on memory footprints. From the report in Listing 18 we would expect a low reuse value for JSMITH.

Through looking at the performance profiles for several large database systems I have found that once the number of SQL areas being managed by the SGA exceeds several thousand (>7-10 thousand) performance begins to degrade. Therefore knowing the total number of SQL areas and how they are dispersed among the users is also an important metric. In a later lesson we will see the hash value report which will give this data. An example run of the hash report for the database being used here for examples gives the result shown in Listing 19.

Date: 01/28/00                                                   Page:   1
Time: 10:48 AM            Shared Hash Value Report                MAULT         
                              dw database                                 
                                                                          
                               Total Hash  SQL With SQL Sharing           
USERNAME                           Values Same Hash        Hash           
------------------------------ ---------- --------- -----------           
JSMITH                              11681         0     100.000           
SYS                                  3469         0     100.000           
PRECISE                               322         0     100.000           
DWPROC                                245         0     100.000           
DSSUSER                               125         0     100.000           
SMANN                                 125         0     100.000           
NETSPO                                 98         0     100.000           
DCHUN                                  50         0     100.000           
MAULT                                  45         0     100.000           
GCMATCH                                37         0     100.000           
MRCHDXD                                26         0     100.000            
DWSYBASE                               21         0     100.000           
TMANCEOR                               15         0     100.000           
CWOOD                                  11         0     100.000           
DATAWHSE                                6         0     100.000           
SYSTEM                                  6         0     100.000           
PATROL                                  6         0     100.000           
MRCHAEM                                 5         0     100.000           
BKUEHNE                                 4         0     100.000           
JHUGHES                                 2         0     100.000           
MRCHPHP                                 2         0     100.000           
MRCHPWR                                 2         0     100.000           
WPEREZ                                  2         0     100.000           
TTIERNEY                                2         0     100.000           
BUYRMRA                                 1         0     100.000           
INVCMTB                                 1         0     100.000           
JOTOOLE                                 1         0     100.000           
SHOLETZ                                 1         0     100.000           
SSCOTT                                  1         0     100.000           
DEMERY                                  1         0     100.000           
DBSNMP                                  1         0     100.000           
                               ---------- ---------                       
sum                                 16314         0     
                                                                                            

Listing 19: Example Hash Report showing number of assigned SQL Areas

Notice how the JSMITH user, as we would expect, is hogging a majority of the SQL areas and causing us to easily exceed the performance barrier for number of SQL areas.

Another useful thing to know is what statements are being repeated. By generating a report which shows the duplicate statements you can then go back to the developers or the users that are generating the statements and help them to correct the statements so they process data more effectively in a reusable manner. The report in Source 15 shows a method for obtaining this information.

REM script similar_sql
REM Mike Ault DBMentors International Jan 1999
SET LINES 132 PAGES 55
COL num_of_times HEADING 'Number|Of|Repeats'
COL SUBSTR(a.sql_text,1,60) HEADING 'SubString - 90 Characters'
COL username FORMAT A10 HEADING 'User'
@title132 'Similar SQL'
SPOOL rep_out\&db\similar_sql
SELECT
    b.username,
    substr(a.sql_text,1,90),
    count(a.sql_text) num_of_times
FROM
    v$sqlarea a, dba_users b
WHERE
    a.parsing_user_id=b.user_id
GROUP BY
    b.username,SUBSTR(a.sql_text,1,90)
HAVING
    COUNT(a.sql_text)>=20
ORDER BY 3 DESC
/
SPOOL OFF
CLEAR COLUMNS
TTITLE OFF


Source 15:  Example report for duplicate SQL

An example output from the report in Source 15 is shown in Listing 20.

Date: 01/18/00                                                   Page:   1
Time: 09:10 AM                  Similar SQL                       MAULT
                              dw database                                 
                                                                          
                                                                Number    
                                                                    Of    
SubString - 90 Characters                                      Repeats    
------------------------------------------------------------ ---------    
select a.TitleStatus,b.ProductLine,a.Category from UserArea.      4960    
select sum(a.OHQty + a.OOQty) from UserArea.InventoryPP a,us      4885    
select b.OrderNum, b.OrderLineNum, b.VendorID, c.ProductShor       676    
declare  lERROR_ROW DATAWHSE.ERROR_LOG%ROWTYPE;  lDBCallOK n       660    
declare  lLLogSeqNum number;  lERROR_ROW DATAWHSE.ERROR_LOG%       660    
declare     LPLogSeqNum number;     lERROR_ROW DATAWHSE.ERRO       630    
declare  lDBCallOK number := 0;  lFatalError number := 16;         615    
SELECT COUNT(*)   FROM DATAWHSE.LOADER_LOG  WHERE DATACOLLEC       571    
Select  Sum(TotalSales + Estimate),sum(TAStoreSales.FieldPla       199    
select logging from sys.dba_tablespaces where tablespace_nam       194    
SELECT COMMENTS FROM ALL_COL_COMMENTS WHERE TABLE_NAME = 'PO        82    
Select  TAStore.DistrictNo,Sum(TotalSales + Estimate),sum(TA        82    
SELECT COMMENTS FROM ALL_COL_COMMENTS WHERE TABLE_NAME = 'CO        65    
declare  lDBCallOK number := 0;  begin   :lParmResult := lDB        64    
Select  TAStore.RegionNo,Sum(TotalSales + Estimate),sum(TASt        56    
Select (LastName || ' ' || FirstName) From UserArea.WaldensB        51    
Select Subject From UserArea.FFCTitleCTCurrent where SKU = '        51    
Select ProductLongName From UserArea.FFCTitleCTCurrent where        51    
Select AuthorName From UserArea.FFCTitleCTCurrent where SKU         51    
Select BuyerName From UserArea.TitleCurrent Where Bkey in (S        51    
Select FFCBuyerCode From   UserArea.FFCTitleInvMgmt where SK        51    
Select  TAStore.StoreNo,Sum(TotalSales + Estimate),sum(TASto        46     
select A.status, A.timestamp, A.last_ddl_time from all_objec        36    
SELECT COMMENTS FROM ALL_COL_COMMENTS WHERE TABLE_NAME = 'DW        35    
SELECT COMMENTS FROM ALL_COL_COMMENTS WHERE TABLE_NAME = 'AS        33    
Select  sum(TAStoreSales.NewSignUps),sum(TAStoreSales.Renewa        33    
Select  Sum(TYSales+Est),sum(TABrdsSales.Forecast),Sum(TYSal        33    
SELECT status, timestamp, last_ddl_time FROM  all_objects  W        32    
Select  sum(TATitleSMmM.SaleAmt),sum(TATitleSMmM.SaleQty) Fr        30    
declare  lLogRow DATAWHSE.LOADER_LOG%ROWTYPE;  lERROR_ROW DA        27    
Select  TATitleSMmM.BKey,TATitleSMmM.BKey,TATitleSMmM.TitleN        23    
select b.OrderNum, c.ProductShortName, a.LastName, a.FirstNa        22    
SELECT A.TRANSEQNUM TRANSEQNUM,A.JRNLSEQNUM JRNLSEQNUM,A.JRN        20    
SELECT COMMENTS FROM ALL_COL_COMMENTS WHERE TABLE_NAME = 'BO        20    
Select  TATitleSMmM.BKey,TATitleSMmM.TitleName,TATitleSMmM.A        20    

Listing 20:  Example Listing from Similar SQL Report

Usually you won?t be able to add the user name to the report in Listing 20, on a lark I decided to see what would happen if I tied the statements back to the users in the test database. Sure enough JSMITH owned the first two sets of duplicate statements.

 


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.