Tag: statistics

Database, Errors and Bugs

ORA-20001: Statistics Advisor: Invalid task name for the current user

 ,

Hi all,
So, I start seeing on my alert log:

2019-12-26T00:42:03.543268-05:00
Errors in file /u01/app/oracle/diag/rdbms/cdb1/CDB1/trace/CDB1_j000_89793.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_36807"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197

After some research I found Bug 22879263 – BETA 12.2 – ORA-12012 , ORA-20001 AND ORA-06512 FROM ORA$AT_OS_OPT_SY_88 JOB.

This was suppose to be fixed on 12.2.0.1, however I’m still getting. Anyway, I executed the below and it got fixed:

EXEC dbms_stats.init_package();

Ref: Recurring ORA-12012, ORA-20001, ORA-06512 In Container Database (Doc ID 2420581.1)

Hope it helps!

Database, Performance, SQL | PLSQL

Restore table statistics using dbms_stats

 

Hi all, another quick as reference. Table statistics as everyone knows are  very important when the CBO is creating the execution plan. When you have new statistics Oracle will invalid the current execution plans affected by them and create new execution plans based on the new statistics. Most of the time it gets right  if that causes your execution plan to change for worse?

In this situations dba_tab_stats_history   view and dbms_stats.restore_table_stats procedures are your friends.

To validate set and check and set the stats history:

select dbms_stats.get_stats_history_retention from dual; 
exec dbms_stats.alter_stats_history_retention(30);

On dba_tab_stats_history you can view the last statistics available:

select table_name, stats_update_time, from dba_tab_stats_history where table_name='TABLE1' and owner='USER' order by 2 desc;

Use the dbms_stats.restore_table_stats to restore the statistics:

exec dbms_stats.restore_table_stats(ownname=>'USER',tabname=>'TABLE1',AS_OF_TIMESTAMP=>'DATE from column STATS_UPDATE_TIME on dba_tab_stats_history');

Hope it helps!

Elisson Almeida

Database, Errors and Bugs

ORA-600 [kggsmGetString:1]

 , , ,

Hi all!
So, checking on this error, found the following relevant reference: ORA-600 [kggsmGetString:1] (Doc ID 1541187.1).
Once it was during a SQL running, the only match could be Bug 17235420 – ORA-600 [kggsmGetString:1] with adaptive cursor sharing (Doc ID 17235420.8).

The problem?
No detailed information, no workaround, only patches to apply. The only information is “ORA-600 [kggsmGetString:1] can occur in 12c if adaptive cursor sharing is used.”

So, checking on SQL Plan details, could see it’s using the following adaptive optimizations:

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - statistics feedback used for this statement
   - this is an adaptive plan (rows marked '-' are inactive)
   - 2 Sql Plan Directives used for this statement

MOS note Recommendations for Adaptive Features in Oracle Database 12c Release 1 (Adaptive Features, Adaptive Statistics and 12c SQL Performance)” ID 2312911.1 recommends to apply Oct 2017 BP or one-off patches to disables adaptive statistics only.

In this case, the easiest solution is to disable both, adaptive plans, as this release only has one parameter to control that – optimizer_adaptive_features which defaults to TRUE.

And it’s online:

SQL> show parameter adaptive

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_features	     boolean	 TRUE
optimizer_adaptive_reporting_only    boolean	 FALSE
parallel_adaptive_multi_user	     boolean	 TRUE
SQL> alter system set optimizer_adaptive_features=false scope=both;

System altered.

SQL> show parameter adaptive

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_features	     boolean	 FALSE
optimizer_adaptive_reporting_only    boolean	 FALSE
parallel_adaptive_multi_user	     boolean	 TRUE

After that, issue solved. ORA-600 didn’t repeated.

Hope it helps!

Database, Performance

11g+ Features: Extended Statistics

 , ,

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Extended Statistics

One of the most expected features of Oracle 11g is improvements to the dbms_stats package, specifically the ability to aid complex queries by providing extended statistics to the cost-based optimizer (CBO).

