Read listener.log from a table

Read listener.log from a table

--DROP DIRECTORY LISTENER_LOG
--DROP TABLE LISTENER_TABLE

CREATE OR REPLACE DIRECTORY LISTENER_LOG AS '/software/oracle/diag/tnslsnr/my_server/lsnr_igt/trace';

CREATE TABLE LISTENER_TABLE (line varchar2(4000) )
ORGANIZATION EXTERNAL (
 type oracle_loader
 default directory LISTENER_LOG
 access parameters
( records delimited by newline
  nobadfile
  nologfile
  nodiscardfile
  fields ldrtrim
  missing field values are null
  reject rows with all null fields
 ( line char(4000) )
 )
location ('lsnr_igt.log')
)
reject limit unlimited;

select * from LISTENER_TABLE;


CREATE TABLE LISTENER_TABLE_CONNECT (connect_date VARCHAR2(100),
                                     connect_str  VARCHAR2(4000) )
TABLESPACE IGT_TABLE;

INSERT /*+ APPEND */ INTO LISTENER_TABLE_CONNECT (connect_date, connect_str)
SELECT SUBSTR(line,1,20), line
FROM LISTENER_TABLE
WHERE line LIKE '%CONNECT_DATA%'
  AND line LIKE '%JUL-2021%'
 

Set OPTIMIZER_FEATURES_ENABLE to a lower version.

==========

Issue

==========

Workaround for new oracle version optimizer bug.

Setting OPTIMIZER_FEATURES_ENABLE to a lower version.

==========

Example

==========

SELECT name, value 

  FROM V$PARAMETER 

 WHERE name LIKE '%optimizer_features_enable%'

NAME                           VALUE
------------------------------ ------------------------------
optimizer_features_enable      19.1.0
 
ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE='12.1.0.2' SCOPE=BOTH;

SELECT name, value 

  FROM V$PARAMETER 

 WHERE name LIKE '%optimizer_features_enable%'

NAME                           VALUE
------------------------------ ------------------------------
optimizer_features_enable      12.1.0.2

DBA_AUTOTASK_OPERATION

=======================

DBA_AUTOTASK_OPERATION

=======================

DBA_AUTOTASK_OPERATION displays all automated maintenance task operations.

SELECT client_name, operation_name, status

  FROM DBA_AUTOTASK_OPERATION;

client_name                     opearation_name           status 

------------------------------- ------------------------- -----------

auto optimizer stats collection auto optimizer stats job  ENABLED

auto space advisor          auto space advisor job   ENABLED

sql tuning advisor         automatic sql tuning task ENABLED

To see current settings:

SELECT client_name, status, attributes 

FROM DBA_AUTOTASK_CLIENT;

By default, all are enabled.

 

CLIENT_NAME                       STATUS   ATTRIBUTES

--------------------------------- -------- ------------------------------------------------------

auto optimizer stats collection   ENABLED  ON BY DEFAULT, VOLATILE, SAFE TO KILL

auto space advisor                ENABLED  ON BY DEFAULT, VOLATILE, SAFE TO KILL

sql tuning advisor                ENABLED  ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL

 

SELECT window_name, autotask_status, optimizer_stats 

FROM DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME         AUTOTASK_STATUS     OPTIMIZER_STATS

------------------- ------------------- -----------------------------

SUNDAY_WINDOW       ENABLED             ENABLED

SATURDAY_WINDOW     ENABLED             ENABLED

FRIDAY_WINDOW       ENABLED             ENABLED

THURSDAY_WINDOW     ENABLED             ENABLED

WEDNESDAY_WINDOW    ENABLED             ENABLED

TUESDAY_WINDOW      ENABLED             ENABLED

MONDAY_WINDOW       ENABLED             ENABLED

Scheduler windows details:

SELECT window_name, resource_plan FROM DBA_SCHEDULER_WINDOWS;

WINDOW_NAME         RESOURCE_PLAN

------------------- ------------------------

WEEKEND_WINDOW                             

WEEKNIGHT_WINDOW                           

SUNDAY_WINDOW      DEFAULT_MAINTENANCE_PLAN

SATURDAY_WINDOW    DEFAULT_MAINTENANCE_PLAN

FRIDAY_WINDOW      DEFAULT_MAINTENANCE_PLAN

THURSDAY_WINDOW    DEFAULT_MAINTENANCE_PLAN

WEDNESDAY_WINDOW   DEFAULT_MAINTENANCE_PLAN

TUESDAY_WINDOW     DEFAULT_MAINTENANCE_PLAN

MONDAY_WINDOW      DEFAULT_MAINTENANCE_PLAN

Autotasks are enabled by default in 11g and 12c.

To disable the tasks which requires license:

exec DBMS_AUTO_TASK_ADMIN.DISABLE('sql tuning advisor', NULL, NULL);

exec DBMS_AUTO_TASK_ADMIN.DISABLE('auto space advisor', NULL, NULL);

Activation / Deactivation

To disable all AUTO_TASKS:

EXEC DBMS_AUTO_TASK_ADMIN.disable;

To deactivate only Optimizer Stats Collection:

BEGIN  

  DBMS_AUTO_TASK_ADMIN.disable(

      client_name => 'auto optimizer stats collection',

      operation => NULL,

      window_name => NULL);

END;

/ 

SELECT client_name, status

FROM DBA_AUTOTASK_CLIENT;

CLIENT_NAME                       STATUS  

--------------------------------- --------

auto optimizer stats collection   DISABLED

auto space advisor                ENABLED 

sql tuning advisor                ENABLED 

To Enable jobs:

BEGIN  

  DBMS_AUTO_TASK_ADMIN.enable(

      client_name => 'sql tuning advisor',

      operation => NULL,

      window_name => NULL);

END;

/ 

BEGIN  

  DBMS_AUTO_TASK_ADMIN.enable(

      client_name => 'auto optimizer stats collection',

      operation => NULL,

      window_name => NULL);

END;

/ 

CLIENT_NAME                           STATUS

------------------------------------- -------------------------

auto optimizer stats collection       ENABLED

auto space advisor                    DISABLED

sql tuning advisor                    ENABLED

- To manually run the jobs:

1. Auto optimizer stats collection:

EXEC DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC;

2. Auto sql tuning advisor: 

EXEC DBMS_AUTO_SQLTUNE.EXECUTE_AUTO_TUNING_TASK;

3. Auto space advisor: 

EXEC DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC;

- DBMS_STATS.GATHER_DATABASE_STATS_JOB slow

In case DBMS_STATS.GATHER_DATABASE_STATS_JOB is slow and using a lot of CPU, it might be because of missing dictionary stats.

 

SOLUTION: Gather dictionary stats and fixed object stats by executing following: 

EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');

EXEC DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

Then enable all processes to run

BEGIN  

  DBMS_AUTO_TASK_ADMIN.enable(

      client_name => 'sql tuning advisor',

      operation => NULL,

      window_name => NULL);

END;

/ 

BEGIN  

  DBMS_AUTO_TASK_ADMIN.enable(

      client_name => 'auto optimizer stats collection',

      operation => NULL,

      window_name => NULL);

END;

/ 

BEGIN  

  DBMS_AUTO_TASK_ADMIN.enable(

      client_name => 'auto space advisor',

      operation => NULL,

      window_name => NULL);

END;

/