Performance Tuning on SQL's Over DB link
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Performance Tuning on SQL's Over DB link

  1. #1
    Join Date
    Sep 2008
    Posts
    4

    Question Performance Tuning on SQL's Over DB link

    Hi ,
    I have observe the following sql statement behave differently in the given senarios;

    The following sql statement was fired in two differnt sessions;
    sql statement (A) :-

    select dt.*,lm.*,(fn_get_details(lm.col1)*nvl(lm.col3,0))) as amount
    from tab1@dblink1 lm,tab2@dblink dt
    where lm.col1 <> 'C' and
    lm.col2 = 'A' and
    lm.col3 = dt.col1


    session 1. issued the statement "set transaction read only"; and then execute the sql statement(A).

    session 2. just execute the sql statement(A)

    results :
    session 1. return the results in quick time.
    session 2. executing the statement for long time and not retunning a single record.

    please let me know how to add the "set transaction read only" for all sessions for a perticular db user. I have already tryied database trigger and it doen't work;

    see trigger details below;

    CREATE OR REPLACE TRIGGER SYSTEM.test1
    AFTER LOGON
    ON DATABASE
    BEGIN
    IF (USER = 'RPT_USER') THEN
    SET TRANSACTION READ ONLY;
    END IF;
    END test1;


    Thanks
    KDev

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Can you run the SQL statement with 10046 trace level 8 and attach the trace file?

  3. #3
    Join Date
    Sep 2008
    Posts
    4
    hi
    please let me know how to do the 10046 trace level 8

    thanks
    KDev

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Better for you to check Oracle documentation, nevertheless here is a clue...
    Code:
    alter session set timed_statistics = true;
    alter session set sql_trace = true;
    set autotrace on explain
    alter session set events='10046 trace name context forever, level 8';
    ++ EXECUTE SQL ++
    alter session set sql_trace = false;
    alter session set timed_statistics = false;
    I'm serious, check the documentation. If you don't do it we will know
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136

  6. #6
    Join Date
    Sep 2008
    Posts
    4
    Hi ,

    I have got the tkprof report. Please click the below link.

    Thanks,
    KDev.

    http://docs.google.com/Doc?id=dgccm8...invite=c593672

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    The bottleneck query is

    Code:
    select * 
    from
     ntb_rpt_cud0018_1
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.26       2.89          0         37          0           0
    Execute      1      0.00       0.01          0          0          0           0
    Fetch        1      1.15     502.94          0          0          1           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        3      1.41     505.85          0         37          1           0
    It ran for 505 seconds and returned 0 rows.

    Is it a view? Can you optimize the query?

  8. #8
    Join Date
    Sep 2008
    Posts
    4
    Hi ,
    yes it's a view. I have optimize the query . it's runs fine with the clause "set transaction read only" and also it's run fine with our production db.

    Kdev.

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    I hope you under the consequences of "set transaction read only".

    From Oracle doc:
    The READ ONLY clause establishes the current transaction as a read-only transaction. This clause established transaction-level read consistency.

    All subsequent queries in that transaction see only changes that were committed before the transaction began. Read-only transactions are useful for reports that run multiple queries against one or more tables while other users update these same tables.

    This clause is not supported for the user SYS. That is, queries by SYS will return changes made during the transaction even if SYS has set the transaction to be READ ONLY.

Posting Permissions

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



Click Here to Expand Forum to Full Width