Thursday Apr 21, 2016

Oracle April 2016 PSU and Proactive BPs are there

Hurray, it's Patching Day!

Sounds a bit like D-Day ;-) But April 19, 2016 the most recent April PSUs (Patch Set Updates) and BPs (Bundle Patches) got released.

Find all the necessary information with the below links: 

The important change in the April PSU/BP release:
The database patch for "Engineered Systems and Database In-Memory 12.1.0.2" luckily got renamed into "Proactive Bundle Patch 12.1.0.2". That is not only a rebranding but it should express that we would like to encourage you to apply the Bundle Patches
instead of the PSUs. Simple reason is that the BPs will contain optimizer fixes. 

In the MOS Note: 2102148.1 (Patch Set Update and Critical Patch Update April 2016 Availability Document) you'll find a section 3.1.4 linking to the database patches

This is the recommended one for Oracle Database 12.1.0.2:

  • Database Proactive Bundle Patch 12.1.0.2.160419 (Apr2016) Patch 22899531,

But right now it is available for Linux-x86-64, zLinux and Intel Solaris only. Not sure when the others will get released. Please find links to the regular PSUs and other ports and releases such as 11.2.0.4 and Windows etc in the above MOS Note: 2102148.1.

This is the list of fixes included in this Bundle Patch:

And don't worry about the name - I found out yesterday that not all MOS Notes have adopted the new naming convention to rename "Bundle Patches for Engineeered Systems and DB In-Memory" which was very misleading anyway into the new "Proactive Bundle Patches" naming. This may take a few additional days I'd guess ...

I will download it right now and patch my HOL environment.

And as usual don't forget the most recent version of opatch (Patch 6880880).

opatch download MOS

.

--Mike 
.


Tuesday Mar 22, 2016

GC Freelist Session Waits causing slowness and hangs

Best Practice Hint

One of the best things in my job:
I learn from you folks out there. Everyday. 

Credits here go to Maciej Tokar who did explain the below topic to me via LinkedIn - thanks a lot, Maciej! 
.

Locks are not being closed fast enough, resulting in gc freelist waits

You can find a reference for Global Cache Freelist in the Oracle Documentation. This issue here can or will lead to database being slow, up to complete hangs. Based on my research it looks as the issue is not related to RAC only but a general thing. In your session waits you'll spot this:

Event                               Event Class        % Event   Sessions
----------------------------------- --------------- ---------- ----------
gc freelist                         Cluster              41.37       8.61

This has been logged as a bugs 21352465 (public) and 18228629 (not public). It causes locks are not being closed fast enough, resulting in gc freelist waits. In conjunction the default for _gc_element_percent seemed to be too low at 120 (or 110 in 11.2.0.4).

Actually the issue can affect not only Oracle Database 12.1.0.2 but also Oracle Database 11.2.0.3 and 11.2.0.4.

See MOS Note:2055409.1 (Database Hangs with High "gc freelist" wait ) for further details.
.

Solution

  • Apply the patch for bug 18228629 on top of a PSU or BP where available
    • See the drop-down list to the right labeled "Release" to access the correct patch for your release
    • Unlike the above MOS Note states in Oracle Database 12.1.0.2 it is only available on top of the January 2016 PSU and BP and two other Exadata BPs - and on Linux only!
      .
  • Use the workaround and set _gc_element_percent = 200
    • This will require an instance restart as the parameter can't be changed dynamically:
      alter system set "_gc_element_percent"=200 scope=spfile;
      .

Epilogue

We've had a lot of discussions about underscore parameter in the past weeks. And I'm not a big fan of underscores especially when it comes to upgrades as experiences has shown that having underscores set one day may make it hard to remove them the other day - and underscores can significantly impact the upgrade duration in a negative way.

But on the other hand, if an issue is seriously affecting many customers, and there's no patch available for your platform and environment right now, what else can one do?
.

--Mike

.

Tuesday Mar 01, 2016

Differences between Automatic Statistics Gathering job and GATHER_SCHEMA_STATS

Recently a customer raised a question whether there are differences between the Automatic Statistics Gathering job and a manual creation of stats via the GATHER_SCHEMA_STATS procedure.

The results in performance were quite interesting. Performance after an upgrade from Oracle Database 11.2.0.3 to Oracle Database 11.2.0.4 was not good when the automatic stats job got used. But performance changed significantly to the better when schema stats were created with the downside of taking more resources during the gathering.

Is the Automatic Stats Gathering job enabled?

That question can be answered quite easily. There's a very good MOS Note:1233203.1 - FAQ: Automatic Statistics Collection displaying this query:

SELECT CLIENT_NAME, STATUS FROM DBA_AUTOTASK_CLIENT WHERE CLIENT_NAME='auto optimizer stats collection';

The MOS Note has also the code to enable (or disable) the job.
.

Which parameters/settings are used?

That question is a bit more tricky as the Note says: "The automatic statistics-gathering job uses the default parameter values for the DBMS_STATS procedures". But how do I display them?

The following script will display the parameters being used during the Automatic Statistics Gathering:

SET ECHO OFF
SET TERMOUT ON
SET SERVEROUTPUT ON
SET TIMING OFF
DECLARE
   v1  varchar2(100);
   v2  varchar2(100);
   v3  varchar2(100);
   v4  varchar2(100);
   v5  varchar2(100);
   v6  varchar2(100);
   v7  varchar2(100);
   v8  varchar2(100);
   v9  varchar2(100);
   v10 varchar2(100);        
