EasyReliableDBA

Software Training and dumps for Exam are available on www.easyreliable.com To download dumps. Please visit Links and Just pay $5/Rs300 http://easyreliable.com/easydumps/ This blog is Oracle DBA blog which cover Oracle RAC(10g,11g ,12c and 19c), Dataguard, RMAN,Backup&Recovery,Troubleshooting and Performance Tuning and other topics like Oracle Cloud,MongoDB,Golden Gate and Exadata.Please subscribe below to get update on my blog.We also provide Online SoftwareTraining and DBA Consulting

Friday, 2 February 2018

Collecting statistics for large table in oracle

To improve performance of default gather stats in ORACLE Database 11g

Sometime Default stats job will not collect statistic for large table. So, it is required to provide preference for those tables which will improve performance of overall database.

Our Production Database (7 TB) consists of some large tables , which size is more than 200 GB

Stats Scheduler is running every morning at 01:00 for 5 Hours . but scheduler is no picking large tables

which resulted statistics for some large tables are obsoletes

The Oracle Statistics are being processed in parallel

stats_parallel_degree = 2 , which means that basically up to 2 tables/indexes are being processed at the same time, but

one single thread processes each table. For the sake of simplicity, I call it "external parallelism"

It means that when the statistics of some large tables are obsoletes , and are automatically recalculated, it takes some significant amount of time, more than 9 hours for the stats jobs to be processed.

For all those reasons, I am trying to implement the "internal parallelism" , through the dbms_stats package, in order to configure several threads for the largest tables

Oracle uses the default degree of parallelism based on the number of CPUs. Note that the
default degree is NULL, which means that the database collects statistics using parallelism only if you set
the degree of parallelism at the table level with the DEGREE clause.

The maintenance tasks can run for a max of 8 hrs from 10pm on week nights, (will be ended at 6am, no matter if work is complete or not)

Lets look at the GATHER_STATS_PROG and it’s details

It’s main job is to collect statistics based on the following:

1. the tables found in the sys.DBA_TAB_MODIFICATIONS table.

2. The sample size set by by the database, (commonly at 25% but I’ve seen it determine 100% in some databases)

3. Deciding the method option for the tables, determining if histograms are required, etc..

4. Determining the date the job is to be processing stats for, the priority of what objects should be gathered and in what order.

5. Exiting the gather stats job at the end of the maintenance window and not “over-running” this window.

If you have a database that is not a small, OLTP, I would like you to now go an inspect your sys.DBA_TAB_MODIFCATIONS table with the following query:

select * from sys.dba_tab_modifications

where table_owner not in (‘SYS’,’SYSTEM’)

order by timestamp;

Note

When Oracle is collecting the Optimizer Statistics, it will look into the following argument specified with
DBMA_STATS package
CASCADE- Gather statistics on the indexes as well
DEGREE -- Degree of parallelism
ESTIMATE_PERCENT- Percentage of rows to estimate (NULL
means compute):
METHOD_OPT
NO_INVALIDATE - Does not invalidate the dependent cursors if set to TRUE

GRANULARITY-- Granularity of statistics to collect (for partitioned tables).
PUBLISH -- Collect Statistics info directly in DD or in Private area
INCREMENTAL- Pertaining to Partition tables global Statistics information
STALE_PERCENT- When the Statistics is considered outdated, default to 10

Please Note that the arguments in Bold above are new in 11g.

Improving the efficiency of gathering statistics

As data volumes grow and maintenance windows shrink, it is more important than ever to gather statistics in a timely manner. Oracle offers a variety of ways to speed up the statistics collection, from parallelizing the statistics gathering operations to generating statistics rather than collecting them.
Using parallelism
Parallelism can be leveraged in several ways for statistics collection

1) Intra object parallelism
2) Inter object parallelism
3)A combination of both intra and inter object parallelism

Intra object parallelism

