performance issue on production

[Roshan]

Oracle Database 12.1.0.2
Red Hat linux 6

Hi,

I have noticed we are getting several performance issue on a production server.

I have checked AWR report and found the sql

SELECT max( CASE WHEN t.online_status = 'ONLINE' THEN 0 WHEN t.online_status = 'SYSTEM' THEN 1 WHEN t.online_status = 'RECOVER' THEN 2 WHEN t.online_status = 'SYSOFF' THEN 3 WHEN t.online_status = 'OFFLINE' THEN 4 ELSE 100 END) online_status, max( CASE WHEN t.status = 'AVAILABLE' THEN 0 WHEN t.status = 'INVALID' THEN 1 ELSE 100 END ) status, SUM(t.maxbytes) - SUM(t.bytes) free, trunc(100 * (SUM(t.maxbytes) - SUM(t.bytes)) / SUM(t.maxbytes), 12) free_perc, SUM(t.maxblocks) - SUM(t.blocks) blocks_free, trunc(100 * (SUM(t.maxblocks) - SUM(t.blocks)) / SUM(t.maxblocks), 12) blocks_free_perc, SUM(t.bytes) used, SUM(t.disk_bytes) disk_used FROM ( SELECT a.tablespace_name, a.file_name, a.online_status, a.status, CASE WHEN a.AUTOEXTENSIBLE = 'NO' THEN a.BYTES ELSE a.MAXBYTES END maxbytes, ( SELECT a.BYTES - NVL(SUM(b.BYTES), 0) FROM dba_free_space b WHERE b.FIL E_ID = a.FILE_ID ) bytes, a.BYTES disk_bytes, CASE WHEN a.AUTOEXTENSIBLE = 'NO' THEN a.BLOCKS ELSE a.MAXBLOCKS END maxblocks, ( SELECT a.BLOCKS - NVL(SUM(b.BLOCKS), 0) FROM dba_free_space b WHERE b.FILE_ID = a.FILE_ID ) blocks FROM dba_data_files a ) t WHERE t.tablespace_name = 'CBS'

which is running since yesterday. I noticed the elapsed time is greater than Executions. Kindly advise if this is causing performance degradation

Today:
awrrpt_1_11_12.html
awrrpt_181217_10_11.html

Yesterday

awrrpt_17122017_18_19.html

Regards,

Roshan

[ErmanArslansOracleBlog]

You have lots of CPU waits. a Cpu bound env..
Please concantrate on "SQL ordered by CPU Time". Concantrate on CPU Time(s).. (there are sqls which have lots of executions, a small optimization that can be done on them, may affect your performance immediately.)

Try to tune the SQLs.

Consider setting sga to an optimal value. (increase)
Also, consider using Hugepages for SGA. (if not already using them, you should configure them immediately.)

[Roshan]

Thanks.

I have checked the sql with the development team and after some fine tuning it is ok now. We will plan to increase the cpu as well.

Can you please advise by how much can I increase the sga if the system memory is 252G?

For the Hugepage configuration, from the link below

https://docs.oracle.com/database/121/UNXAR/appi_vlm.htm#UNXAR402

step G.1.2 Configuring HugePages on Linux

the value of memlock -> 240000000

?

[Roshan]

current value of sga: 75G

[ErmanArslansOracleBlog]

According to the ADDM findings that is present in your AWRS;
the recommended value for sga_target is 96000M.

[ErmanArslansOracleBlog]

When Hugepages are configured;  the memlock setting is specified in KB, and the maximum locked memory limit should be set to at least 90 percent of the current RAM.

you have 252GB memory installed on your db server.. So, do the math...

[Roshan]

Thanks for support Erman.

we are planning to upgrade cpu(virtual) from 8 cores on a red hat Linux 6.7 server today. What is the maximum cpu that red hat Linux supports? 16 or 32?

[ErmanArslansOracleBlog]

Check this note ->

https://access.redhat.com/articles/rhel-limits

I suppose you are using Intel..

If your OS is Redhat 6.7 64 bit, then 384 virtual cpus is the max.

32 is the max vcpu for 32 bit redhat 6.7