BEGIN
   dbms_output.put_line('Automatic Stats Gathering Job - Parameters');
   dbms_output.put_line('==========================================');
   v1 := dbms_stats.get_prefs('AUTOSTATS_TARGET');
   dbms_output.put_line(' AUTOSTATS_TARGET:  ' || v1);
   v2 := dbms_stats.get_prefs('CASCADE');
   dbms_output.put_line(' CASCADE:           ' || v2);
   v3 := dbms_stats.get_prefs('DEGREE');
   dbms_output.put_line(' DEGREE:            ' || v3);
   v4 := dbms_stats.get_prefs('ESTIMATE_PERCENT');
   dbms_output.put_line(' ESTIMATE_PERCENT:  ' || v4);
   v5 := dbms_stats.get_prefs('METHOD_OPT');
   dbms_output.put_line(' METHOD_OPT:        ' || v5);
   v6 := dbms_stats.get_prefs('NO_INVALIDATE');
   dbms_output.put_line(' NO_INVALIDATE:     ' || v6);
   v7 := dbms_stats.get_prefs('GRANULARITY');
   dbms_output.put_line(' GRANULARITY:       ' || v7);
   v8 := dbms_stats.get_prefs('PUBLISH');
   dbms_output.put_line(' PUBLISH:           ' || v8);
   v9 := dbms_stats.get_prefs('INCREMENTAL');
   dbms_output.put_line(' INCREMENTAL:       ' || v9);
   v10:= dbms_stats.get_prefs('STALE_PERCENT');
   dbms_output.put_line(' STALE_PERCENT:     ' || v10);
END;
/

The settings of the DBMS_STATS.GATHER_SCHEMA_STATS procedure are documented:
https://docs.oracle.com/database/121/ARPLS/d_stats.htm#ARPLS68577 

When you compare the two you'll see that the settings/defaults are identical. 
.

But what is the difference between these two?

Both activities use the same parameters. So the stats will look the same - IF they get created. The real difference between the Automatic Statistics Gathering job and a manual invocation of GATHER_SCHEMA_STATS is that the latter will refresh ALL statistics whereas the Automatic Statistics Gathering job will refresh only statistics on objects where statistics are missing or marked as STALE.

The same behavior appears when you compare the recommendation to gather dictionary statistics before the upgrade by using DBMS_STATS.GATHER_DICTIONARY_STATS versus a DBMS_STATS.GATHER_SCHMEA_STATS('SYS')call. The latter will refresh all statistics whereas the first one will take less resources but refresh only STALE and missing statistics.
.

A simple example

This script is kept as simple as possible.

  • It creates a test user
  • It creates two tables within this user - tablespace USERS
  • It inserts and updates information in the two tables
  • It flushes the monitoring information (how many DMLs got run?) out
  • It gathers stats on only one table to verify that STALE is working as intended
  • It kicks off the automatic stats gathering job
  • It kicks off the schema stats gathering call
  • It compares results before/after in the stats history table 

set timing on
set serverout on
set echo on
set termout on
column table_name Format a5
column owner      Format a6
column stale_stats Format a4
column last_analyzed Format a15
column sample_size format 9999999
drop user test1 cascade;
create user test1 identified by test1;
grant connect, resource, dba to test1;
alter user test1 default tablespace USERS;
create table TEST1.TAB1 as select * from dba_objects where rownum<50001;
exec dbms_stats.gather_table_stats('TEST1','TAB1');
create table TEST1.TAB2 as select * from dba_objects where rownum<50001;
exec dbms_stats.gather_table_stats('TEST1','TAB2');
insert into TEST1.TAB1 select * from dba_objects where rownum<50001;
commit;
insert into TEST1.TAB2 select * from dba_objects where rownum<50001;
commit;
insert into TEST1.TAB2 select * from dba_objects where rownum<50001;
commit;
update TEST1.TAB1 set object_id=object_id+0;
commit;
update TEST1.TAB2 set object_id=object_id+1;
commit;
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
select table_name,owner,stale_stats,to_char(last_analyzed,'DD-MON HH24:MI:SS') LAST_ANALYZED,SAMPLE_SIZE from dba_tab_statistics where table_name in ('TAB1','TAB2');
exec DBMS_STATS.GATHER_TABLE_STATS('TEST1','TAB1');
select table_name,owner,stale_stats,to_char(last_analyzed,'DD-MON HH24:MI:SS') LAST_ANALYZED,SAMPLE_SIZE from dba_tab_statistics where table_name in ('TAB1','TAB2');
exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;
pause Wait a bit - then press return ...
select table_name,owner,stale_stats,to_char(last_analyzed,'DD-MON HH24:MI:SS') LAST_ANALYZED,SAMPLE_SIZE from dba_tab_statistics where table_name in ('TAB1','TAB2');
exec dbms_stats.gather_schema_stats('TEST1');
select table_name,owner,stale_stats,to_char(last_analyzed,'DD-MON HH24:MI:SS') LAST_ANALYZED,SAMPLE_SIZE from dba_tab_statistics where table_name in ('TAB1','TAB2');
prompt End ...

.

The results

exec
DBMS_STATS.
FLUSH_DATABASE_MONITORING_INFO;
TABLE OWNER  STAL LAST_ANALYZED   SAMPLE_SIZE
----- ------ ---- --------------- -----------
TAB1  TEST1  YES  29-FEB 22:37:07       50000
TAB2  TEST1  YES  29-FEB 22:37:07       50000

exec
DBMS_STATS.
GATHER_TABLE_STATS('TEST1','TAB1');
TABLE OWNER  STAL LAST_ANALYZED   SAMPLE_SIZE
----- ------ ---- --------------- -----------
TAB1  TEST1  NO   29-FEB 22:37:12      100000
TAB2  TEST1  YES  29-FEB 22:37:07       50000

