Performance Tuning on SQL's Over DB link

[kavingad]

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

[tamilselvan]

Can you run the SQL statement with 10046 trace level 8 and attach the trace file?

[kavingad]

hi
please let me know how to do the 10046 trace level 8

thanks
KDev

[PAVB]

Better for you to check Oracle documentation, nevertheless here is a clue...

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

[tamilselvan]

Please read this:

http://oracleact.com/papers/beacon-ora-perf-class-3.pdf

[kavingad]

Hi ,

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

Thanks,
KDev.

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

[tamilselvan]

The bottleneck query is

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?

[kavingad]

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.

[tamilselvan]

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.