Performance Issues with SQL Queries

[prabhunoule]

Dear Erman,

Whenever below SQL queries run, we observe performance issues. Any suggestions?

We are  on R12.2.5 and on 19c database.


[12:58 PM] SATISH GUDDHATI
SQL> select sql_text from gv$sqltext where sql_id='7b5w34atn8q1h' order by piece;
 
SQL_TEXT

----------------------------------------------------------------

BEGIN FND_CP_OPP_IPC.Get_Message(:1,:2,:3,:4,:5,:6,:7,:8); END;

BEGIN FND_CP_OPP_IPC.Get_Message(:1,:2,:3,:4,:5,:6,:7,:8); END;
 
SQL> select sql_text from gv$sqltext where sql_id='497wh6n7hu14f' order by piece;
 
SQL_TEXT

----------------------------------------------------------------

BEGIN FND_CP_GSM_IPC.Get_Message(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)

BEGIN FND_CP_GSM_IPC.Get_Message(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)

; END;

; END;

SQL> select distinct sql_id,count(*) from gv$session where status='ACTIVE' group by sql_id order by 2 desc;
 
SQL_ID          COUNT(*)

------------- ----------

497wh6n7hu14f          9

7b5w34atn8q1h          6





Thanks & Regards,
Prabhu

[ErmanArslansOracleBlog]

Those may not be the cause of your performance degradation.. Seeing them in ACTIVE state is normal.

Please see -> What Causes The Following Messages: Begin Fnd_cp_gsm_ipc.Get_message(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10); End; (Note:868935.1)

Please do a performance analysis on your system and ensure the cause of the decrease in performance.. If that fnd_cp_gsm calls are the real causes, only then we should investigate this further.

[prabhunoule]

Hi Erman,

We have 2 node RAC with 4 core in each server. We have 250 GB RAM.
Our SGA and PGA are as below.
SQL> sho parameter SGA
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 25G
sga_target big integer 25G
SQL> sho parameter PGA
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 0
pga_aggregate_target big integer 5G


Currently we have sessions 1600 and processes 800. Can you advice, with this configuration, how many processes will this server support?




Thanks & Regards,
Prabhu

[ErmanArslansOracleBlog]

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