exec
DBMS_AUTO_TASK_IMMEDIATE.
GATHER_OPTIMIZER_STATS;

TABLE OWNER  STAL LAST_ANALYZED   SAMPLE_SIZE
----- ------ ---- --------------- -----------
TAB1  TEST1  NO   29-FEB 22:37:12      100000
TAB2  TEST1  NO   29-FEB 22:37:13      150000

exec
dbms_stats.
gather_schema_stats('TEST1');

TABLE OWNER  STAL LAST_ANALYZED   SAMPLE_SIZE
----- ------ ---- --------------- -----------
TAB1  TEST1  NO   29-FEB 22:37:43      100000
TAB2  TEST1  NO   29-FEB 22:37:43      150000

The results can be interpreted this way:

  • The sample size of 50k is based on the first activity during the CTAS
  • Once table TAB1 gets analyzed the sample size is now correct - and the time stamp got updated - statistics on TAB2 are still marked STALE of course as the underlying table has changed by more than 10%
  • The Automatic Statistics Gathering job will refresh only stats for objects where stats are missing or marked STALE - in this example here TAB2. Table TAB1's statistics remain unchanged.
  • When the GATHER_SCHEMA_STATS job gets invoked it will refresh all statistics - regardless if they were STALE or not. 

This is the behavior the customer who raised the question about differences in these two ways to create statistics may have seen. The GATHER_SCHEMA_STATS job took longer and consumed more resources as it will refresh all statistics regardless of the STALE attribute.

And it's hard to figure out why the refresh of statistics created in a previous release may have led to suboptimal performance, especially as we talk about a patch set upgrade - and not a full release upgrade. Thanks to Wissem El Khlifi who twittered the following annotations I forgot to mention:

  • The Automatic Statistics Gathering job prioritizes objects with NO statistics over objects with STALE statistics
  • The Automatic Statistics Gathering job may get interrupted or skip objects leaving them with NO statistics gathered. You can force this by locking statistics - so the Auto job will skip those completely

You'll find more information about the Automatic Statistics Gathering job here:

And another strange finding ...

When I played with this example in 12c I encountered the strange behavior of the GATHER_OPTIMIZER_STATS call taking exactly 10 minutes unti it returns to the command prompt.

First I thought this is a Multitenant only issue. But I realized quickly: this happens in non-CDB databases in Oracle 12c as well. And when searching the bug database I came across the following unpublished bug:

  • Bug 14840737
    DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS RETURNS INCORRECTLY

which got logged in Oct 2012 and describes this exact behavior. I kick off the job - it will update the stats pretty soon after - but still take 10 minutes to return control to the command prompt. It is supposed to be fixed in a future release of Oracle Database ... 

 

--Mike 

Saturday Oct 17, 2015

Extended Support Fee for Oracle 11.2.0.4 waived until May 31, 2017 - Extended Support until Dec 2020

Friday, Oct 16, 2015, Oracle announced that the Extended Support for Oracle Database 11.2.0.4 will be waived until May 31, 2017. After this period of Waived Extendend Support, Extended Support for Oracle Database 11.2.0.4 will be offered until end of December 2020.

This information can be found here:

Something to mention:

  • There's no bug fixing support for Oracle Database 11.2.0.3 or below available anymore
  • If you'll compare the effort to upgrade to Oracle Database 11.2.0.4 to Oracle 12.1.0.2 you'll have to do the exact same amount of work. tests etc - no difference


    .
  • End of Premier Support means that Oracle will not offer fixes for new issues unless an extra-cost offer called Extended Support has been purchased. But you'll still get Sustaining Support until you'll stop working on the release. For a description what terms such as Premier Support, Extended Support and Sustaining Support mean please refer to the Lifetime Support Policy.
    Premier Support for Oracle Database 11.2 ended on Jan-31, 2015
    .
  • For Oracle Database 11.2.0.4 - the Terminal (final) Patch Set for Oracle Database 11.2 - Oracle will waive (no extra cost, no action required) Extended Support to every customer with a valid support contract until May 31, 2017.
    You can extend the Extended Support for Oracle Database 11.2.0.4 until 31-DEC-2020 - but then at the usual extra cost. 
    This information can be found in MOS Note:742060.1.
    .
  • For Oracle Database 11.2.0.3 Oracle has waived the Extended Support, but as Extended Support for Oracle Database 11.2.0.3 ended on 27-AUG-2015 no bug fixes are available (including PSUs, SPUs and BPs) anymore.
    There's no bug fixing support for Oracle Database 11.2.0.3 anymore.
    This information can be found in MOS Note:742060.1.
    .
  • For Oracle Database 11.2.0.2 there's no Extended Support offering available.
    There's no bug fixing support for Oracle Database 11.2.0.2 anymore.
    This information can be found in MOS Note:742060.1.
    ..
  • For Oracle Database 11.2.0.1 there's no Extended Support offering available.
    There's no bug fixing support for Oracle Database 11.2.0.1 anymore.
    This information can be found in MOS Note:742060.1.
    .
--Mike

Monday Jun 29, 2015

Premier Support for Oracle 11.2 has ended months ago

PLEASE FIND AN UPDATED BLOG POST HERE:

https://blogs.oracle.com/UPGRADE/entry/no_extra_fee_for_extended


I have blogged many times about the End of Premier Support for Oracle Database 11.2 and the different stages and regulations for Extended Support for this release. But still I'm getting inquiries almost every day, internally and externally.

Let me first point out that I'm not a rep of Oracle Support. I'm just a Product Manager with some Oracle Support background. So you'll always have to consult our officially available documents and sources as regulations may change after I published this blog post. I'm just trying to summarize what I know at the moment to help you avoid some of the usual misunderstandings.

