Re: Performance Issues with SQL Queries

Posted by ErmanArslansOracleBlog on Oct 31, 2023; 1:15pm
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/Performance-Issues-with-SQL-Queries-tp12136p12146.html

It depends.. But 25G for SGA seemed like a low value to me.
You got 4 cores in each server.. This is not a very loaded environment I guess.

Sizing SGA and PGA depends on the environment.. SGA and PGA may not be the cause of the low performance either.

Some references for SGA and PGA sizing.

How to Read PGA Memory Advisory Section in AWR and Statspack Reports (Doc ID 786554.1)
How To Super-Size Work Area Memory Size Used By Sessions? (Doc ID 453540.1)
About Dynamic SGA Sizing (Doc ID 266702.1)
Automatic PGA Memory Management (Doc ID 223730.1)
Document 62143.1 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention
Document 257643.1 Oracle Database Automated SGA Memory Tuning

Document 295626.1 How To Use Automatic Shared Memory Management (ASMM)
Document 270935.1 Shared Pool Sizing

Oracle® Database Concepts
11g Release 2 (11.2)
Part Number E25789-01
14 Memory Architecture
http://docs.oracle.com/cd/E25054_01/server.1111/e25789/memory.htm#CNCPT007

Oracle Database Memory Management
http://docs.oracle.com/cd/E25054_01/server.1111/e25789/cncptdba.htm#CNCPT1244

Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E25494-02
Using Automatic Shared Memory Management
http://docs.oracle.com/cd/E11882_01/server.112/e25494/memory.htm#ADMIN11203

Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)
Part Number E16638-06
Chapter 7 Configuring and Using Memory
Sections 7.3 Configuring and Using the Shared Pool and Large Pool
http://docs.oracle.com/cd/E28271_01/server.1111/e16638/memory.htm#autoId30