The 11g extended statistics are intended to improve the optimizers guesses for the cardinality of combined columns and columns that are modified by a built-in or user-defined function. In Oracle 10g dynamic sampling can be used to provide inter-table cardinality estimates, but dynamic sampling has important limitations. However, the 11g create_extended_stats in dbms_stats relieves much of the problem of sub-optimal table join orders allowing for extended statistics on correlated columns.

One of the expectation is to avoid using the ORDERED hint, one of the most popular SQL tuning hints, used to to specify that the tables be joined together in the same order that they appear in the FROM clause.

This feature can be controlled by hidden parameter:

_optimizer_enable_extended_stats in case of SQL Performance regression.

 

You can also see more detailed material here:
– About improvements on 18c (yeah, lots of things on 12c and 18c).
– Oracle 12c: Automatic Column Group Detection in Oracle Database 12c Release 1 (12.1)
– Oracle Base: Extended Statistics on 11gR2.

Cheers!

Database, Errors and Bugs, Migration | Upgrades | Patching, Performance

ORA-00932 to Export Statistics After 12c Upgrade – Undocumented

 , , , ,

Hello All,
I have a client that use to Export and Import Dictionary Statistics using a stats table. However, after upgrading database to 12c, is started to raise error below to export data to a stats table (created in 11g) or even to update stats in this table.

SQL> exec dbms_stats.upgrade_stat_table('GREPORA','MYSTATSTABLE');
DECLARE
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected BINARY got NUMBER
ORA-06512: at "SYS.DBMS_STATS", line 40183
ORA-06512: at line 28

SQL> EXEC DBMS_STATS.EXPORT_FIXED_OBJECTS_STATS (stattab => 'MYSTATSTABLE',statid  => 'TEST_MATHEUS',statown => 'GREPORA' );
BEGIN DBMS_STATS.EXPORT_FIXED_OBJECTS_STATS (stattab => 'MYSTATSTABLE',statid  => 'TEST_MATHEUS',statown => 'GREPORA' ); END;

*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected BINARY got NUMBER
ORA-06512: at "SYS.DBMS_STATS", line 37085
ORA-06512: at line 1

I reviewed several references (all very interesting, by the way, recommend you to take a look):
– ORA-20002 on Importing Statistics using DBMS_STATS Procedures (Doc ID 740345.1)
– Datapump Export Fails With Ora-00932 (Doc ID 1300803.1)
– Error “ORA-00932 – Inconsistent Datatypes: Expected BINARY Got NUMBER” When Executing the Primavera Gather-Statistics Scripts on an Oracle 12c Database (Doc ID 2111114.1)
– https://oracle-base.com/articles/12c/concurrent-statistics-collection-12cr1
– http://www.morganslibrary.org/reference/pkgs/dbms_stats.html
– How To Export and Import Dictionary Statistics (Doc ID 1450820.1)
– Fixed Objects Statistics (GATHER_FIXED_OBJECTS_STATS) Considerations (Doc ID 798257.1)

But no reference found to this specific issue, not even in MOS.

After some tests, I realized that maybe columns order in stats table can be changed in implementation between 11g and 12c. Bingo! The same columns and datatypes are in place, but in different order.

This is not documented in MOS neither on internet/independent blogs, but seems EXPORT% on DBMS_STATS presume stats table in it’s internal code without specified column orders.
As we know, a good development practice is to specify column names when performing any operation. Bad on you, Oracle!

More“ORA-00932 to Export Statistics After 12c Upgrade – Undocumented”

Database, Performance, SQL | PLSQL

Statistics not Being Auto Purged – Splitting Purge

 , , , ,

Hi all!
The post Purge SYSAUX Tablespace,  made on Fabruary 8this, is yet being high accessed. So, if  you’re interested, here it goes another post about:

Last week I supported a database was not purging statistics through MMON job, because is timeouting. Worst than simply that, the database is not purging statistics since 2012 and SYSAUX was huge!
To understand: By default, the MMON performs the automatic purge that removes all history older than:
1) current time – statistics history retention (by default 31 days)
2) time of recent analyze in the system – 1
MMON performs the purge of the optimizer stats history automatically, but it has an internal limit of 5 minutes to perform this job. If the operation takes more than 5 minutes, then it is aborted and stats not purged.