Premier Support for Oracle 11.2 has already ended

Fact. Premier Support for Oracle 11.2 has ended on Jan 31, 2015. Period. Regardless of the patch level you may be on. No further discussion here please. Just for your notes: this has happened a while ago in case you've missed the date:

PLEASE FIND AN UPDATED BLOG POST HERE:

https://blogs.oracle.com/UPGRADE/entry/no_extra_fee_for_extended


--Mike 

Tuesday Jun 23, 2015

Package Differences between Oracle 11.2.0.4 and 12.1.0.2?

The question sounds pretty trivial:

Which packages did exist in Oracle 11.2.0.4 but don't exist in Oracle 12.1.0.2 anymore?

And going a bit deeper into the topic,

Which procedures and functions calls of SYS packages have been changed since then?

.

A colleague of mine (thanks to Thomas Kempkens from ACS Support for writing up a fancy procedure - all credits go to him) had to dig this out for a customer,

See the result.
These packages don't exist in Oracle 12.1.0.2 anymore but did exist in Oracle 11.2.0.4 (without PSUs):

  • CWM2_OLAP_INSTALLER
  • DBMS_AMD
  • DBMS_APPCTX
  • DBMS_DBLINK
  • DBMS_DM_IMP_INTERNAL
  • DBMS_DM_UTIL_INTERNAL
  • DBMS_DUMA_INTERNAL
  • DBMS_JDM_INTERNAL
  • DBMS_RULE_COMPATIBLE_90
  • DBMS_SQL2
  • DBMS_XDSUTL
  • DBMS_XS_SECCLASS_INT
  • DBMS_XS_SECCLASS_INT_FFI
  • EXF$DBMS_EXPFIL_SYSPACK
  • HTMLDB_SYSTEM
  • ODM_ABN_MODEL
  • SQLJUTL2

17 packages don't exist anymore.

