Adaptive Query Optimization: SQL Plan Directives Causing High CPU after 12c Upgrade

Hello all!

As DBAs we are always being recommended by Oracle and also recommending to clients to update their databases, but we have to be aware about new features and their effects. This is the case of Adaptive Query Optimization and in this particular case on SQL Plan Directives.

SQL Plan Directives are one of the functionalities that compose the Adaptive Query Optimization in Oracle Database 12c. The basic idea is pretty interesting: The SQL Optimizer keeps reviewing bad (“suboptimal”, as they like to say) plans, tipically incorrect cardinality estimations and generates SQL plan directives, like for missing histograms or extended statistics.

In my case, just after the upgrade to 12c (made on Jan 27th), the CPU usage increased for the same report always ran in the database:

12c_upgrade

Ok, how to check it?

More“Adaptive Query Optimization: SQL Plan Directives Causing High CPU after 12c Upgrade”

EM Event: Number of failed login attempts exceeds threshold value [Part 2]

Hello,
As a second part of last week’s post, here is a way to accomplish that without using audit. Using a simple logon trigger! šŸ™‚
Take a look:

First step: Create a table to store the data.

CREATE TABLE SYS.TRG_LOGON (SCHEMA VARCHAR2(50), SERVER VARCHAR2(200), FAILTIME DATE, ERROR VARCHAR2(200));

More“EM Event: Number of failed login attempts exceeds threshold value [Part 2]”

User EXPIRED(GRACE) – How to Never Expire!

Ok, this is a common issue, once it’s like the default behavior of oracle for DEFAULTĀ profile, used as DEFAULT to new users. But it’s really easy to solve. šŸ™‚

Before expiring you should be received some:

ORA-28002: the password will expire within x days

Ok then, but what if you are facing errors to reuse the same password, don’t know the password and yet, how to never let it happen again?

More“User EXPIRED(GRACE) – How to Never Expire!”

GOLDENGATE process show RUNNING status, when not RUNNING.

This might occur in case of crash onĀ host or database node whereĀ goldengate is running.

Goldengate maintains information about the process status in the file system, when the server crashes the file that has PID data is not released, and the process status in GGSCI shows erroneous information.

In GGSCI, process status it’s OK:

GGSCI (greporaLAB.net) 7> info mgr

Manager is running (IP port greporaLAB.net.7809, Process ID 42064).

If you try to stop process, you will not be able to:

More“GOLDENGATE process show RUNNING status, when not RUNNING.”

OGG-02077 Extract encountered a read error in the asynchronous reader thread and is abending: Error code 1683

Have this crazy in ggserr.log?

Source Context :
SourceModule : [er.redo.ora]
SourceID : [/scratch/aime/adestore/views/aime_stuya22/oggcore/OpenSys/src/app/er/redo/oracle/redoora.c]
SourceFunction : [REDOORA_handle_read_error(errmsg_t &)]
SourceLine : [11630]

ERROR OGG-02077 Extract encountered a read error in the asynchronous reader thread and is abending: Error code 1683, error message: ORA-01683: unable to extend index . partition by in tablespace.

Check db alert_*.log, you should find some alert.

ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_1470969483_24757 by 128 in tablespace SYSAUX
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_1470969483_24757 by 128 in tablespace SYSAUX

Resolving issue:

alter tablespace SYSAUX add datafile '+DG' size 1g;

Some options are:
Reduce AWR retention:

SQL> SELECT extract(day from snap_interval) *24*60+extract(hour from snap_interval) *60+extract(minute from snap_interval) snapshot_Interval,
extract(day from retention) *24*60+extract(hour from retention) *60+extract(minute from retention) retention_Interval
FROM dba_hist_wr_control;
SQL> exec DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(retention => 10800, interval => 10);

Reduce Statistics Level:

SQL> show parameter STATISTICS_LEVEL
NAME                 TYPE        VALUE
---------------------------------------------------------
statistics_level     string     ALL

SQL> alter system set STATISTICS_LEVEL = TYPICAL scope=both sid='*';

Type your message when beep šŸ™‚

EM Repository: ORA-00060: Deadlock detected error in alert log

Hello!
Some time ago I found some ORA-00060: Deadlock detected. errors in a client OEM database… Like this:

Thu Dec 22 09:01:55 2016
ORA-00060: Deadlock detected. More info in file /oracle/oemdb/diag/rdbms/oemdb/oemdb/trace/oemdb_ora_1757.trc.
Thu Dec 22 09:02:07 2016
ORA-00060: Deadlock detected. More info in file /oracle/oemdb/diag/rdbms/oemdb/oemdb/trace/oemdb_ora_1759.trc.
ORA-00060: Deadlock detected. More info in file /oracle/oemdb/diag/rdbms/oemdb/oemdb/trace/oemdb_ora_1759.trc.

In summary, after investigating the trace (as per below), found that the issueis caused by the following command:

More“EM Repository: ORA-00060: Deadlock detected error in alert log”