Pavan DBA's Blog

Hello Friends,

after ages, I got some time to post an article in my blog related to performance issue i faced recently.

To give a background, we have a database running on 10.1.0.4 version and in which there is a create table statement executing in one of data load jobs.

Statement is as below (Note – object names were changed because of security reasons)

CREATE TABLE XYZ.TABLE1 AS SELECT  /*+diriving_site(source) ORDERED */     SOURCE.* FROM     (SELECT NVL(MAX(UPDATED_ON),TO_DATE(‘1900-01-01′,’YYYY-DD-MM’)) MTIME FROM XYZ.TABLE2) T     , ABC.VIEW1@BRING_DATA SOURCE WHERE     source.UPDATED_ON >= t.MTIME

The above statement is trying to create a table by joining a remote view (VIEW1@BRING_DATA) with a table TABLE2 by checking the last updated date (updated_on)

This statement used to run for 5 min at max in 10.1.0.4 version. Recently we have migrated this database to 11.2.0.3. After that, the same statement is taking more than 6 hours of time.

For further analysis, first i checked the last_analyzed_date of tables and indexes involved and everything is perfect. Then I started looking into explain plan of the statement and compared that with the plan on 10.1.0.4 version.

Below is the explain plan on 10.1.0.4

Below is the explain plan on 11.2.0.3

So, from the above explain plans, you can observe that in 10g, whatever the data required from the remote view (ABC.VIEW1@BRING_DATA) is getting filtered in the remote site itself whereas on 11g, Oracle is pulling entire data from remote view and then it is filtering on the local database. This is the main reason for the time delay in 11g.

The primary reason for this is change of optimizer behaviour in 11.2.0.3 version. To fix this, we have modified the application code into 3 sections

1. creating an empty table 2. declaring a pl/sql block as below

DECLARE     V_DATE DATE; BEGIN     SELECT NVL (MAX (UPDATED_ON),TO_DATE (‘1900-01-01’, ‘YYYY-DD-MM’)) INTO V_DATE FROM XYZ.TABLE2;

3. inserting the data into XYZ.TABLE1

After the code change, the table is getting created in just 20sec including data which is a wonderful improvement in response time.

Hope this will be a good learning for us on tuning issues… HAPPY LEARNING 🙂