| |
 |
|
Diving
Into the Shared Pool - An In Depth Look at Tuning the Shared Pool (Part 1)
Oracle Tips by Mike Ault |
Monitoring and Tuning the Shared Pool
Date: 11/18/98
Page: 1
Time: 04:18 PM Users SQL Area
Memory Use SYSTEM
ORTEST1
database
Shared Persistent Runtime
Used Mem
User Bytes Bytes Bytes
Areas Sum
--------------- ------------ ------------ ------------ ---------
------------
GRAPHICS_Remote DBA 67,226 4,640 30,512
10 102,378
SYS 830,929 47,244 153,652 80
1,031,825
SYSTEM 2,364,314 37,848 526,228 63
2,928,390
------------ ------------ ------------ ---------
------------
sum 3,262,469 89,732 710,392 153
4,062,593
3 rows selected.
Figure 6: Example Output From Figure 5
In the example output we see that SYSTEM user
holds the most SQL areas and our application Remote DBA user, GRAPHICS_Remote DBA
holds the least. Since these reports where run on my small Oracle
8.0.5 database this is normal, however, 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. Again, the script in Figure 7 shows
the actual in memory SQL areas for a specific user. Figure 8 shows
the example output from a report run against GRAPHICS_USER using the
script in Figure 7.
SEE CODE DEPOT FOR FULL SCRIPTS
 |
If you like
Oracle tuning, you may enjoy the book
Oracle Tuning: The Definitive Reference
, with over 900 pages of BC's favorite tuning tips &
scripts.
You can buy it directly from the
publisher and save 30%, and get instant access to the code
depot of Oracle tuning scripts. |
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
