AWR

How to Read CPU Utilization of OS Statistics in AWR

Posted on
CPU Utilization Breakdown
CPU Utilization We might have some doubts about OS CPU utilization when we read an AWR report like the following: We all may be confused among these components, which are %BUSY, %USER, %SYS, %IOWAIT and %IDLE. To figure them out, we need to know their relationships. Breakdown Structure Here is a breakdown structure of CPU […]
STATSPACK

How to Workaround Oracle AWR in Standard Edition

Posted on
Oracle AWR in Standard Edition I was assigned to collect performance data on an unfamiliar database. While I ran AWR report by executing @?/rdbms/admin/awrrpt.sql under SQL prompt, I got an empty report with many errors (ORA-20023 and others) like this: WARNING (-20023) ORA-20023: Missing start and end values for time model stat: parse time elapsed […]
SQL Tuning

How to Correct Wrong Execution Path

Posted on
SQL statements including queries and DML sometimes execute with the different index because of the following reasons: Missing index The target index might be dropped accidentally by someone else. Therefore, the optimizer have no choice but to use the wrong index instead, which may not match your expectations. You should check the existence of desired […]
Memory

How to Resolve ORA-27137: unable to allocate large pages to create a shared memory segment

Posted on
ORA-27137 Tried to startup a database to nomount, but failed. SQL> startup nomount; ORA-27137: unable to allocate large pages to create a shared memory segment Linux-x86_64 Error: 12: Cannot allocate memory Additional information: 402653184 Additional information: 1 Except that you have special reasons to use hugepagesfor this database, you can disable it by this. SQL> […]
Memory

How to Raise SGA_TARGET or MEMORY_TARGET

Posted on
Tunning ASSM or ASM target can never be done within memory scope, which is not allowed.SQL> alter system set sga_target=64g scope=both;alter system set sga_target=64g scope=both*ERROR at line 1:ORA-02097: parameter cannot be modified because specified value is invalidORA-00823: Specified value of sga_target greater than sga_max_sizeDid you have ever set SGA_MAX_SIZE? Let’s make sure this by inspecting […]
Performance

How to Gather Statistics on Database Level for Various Situations

Posted on
DBMS_STATS.GATHER_DATABASE_STATS You can do it without any indications, it will run as its own will. SQL> exec dbms_stats.gather_database_stats(); PL/SQL procedure successfully completed. Probably, you might want to indicate the sampling percentage. SQL> exec dbms_stats.gather_database_stats(estimate_percent => 20); PL/SQL procedure successfully completed. Seems slow? you can gather the statistics parallelly to speed it up. SQL> set timing […]
Memory

What is Variable Size SGA

Posted on
SQL> show sgaTotal System Global Area 6.1189E+10 bytesFixed Size                   830456 bytesVariable Size            3.4175E+10 bytesDatabase Buffers         2.7011E+10 bytesRedo Buffers                1847296 bytesYou might be confused about the memory allocation message during startup. […]