SGA Tuning

Any database SGA tuning exercise should be based on the observed hit ratios of the SGA memory structures. The "Speedway" health check can review hit ratios and provide advice on database parameter sizing. Speedway can run a health check over various SGA memory structures as follows:

  • Data Dictionary

  • Shared Pool

  • Library Cache

  • Shared Pool

  • Buffer Cache

 

The below example illustrates a recommendation to increase the buffer cache size based on suboptimal hit ratios:

Illustration 1: Speedway(Health Check)

Speedway can also obtain a more verbose analysis of memory structure sizing and expected benefits by querying the internal database memory advisors as per below. Speedway can provide advice over various memory structures as follows:

  • PGA

  • DB Cache

  • Java Pool

  • Shared Pool

  • Streams Pool

 

The below example illustrates how Speedway provides advice and the anticipated benefit for a buffer cache resizing:

Illustration 2: Speedway(Advisors)

Speedway also has a seperate piece of functionality to perform a more granular hit ratio inspection into the SGA:

Illustration 3: Speedway(SGA Hit Ratios)

Complete Database Performance Management