Intra object parallelism is controlled by the DEGREE parameter in the DBMS_STATS.GATHER_*_STATS procedures.
The DEGREE parameter controls the number of parallel server processes that will be used to gather the statistics.
By default Oracle uses the same number of parallel server processes specified as an attribute of the table in the data dictionary (Degree of Parallelism).
All tables in an Oracle database have this attribute
set to 1 by default. It may be useful to explicitly set this parameter for the statistics collection on a large table to speed up statistics collection.
Alternatively you can set DEGREE to AUTO_DEGREE ;

Oracle will automatically determine the
appropriate number of parallel server processes that should be used to gather statistics, based on the size of an object. The value can be between 1 (serial execution) for small objects to DEFAULT_DEGREE
(PARALLEL_THREADS_PER_CPU X CPU_COUNT ) for larger objects.
15Best Practices for Gathering Optimizer Statistics
Figure 15. Use Intra object parallelism via the DEGREE parameter of the DBMS_STATS.GATHER_*_STATS
procedures
You should note that setting the DEGREE for a partitioned table means that multiple parallel sever
processes will be used to gather statistics on each partition but the statistics will not be gathered
concurrently on the different partitions. Statistics will be gathered on each partition one after the other.
Inter object parallelism
In Oracle Database 11.2.0.2, inter object parallelism was introduced and is controlled by the global
statistics gathering preference CONCURRENT 8 . When CONCURRENT is set to TRUE , Oracle employs the
Oracle Job Scheduler and Advanced Queuing components to create and manage multiple statistics
gathering jobs concurrently. Gathering statistics on multiple tables and (sub)partitions concurrently can
reduce the overall time it takes to gather statistics by allowing Oracle to fully utilize a multi-processor
environment.
The maximum number of active concurrent statistics gathering jobs is controlled by the
JOB_QUEUE_PROCESSES parameter. By default the JOB_QUEUE_PROCESSES is set to 1000. Typically
this is too high for a CONCURRENT statistics gathering operation especially if parallel execution will also
be employed. A more appropriate value would be 2 X total number of CPU cores (this is a per node
parameter in a RAC environment). You need to make sure that you set this parameter system-wise
( ALTER SYSTEM ... or in init.ora file) rather than at the session level ( ALTER SESSION ).
Combining Intra and Inter parallelism
Each of the statistics gathering jobs in a concurrent statistics gather operation can execute in parallel.
Combining concurrent statistics gathering and parallel execution can greatly reduce the time it takes to
gather statistics.
More information on concurrent statistics gathering can be found in part one of this series, Understanding
Optimizer Statistics.
8
16Best Practices for Gathering Optimizer Statistics
Figure 16. Use Inter and Intra object parallelism to speed up a DBMS_STATS.GATHER_TABLE_STATS on a
partitioned table.
When using parallel execution as part of a concurrent statistics gathering you should disable the
PARALLEL_ADAPTIVE_MULTI_USER initialization parameter to prevent the parallel jobs from being
down graded to serial. Again this should be done at a system level and not at a session level. That is;
Figure 17. Disable parallel_adaptive_mutli_user parameter
Incremental statistics
Gathering statistics on partitioned tables consists of gathering statistics at both the table level (global
statistics) and (sub)partition level. If the INCREMENTAL 9 preference for a partitioned table is set to
TRUE , the DBMS_STATS.GATHER_*_STATS parameter GRANULARITY includes GLOBAL, and
ESTIMATE_PERCENT is set to AUTO_SAMPLE_SIZE , Oracle will accurately derive all global level
statistics by scanning only those partitions that have been added or modified, and not the entire table.
Incremental global statistics works by storing a synopsis for each partition in the table. A synopsis is
statistical metadata for that partition and the columns in the partition. Aggregating the partition level
statistics and the synopses from each partition will accurately generate global level statistics, thus
eliminating the need to scan the entire table. When a new partition is added to the table, you only need
More information on Incremental Statistics can be found in part one of this series, Understanding
Optimizer Statistics.
9
17Best Practices for Gathering Optimizer Statistics
to gather statistics for the new partition. The table level statistics will be automatically and accurately
calculated using the new partition synopsis and the existing partitions’ synopses.
Note partition statistics are not aggregated from subpartition statistics when incremental statistics are
enabled.

