Month: June 2018

Aside

Test Your Changes

Following on from my previous post on there’s no such thing as a small change…

Please do not make any changes to a production system – a live system – without first testing for any side effects. For example, please do not read a blog post or a book chapter, and then check your system and find you are using manual memory management – and then just turn on automatic memory management. Query plans may change and performance may be impacted. One of three things could happen:

  • Things run exactly the same
  • Things run better than they did before
  • Things run much worse than they did before

Exercise caution before making changes; test the proposed change first!

Querying the alert log via SQL

Quick tip regarding the Oracle database alert log (from 11g onwards). There is a fixed table X$DBGALERTEXT:


SQL> select message_text from X$DBGALERTEXT where rownum <= 30;

MESSAGE_TEXT
-----------------------------------------------------------------------------------------------------------------------------------------
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 2
Number of processor cores in the system is 2
Number of processor sockets in the system is 1
Shared memory segment for instance monitoring created
CELL communication is configured to use 0 interface(s):
CELL IP affinity details:
NUMA status: non-NUMA system
cellaffinity.ora status: N/A
CELL communication will use 1 IP group(s):
Grp 0:
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.

ORACLE_HOME = /u01/app/oracle/product/11.2.0/orcl
System name:Linux
Node name:ODIGettingStarted
Release:2.6.39-400.17.1.el6uek.x86_64
Version:#1 SMP Fri Feb 22 18:16:18 PST 2013
Machine:x86_64
Using parameter settings in client-side pfile /u01/app/oracle/admin/orcl/pfile/init.ora on machine ODIGettingStarted
System parameters with non-default values:

30 rows selected.

My personal opinion? This can be useful if you're looking to create some custom alert log monitoring. However I still prefer to  monitor my alert logs using shell scripts since accessing this X$ table requires the instance to be up and operational. But if you don't have access to the OS then this could be useful.

I also found the following Metalink note:
High CPU for Queries on X$DBGALERTEXT (Doc ID 2056666.1)

APPLIES TO:

Oracle Database – Enterprise Edition – Version 11.2.0.1 and later
Information in this document applies to any platform.

SYMPTOMS

  • Query on X$DBGALERTEXT consumes high CPU taking a long time to complete.For example:
SELECT count(*)
FROM X$DBGALERTEXT
WHERE to_date(to_char(originating_timestamp, ‘dd-mon-yyyy hh24:mi’), ‘dd-mon-yyyy hh24:mi’) > to_date(to_char(systimestamp – .00694, ‘dd-mon-yyyy hh24:mi’), ‘dd-mon-yyyy hh24:mi’) /* last 10 minutes */
AND (
message_text = ‘ORA-00600’
OR message_text LIKE ‘útal%’
OR message_text LIKE ‘%error%’
OR message_text LIKE ‘%ORA-%’
OR message_text LIKE ‘%terminating the instance%’
);
  • It can also cause ORA-700 [dbgrfafr_1].

Comments in your SPFILE

Consider using the COMMENT= clause to document why a particular change was made the next time you make a change to a parameter using an SPFILE:

SQL> alter system set pga_aggregate_target=512m comment='Changed 04-JUN-2018, AWR recommendation, MR';

System altered.

 

SQL> select value, update_comment from v$parameter where name = 'pga_aggregate_target';SQL> select value, update_comment from v$parameter where name = 'pga_aggregate_target';
VALUE      UPDATE_COMMENT
-------------------- ---------------------------------------------------------------------------------------------------
536870912  Changed 04-JUN-2018, AWR recommendation, MR