Running GSS with Auto Option

In this post, we'll see how to run Gather Schema Statistics with Auto option.

My Env:

Oracle DB   : 12.1.0.2
Oracle EBS : 12.2.5

One important aspect of a healthy Oracle database is database statistics. It’s the main data on which CBO feeds to generate good execution plans for SQLs. Implementing a periodic and consistent gather statistics procedure in Oracle E-Business Suite is every Applications DBA’s duty. But often I see that they are not set up properly to take advantage of the latest database features.  So let’s take advantage of new features around gathering database statistics.

GATHER AUTO : Initially introduced in 11gR2 as DBMS_STATS.AUTO_SAMPLE_SIZE, which yields a significant reduction in the time it takes to collect highly accurate statistics. Oracle decides which objects to gather fresh statistics for and also decides how to get them. Oracle determines the estimate percent to be used. The parallelism used is based on the init.ora setting. This also used *_TAB_MODIFICATIONS to decide on the eligible candidates.

Let us see how to submit GSS Auto in Oracle EBS.

After sometime, the request failed with error and on checking the log file we found the below issue.  

+---------------------------------------------------------------------------+

Application Object Library: Version : 12.2

Copyright (c) 1998, 2013, Oracle and/or its affiliates. All rights reserved.

FNDGSCST: Gather Schema Statistics

+---------------------------------------------------------------------------+

Current system time is 11-JUL-2019 14:10:42

+---------------------------------------------------------------------------+

**Starts**11-JUL-2019 14:10:43

**SQL error and free**11-JUL-2019 14:12:41

ORACLE error 600 in FDPSTP

Cause: FDPSTP failed due to ORA-00600: internal error code, arguments: [kqllod:no stub for dependency parent], [], [], [], [], [], [], [], [], [], [], []

.

The SQL statement being executed at the time of the error was: SELECT 11-JUL-2019 14:12:41

+---------------------------------------------------------------------------+

Start of log messages from FND_FILE

+---------------------------------------------------------------------------+

In GATHER_SCHEMA_STATS , schema_name= ALL percent= 100 degree = 8 internal_flag= NOBACKUP

stats on table WF_NOTIFICATION_OUT is locked

+---------------------------------------------------------------------------+

End of log messages from FND_FILE

+---------------------------------------------------------------------------+

+---------------------------------------------------------------------------+

No completion options were requested.

Output file size:

0

+---------------------------------------------------------------------------+

Concurrent request completed

Current system time is 11-JUL-2019 14:12:41

+-------------------------------------------------------------------

--------+

With this above log files info we couldn't debug much on the issue. Now we need to debug info from the alert log. What we should do is, we should check for the ORA-600 error from alert log file and should check in the trace file created for this ORA-600 error. In this trace file we can get some more detailed info which will be helpful in debugging.

Trace file /ebsdev/oracle/R12DEV/12.1.0/admin/R12DEV_ebsdev/diag/rdbms/r12dev/R12DEV/trace/R12DEV_j001_15186.trc

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

ORACLE_HOME = /ebsdev/oracle/R12DEV/12.1.0

System name:    Linux

Node name: ebsdev.sify.net

Release:   4.1.12-37.4.1.el6uek.x86_64

Version:   #2 SMP Tue May 17 07:23:38 PDT 2016

Machine:   x86_64

Instance name: R12DEV

Redo thread mounted by this instance: 1

Oracle process number: 217

Unix process pid: 15186, image: oracle@ebsdev.sify.net (J001)

*** 2019-07-11 14:10:00.851

*** SESSION ID:(1260.1524) 2019-07-11 14:10:00.851

*** CLIENT ID:() 2019-07-11 14:10:00.851

*** SERVICE NAME:(SYS$USERS) 2019-07-11 14:10:00.851

*** MODULE NAME:() 2019-07-11 14:10:00.851

*** CLIENT DRIVER:() 2019-07-11 14:10:00.851

*** ACTION NAME:() 2019-07-11 14:10:00.851

ORA-00600: internal error code, arguments: [kqllod:no stub for dependency parent], [], [], [], [], [], [], [], [], [], [], []

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 85

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2199

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2778

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058

ORA-06512: at "SYS.DBMS_IREFRESH", line 687

ORA-06512: at "SYS.DBMS_REFRESH", line 195

ORA-06512: at line 1

After applying the RDBMS patch 27883586, the particular ORA-600 issue got resolved.

Now resubmitted the concurrent with same set of parameters and this time it completed successfully.

Ref : Best Practices for Gathering Statistics with Oracle E-Business Suite (Doc ID        1586374.1)

         Refresh or Create MVIEW in Edition = ORA-600[kqllod:no Stub For Dependency Parent] (Doc ID 2408062.1)

                     ****************************நன்றி****************************