eg

1) Identify by big tables and table having more transactions and set parallelism for those tables

table_table
table_tt_SHARE
table_SNAPSHOT
table_ACTION
tt_BNPL_table
table_ACCOUNT
FORMATTED_table_LINE
FORMATTED_table
REWARD_table_DETAILS
tableTION

Pre-check

select dbms_stats.get_prefs ('DEGREE','table_table') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','table_tt_SHARE') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','table_SNAPSHOT') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','table_ACTION') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','tt_BNPL_table') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','table_ACCOUNT') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','FORMATTED_table_LINE') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','FORMATTED_table') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','REWARD_table_DETAILS') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','tableTION') Degree from dual;

Implementation

select dbms_stats.get_prefs ('DEGREE','table_table') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','table_tt_SHARE') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','table_SNAPSHOT') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','table_ACTION') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','tt_BNPL_table') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','table_ACCOUNT') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','FORMATTED_table_LINE') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','FORMATTED_table') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','REWARD_table_DETAILS') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','tableTION') Degree from dual;

exec dbms_stats.set_table_prefs('schema', 'table_table', 'DEGREE', '4');
exec dbms_stats.set_table_prefs('schema', 'table_tt_SHARE', 'DEGREE', '4');
exec dbms_stats.set_table_prefs('schema', 'table_SNAPSHOT', 'DEGREE', '4');
exec dbms_stats.set_table_prefs('schema', 'table_ACTION', 'DEGREE', '4');
exec dbms_stats.set_table_prefs('schema', 'tt_BNPL_table', 'DEGREE', '4');
exec dbms_stats.set_table_prefs('schema', 'table_ACCOUNT', 'DEGREE', '4');
exec dbms_stats.set_table_prefs('schema', 'FORMATTED_table_LINE', 'DEGREE', '4');
exec dbms_stats.set_table_prefs('schema', 'FORMATTED_table', 'DEGREE', '4');
exec dbms_stats.set_table_prefs('schema', 'REWARD_table_DETAILS ', 'DEGREE', '4');
exec dbms_stats.set_table_prefs('schema', 'tableTION', 'DEGREE', '4');

select dbms_stats.get_prefs ('DEGREE','table_table') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','table_tt_SHARE') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','table_SNAPSHOT') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','table_ACTION') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','tt_BNPL_table') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','table_ACCOUNT') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','FORMATTED_table_LINE') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','FORMATTED_table') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','REWARD_table_DETAILS') Degree from dual;
select dbms_stats.get_prefs ('DEGREE','tableTION') Degree from dual;

Rollback

exec DBMS_STATS.DELETE_TABLE_PREFS('schema', ' table_table ', 'DEGREE');
exec DBMS_STATS.DELETE_TABLE_PREFS('schema', ' table_tt_SHARE', 'DEGREE');
exec DBMS_STATS.DELETE_TABLE_PREFS('schema', ' table_SNAPSHOT', 'DEGREE');
exec DBMS_STATS.DELETE_TABLE_PREFS('schema', ' table_ACTION', 'DEGREE');
exec DBMS_STATS.DELETE_TABLE_PREFS('schema', ' tt_BNPL_table', 'DEGREE');
exec DBMS_STATS.DELETE_TABLE_PREFS('schema', ' table_ACCOUNT', 'DEGREE');
exec DBMS_STATS.DELETE_TABLE_PREFS('schema', ' FORMATTED_table_LINE ', 'DEGREE');
exec DBMS_STATS.DELETE_TABLE_PREFS('schema', ' FORMATTED_table', 'DEGREE');
exec DBMS_STATS.DELETE_TABLE_PREFS('schema', ' REWARD_table_DETAILS', 'DEGREE');
exec DBMS_STATS.DELETE_TABLE_PREFS('schema', ' tableTION', 'DEGREE');