And this is the list of
Procedures and Functions Calls inside of Oracle SYS packages which have changed in Oracle 12.1.0.2:

  • PACKAGE_NAME               SUBTYPE    SUBTYPE_NAME
  • -------------------------- ---------- ------------------------------------------------
  • CWM2_OLAP_INSTALLER        PROCEDURE  VALIDATE_CWM2_INSTALL
  • DBMS_AMD                   PROCEDURE  MOVE_OLAP_CATALOG
  • DBMS_APPCTX                PROCEDURE  CLEAR_CONTEXT
  • DBMS_APPCTX                PROCEDURE  SET_CONTEXT
  • DBMS_AQADM_SYS             PROCEDURE  GET_CORR_MSG
  • DBMS_AQADM_SYS             PROCEDURE  RESET_CORR_MSG
  • DBMS_AQADM_SYSCALLS        PROCEDURE  KWQA_3GL_UPDPERSTATS
  • DBMS_DATA_MINING           FUNCTION   GET_MODEL_DETAILS_ABN RETURNS DM_ABN_DETAILS
  • DBMS_DATA_MINING_INTERNAL  PROCEDURE  CREATE_TEMP_META_TABLE
  • DBMS_DBLINK                PROCEDURE  UPGRADE
  • DBMS_DDL_INTERNAL          FUNCTION   GEN_NEW_OBJECT_ID RETURNS BINARY_INTEGER
  • DBMS_DM_EXP_INTERNAL       FUNCTION   GET_TRACE RETURNS BOOLEAN
  • DBMS_DM_EXP_INTERNAL       PROCEDURE  DELETE_FROM_TEMP
  • DBMS_DM_EXP_INTERNAL       PROCEDURE  DO_TEMP_TABLE
  • DBMS_DM_EXP_INTERNAL       PROCEDURE  DUMP_STATUS
  • DBMS_DM_EXP_INTERNAL       PROCEDURE  GET_LIST_MODELS
  • DBMS_DM_EXP_INTERNAL       PROCEDURE  INSERT_LIST_MODELS
  • DBMS_DM_EXP_INTERNAL       PROCEDURE  SET_TRACE
  • DBMS_DM_IMP_INTERNAL       PROCEDURE  REGISTER_TEMP_TABLE
  • DBMS_DM_IMP_INTERNAL       PROCEDURE  UNREGIST_TEMP_TABLE
  • DBMS_DM_MODEL_EXP          FUNCTION   INSTANCE_INFO_EXP RETURNS VARCHAR2
  • DBMS_DM_MODEL_EXP          FUNCTION   SCHEMA_INFO_EXP RETURNS VARCHAR2
  • DBMS_DM_MODEL_EXP          PROCEDURE  SCHEMA_CALLOUT
  • DBMS_DM_UTIL               PROCEDURE  DISPLAY_MSG
  • DBMS_DM_UTIL               PROCEDURE  DUMP_MSG
  • DBMS_DM_UTIL_INTERNAL      PROCEDURE  DUMP_DM_TRACE
  • DBMS_DUMA_INTERNAL         FUNCTION   GET_PROPERTY RETURNS NUMBER
  • DBMS_DUMA_INTERNAL         FUNCTION   READ_LONG_TO_BLOB RETURNS BLOB
  • DBMS_DUMA_INTERNAL         PROCEDURE  CONVERT_LONG
  • DBMS_DUMA_INTERNAL         PROCEDURE  SCAN_TABLE
  • DBMS_DUMA_INTERNAL         PROCEDURE  WRITE_BLOB_TO_LONG
  • DBMS_INTERNAL_LOGSTDBY     PROCEDURE  EDS_EVOLVE_TABLE_1_FINISH
  • DBMS_INTERNAL_LOGSTDBY     PROCEDURE  END_INSTANTIATION
  • DBMS_INTERNAL_LOGSTDBY     PROCEDURE  INSTANTIATE_FEATURE
  • DBMS_INTERNAL_LOGSTDBY     PROCEDURE  LOCK_TABLES
  • DBMS_INTERNAL_LOGSTDBY     PROCEDURE  PREPARE_INSTANTIATION
  • DBMS_INTERNAL_LOGSTDBY     PROCEDURE  REGISTER_SCHEMA
  • DBMS_INTERNAL_LOGSTDBY     PROCEDURE  UNSUPPORTED_DML
  • DBMS_JAVA                  FUNCTION   DEPLOY_INVOKE RETURNS VARCHAR2
  • DBMS_JAVA                  FUNCTION   DROP_JAR RETURNS NUMBER
  • DBMS_JAVA                  FUNCTION   FINISH_LOADING_JAR RETURNS NUMBER
  • DBMS_JAVA                  FUNCTION   GETSOURCECHUNK RETURNS VARCHAR2
  • DBMS_JAVA                  FUNCTION   HANDLEMD5 RETURNS RAW
  • DBMS_JAVA                  FUNCTION   INITGETSOURCECHUNKS RETURNS NUMBER
  • DBMS_JAVA                  FUNCTION   JAR_STATUS RETURNS NUMBER
  • DBMS_JAVA                  FUNCTION   RJBC_INIT RETURNS VARCHAR2
  • DBMS_JAVA                  FUNCTION   RJBC_NORMALIZE RETURNS NUMBER
  • DBMS_JAVA                  FUNCTION   RJBC_REQUEST RETURNS NUMBER
  • DBMS_JAVA                  FUNCTION   RJBC_RESPOND RETURNS NUMBER
  • DBMS_JAVA                  FUNCTION   START_LOADING_JAR RETURNS NUMBER
  • DBMS_JAVA                  PROCEDURE  DEPLOY_COPY
  • DBMS_JAVA                  PROCEDURE  DEPLOY_OPEN
  • DBMS_JAVA                  PROCEDURE  RJBC_DONE
  • DBMS_JAVA                  PROCEDURE  RJBC_OUTPUT
  • DBMS_JDM_INTERNAL          FUNCTION   GET_JDM_ATTR_NAMES RETURNS JDM_ATTR_NAMES
  • DBMS_JDM_INTERNAL          FUNCTION   GET_JDM_NUM_VALS RETURNS JDM_NUM_VALS
  • DBMS_JDM_INTERNAL          FUNCTION   GET_JDM_STR_VALS RETURNS JDM_STR_VALS
  • DBMS_JDM_INTERNAL          FUNCTION   TO_CHAR_VARRAY RETURNS VARCHAR2
  • DBMS_JDM_INTERNAL          FUNCTION   UNIQUE_OBJECT_NAME RETURNS VARCHAR2
  • DBMS_JDM_INTERNAL          PROCEDURE  APPLY_TASK
  • DBMS_JDM_INTERNAL          PROCEDURE  BUILD_TASK
  • DBMS_JDM_INTERNAL          PROCEDURE  EXPLAIN_TASK
  • DBMS_JDM_INTERNAL          PROCEDURE  EXPORT_TASK
  • DBMS_JDM_INTERNAL          PROCEDURE  IMPORT_TASK
  • DBMS_JDM_INTERNAL          PROCEDURE  PREDICT_TASK
  • DBMS_JDM_INTERNAL          PROCEDURE  PROFILE_TASK
  • DBMS_JDM_INTERNAL          PROCEDURE  SQL_APPLY_TASK
  • DBMS_JDM_INTERNAL          PROCEDURE  TEST_TASK
  • DBMS_JDM_INTERNAL          PROCEDURE  XFORM_SEQ_TASK
  • DBMS_JDM_INTERNAL          PROCEDURE  XFORM_TASK
  • DBMS_LOGMNR_INTERNAL       PROCEDURE  ADDXTENDEDPKLOGGROUP
  • DBMS_LOGMNR_INTERNAL       PROCEDURE  DROPXTENDEDPKLOGGROUP
  • DBMS_LOGMNR_INTERNAL       PROCEDURE  KRVGDRM2
  • DBMS_LOGMNR_INTERNAL       PROCEDURE  LOGMNR_KRVILD
  • DBMS_LOGMNR_INTERNAL       PROCEDURE  LOGMNR_KRVIULD
  • DBMS_LOGMNR_INTERNAL       PROCEDURE  SET_PARAMETER
  • DBMS_LOGMNR_INTERNAL       PROCEDURE  UNSET_PARAMETER
  • DBMS_LOGSTDBY              FUNCTION   VALIDATE_AUTH RETURNS BOOLEAN
  • DBMS_LOGSTDBY              PROCEDURE  EDS_EVOLVE_TABLE
  • DBMS_METADATA_INT          PROCEDURE  CLEAR_CACHE
  • DBMS_METADATA_INT          PROCEDURE  CLEAR_XSL_CACHE
  • DBMS_OBJECTS_UTILS         FUNCTION   SPLIT_SOURCE RETURNS NUMBER
  • DBMS_OBJECTS_UTILS         PROCEDURE  RECOMPILE_TYPES
  • DBMS_OBJECTS_UTILS         PROCEDURE  UPDATE_TYPES
  • DBMS_REPORT                FUNCTION   STORE_FILE RETURNS NUMBER
  • DBMS_REPORT                PROCEDURE  CREATE_SHARED_DIRECTORY
  • DBMS_REPORT                PROCEDURE  DROP_SHARED_DIRECTORY
  • DBMS_RMIN                  PROCEDURE  INSTALL
  • DBMS_RMIN                  PROCEDURE  RM$_CALIBRATE_IO
  • DBMS_RMIN                  PROCEDURE  RM$_CLRPAREA
  • DBMS_RMIN                  PROCEDURE  RM$_CONSUMER_GROUP_MAPPING_PRI
  • DBMS_RMIN                  PROCEDURE  RM$_CRTPAREA
  • DBMS_RMIN                  PROCEDURE  RM$_CUP_CATEGORY
  • DBMS_RMIN                  PROCEDURE  RM$_CUP_GROUP
  • DBMS_RMIN                  PROCEDURE  RM$_CUP_PLAN
  • DBMS_RMIN                  PROCEDURE  RM$_CUP_PLAN_DIRECTIVE
  • DBMS_RMIN                  PROCEDURE  RM$_CUP_STORAGE_POOL_MAPPING
  • DBMS_RMIN                  PROCEDURE  RM$_DRP_CATEGORY
  • DBMS_RMIN                  PROCEDURE  RM$_DRP_GROUP
  • DBMS_RMIN                  PROCEDURE  RM$_DRP_PLAN
  • DBMS_RMIN                  PROCEDURE  RM$_DRP_PLAN_CSD
  • DBMS_RMIN                  PROCEDURE  RM$_DRP_PLAN_DIRECTIVE
  • DBMS_RMIN                  PROCEDURE  RM$_SET_CAPABILITY
  • DBMS_RMIN                  PROCEDURE  RM$_SET_CONSUMER_GROUP_MAPPING
  • DBMS_RMIN                  PROCEDURE  RM$_SET_INSTANCE_CAPABILITY
  • DBMS_RMIN                  PROCEDURE  RM$_SUBPAREA
  • DBMS_RMIN                  PROCEDURE  RM$_SWTCH_SESSION_GROUP
  • DBMS_RMIN                  PROCEDURE  RM$_SWTCH_USR_GROUP
  • DBMS_RMIN                  PROCEDURE  RM$_VLDPAREA
  • DBMS_RMIN                  PROCEDURE  UNINSTALL
  • DBMS_RULE_COMPATIBLE_90    FUNCTION   INCOMPATIBLE_RULES_ENGINE_OBJ RETURNS NUMBER
  • DBMS_RULE_COMPATIBLE_90    PROCEDURE  DOWNGRADE_RULE_OBJECTS
  • DBMS_RULE_COMPATIBLE_90    PROCEDURE  INCOMPATIBLE_EVCTXS
  • DBMS_RULE_COMPATIBLE_90    PROCEDURE  INCOMPATIBLE_RULES
  • DBMS_RULE_COMPATIBLE_90    PROCEDURE  INCOMPATIBLE_RULESETS
  • DBMS_RULE_COMPATIBLE_90    PROCEDURE  UPGRADE_RULE_OBJECTS
  • DBMS_SPM_INTERNAL          FUNCTION   FETCH_PLAN_INFO RETURNS REF CURSOR
  • DBMS_SPM_INTERNAL          PROCEDURE  GET_SPM_HINTSET
  • DBMS_SQL2                  PROCEDURE  EXPAND_SQL_TEXT
  • DBMS_SQL2                  PROCEDURE  PARSE
  • DBMS_STATS                 FUNCTION   TO_CASCADE_TYPE RETURNS BOOLEAN
  • DBMS_STATS                 FUNCTION   TO_DEGREE_TYPE RETURNS NUMBER
  • DBMS_STATS                 FUNCTION   TO_ESTIMATE_PERCENT_TYPE RETURNS NUMBER
  • DBMS_STATS                 FUNCTION   TO_PUBLISH_TYPE RETURNS BOOLEAN
  • DBMS_STATS_INTERNAL        FUNCTION   COMPOSE_HASHVAL_CLOB RETURNS CLOBTAB
  • DBMS_STATS_INTERNAL        FUNCTION   GET_CHTAB RETURNS CHTAB
  • DBMS_STATS_INTERNAL        FUNCTION   GET_SYNOPSIS_BLKCNT RETURNS NUMBER(38)
  • DBMS_STATS_INTERNAL        PROCEDURE  DELETE_TABLE_SYNOPSIS
  • DBMS_STATS_INTERNAL        PROCEDURE  EXPORT_SYNOPSIS_HEAD
  • DBMS_SWRF_REPORT_INTERNAL  FUNCTION   TO_1000S RETURNS VARCHAR2
  • DBMS_SWRF_REPORT_INTERNAL  FUNCTION   TO_1024S RETURNS VARCHAR2
  • DBMS_WORKLOAD_REPLAY       FUNCTION   SYNCPOINT_WAIT_TO_POST RETURNS NUMBER
  • DBMS_WRR_INTERNAL          FUNCTION   DATE_TO_EPOCH_SECS RETURNS BINARY_INTEGER
  • DBMS_WRR_INTERNAL          FUNCTION   IS_CONSOLIDATED_DIR RETURNS BOOLEAN
  • DBMS_XDS                   PROCEDURE  REFRESH_DSD
  • DBMS_XDSUTL                PROCEDURE  INVALIDATE_DSD_CACHE
  • DBMS_XDSUTL                PROCEDURE  INVALIDATE_DSD_CACHE_BY_ACLID
  • DBMS_XS_SECCLASS_INT       PROCEDURE  DELETE_SEC_CLASS
  • DBMS_XS_SECCLASS_INT       PROCEDURE  GET_LEAF_PRIVILEGES
  • DBMS_XS_SECCLASS_INT_FFI   PROCEDURE  DELETE_SEC_CLASS
  • DBMS_XS_SECCLASS_INT_FFI   PROCEDURE  GET_LEAF_PRIVILEGES
  • DBMS_XS_SESSIONS           FUNCTION   CHECK_ROLE RETURNS BOOLEAN
  • DBMS_XS_SESSIONS           FUNCTION   CHECK_SESSION RETURNS BOOLEAN
  • DBMS_XS_SESSIONS           FUNCTION   GET_SESSIONID_FROM_COOKIE RETURNS RAW
  • DBMS_XS_SESSIONS           PROCEDURE  ABORTATTACH_SESSION
  • DBMS_XS_SESSIONS           PROCEDURE  GET_SESSION_COOKIE
  • DBMS_XS_SESSIONS           PROCEDURE  SET_NAMESPACE_ACL
  • DBMS_XS_SESSIONS           PROCEDURE  SET_NAMESPACE_HANDLER
  • DBMS_XS_SESSIONS_FFI       PROCEDURE  ABORTATTACH_SESSION
  • DBMS_XS_SESSIONS_FFI       PROCEDURE  CHECK_ROLE
  • DBMS_XS_SESSIONS_FFI       PROCEDURE  CHECK_SESSION
  • DBMS_XS_SESSIONS_FFI       PROCEDURE  GET_SESSION_COOKIE
  • DBMS_XS_SESSIONS_FFI       PROCEDURE  SET_NAMESPACE_ACL
  • DBMS_XS_SESSIONS_FFI       PROCEDURE  SET_NAMESPACE_HANDLER
  • DMP_SEC                    FUNCTION   GET_MODEL_DETAILS_ABN RETURNS VARCHAR2(32767)
  • DMP_SYS                    PROCEDURE  DUMP_MODEL_REC
  • DMP_SYS                    PROCEDURE  DUMP_TRACE
  • DMP_SYS                    PROCEDURE  DUMP_VARCHAR2A
  • DMP_SYS                    PROCEDURE  DUMP_VARCHAR2S
  • DM_QGEN                    PROCEDURE  LS_DUMP
  • EXF$DBMS_EXPFIL_SYSPACK    FUNCTION   GET_CMTSCN_FOR_XID RETURNS NUMBER
  • EXF$DBMS_EXPFIL_SYSPACK    FUNCTION   GET_EXPRID_4M_TEXTID RETURNS VARCHAR2
  • EXF$DBMS_EXPFIL_SYSPACK    FUNCTION   GET_SPATIAL_MDATAREP RETURNS VARCHAR2
  • EXF$DBMS_EXPFIL_SYSPACK    FUNCTION   GET_TAB_NAME RETURNS VARCHAR2
  • EXF$DBMS_EXPFIL_SYSPACK    FUNCTION   GET_TAB_OWNER RETURNS VARCHAR2
  • EXF$DBMS_EXPFIL_SYSPACK    FUNCTION   GET_TYPELV_STATUS RETURNS VARCHAR2
  • EXF$DBMS_EXPFIL_SYSPACK    FUNCTION   GET_TYPE_OID RETURNS RAW
  • EXF$DBMS_EXPFIL_SYSPACK    FUNCTION   PROC_IS_DEFINERS RETURNS NUMBER
  • EXF$DBMS_EXPFIL_SYSPACK    FUNCTION   TAB_COLUMN_EXISTS RETURNS NUMBER
  • EXF$DBMS_EXPFIL_SYSPACK    PROCEDURE  CLR_EXP_DEPACTION
  • EXF$DBMS_EXPFIL_SYSPACK    PROCEDURE  CLR_OBJECT_NOEXP
  • EXF$DBMS_EXPFIL_SYSPACK    PROCEDURE  CLR_USER_NOEXP
  • EXF$DBMS_EXPFIL_SYSPACK    PROCEDURE  DOWNGRADE_COMPILED_SPARSE
  • EXF$DBMS_EXPFIL_SYSPACK    PROCEDURE  PARSE_PROC_AS_OWNER
  • EXF$DBMS_EXPFIL_SYSPACK    PROCEDURE  PARSE_PROC_AS_OWNER2
  • EXF$DBMS_EXPFIL_SYSPACK    PROCEDURE  SET_EXP_DEPACTION
  • EXF$DBMS_EXPFIL_SYSPACK    PROCEDURE  SET_OBJECT_NOEXP
  • HTMLDB_SYSTEM              FUNCTION   VERIFY_USER RETURNS BINARY_INTEGER
  • INITJVMAUX                 FUNCTION   DO_JIS_DROP RETURNS BOOLEAN
  • KUPD$DATA                  FUNCTION   CONVENTIONAL_LOAD_CALLOUT RETURNS BINARY_INTEGER
  • KUPF$FILE_INT              FUNCTION   GET_ENCPWD RETURNS RAW
  • KUPF$FILE_INT              FUNCTION   GET_OBFPWD RETURNS RAW
  • KUPF$FILE_INT              PROCEDURE  ENCRYPT_PWD
  • KUPP$PROC                  PROCEDURE  INTALGCONV
  • LOGMNR_DICT_CACHE          PROCEDURE  PURGE_SCN
  • LOGSTDBY_INTERNAL          PROCEDURE  EDS_EVOLVE_TABLE_1_I
  • ODM_ABN_MODEL              PROCEDURE  BUILD
  • ODM_ABN_MODEL              PROCEDURE  ESTIMATE_FEATUREBLD_TIME
  • ODM_ABN_MODEL              PROCEDURE  GET_MODEL_STATE
  • ODM_CLUSTERING_UTIL        FUNCTION   IS_DEBUG_ENABLED RETURNS BOOLEAN
  • ODM_MODEL_UTIL             FUNCTION   UNIQUE_SEQUENCE_NAME RETURNS VARCHAR2(30)
  • ODM_MODEL_UTIL             PROCEDURE  CREATE_SEQUENCE
  • ODM_MODEL_UTIL             PROCEDURE  DEBUG_DUMP
  • ODM_MODEL_UTIL             PROCEDURE  DROP_SEQUENCE
  • ODM_MODEL_UTIL             PROCEDURE  GET_TIME_MILLISECONDS
  • ODM_UTIL                   FUNCTION   COMPUTE_LOG_COMBINATIONS RETURNS NUMBER
  • PRVT_RTADDM                FUNCTION   EXTRACTOSSTAT RETURNS NUMBER
  • PRVT_RTADDM                FUNCTION   GETDATA RETURNS CLOB_T
  • PRVT_RTADDM                FUNCTION   GETVERSION RETURNS VARCHAR2
  • SQLJUTL2                   FUNCTION   EVALUATE RETURNS LONG RAW
  • SQLJUTL2                   FUNCTION   INVOKE RETURNS LONG RAW
  • SQLJUTL2                   FUNCTION   REFLECT RETURNS LONG
  • SQLJUTL2                   FUNCTION   REFLECT2 RETURNS CLOB
  • STANDARD                   FUNCTION   ASCII RETURNS BINARY_INTEGER

