-
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
-
Can you run the SQL statement with 10046 trace level 8 and attach the trace file?
-
hi
please let me know how to do the 10046 trace level 8
thanks
KDev
-
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.
-
-
Hi ,
I have got the tkprof report. Please click the below link.
Thanks,
KDev.
http://docs.google.com/Doc?id=dgccm8...invite=c593672
-
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?
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|