select dbms_stats.get_prefs ('DEGREE','table_table') Degree from dual;

exec dbms_stats.set_table_prefs('schema', table_table', 'DEGREE', '4')

Rollback

exec DBMS_STATS.DELETE_TABLE_PREFS('schema', 'table_table', 'DEGREE')

    Bug 16475397

DBMS_STATS.DELETE_TABLE_PREFS ('SCOTT', 'EMP', 'DEGREE');

SQL> select dbms_stats.get_prefs ('STALE_PERCENT','table_table') stale_percent from dual;

STALE_PERCENT
---------------------------------------------------------------------
SQL> select dbms_stats.get_prefs ('DEGREE','table_table') stale_percent from dual;

STALE_PERCENT
---------------------------------------------------------------
NULL

SQL>

Export and Import statistics for big Oracle database Step by Step

Recommendation for gather stats for big database where it is not completed in scheduled window in oracle 11g.

If we have any clone database of production, We can run gather stats on that database ,export from clone database and import gather stats on Production database

Enviroment

Clone Database = Source Database

Production Dataase = Target Server (Target Database )

Summary

On Source

1. Gather stats for schema TEST_DBA

2. Create table(NEW_STATS_NOV2017 ) to store statistics

3. Store schema stats to table STATS_TABLE

4. Export the table STATS_TABLE using datapump or exp

5. Transfer the dump to target server

On Target

1. Delete the stats before import on target server

2. Import using impdp or imp

3. Importing stats into same schema dbms_stats

4. Importing into different schema

1) Create dynamic sql for statistics and run on clone database