204 procedure and function calls have been changed.

-- Mike

PS: The above listening is without PSUs applied - I learned that the list my vary slightly based on the PSUs or BPs you have applied. Credits to my colleague Katsumi-san, who pointed my to the DBMS_OPTIM_BUNDLE package which seems to be introduced with the October 2014 PSU. Philippe Fierens has blogged about the package: http://pfierens.blogspot.co.uk/2014_10_01_archive.html

Friday Nov 07, 2014

Sleeping Beauties - Upgrade to 11.2.0.4 can be slow

A customer from the US did contact me past week via LinkedIn and raised a question:

"Is it expected that my patch set upgrade from Oracle 11.2.0.3 to Oracle 11.2.0.4 takes over 3 hours?"

Of course, no - this is not expected

This is the upgrade stats gathered post upgrade with utlu112s.sql:

SQL> @?/rdbms/admin/utlu112s.sql ; .
Oracle Database 11.2 Post-Upgrade Status Tool 10-31-2014 10:05:29
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
Oracle Server  
. VALID 11.2.0.4.0 02:46:19
JServer JAVA Virtual Machine
. VALID 11.2.0.4.0 00:08:34
[..]
Final Actions
. 00:00:00
Total Upgrade Time: 03:06:47

No, this is not really expected. So we tried to nail down the root cause finding out these statements in the upgrade script c1102000.sql are causing the trouble:

