| |
 |
|
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
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
WHERE
a.parsing_user_id = b.user_id
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
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
Shared
Pers
User Text
Executions Loads Used By Bytes Bytes
--------------
------------------------------------------------------------
---------- ------ ------- ------ -----
GRAPHICS_DBA BEGIN dbms_lob.read (:1, :2, :3, :4);
END;
2121 1 0
10251
488
alter session set nls_language= 'AMERICAN' nls_territory=
7 1
0 3975
408
'AMERICA' nls_currency= '$' nls_iso_currency=
'AMERICA'
nls_numeric_characters= '.,' nls_calENDar= 'GREGORIAN'
nls_date_format= 'DD-MON-YY' nls_date_language=
'AMERICAN'
nls_sort= 'BINARY'
BEGIN :1 := dbms_lob.getLength (:2); END;
6 1 0
9290
448
SELECT TO_CHAR(image_seq.nextval) FROM dual
6 1
0 6532
484
SELECT graphic_blob FROM internal_graphics WHERE
2 1 0
5863 468
graphic_id=10
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
graphic_id=12
SELECT graphic_blob FROM internal_graphics WHERE
1 1 0
6079 468
graphic_id=32
SELECT graphic_blob FROM internal_graphics WHERE
1 1 0 6074
468
graphic_id=4
SELECT graphic_blob FROM internal_graphics WHERE
1 1 0
5962 468
graphic_id=8
***************
------ ----
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
REM
CREATE OR REPLACE VIEW sql_garbage AS
SELECT
b.username users,
SUM(a.sharable_mem+a.persistent_mem) Garbage,
TO_NUMBER(null) good
FROM
sys.v_$sqlarea a, dba_users b
WHERE
(a.parsing_user_id = b.user_id and a.executions<=1)
GROUP BY b.username
UNION
SELECT DISTINCT
b.username users,
TO_NUMBER(null) garbage,
SUM(c.sharable_mem+c.persistent_mem) Good
FROM
dba_users b, sys.v_$sqlarea c
WHERE
(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
union
select 2 nopr,
'-------------' users,'--------------' garbage,'--------------' good,
'--------------' good_percent from dual
union
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.
|
