| |
 |
|
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.
|
