11 Replies Latest reply: Mar 6, 2013 10:39 AM by ozgurumut RSS

    "Create View" Long time

    ozgurumut
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              Thanks for everthing....