Pavan DBA's Blog

The DBA Knowledge Store

Posts Tagged ‘performance tuning’

TechMaster – New Oracle Performance Tuning batch from 6th Nov

Posted by Pavan DBA on October 31, 2017


TechMaster announcing Oracle Performance Tuning online training by Kumar

Mode: Online training
Duration: 20 classes
Frequency: 5 days a week
Timings: 6 – 7 AM IST
Batch starting date: 6th Nov 2017 @ 6 AM IST

Demo date & time: 4th Nov 2017 at 6 AM IST
Please attend demo and first two classes for free using this link – https://global.gotomeeting.com/join/871212917

For more details, contact:-
Email to: admin@thetechmaster.in
Call or WhatsApp to: +919676516545
http://www.thetechmaster.in

Advertisements

Posted in Training | Tagged: , , , , , , | Leave a Comment »

How to resolve remote data fetching when taking more time?

Posted by Pavan DBA on December 3, 2013


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

explain plan_WMSB10g - Copy

Below is the explain plan on 11.2.0.3

explain plan_IB11g - Copy

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 🙂

Posted in Perf Tuning | Tagged: , , , , , , , , , | 4 Comments »

 
%d bloggers like this: