This discussion is archived
11 Replies Latest reply: Mar 6, 2013 8:39 AM by ozgurumut RSS

"Create View" Long time

ozgurumut Newbie
Currently Being Moderated
Hi All,

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 ?

B.R.

Edited by: ozgurumut on Mar 6, 2013 1:03 AM
  • 1. Re: "Create View" Long time
    CKPT Guru
    Currently Being Moderated
    ozgurumut wrote:
    Hi All,

    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 ?

    B.R.

    Edited by: ozgurumut on Mar 6, 2013 1:03 AM
    Physical standby in read-write mode? If it is in read-write mode then you have to say it as Primary database.
    or you executed "Create view" in primary database? If so can you check what is waiting from v$session_event using the sid of that session.

    or you can gather ASH report for that duration to check.
  • 2. Re: "Create View" Long time
    ozgurumut Newbie
    Currently Being Moderated
    Hi CKPT,

    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 :)

    You can find commands on below.

    at  9:00 am
    CREATE RESTORE POINT CHECKPOINT_1 GUARANTEE FLASHBACK DATABASE;
    ALTER DATABASE ACTIVATE STANDBY DATABASE;
    STARTUP MOUNT FORCE;
    ALTER DATABASE OPEN;

    at 17:00 pm
    STARTUP MOUNT FORCE;
    FLASHBACK DATABASE TO RESTORE POINT CHECKPOINT_1;
    ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
    STARTUP MOUNT FORCE;
    DROP RESTORE POINT CHECKPOINT_1;
    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;
  • 3. Re: "Create View" Long time
    ozgurumut Newbie
    Currently Being Moderated
    Hi CKPT,

    You can find ASH report on this link http://www.2shared.com/document/VwEVound/ASH_Rpt_-_mngdb1.html

    Edited by: ozgurumut on Mar 6, 2013 3:58 AM
  • 4. Re: "Create View" Long time
    CKPT Guru
    Currently Being Moderated
    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.html
    Reviewed 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",

    Or else you can try again as below.
    1) Connect to the session where you are going to give command "create view"
    2) Gather sid from v$session
    3) from the other session check
    SQL> select event, total_waits, Total_timeouts, time_waited, max_wait from v$session where sid=<>;
    Here you have to mention sid you gathered from the step 2.

    Then you can see where it is waiting exactly, Moreover i dont think it is issue with Standby database[read-write]
  • 5. Re: "Create View" Long time
    ozgurumut Newbie
    Currently Being Moderated
    Yes , I didn't mention in my first post :)

    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

    Do you know "xdb schema cache initialization" event type ?

    Thanks...
  • 6. Re: "Create View" Long time
    CKPT Guru
    Currently Being Moderated
    ozgurumut wrote:
    Yes , I didn't mention in my first post :)

    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

    Do you know "xdb schema cache initialization" event type ?

    Thanks...
    I asked something and you used something other query. :( Please read once again my previous post what i have provided....
    Once again, this is not issue for Data Guard environment. BTW , have you used force command with "create view" or any other locks during that time?
  • 7. Re: "Create View" Long time
    ozgurumut Newbie
    Currently Being Moderated
    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 :)
  • 8. Re: "Create View" Long time
    CKPT Guru
    Currently Being Moderated
    ozgurumut wrote:
    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 :)
    Ok, got it. I mean to say v$session_event http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2091.htm
  • 9. Re: "Create View" Long time
    ozgurumut Newbie
    Currently Being Moderated
    SQL Result on below ;

    SQL> select event, total_waits, Total_timeouts, time_waited, max_wait from v$session_event where sid=16321;

    EVENT,TOTAL_WAITS,TOTAL_TIMEOUTS,TIME_WAITED,MAX_WAIT
    log file sync,1,0,0,0
    db file sequential read,60,0,14,1
    cursor: pin S wait on X,26287,26287,25787,1
    SQL*Net message to client,43,0,0,0
    SQL*Net more data to client,10,0,0,0
    SQL*Net message from client,43,0,78472,26872
    SQL*Net more data from client,3,0,0,0
    events in waitclass Other,48,48,45569,977
  • 10. Re: "Create View" Long time
    CKPT Guru
    Currently Being Moderated
    EVENT,TOTAL_WAITS,TOTAL_TIMEOUTS,TIME_WAITED,MAX_WAIT
    events in waitclass Other,48,48,45569,977
    I can say concern is here, *Events in waticlass other" , Which is related to locking.
    you have to take a look what are the objects the view is referring, Use create view force command, do check any locks prior to that.

    This is not related to data guard question, If you have more question please close this thread as answered here and open in forums of General Question.s

    Thank you.
  • 11. Re: "Create View" Long time
    ozgurumut Newbie
    Currently Being Moderated
    Thanks for everthing....

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points