Memory Advisories

Shared Pool Response time Query:

	SELECT 'Shared Pool' component, shared_pool_size_for_estimate estd_sp_size,
	estd_lc_time_saved_factor parse_time_factor,
	CASE
	WHEN current_parse_time_elapsed_s + adjustment_s < 0
	THEN 0
	ELSE current_parse_time_elapsed_s + adjustment_s
	END response_time
	FROM (SELECT shared_pool_size_for_estimate, shared_pool_size_factor,
	estd_lc_time_saved_factor, a.estd_lc_time_saved,
	e.VALUE / 100 current_parse_time_elapsed_s,
	c.estd_lc_time_saved - a.estd_lc_time_saved adjustment_s
	FROM v$shared_pool_advice a,
	(SELECT *
	FROM v$sysstat
	WHERE NAME = 'parse time elapsed') e,
	(SELECT estd_lc_time_saved
	FROM v$shared_pool_advice
	WHERE shared_pool_size_factor = 1) c);

	sample output:-

	COMPONENT         ESTD_SP_SIZE PARSE_TIME_FACTOR RESPONSE_TIME
	--------------------     ------------ ----------------- -------------
	Shared Pool         24         .9936         1154.2
	Shared Pool         32         .9983         1120.2
	Shared Pool         40         1         1108.2
	Shared Pool         48         1.0012         1099.2
	Shared Pool         56         1.0037         1081.2
	Shared Pool         64         1.0043         1077.2
	Shared Pool         72         1.0047         1074.2
	Shared Pool         80         1.0051         1071.2

Notes:- The current size of shared poil is at 40M where the response time i.e 1108, 
if you double the shared pool i.e 80M the response time may like improve 1071.

DB Cache Advisory

	SELECT 'DB ' || TO_CHAR (block_size / 1024) || 'K CACHE' component,
	size_for_estimate cache_size,
	estd_physical_read_factor phy_reads_factor,
	ROUND (estd_physical_reads * timeperio / 100) response_time
	FROM v$db_cache_advice,
	(SELECT SUM (time_waited)
	/ DECODE (SUM (total_waits), 0, 1, SUM (total_waits))
	timeperio
	FROM v$system_event
	WHERE event LIKE 'db file%read'),
	(SELECT VALUE BLOCKSIZE
	FROM v$parameter
	WHERE NAME = 'db_block_size');

	COMPONENT         CACHE_SIZE PHY_READS_FACTOR RESPONSE_TIME
	--------------------     ---------- ---------------- -------------
	DB 8K CACHE         8         	 1.1474         2871
	DB 8K CACHE         16        	 1.0374         2596
	DB 8K CACHE         24        	 1.0057         2516
	DB 8K CACHE         32        	 1.002         	2507
	DB 8K CACHE         40        	 1         	2502
	DB 8K CACHE         48         .9991         	2500
	DB 8K CACHE         56         .9979         	2497
	DB 8K CACHE         64         .9973         	2495
	DB 8K CACHE         72         .9964         	2493
	DB 8K CACHE         80         .9958         	2492
	DB 8K CACHE         88         .995         	2490
	DB 8K CACHE         96         .9947        	2489
	DB 8K CACHE         104         .9938        	2487
	DB 8K CACHE         112         .9932        	2485
	DB 8K CACHE         120         .9923         	2483
	DB 8K CACHE         128         .9913         	2480
	DB 8K CACHE         136         .3678         	920
	DB 8K CACHE         144         .2235         	559
	DB 8K CACHE         152         .2152         	538
	DB 8K CACHE         160         .2006         	502
	Notes:- The current size of db cache is at 40M with response time 2507, 
	if you double 80M the db cache your response time may come down to 2492

PGA Advisory

	SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
		ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
		ESTD_OVERALLOC_COUNT
	 FROM   v$pga_target_advice;

	The output of this query might look like the following:

	TARGET_MB  CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
	 ---------- -------------- --------------------
	 63         23             367
	 125        24             30
	 250        30             3
	 375        39             0
	 500        58             0
	 600        59             0
	 700        59             0
	 800        60             0
	 900        60             0
	 1000       61             0
	 1500       67             0
	 2000       76             0
	 3000       83             0
	 4000       85             0
 
	Keep estimated overalloc count to 0 always, if need increase pga

SGA Advisory

	select * from v$sga_target_advice;
	SGA Size    	Size Factor    	DB_TIME
	--------	---------	---------
	292        	0.25        	9.0873
	584        	0.5        	2.9462
	876        	0.75        	1.0901
	1168       	 1        	1
	1460       	 1.25        	0.9494
	1752       	 1.5        	0.923
	2044       	 1.75        	0.6024
	2336       	 2        	0.6015

	Notes:- The current size of SGA is at 1168 M and increase to sga 2 times 
	i.e 2336 may give the db response time to 0.06015 from 1.