| Please Make me clear..... [message #52053] |
Sat, 29 June 2002 07:43  |
Chinnu
Messages: 4
Registered: June 2002
|
Junior Member |
|
|
Hello,
This is my interview questions which i don't have answers:
1) How do you calculate your SGA for your application.
2) What is your database size ? How can i calculate ?
Any suggessions and documentation would be appreciable.
Chinnu
|
|
|
|
| Re: Please Make me clear..... [message #52061 is a reply to message #52053] |
Mon, 01 July 2002 04:25  |
Depa
Messages: 30
Registered: June 2002
|
Member |
|
|
This script will calculate the size of the SGA of an existing database based upon the current usage
set serverout on
DECLARE
l_uplift CONSTANT NUMBER := 0.3; /* i.e. 30% above calculated */
l_numusers NUMBER DEFAULT 50; /* Change this to a predicted number if not an existing database */
l_avg_uga NUMBER;
l_max_uga NUMBER;
l_sum_sql_shmem NUMBER;
l_sum_obj_shmem NUMBER;
l_total_avg NUMBER;
l_total_max NUMBER;
BEGIN
dbms_output.enable(20000);
IF ( l_numusers = 0) THEN
SELECT sessions_highwater
INTO l_numusers
FROM v$license;
dbms_output.put_line('Maximum concurrent users on this database = '
||TO_CHAR(l_numusers));
ELSE
dbms_output.put_line('Calculating SGA for = '
||TO_CHAR(l_numusers)||' concurrent users');
END IF;
dbms_output.new_line;
SELECT
avg(value)*l_numusers
,max(value)*l_numusers
INTO l_avg_uga, l_max_uga
FROM v$sesstat s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name = 'session uga memory max';
SELECT
sum(sharable_mem) INTO l_sum_sql_shmem
FROM v$sqlarea;
SELECT
sum(sharable_mem) INTO l_sum_obj_shmem
FROM v$db_object_cache;
l_total_avg := l_avg_uga + l_sum_sql_shmem + l_sum_obj_shmem;
l_total_max := l_max_uga + l_sum_sql_shmem + l_sum_obj_shmem;
dbms_output.put_line('Recommended Shared_pool size between :'
|| TO_CHAR(ROUND(l_total_avg + (l_total_avg * l_uplift), 0) )
||' and '
|| TO_CHAR(ROUND(l_total_max + (l_total_max * l_uplift), 0) )
||' bytes');
dbms_output.put_line('Recommended Shared_pool size between :'
|| TO_CHAR(ROUND(
(l_total_avg + (l_total_avg * l_uplift)) /(1024*1024), 0) )
||' and '
|| TO_CHAR(ROUND(
(l_total_max + (l_total_max * l_uplift )) /(1024*1024) ,0) )
||' M bytes');
end;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
