Loading ...
Sorry, an error occurred while loading the content.

Parallel Extract using Global Temp Table while gathering of stats.

Expand Messages
  • Joe-Nino B. Casimiro
    Hi Guys, We re using Global Temp Table for our 50+ extracts and we normally truncated/populate it during the process. Question is even though each session has
    Message 1 of 6 , Jan 21, 2008
    • 0 Attachment
      Hi Guys,

      We're using Global Temp Table for our 50+ extracts and we normally truncated/populate it during the process. Question is even though each session has its own storage for the same GTT, will gathering of stats in one extract(process) has a complicated or create problem on another process?

      I just want to confirm from you guys. Correct me if im wrong as the stat is only stored into one table and probably used the previous one that's why its better to remove the stats?

      Your inputs is highly appreciated.

      Thanks,
      Onin



      Looking for last minute shopping deals? Find them fast with Yahoo! Search.
    • David Kurtz
      I wrote a blog entry about this recently: http://blog.psftdba.com/2008/01/global-temporary-tables-and-peoplesoft.html You are right, all instances of the same
      Message 2 of 6 , Jan 21, 2008
      • 0 Attachment
        I wrote a blog entry about this recently: http://blog.psftdba.com/2008/01/global-temporary-tables-and-peoplesoft.html

        You are right, all instances of the same global temporary table share the same statistics.  So whoever was the last session to collect stats on a GTT table - those statistics will be used for all subsequent cursors in all sessions that reference the same GTT.  There is only one table definition in the Oracle catalog.

        optimizer_dynamic_sampling looks like an attractive option.  You need to set this parameter to at least 2.  This is the default in 10g , but it defaults to 1 in 9i - and you may want to set it higher.
        see another Blog entry: http://blog.psftdba.com/2007/05/updatestats-v-optimizer-dynamic.html

        On 21/01/2008, Joe-Nino B. Casimiro <onin_as_sysdba@...> wrote:
        Hi Guys,

        We're using Global Temp Table for our 50+ extracts and we normally truncated/populate it during the process. Question is even though each session has its own storage for the same GTT, will gathering of stats in one extract(process) has a complicated or create problem on another process?

        I just want to confirm from you guys. Correct me if im wrong as the stat is only stored into one table and probably used the previous one that's why its better to remove the stats?

        Your inputs is highly appreciated.

        Thanks,
        Onin



        Looking for last minute shopping deals? Find them fast with Yahoo! Search.



        --
        regards
        _________________________
        David Kurtz
        Go-Faster Consultancy Ltd.
        tel: +44 (0)7771 760660
        fax: +44 (0)7092 348865
        mailto: david.kurtz@...
        web: www.go-faster.co.uk
        Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
        The PeopleSoft DBA Blog: http://psftdba.blogspot.com
        PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
      • Joe-Nino B. Casimiro
        Hi David, I explore the article you wrote and seems we haven t meet the expectation with regards to this GTT running simultaneosly with extract process. We
        Message 3 of 6 , Jan 29, 2008
        • 0 Attachment
          Hi David,

          I explore the article you wrote and seems we haven't meet the expectation with regards to this GTT running simultaneosly with extract process. We collected the stats using optimizer dynamic sampling for every process but there's no performance gain. Would you have any recommendation?

          The scenario will be look like this. For instance, Job1-3 are run concurrently. Each processes collecting/gather stats with optimizer dynamic sampling. Is there anything you may recommend?

          Job1                    Job2                    Job3                        
          5K inserted to TMPA     50K inserted to TMPA    50M inserted to TMPA
          1K Inserted to TMPB     10K inserted to TMPB    10M inserted to TMPB
          TMPs analyzed           TMPs analyzed           TMPs analyzed

          Thanks & regards,
          Onin


          ----- Original Message ----
          From: David Kurtz <david.kurtz@...>
          To: psftdba@yahoogroups.com
          Sent: Tuesday, January 22, 2008 12:49:55 AM
          Subject: Re: PeopleSoft DBA Forum Parallel Extract using Global Temp Table while gathering of stats.

          I wrote a blog entry about this recently: http://blog. psftdba.com/ 2008/01/global- temporary- tables-and- peoplesoft. html

          You are right, all instances of the same global temporary table share the same statistics.  So whoever was the last session to collect stats on a GTT table - those statistics will be used for all subsequent cursors in all sessions that reference the same GTT.  There is only one table definition in the Oracle catalog.

          optimizer_dynamic_ sampling looks like an attractive option.  You need to set this parameter to at least 2.  This is the default in 10g , but it defaults to 1 in 9i - and you may want to set it higher.
          see another Blog entry: http://blog. psftdba.com/ 2007/05/updatest ats-v-optimizer- dynamic.html

          On 21/01/2008, Joe-Nino B. Casimiro <onin_as_sysdba@ yahoo.com> wrote:
          Hi Guys,

          We're using Global Temp Table for our 50+ extracts and we normally truncated/populate it during the process. Question is even though each session has its own storage for the same GTT, will gathering of stats in one extract(process) has a complicated or create problem on another process?

          I just want to confirm from you guys. Correct me if im wrong as the stat is only stored into one table and probably used the previous one that's why its better to remove the stats?

          Your inputs is highly appreciated.

          Thanks,
          Onin



          Looking for last minute shopping deals? Find them fast with Yahoo! Search.



          --
          regards
          ____________ _________ ____
          David Kurtz
          Go-Faster Consultancy Ltd.
          tel: +44 (0)7771 760660
          fax: +44 (0)7092 348865
          mailto: david.kurtz@ go-faster. co.uk
          web: www.go-faster. co.uk
          Book: PeopleSoft for the Oracle DBA: http://www.psftdba. com
          The PeopleSoft DBA Blog: http://psftdba. blogspot. com
          PeopleSoft DBA Forum: http://groups. yahoo.com/ group/psftdba



          Looking for last minute shopping deals? Find them fast with Yahoo! Search.
        • David Kurtz
          I m not sure what you are asking. What difference did you expect to see? What differences are you seeing? If you are asking why things aren t different you
          Message 4 of 6 , Jan 29, 2008
          • 0 Attachment
            I'm not sure what you are asking. What difference did you expect to
            see? What differences are you seeing?

            If you are asking why things aren't different you need to produce
            something that will show what each job is doing (probably Oracle SQL
            Trace).

            --- In psftdba@yahoogroups.com, "Joe-Nino B. Casimiro"
            <onin_as_sysdba@...> wrote:
            >
            > Hi David,
            >
            > I explore the article you wrote and seems we haven't meet the
            expectation with regards to this GTT running simultaneously with
            extract process. We collected the stats using optimizer dynamic
            sampling for every process but there's no performance gain. Would you
            have any recommendation?
            >
            > The scenario will be look like this. For instance, Job1-3 are run
            concurrently. Each processes collecting/gather stats with optimizer
            dynamic sampling. Is there anything you may recommend?
            >
            > Job1 Job2 Job3

            > 5K inserted to TMPA 50K inserted to TMPA 50M inserted to TMPA
            > 1K Inserted to TMPB 10K inserted to TMPB 10M inserted to TMPB
            > TMPs analyzed TMPs analyzed TMPs analyzed
            >
            > Thanks & regards,
            > Onin
            >
            >
          • Joe-Nino B. Casimiro
            Hi David, We re expecting to see a performance gain if we consider using GTT from DTT. But you give me a clue on what next to be done. We ll try to do a TRACE
            Message 5 of 6 , Jan 29, 2008
            • 0 Attachment
              Hi David,

              We're expecting to see a performance gain if we consider using GTT from DTT. But you give me a clue on what next to be done. We'll try to do a TRACE for each process.

              Regards,
              Onin


              ----- Original Message ----
              From: David Kurtz <david.kurtz@...>
              To: psftdba@yahoogroups.com
              Sent: Wednesday, January 30, 2008 12:46:27 AM
              Subject: Re: PeopleSoft DBA Forum Parallel Extract using Global Temp Table while gatherin

              I'm not sure what you are asking. What difference did you expect to
              see? What differences are you seeing?

              If you are asking why things aren't different you need to produce
              something that will show what each job is doing (probably Oracle SQL
              Trace).

              --- In psftdba@yahoogroups .com, "Joe-Nino B. Casimiro"
              <onin_as_sysdba@ ...> wrote:

              >
              > Hi David,
              >
              > I explore the article you wrote and seems we haven't meet the
              expectation with regards to this GTT running simultaneously with
              extract process. We collected the stats using optimizer dynamic
              sampling for every process but there's no performance gain. Would you
              have any recommendation?
              >
              > The scenario will be look like this. For instance, Job1-3 are run
              concurrently. Each processes collecting/gather stats with optimizer
              dynamic sampling. Is there anything you may recommend?
              >
              > Job1 Job2 Job3

              > 5K inserted to TMPA 50K inserted to TMPA 50M inserted to TMPA
              > 1K Inserted to TMPB 10K inserted to TMPB 10M inserted to TMPB
              > TMPs analyzed TMPs analyzed TMPs analyzed
              >
              > Thanks & regards,
              > Onin
              >
              >




              Looking for last minute shopping deals? Find them fast with Yahoo! Search.
            • David Kurtz
              A Global Temporary Table is still a table, and in many ways it is handled in a similar manner. It still has a physical existence, although it is in the
              Message 6 of 6 , Jan 29, 2008
              • 0 Attachment
                A Global Temporary Table is still a table, and in many ways it is
                handled in a similar manner.

                It still has a physical existence, although it is in the temporary
                tablespace which should have a temp file rather than a data files.

                The major differences that one normally hopes to exploit to achieve
                performance improvements are:
                * you do not generate redo when issue DML against it because it is not
                a recoverable object, but you still generate undo.
                * when you truncate it, Oracle does not flush dirty blocks from the
                buffer cache to disk (as it does with normal tables which leads to
                local write wait). Again, this is because it is not a recoverable object.
                * when multiple processes/sessions issue DML against what is
                apparently the same table, there will operate on different database
                blocks because each session has its own physical copy. With a normal
                table, if they were to update or delete different rows in the same
                data block, the database would have to perform read consistent
                recoveries of that block to the different SCNs of each subsequent session.
                * Each session starts with a 'virgin' version of the GTT, with the
                high water mark reset, so full scans will not degrade because the HWM
                has been raised by a previous process with a lot of data to process.

                It is perfectly possible to introduce GTTs and not get any improvement
                in performance because you are not doing anything significantly different.

                In you original e-mail you described populating the tables. If
                writing redo to disk is not a problem in your system you won't see
                much improvement in your process by introducing GTTs.

                DMK

                --- In psftdba@yahoogroups.com, "Joe-Nino B. Casimiro"
                <onin_as_sysdba@...> wrote:
                >
                > Hi David,
                >
                > We're expecting to see a performance gain if we consider using GTT
                from DTT. But you give me a clue on what next to be done. We'll try to
                do a TRACE for each process.
                >
                > Regards,
                > Onin
                >
                >
                > ----- Original Message ----
                > From: David Kurtz <david.kurtz@...>
                > To: psftdba@yahoogroups.com
                > Sent: Wednesday, January 30, 2008 12:46:27 AM
                > Subject: Re: PeopleSoft DBA Forum Parallel Extract using Global Temp
                Table while gatherin
                >
                >
                >
                >
                >
                >
                >
                >
                >
                >
                >
                >
                >
                >
                > I'm not sure what you are asking. What difference did
                you expect to
                >
                > see? What differences are you seeing?
                >
                >
                >
                > If you are asking why things aren't different you need to produce
                >
                > something that will show what each job is doing (probably Oracle SQL
                >
                > Trace).
                >
                >
                >
                > --- In psftdba@yahoogroups .com, "Joe-Nino B. Casimiro"
                >
                > <onin_as_sysdba@ ...> wrote:
                >
                > >
                >
                > > Hi David,
                >
                > >
                >
                > > I explore the article you wrote and seems we haven't meet the
                >
                > expectation with regards to this GTT running simultaneously with
                >
                > extract process. We collected the stats using optimizer dynamic
                >
                > sampling for every process but there's no performance gain. Would you
                >
                > have any recommendation?
                >
                > >
                >
                > > The scenario will be look like this. For instance, Job1-3 are run
                >
                > concurrently. Each processes collecting/gather stats with optimizer
                >
                > dynamic sampling. Is there anything you may recommend?
                >
                > >
                >
                > > Job1 Job2 Job3
                >
                >
                >
                > > 5K inserted to TMPA 50K inserted to TMPA 50M inserted to TMPA
                >
                > > 1K Inserted to TMPB 10K inserted to TMPB 10M inserted to TMPB
                >
                > > TMPs analyzed TMPs analyzed TMPs analyzed
                >
                > >
                >
                > > Thanks & regards,
                >
                > > Onin
                >
                > >
                >
                > >
                >
                >
                >
                >
                >
                >
                >
                >
                >
                >
                >
                >
                >
                >
                > <!--
                >
                > #ygrp-mkp{
                > border:1px solid #d8d8d8;font-family:Arial;margin:14px
                0px;padding:0px 14px;}
                > #ygrp-mkp hr{
                > border:1px solid #d8d8d8;}
                > #ygrp-mkp #hd{
                >
                color:#628c2a;font-size:85%;font-weight:bold;line-height:122%;margin:10px
                0px;}
                > #ygrp-mkp #ads{
                > margin-bottom:10px;}
                > #ygrp-mkp .ad{
                > padding:0 0;}
                > #ygrp-mkp .ad a{
                > color:#0000ff;text-decoration:none;}
                > -->
                >
                >
                >
                > <!--
                >
                > #ygrp-sponsor #ygrp-lc{
                > font-family:Arial;}
                > #ygrp-sponsor #ygrp-lc #hd{
                > margin:10px 0px;font-weight:bold;font-size:78%;line-height:122%;}
                > #ygrp-sponsor #ygrp-lc .ad{
                > margin-bottom:10px;padding:0 0;}
                > -->
                >
                >
                >
                > <!--
                >
                > #ygrp-mlmsg {font-size:13px;font-family:arial, helvetica, clean,
                sans-serif;}
                > #ygrp-mlmsg table {font-size:inherit;font:100%;}
                > #ygrp-mlmsg select, input, textarea {font:99% arial, helvetica,
                clean, sans-serif;}
                > #ygrp-mlmsg pre, code {font:115% monospace;}
                > #ygrp-mlmsg * {line-height:1.22em;}
                > #ygrp-text{
                > font-family:Georgia;
                > }
                > #ygrp-text p{
                > margin:0 0 1em 0;}
                > #ygrp-tpmsgs{
                > font-family:Arial;
                > clear:both;}
                > #ygrp-vitnav{
                > padding-top:10px;font-family:Verdana;font-size:77%;margin:0;}
                > #ygrp-vitnav a{
                > padding:0 1px;}
                > #ygrp-actbar{
                > clear:both;margin:25px
                0;white-space:nowrap;color:#666;text-align:right;}
                > #ygrp-actbar .left{
                > float:left;white-space:nowrap;}
                > .bld{font-weight:bold;}
                > #ygrp-grft{
                > font-family:Verdana;font-size:77%;padding:15px 0;}
                > #ygrp-ft{
                > font-family:verdana;font-size:77%;border-top:1px solid #666;
                > padding:5px 0;
                > }
                > #ygrp-mlmsg #logo{
                > padding-bottom:10px;}
                >
                > #ygrp-vital{
                > background-color:#e0ecee;margin-bottom:20px;padding:2px 0 8px 8px;}
                > #ygrp-vital #vithd{
                >
                font-size:77%;font-family:Verdana;font-weight:bold;color:#333;text-transform:uppercase;}
                > #ygrp-vital ul{
                > padding:0;margin:2px 0;}
                > #ygrp-vital ul li{
                > list-style-type:none;clear:both;border:1px solid #e0ecee;
                > }
                > #ygrp-vital ul li .ct{
                >
                font-weight:bold;color:#ff7900;float:right;width:2em;text-align:right;padding-right:.5em;}
                > #ygrp-vital ul li .cat{
                > font-weight:bold;}
                > #ygrp-vital a{
                > text-decoration:none;}
                >
                > #ygrp-vital a:hover{
                > text-decoration:underline;}
                >
                > #ygrp-sponsor #hd{
                > color:#999;font-size:77%;}
                > #ygrp-sponsor #ov{
                > padding:6px 13px;background-color:#e0ecee;margin-bottom:20px;}
                > #ygrp-sponsor #ov ul{
                > padding:0 0 0 8px;margin:0;}
                > #ygrp-sponsor #ov li{
                > list-style-type:square;padding:6px 0;font-size:77%;}
                > #ygrp-sponsor #ov li a{
                > text-decoration:none;font-size:130%;}
                > #ygrp-sponsor #nc{
                > background-color:#eee;margin-bottom:20px;padding:0 8px;}
                > #ygrp-sponsor .ad{
                > padding:8px 0;}
                > #ygrp-sponsor .ad #hd1{
                >
                font-family:Arial;font-weight:bold;color:#628c2a;font-size:100%;line-height:122%;}
                > #ygrp-sponsor .ad a{
                > text-decoration:none;}
                > #ygrp-sponsor .ad a:hover{
                > text-decoration:underline;}
                > #ygrp-sponsor .ad p{
                > margin:0;}
                > o{font-size:0;}
                > .MsoNormal{
                > margin:0 0 0 0;}
                > #ygrp-text tt{
                > font-size:120%;}
                > blockquote{margin:0 0 0 4px;}
                > .replbq{margin:4;}
                > -->
                >
                >
                >
                >
                >
                >
                >
                >
                >
                ____________________________________________________________________________________
                > Be a better friend, newshound, and
                > know-it-all with Yahoo! Mobile. Try it now.
                http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
                >
              Your message has been successfully submitted and would be delivered to recipients shortly.