select 'EXEC DBMS_STATS.GATHER_TABLE_STATS('''||owner||''','||''''|| table_name||''''||',CASCADE=>TRUE, DEGREE=>10, ESTIMATE_PERCENT=>100,METHOD_OPT=>''FOR ALL COLUMNS SIZE 1'');' from dba_tables where owner in ('TEST_DBA')
and last_analyzed < sysdate - 8

2. Create Stat table in clone database .

EXEC DBMS_STATS.CREATE_STAT_TABLE('SYS','NEW_STATS_NOV2017','USERS');

3. Store new stats in stat table on clone database.

EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('TEST_DBA','NEW_STATS_NOV2017',STATOWN=>'SYS');

4. Take export backup of stat table from clone database.

exp tables=NEW_STATS_NOV2017 file=NEW_STATS_NOV2017.dmp log=_EXP_NEW_STATS_NOV2017.log recordlength=64445

5. FTP export backup from clone database(Source Database) server to production server(Targe) in binary mode.

On Target

----------------------------------------------------------------------------------------------------
1. Create another stats table in production database on Saturday any time

   EXEC DBMS_STATS.CREATE_STAT_TABLE('SYS','OLD_STATS_NOV2017','USERS');

----------------------------------------------------------------------------------------------------
2. Export existing stats in new stat table on production database.
(To take backup of existing stats)

   EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('TEST_DBA','OLD_STATS_NOV2017',STATOWN=>'SYS');

----------------------------------------------------------------------------------------------------
3. Import new stats table on production database from export backup file.

   imp full=y file=NEW_STATS_NOV2017.dmp log=IMP_NEW_STATS_NOV2017.dmp

----------------------------------------------------------------------------------------------------
4. Import stats from new stats table on production database.
(Stop listener and put database in restricted mode)

   EXEC DBMS_STATS.IMPORT_SCHEMA_STATS('TEST_DBA','NEW_STATS_NOV2017',STATOWN=>'SYS');


----------------------------------------------------------------------------------------------------
5. Take stats of SYS schema in production server.

   EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');

============END=============================================

SQL>
SQL> show parameter pending

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics     boolean     FALSE
SQL>

exec dbms_stats.set_schema_prefs('PILOT_DBA', 'PUBLISH', 'false');

select 'EXEC DBMS_STATS.GATHER_TABLE_STATS('''||owner||''','||''''|| table_name||''''||',CASCADE=>TRUE, DEGREE=>10, ESTIMATE_PERCENT=>100,METHOD_OPT=>''FOR ALL COLUMNS SIZE 1'');' from dba_tables
where owner in ('TEST_DBA')
and last_analyzed < sysdate - 8

or

dbms_stats.gather_schema_stats

As earlier mentioned, statistics for a table are published immediately by default, so we use the DBMS_STATS package to change this default behaviour.

SQL> exec dbms_stats.set_table_prefs('SH','SALES','PUBLISH','FALSE');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('PUBLISH', 'SH', 'SALES' ) FROM DUAL;

DBMS_STATS.GET_PREFS('PUBLISH','SH','SALES')
----------------------------------------------------------------------------------------------
FALSE

EXEC dbms_stats.gather_schema_stats('PILOT_DBA', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, degree =>DBMS_STATS.DEFAULT_DEGREE, granularity => 'ALL', CASCADE => TRUE);

SELECT TABLE_NAME,PARTITION_NAME ,LAST_ANALYZED FROM DBA_TAB_PENDING_STATS;

alter session set OPTIMIZER_USE_PENDING_STATISTICS=TRUE;
or
exec dbms_stats.publish_pending_stats('QUEST',null);

if issue,pending area could be discarded

alter session set OPTIMIZER_USE_PENDING_STATISTICS=FALSE;

2. Create Stat table in TestDB server.

   EXEC DBMS_STATS.CREATE_STAT_TABLE('SYS','NEW_STATS_NOV2017','USERS');

3. Export new stats in stat table.

EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('TEST_DBA','NEW_STATS_NOV2017',STATOWN=>'SYS');

4. Take export backup of stat table.

   exp tables=NEW_STATS_NOV2017 file=NEW_STATS_NOV2017.dmp log=_EXP_NEW_STATS_NOV2017.log recordlength=64445

5. FTP export backup from trdprd server to production server(milldale) in binary mode.

----------------------------------------------------------------------------------------------------
6. Create another stat table in production server(camprd) on saturday any time

   EXEC DBMS_STATS.CREATE_STAT_TABLE('SYS','OLD_STATS_NOV2017','USERS');

----------------------------------------------------------------------------------------------------
7. Export existing stats in new stat table. (To take backup of existing stats)

   EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('TEST_DBA','OLD_STATS_NOV2017',STATOWN=>'SYS');

----------------------------------------------------------------------------------------------------
8. Import new stat table from export backup file.

   imp full=y file=NEW_STATS_NOV2017.dmp log=IMP_NEW_STATS_NOV2017.dmp

----------------------------------------------------------------------------------------------------
9. Import stats from new stat table. (Stop listener and put database in restricted mode)

   EXEC DBMS_STATS.IMPORT_SCHEMA_STATS('TEST_DBA','NEW_STATS_NOV2017',STATOWN=>'SYS');

----------------------------------------------------------------------------------------------------
10. Take stats of SYS schema in production server.

   EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');

=======================final==================

1-check current setting of statistic preference statistics at datbase level

show parameter optimizer_use_pending_statistics

2) sets the preference for the PUBLISH parameter to false (default=true) for
pilot_dba schema.So that the database won’t automatically publish new the statistics
where as It will write them to the 'pending' area, not directly to the data dictionary.

exec dbms_stats.set_schema_prefs('PILOT_DBA', 'PUBLISH', 'false');
or
alter session set OPTIMIZER_USE_PENDING_STATISTICS=FALSE;

3) Gether stats either table or schema level

Table level syntax

select 'EXEC DBMS_STATS.GATHER_TABLE_STATS('''||owner||''','||''''|| table_name||''''||',CASCADE=>TRUE, DEGREE=>10, ESTIMATE_PERCENT=>100,METHOD_OPT=>''FOR ALL COLUMNS SIZE 1'');' from dba_tables
where owner in ('TEST_DBA')
and last_analyzed < sysdate - 8

or

schema level sysntax

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME =>'TEST_DBA',CASCADE=>TRUE,DEGREE=>15, ESTIMATE_PERCENT=>100,METHOD_OPT=>''FOR ALL COLUMNS SIZE 1);
or

exec dbms_stats.gather_schema_stats(ownname=>'TEST_DBA',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, cascade=>TRUE, degree=>4);

Now 2 sets of statistics for objects in the TEST_DBA schema will be available - the current statistics still in the data dictionary and the new ones in the pending area.

4) By setting the (dynamic) initialisation parameter 'optimizer_use_pending_statistics' to 'true' the statistics in the pending area will be used

alter session set OPTIMIZER_USE_PENDING_STATISTICS=TRUE;

or
exec dbms_stats.publish_pending_stats('TEST_DBA',null);

Perform your tests by running a workload against the schema and checking the performance and the execution plans

If you’re happy with the new set of (pending) statistics, make them public by executing this statement:

5) rollback plan

if issue,pending area could be discarded

alter session set OPTIMIZER_USE_PENDING_STATISTICS=FALSE;
or
exec dbms_stats.set_schema_prefs('TEST_DBA', 'PUBLISH', 'false');

How to change Scheduler maintenance windows in Oracle Step by Step

Pre-implementation

---To check job history

ALTER SESSION SET NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS';

column job_name format a30
column log_date format a40
column actual_start_date format a40
column run_duration format a60
column cpu_used format a50
SELECT job_name, log_date, status, actual_start_date, run_duration, cpu_used FROM dba_scheduler_job_run_details where job_name like '%STA%' and trunc(log_date) = trunc(sysdate -2) ORDER BY LOG_DATE DESC;

----stats job window--------

select job_name,PROGRAM_NAME,SCHEDULE_NAME,SCHEDULE_TYPE,START_DATE,STATE   from dba_scheduler_jobs where job_name like '%STA%';

--To check job window

set linesize 120
column WINDOW_NAME format a20
column ENABLED format a7
column REPEAT_INTERVAL format a60
column DURATION format a15

select job_name,job_type,program_name,schedule_name,job_class from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';

-- To check job window details

SQL>select window_name, enabled, repeat_interval, duration from dba_scheduler_windows where window_name in (select WINDOW_NAME from dba_scheduler_wingroup_members where WINDOW_GROUP_NAME='EVERYNIGHT_WINDOW_GROUP')
/

SQL> select * from dba_scheduler_wingroup_members where window_group_name = 'EVERYNIGHT_WINDOW_GROUP';

WINDOW_GROUP_NAME              WINDOW_NAME
------------------------------ ------------------------------
EVERYNIGHT_WINDOW_GROUP        EVERYNIGHT_WINDOW

select window_name,repeat_interval,duration from dba_scheduler_windows where window_name='EVERYNIGHT_WINDOW';

Implementation
--------------------------

Plan A : To change duration of existing window

BEGIN
dbms_scheduler.disable(
    name => 'EVERYNIGHT_WINDOW');
dbms_scheduler.set_attribute(
    name      => 'EVERYNIGHT_WINDOW',
    attribute => 'DURATION',
    value     => numtodsinterval(6, 'hour'));
dbms_scheduler.enable(
    name => 'EVERYNIGHT_WINDOW');
END;

exec dbms_scheduler.set_attribute('EVERYNIGHT_WINDOW','repeat_interval','FREQ=daily;byhour=01;byminute=0; bysecond=0');

Plan B: create new window and assigned to gather stat job

BEGIN
dbms_scheduler.create_window(
    window_name     => 'EVERYNIGHT_WINDOW_6HRS',
    duration        => numtodsinterval(6, 'hour'),
    resource_plan   => 'DEFAULT_MAINTENANCE_PLAN',
    repeat_interval => 'FREQ=DAILY;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
dbms_scheduler.add_window_group_member(
    group_name => 'EVERYNIGHT_WINDOW_GROUP_6HRS',
    window_list => 'EVERYNIGHT_WINDOW_6HRS');
END;

exec dbms_scheduler.set_attribute('EVERYNIGHT_WINDOW_6HRS','repeat_interval','FREQ=daily;byhour=01;byminute=0; bysecond=0');

Post Implementation
------------------ ---------------
SQL> column REPEAT_INTERVAL format a60
column DURATION format a15
SQL> SQL>
SQL> select window_name, enabled, repeat_interval, duration from dba_scheduler_windows;

Verification
---------------

To check job window

set linesize 120
column WINDOW_NAME format a20
column ENABLED format a7
column REPEAT_INTERVAL format a60
column DURATION format a15

select window_name, enabled, repeat_interval, duration from dba_scheduler_windows;

A patch conflict is occurring while patching database with Oct 2017 PSU. Oracle version : 11.2.0.4

========OCT 2017 psu on 11.2.0.4 conflict patch==============

Issue Details

Conflict details:
server:database:/oracle/CH21091/26392168 $$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation. All rights reserved.

PREREQ session

Oracle Home : /oracle/product/cam44ppt/11.2.0.4
Central Inventory : /oracle/oraInventory
from : /oracle/product/cam44ppt/11.2.0.4/oraInst.loc
OPatch version : 11.2.0.3.6
OUI version : 11.2.0.4.0
Log file location : /oracle/product/cam44ppt/11.2.0.4/cfgtoollogs/opatch/opatch2017-12-27_08-25-37AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

ZOP-40: The patch(es) has conflicts with other patches installed in the Oracle Home (or) among themselves.

Prereq "checkConflictAgainstOHWithDetail" failed.

Summary of Conflict Analysis:

There are no patches that can be applied now.

Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches :
25734992, 26392168

Following patches will be rolled back from Oracle Home on application of the patches in the given list :
25734992

Conflicts/Supersets for each patch are:

Patch : 26392168

Conflict with 25734992
Conflict details:
/oracle/product/cam44ppt/11.2.0.4/lib/libgeneric11.a:/qmudx.o

OPatch succeeded.

Analysis on the current patch conflict scenario and subsequent action plan.
> To be applied PSU patch 26392168 (11.2.0.4.171017) has conflicts with the installed patch 25734992.

> However, the patch 25734992 has been regressed and the replacement patch is 26931359.

> When analyzing the conflicts with the replacement patch, it shows conflicts with the PSU 11.2.0.4.171017. A resolution patch 26950781 is available which resolves the conflict. Below is the action plan.

Action Plan:
=========
1. Rollback the conflicting regressed patch 25734992.

2. Apply the PSU patch 26392168.

3. Download the resolution patch. The download link is provided below. Please select the options accordingly as listed and then download.

https://updates.oracle.com/download/26950781.html
Platform or Language : IBM AIX on POWER Systems (64-bit)

You should get the file : p26950781_11204171017_AIX64-5L.zip

4. Apply the newly downloaded patch 26950781.

Recommendation:
=============
I see the current OPatch utility version being used is 11.2.0.3.6 which is an older version. Oracle recommends to use the latest available OPatch version due to enhancements, up-to-date bug fixes and is more compatible with the latest patches. The latest OPatch version 11.2.0.3.17. Updating OPatch version is a fairly simple process and does not require any downtime. The steps are as below:

Steps to update OPatch version:
1. Take a backup of existing $ORACLE_HOME/OPatch directory be renaming it to opatch_backup or any other suitable name of your choice.

2. Download the latest OPatch. The download link is provided below. Please select the options accordingly as listed and then download:

https://updates.oracle.com/download/6880880.html
Select a Release : Oracle 11.2.0.0.0
Platform or Language : IBM AIX on POWER Systems (64-bit)

You should get the zip file : p6880880_112000_AIX64-5L.zip

3. Unzip the downloaded zip file directly to $ORACLE_HOME path and it'll create a directory named OPatch.

4. Verify the OPatch version through commands like : opatch lsinventory OR opatch version.