The problem was very clear in alert.log, through the entry:

Unexpected error from flashback database MMON timeout action
Errors in file /oracle/diag/rdbms/oracle/trace/oracle_mmon_1234567.trc:
ORA-12751: cpu time or run time policy violation

But it’s happening since 2012! How to address that?
More“Statistics not Being Auto Purged – Splitting Purge”

Database, Errors and Bugs

ORA-12012: error on auto execute of job “SYS”.”BSLN_MAINTAIN_STATS_JOB”

 , , ,

Hi all,
Evaluating a database I detected it was failing to execute the default scheduler job SYS.BSLN_MAINTAIN_STATS_JOB. This job is an Oracle defined automatic moving window baseline statistics computation job, that runs only in weekends.
Below the last stack error in the alert log:

2016-04-24 00:00:10.064000 +00:00
Errors in file /db/u1001/oracle/diag/rdbms/MYDB/MYDB/trace/MYDB_j000_15675.trc:
ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073
ORA-06512: at line 1
2016-04-26 15:54:07.480000 +00:00

And the full tracefile:

Trace file /db/u1001/oracle/diag/rdbms/MYDB/MYDB/trace/MYDB_j000_15675.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2
System name:    Linux
Node name:      prddb09
Release:        2.6.18-164.el5
Version:        #1 SMP Tue Aug 18 15:51:48 EDT 2009
Machine:        x86_64
Instance name: MYDB
Redo thread mounted by this instance: 1
Oracle process number: 151
Unix process pid: 15675, image: oracle@prddb09 (J000)
*** 2016-04-24 00:00:10.064
*** SESSION ID:(586.10305) 2016-04-24 00:00:10.064
*** CLIENT ID:() 2016-04-24 00:00:10.064
*** SERVICE NAME:(SYS$USERS) 2016-04-24 00:00:10.064
*** MODULE NAME:(DBMS_SCHEDULER) 2016-04-24 00:00:10.064
*** ACTION NAME:(BSLN_MAINTAIN_STATS_JOB) 2016-04-24 00:00:10.064
ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073
ORA-06512: at line 1

According the notes below, the recommended action is to recreate the DBSNMP component:
Bug 10110625 – DBSNMP.BSLN_INTERNAL reports ORA-6502 running BSLN_MAINTAIN_STATS_JOB (Doc ID 10110625.8)
ORA-12012: Error on Auto Execute of job SYS.BSLN_MAINTAIN_STATS_JOB (Doc ID 1413756.1)
KEWBMBTA: Maintain BSLN Thresholds Failed, Check For Details. (Doc ID 1490391.1)

However, it’s a process that can affect other mechanisms. So, I found the follow note with the same error pointing to a privilege issue:
Ora-06508: Pl/Sql: Could Not Find Program Unit Being Called: “DBSNMP.BSLN_INTERNAL” (Doc ID 1323597.1)

But after granting the privilege as workaround suggested, the fail remais…

MYDB> select * from dba_tab_privs where table_name='DBMS_JOB';
GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR                        PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ------------------------------ -----------
APEX_030200                    SYS                            DBMS_JOB                       SYS                            EXECUTE
SYSMAN                         SYS                            DBMS_JOB                       SYS                            EXECUTE
EXFSYS                         SYS                            DBMS_JOB                       SYS                            EXECUTE
PUBLIC                         SYS                            DBMS_JOB                       SYS                            EXECUTE
SQL> GRANT EXECUTE ON sys.dbms_job to DBSNMP;
Grant succeeded.
MYDB> select * from dba_tab_privs where table_name='DBMS_JOB';
GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR                        PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------
SYSMAN                         SYS                            DBMS_JOB                       SYS                            EXECUTE
APEX_030200                    SYS                            DBMS_JOB                       SYS                            EXECUTE
EXFSYS                         SYS                            DBMS_JOB                       SYS                            EXECUTE
DBSNMP                         SYS                            DBMS_JOB                       SYS                            EXECUTE
PUBLIC                         SYS                            DBMS_JOB                       SYS                            EXECUTE
SQL> EXEC DBMS_SCHEDULER.RUN_JOB('BSLN_MAINTAIN_STATS_JOB');
BEGIN DBMS_SCHEDULER.RUN_JOB('BSLN_MAINTAIN_STATS_JOB'); END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1