194 -- wri$_optstat_histhead_history2.
195 execute immediate
196 q'#create unique index i_wri$_optstat_hh_obj_icol_st on
197 wri$_optstat_histhead_history (obj#, intcol#, savtime, colname)
198 tablespace sysaux #';
199
200 execute immediate
201 q'#create index i_wri$_optstat_hh_st on
202 wri$_optstat_histhead_history (savtime)
203 tablespace sysaux #';
204 end;
205 /

It's index rebuilds on histogram tables. And the customer has a large amount of stats data in his database as the default stats retention is 31 days.

Obviously the index rebuild is not done very efficiently (not done in parallel, no nologging clause). Those things can happen and sometimes this may not cause any issues. But in this case it lead to over 2 hours for just those index rebuilds.  

Luckily my colleague Cindy is an excellent resource for such things - after asking our team I've got the reply that this is tagged with a bug number and code fix already got checked in (under review right now):

bug19855835:
Upgrade from 11.2.0.2 to 11.2.0.4 is slow 

-Mike

PS: Credits go to Tan for bringing this to my attention - and sorry for the inconvenience! 

Thursday Apr 17, 2014

Avoid Poor Performance and Wrong Results in 11.2.0.4

One of the best notes in MOS got refreshed last night for Oracle Database 11.2.0.4. It has not only information about performance bugs but also about the worst and meanest category of issues, wrong query result bugs. Those are the ones I call the "sleeping beauties" as you as a DBA usually won't recognize them. It is recognized at the application level when the result of a report is incorrect or instead of 4 rows just 3 rows get delivered in the output. Therefore you should be highly aware of those and add this note to your bookmarks.

MOS Note 1645862.1Things to Consider Before Upgrading to 11.2.0.4 to Avoid Poor Performance or Wrong Results

And in case you don't want to move to Oracle 11.2.0.4 but searching for information and known issues in Oracle 11.2.0.3 or Oracle 11.2.0.2 then please find the corresponding documents:

  • MOS Note: 1320966.1 Things to Consider Before Upgrading to 11.2.0.2.x to Avoid Poor Performance or Wrong Results
  • MOS Note: 1392633.1 Things to Consider Before Upgrading to 11.2.0.3 to Avoid Poor Performance or Wrong Results
-Mike
About

Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle

Based in Germany. Interlink between customers/partners and the Upgrade Development. Running workshops between Arctic and Antartica. Assisting customers in their reference projects onsite and remotely. Connect via:

- -

Search

Archives
« May 2016
SunMonTueWedThuFriSat
1
2
5
7
8
9
10
12
14
15
16
18
20
21
22
23
24
26
27
28
29
30
31
    
       
Today
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
Workshop Map
x Oracle related Tech Blogs
This week on my Rega & Pono
Upgrade Reference Papers