ozgurumut wrote:Physical standby in read-write mode? If it is in read-write mode then you have to say it as Primary database.
When I run "create view" command, it takes long time on Oracle 10.2.0.5 and Db mode is Physical Standby (Read-Write). I tried some configuration for tuning but didn't reach to solution.
What can be reasons of this latency ? Have you any ideas ?
Edited by: ozgurumut on Mar 6, 2013 1:03 AM
Everyday, We create flashback point then activate Standby DB, finally Open DB for internal user. End of day DB returns to flashback point (flasback restore) to morning. So Read write :)That you haven't mentioned earlier ;)
You can find ASH report on this link http://www.2shared.com/document/VwEVound/ASH_Rpt_-_mngdb1.htmlReviewed ASH report, Am not sure when you took the ASH report. Lets suppose you have given "Create view" command around 9:10 AM and completed by 9:20 AM, Take ASH report only from start date and time of 9:10 AM to 9:20 AM. If you took based on the same then i cannot see any query which is executing "create view",
ozgurumut wrote:I asked something and you used something other query. :( Please read once again my previous post what i have provided....
Yes , I didn't mention in my first post :)
select event,seconds_in_wait,state,wait_time from v$session a where sid=16314;
xdb schema cache initialization,312,WAITING,0
Do you know "xdb schema cache initialization" event type ?
ozgurumut wrote:Ok, got it. I mean to say v$session_event http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2091.htm
I did your steps,
1) Give CREATE OR REPLACE FORCE VIEW
2) took SID from v$session
3) checked SQL from other session. And result on below;
SQL >select event,seconds_in_wait,state,wait_time from v$session a where sid=16314;
EVENT SECONDS_IN_WAIT - STATE - WAIT_TIME
xdb schema cache initialization 312 WAITING 0
According your SQL ( select event, totalwaits, Total_timeouts, time_waited, max_wait from v$session where sid=_) some fields not belong to v$session like total_waits, Total_timeouts, time_waited, max_wait ... :(
Please check your SQL field or check my SQL result , it is same :)
EVENT,TOTAL_WAITS,TOTAL_TIMEOUTS,TIME_WAITED,MAX_WAITI can say concern is here, *Events in waticlass other" , Which is related to locking.
events in waitclass Other,48,48,45569,977