Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2010
    Posts
    2

    Unanswered: ! in place of dblink

    I have this time consuming SQL that I want to optimize ;

    SELECT A3.COMPO,TO_DATE('2010-02-18 00:00:00',
    'YYYY-MM-DD HH24:MIS'),A3.REPORT_HOURS_ID
    FROM DAILYTCLASSRPTSTAT201003_T A3,COMPO_T@! A2
    WHERE A3.UPDATED=TO_DATE('2010-02-18 00:00:00',
    'YYYY-MM-DD HH24:MIS')
    AND A3.COMPO=A2.COMPO
    AND (A2.ADMIN=1 OR A2.ADMIN=2);

    In line 3 at the end, there is COMPO_T@! ; I understand tabl@dblink;

    What does @! mean? also how does one get an explain plan with a remote db reference; anyway to optimize remote db reference?

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Weird ...

    I get an explain plan for the linked database in our environment... but then I've created a PLAN_TABLE in that linked db.

    What's your current explain plan look like?

    ---=cf

  3. #3
    Join Date
    Mar 2010
    Posts
    2
    The SQL was doing 45 million physical reads over 3 hours and swamping disk sytem with 5000 IOPS; system is down now; we are moving it to its own local disk system; will post explain plan; it said something like remotedb with a cost of 1, where the dblink table came; they tell me there is no external database connection; the 2 dblinks are to the same schema as the sql and to another schema; I created some indexes on those COMPO_T tables and when I ran the explain plan again, using directly(without the dblink) I can see a 6 fold reduction in timerons; but the SQL is in a procedure which is wrapped;
    any help greatly appreciated.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Find someone who knows where the source code is located? Or you could trace the connection, to capture the SQL, I dunno how you can optimise SQL you can't see ...

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    This is actually meaning that the code came FROM a different db to be executed fromTHEIR dblink to your db. So, you are seeing the code that another database actually called which has a dblink to your database.

    At least that is usually the case in what I see when @! is involved.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Aug 2009
    Posts
    262
    I 2nd the opinion of Mr. The Duck.

    but again i may be wrong thus waiting for the GODs and prophets of ORACLE to shade their wisdom upon this .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •