Tag: DB

Compression Advisor killed my database!

Over the weekend one of the databases hung due to the flash recovery area was 100% full. I noticed one J001 process consuming significant CPU and I/O resources. Turns out this process was the automatic segment advisor job that runs on the weekend maintenance window.
The SQL executed was something like:

CREATE TABLE .dbms_tabcomp_temp_uncmp
TABLESPACE NOLOGGING
AS
SELECT /*+ FULL(.

) */
*
FROM .

…
After reading Oracle note Id 13463481.8 and confirming this with an SR, this is related to a bug for 11.2.0.3 version and fixed in 11.2.0.4. This bug generates excessive amount of redo when running the compression advisor on a table with a LOB column in a database running in ARCHIVELOG mode.

As we can’t just apply the required patch to the ORACLE_HOME right away, we decided to perform the workaround of disabling the automatic segment advisor task. The compression advisor is part of the segment advisor and is not possible just to disable one or the other.

To disable the segment advisor:

SQL> BEGIN
dbms_auto_task_admin.disable(
client_name => ‘auto space advisor’,
operation => NULL,
window_name => NULL);
END;
/  2    3    4    5    6    7
PL/SQL procedure successfully completed.
After executing the procedure, verify that the “auto space advisor” is disabled.

SQL> SELECT client_name, status FROM dba_autotask_client;
CLIENT_NAME                                                      STATUS
—————————————————————- ——–
auto optimizer stats collection                                  ENABLED
auto space advisor                                               DISABLED
sql tuning advisor                                               ENABLED
Although the advisor will not automatically run, you can always run it manually on the segments or indexes you want to be analyzed.
Thanks,

Alfredo

Oracle Recursive Sessions ORA-00018

Today I want to talk about Oracle Database session usage; last week there was a problem with a database reporting ORA-00018: maximum number of sessions exceeded, but the number of sessions from v$session was much less than the one specified in SESSIONS parameter.
So, what was going on?
Turns out this is explained by internal recursive sessions that also account but they are not seen in the dictionary views. Note ID 419130.1 explains this in detail but I wanted to see this behavior in my 12c test database.
SQL> select count(*) from v$session;
  COUNT(*)
———-
       104
SQL> select count(*) from x$ksuse where bitand(ksspaflg,1) !=0 ;
  COUNT(*)
———-
       132
Also wanted to know what sessions were recursive and seems like all of them are performing DDL operations.
SQL> select INDX,decode(bitand(ksuseflg,19),17,’BACKGROUND’,1,’USER’,2,’RECURSIVE’,’?’),ksuudsna from x$ksuse s WHERE decode(bitand(ksuseflg,19),17,’BACKGROUND’,1,’USER’,2,’RECURSIVE’,’?’)=’RECURSIVE’;
      INDX DECODE(BIT KSUUDSNA
———- ———- ——————————
         8 RECURSIVE  SYS
        11 RECURSIVE  SYS
        14 RECURSIVE  SYS
        15 RECURSIVE  SYS
        17 RECURSIVE  SYS
        19 RECURSIVE  SYS
        20 RECURSIVE  SYS
        21 RECURSIVE  SYS
        29 RECURSIVE  SYS
        32 RECURSIVE  SYS
        38 RECURSIVE  SYS
        39 RECURSIVE  SYS
        40 RECURSIVE  SYS
        42 RECURSIVE  SYS
       123 RECURSIVE  SYS
       126 RECURSIVE  SYS
       129 RECURSIVE  SYS
       134 RECURSIVE  SYS
       136 RECURSIVE  SYS
       137 RECURSIVE  SYS
       138 RECURSIVE  SYS
       139 RECURSIVE  SYS
       142 RECURSIVE  SYS
       144 RECURSIVE  SYS
       145 RECURSIVE  SYS
       148 RECURSIVE  SYS
       154 RECURSIVE  SYS
       155 RECURSIVE  SYS
       156 RECURSIVE  SYS
       157 RECURSIVE  SYS
       242 RECURSIVE  SYS
       247 RECURSIVE  SYS
       249 RECURSIVE  SYS
       250 RECURSIVE  SYS
       252 RECURSIVE  SYS
       253 RECURSIVE  SYS
       255 RECURSIVE  SYS
       257 RECURSIVE  SYS
       259 RECURSIVE  SYS
       264 RECURSIVE  SYS
       265 RECURSIVE  SYS
       269 RECURSIVE  SYS
       272 RECURSIVE  SYS
       273 RECURSIVE  SYS
       275 RECURSIVE  SYS
       276 RECURSIVE  SYS
       278 RECURSIVE  SYS
       367 RECURSIVE  SYS
       369 RECURSIVE  SYS
       371 RECURSIVE  SYS
       375 RECURSIVE  SYS
       376 RECURSIVE  SYS
       379 RECURSIVE  SYS
       381 RECURSIVE  SYS
       383 RECURSIVE  SYS
       384 RECURSIVE  SYS
       388 RECURSIVE  SYS
       389 RECURSIVE  SYS
       391 RECURSIVE  SYS
       392 RECURSIVE  SYS
60 rows selected.
This is something to keep in mind when setting SESSIONS parameter in future.
Thanks,

Alfredo