 |
|
Oracle Tips by Burleson |
Compute Total PGA Size for a
Server
Your challenge is to take the query from Theme
1 and modify it to accept the high-water mark of connected users. You
will use the SQL*Plus accept and prompt commands for this purpose.
set pages
999;
column
pga_size format 999,999,999
show
parAmeter area_size
select
2048576+a.value+b.value pga_size
from
v$parameter a,
v$parameter b
where
a.name = 'sort_area_size'
and
b.name = 'hash_area_size'
;
In SQL*Plus, you can accept a parameter and
then reference it inside your query by placing an ampersand in front
of the variable name:
Hint: Copy this script and run it on your
server.
set heading
off
set echo on
accept
myparm number prompt 'Choose a number between 1 and 10: '
select 'You
chose the number '||&myparm from dual;
If you need more help, you can quickly find the proper syntax
by doing a Google search on “accept prompt sql*plus”. You can also use
the SQL*Plus help facility to see the syntax of the accept command:
SQL> help
accept
ACCEPT
------
Reads a
line of input and stores it in a given user variable.
ACC[EPT]
variable [NUM[BER] | CHAR | DATE] [FOR[MAT] format]
[DEF[AULT] default] [PROMPT text | NOPR[OMPT]] [HIDE]
Your goal is to create a script called pga_size.sql, and your
output should look like this:
SQL> @pga_size
Enter the
high-water mark of connected users: 100
old 2:
&hwm*(2048576+a.value+b.value) pga_size
new 2: 100*(2048576+a.value+b.value) pga_size
PGA_SIZE
------------
362,144,000
ANSWER
set pages
999;
column
pga_size format 999,999,999
accept hwm
number prompt 'Enter the high-water mark of connected users: '
select
&hwm*(2048576+a.value+b.value) pga_size
from
v$parameter a,
v$parameter b
where
a.name = 'sort_area_size'
and
b.name = 'hash_area_size'
;
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