After that, while I was quering on DBSNMP, I realized another instance name active in DBSNMP.BSLN_BASELINES.
I guess this database was created with another instance name and then renamed without DBNID.

MYDB> select * from DBSNMP.BSLN_BASELINES;
DBID INSTANCE_NAME    BASELINE_ID BSLN_GUID                        TI A STATUS
---------- ---------------- ----------- -------------------------------- -- - ---------
4092499541 MYDB                       0 75B49690F8B4742084990643EEFFB6AA HX Y ACTIVE
4092499541 oldname                    0 415373CD9959B77AAEE1804F06D88B60 NW Y ACTIVE

So, I deleted the row and the job started to run successfully:

MYDB> DELETE FROM DBSNMP.BSLN_BASELINES WHERE INSTANCE_NAME ='oldname';
1 row deleted.
MYDB> commit;
Commit complete.
SQL> EXEC DBMS_SCHEDULER.RUN_JOB('BSLN_MAINTAIN_STATS_JOB');
PL/SQL procedure successfully completed.

Execution logs:

MYDB> select *
2    from (select owner, job_name, log_date, status, run_duration
3            from dba_scheduler_job_run_details a
4           where job_name = 'BSLN_MAINTAIN_STATS_JOB'
5           order by log_date)
6   where rownum < 10;
OWNER                          JOB_NAME                  LOG_DATE                            STATUS          RUN_DURATION
------------------------------ ------------------------- ----------------------------------- --------------- ---------------
SYS                            BSLN_MAINTAIN_STATS_JOB   03/04/16 00:00:08,484972 +00:00     FAILED          +000 00:00:08
SYS                            BSLN_MAINTAIN_STATS_JOB   10/04/16 00:00:07,943598 +00:00     FAILED          +000 00:00:07
SYS                            BSLN_MAINTAIN_STATS_JOB   17/04/16 00:00:08,486526 +00:00     FAILED          +000 00:00:08
SYS                            BSLN_MAINTAIN_STATS_JOB   24/04/16 00:00:10,067848 +00:00     FAILED          +000 00:00:09
SYS                            BSLN_MAINTAIN_STATS_JOB   29/04/16 13:58:10,779201 +00:00     FAILED          +000 00:00:01
SYS                            BSLN_MAINTAIN_STATS_JOB   29/04/16 14:01:04,162900 +00:00     SUCCEEDED       +000 00:00:00

I hope it help you too!

Matheus.

Database, Performance, SQL | PLSQL

DBA_TAB_MODIFICATIONS

 , , ,

Do you know the view “dba_tab_modifications”?
It’s very useful to know what has changed since the last stats gathering of a table and all decision/information that comes with… See the example below..

The only need is to run “dbms_stats.flush_database_monitoring_info” before cheking… take a look:

mydb> create TABLE matheus_boesing.test (nro number);
Table created.
mydb> begin
2 for i in 1..1000 loop
3 insert into matheus_boesing.test values (i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
mydb> select table_owner,table_name,inserts,updates,deletes from dba_tab_modifications where table_name ='test' and table_owner='MATHEUS_BOESING';
no rows selected
mydb> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
mydb> select table_owner,table_name,inserts,updates,deletes from dba_tab_modifications where table_name ='test' and table_owner='MATHEUS_BOESING';
TABLE_OWNER TABLE_NAME INSERTS UPDATES DELETES
---------------------- -------------- ---------- ---------- ----------
MATHEUS_BOESING test 1000 0 0

mydb> EXEC DBMS_STATS.GATHER_TABLE_STATS('MATHEUS_BOESING','test');
PL/SQL procedure successfully completed.
mydb> select table_owner,table_name,inserts,updates,deletes from dba_tab_modifications where table_name ='test' and table_owner='MATHEUS_BOESING';
no rows selected

For more information: http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4149.htm

Have a nice day! 😀
Matheus.