Forum Stats

  • 3,752,163 Users
  • 2,250,465 Discussions
  • 7,867,742 Comments

Discussions

Poor performance after enabling db links

24

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Feb 23, 2018 4:58PM

    1) This is the execution plan from last night's ETL (using the db link mode)

    1. SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'1hnnzbwqq1wjh')); 
    2. PLAN_TABLE_OUTPUT 
    3. -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
    4. SQL_ID  1hnnzbwqq1wjh, child number 0 
    5. ------------------------------------- 
    6. SELECT "C1_INTEGRATION_ID","C2_SRC_EFF_FROM_DT" FROM 
    7. "APPS"."C$_727935750_4_0" "C$_727935750_4_0" 
    8. Plan hash value: 562628296 

    Are you sure? That's just a select statement, I thought you were using an insert ..select statement? Neither the statement nor the execution plan show any evidence of a DB link being used.

    I will note however that that plan looks bad! The first bit is a nested loops full tablescan, that is never a good idea. Looking back at your query and view, you're hinting the nested loops here. The interesting thing about the join is that it's against some non-merged views. I expect when your query runs fast, these views are mergable and the nested loops can use an index (or is invalid so use a hash join/something else scalable). I have seen things like distributed queries preventing views to be merged, however like I mentioned earlier, I can't quite see what's distributed here (there are other reasons why simple views can be prevented from being merged, the 10053 trace file would tell you but I don't think it's worth going into that before we have established the facts).

    I'm not familiar with whatever application you're running so I'm not sure what's going on here, maybe you can shed some light. Your query references:

    APPS.FND_FLEX_VALUES   B, APPS.FND_FLEX_VALUE_NORM_HIERARCHY   H 

    But your plan goes to a view named FND_FLEX_VALUES# which is on top of another view named FND_FLEX_VALUES# which is on top of a table named FND_FLEX_VALUES.

    Does that seem right to you?

    The same behaviour looks to be effecting the second part of the query, causing the anti join to do nested loops (via the filter operator) using a tablescan and bulky joins.

    The second plan you shared has two major differences and an explanation,

    -it's parallel enabled

    -It uses hash joins and anti hash joins where you previously had nested loops and filter operations

    -The views were merged so your hints were invalid

    So, I think we have the opposite of what I first proposed, you currently have some hints that are not valid because they don't reference the real objects after the query is transformed, however when you do this over the DB link (although I'm still not confident the first query was over the DB link), the views are no longer mergeable so your hints actually refer to the objects used (the views) - the hints are bad ones though so you get rewarded with a bad plan.

    -Just before I hit "Add Reply", it's dawned on me that the first query may be the execution plan taken from the source DB but the query was fired from an insert statement on the target DB and become fully remote.

    I think we can get a similar performance as without the DB link by removing the hints. The views will probably not merge still though so you'll still have to suffer from predicates not being able to be applied in the most efficient manner. I'll have a play and see if I can come up with a decent solution that allows you to really benefit from the DB link (the pl/sql solution I offered earlier might certainly be okay but not fantastic).

    Charles M
  • Charles M
    Charles M Member Posts: 806 Bronze Badge
    edited Feb 23, 2018 5:18PM

    Hi jgarry,

    Thanks for the information! I'll take some time to review over the weekend.

    One of the executions is using a profile, perhaps you need to work on creating one for the new situation, and work out if you can get better statistics

    I reviewed the implementation documentation, and it looks as though a SQL profile was added to improve performance for the original SQL. It looks like it was created about a year ago ...

    Are you gathering statistics in any special way?

    These are all EBS objects/tables. I'll need to take a look at how statistics are being gathered - I believe it is done, via EBS, periodically.

    Regards,

    Charles

  • Charles M
    Charles M Member Posts: 806 Bronze Badge
    edited Feb 28, 2018 2:44PM

    Thanks Andrew. Just a quick update until I can fully review the rest of the reply ...

    Are you sure? That's just a select statement, I thought you were using an insert ..select statement? Neither the statement nor the execution plan show any evidence of a DB link being used.

    That is true. I only included the select part of the operation. Reason being, is because it looked like that was were all of the time was being spent. I recall seeing some "db link wait" messages when I first started looking at this. I'll see if I can dig them up ...

    As far as other evidence, I posted the step/code when it creates that view. This only happens when it is using the db link mode. So, we can see that it is using the view that it created. The JDBC method hould have something like this:

    create table BI_DW.C$_702522750_4_0( C1_INTEGRATION_ID VARCHAR2(4000) NULL, C2_SRC_EFF_FROM_DT DATE NULL)NOLOGGING

    It uses the staging tables instead ...

    I know you've posted much more. I'll reply when I have read it more thoroughly.

    Regards,

    Charles

  • Unknown
    edited Feb 23, 2018 8:06PM
    That is true. I only included the select part of the operation. Reason being, is because it looked like that was were all of the time was being spent. I recall seeing some "db link wait" messages when I first started looking at this.

    Ok - but what I think Andrew is pointing out is that a query is ONLY executed on ONE MACHINE.

    If you are doing an INSERT/MERGE and a DB link is invovled the query has to be executed on the machine the table in on. In your case that is the TARGET machine.

    But when you use JDBC method you are controlling things from the SOURCE - moving data to the ODI agent and then the agent moves it to the target.

    Charles M
  • Charles M
    Charles M Member Posts: 806 Bronze Badge
    edited Feb 26, 2018 4:46PM

    Hi jgarry,

    I did find that statistics are being run weekly, via EBS concurrent program, with the following parameters: ALL, 10, , NOBACKUP, , LASTRUN, GATHER, , Y. (Gather Statistics in R12 (and 11i) – Expert Oracle )

    Also, I ran the SQL Tuning Advisor in OEM on one of the SQL statements (SELECT "C1_INTEGRATION_ID","C2_SRC_EFF_FROM_DT" FROM "APPS"."C$_543907500_4_0" "C$_543907500_4_0"), in a development instance. The recommendation was to use a SQL Profile ... and would give a 99.99% benefit. I did implement that profile, but it does not look like the it is being used. Any thoughts on why it isn't using the profile?

    Regards,

    Charles

  • jgarry
    jgarry Member Posts: 13,842
    edited Feb 26, 2018 5:07PM

    Given the usual caveats about things changing over time, see Kerry Osborne's Oracle Blog » Blog Archive » Why Isn’t Oracle Using My Outline / Profile / Baseline? – Kerry Osborne’…

    Especially comment #2 for your case, and of course all the other comments are informative.

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Feb 26, 2018 7:48PM
    Charles M wrote:Thanks Andrew. Just a quick update until I can fully review the rest of the reply ...Are you sure? That's just a select statement, I thought you were using an insert ..select statement? Neither the statement nor the execution plan show any evidence of a DB link being used.That is true. I only included the select part of the operation. Reason being, is because it looked like that was were all of the time was being spent. I recall seeing some "db link wait" messages when I first started looking at this. I'll see if I can dig them up ...As far as other evidence, I posted the step/code when it creates that view. This only happens when it is using the db link mode. So, we can see that it is using the view that it created. The JDBC method hould have something like this:
    1. createtableSYFBI_DW.C$_702522750_4_0
    2. (
    3. C1_INTEGRATION_IDVARCHAR2(4000)NULL,
    4. C2_SRC_EFF_FROM_DTDATENULL
    5. )
    6. NOLOGGING
    create table SYFBI_DW.C$_702522750_4_0 (  C1_INTEGRATION_ID VARCHAR2(4000) NULL,  C2_SRC_EFF_FROM_DT DATE NULL ) NOLOGGING
    It uses the staging tables instead ...I know you've posted much more. I'll reply when I have read it more thoroughly.Regards,Charles

    Did you manage to dig out the real executions?

    Have you taken a look at those non-merged views and why they might be? I had a play on my instance with a loop back DB link and there's nothing obvious about using a distributed query that would force some views to be non-mergeable - especially not simple ones as is in your case. Have you tried removing those hints that are doing more bad than good?

  • Charles M
    Charles M Member Posts: 806 Bronze Badge
    edited Feb 27, 2018 1:06PM

    Hi Andrew,

    I am going to remove the hints today. The ETL process will run again overnight. Once it finishes, I'll get a look at the execution plan and post it.

    I still have to review some of the other postings (yours and others; as I do not fully understand all of it and/or need to research further).

    Regards,

    Charles

    AndrewSayer
  • jgarry
    jgarry Member Posts: 13,842
    edited Feb 27, 2018 1:16PM

    It's not magic, but it can be involved enough to seem so.  Try googling for: sqlmaria optimizer

    Other informative things are written by Jonathan Lewis, and of course the docs, among others.

    Charles M
  • Charles M
    Charles M Member Posts: 806 Bronze Badge
    edited Feb 27, 2018 2:17PM

    Thanks jgarry. I just briefly looked at the SQLMaria page. Good site. I read: https://sqlmaria.com/2017/11/21/does-the-explain-plan-command-really-show-the-execution-plan-that-will-be-used/. Very well written and informative. I'll have to check on some of the other aticles as well.

    Regards,

    Chalres

This discussion has been closed.