8 Replies Latest reply: Nov 20, 2012 11:39 AM by rafael ceolim RSS

    CPU consumption by insert with APPEND clause

    rafael ceolim
      Good morning!

      I have this insert in my Main Activity on Grid Control consuming resource:

      ++insert /+ append */ into sys.ora_temp_1_ds_120564 select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */"CHAVEUNICA","CHAVEACESSO","DTPROCESSAMENTO","NRRECIBO","NRPROTOCOLO","XMLNOTA","XMLPROTOCOLO" from "NFISCAL"."TB_NFISCALAUTORIZACAO" sample ( 10.0000000000) t where TBL$OR$IDX$PART$NUM("NFISCAL"."TB_NFISCALAUTORIZACAO",0,4,0,"ROWID") = :objn++*+

      The table "TB_NFISCALAUTORIZACAO" has 256 millions of lines. I would like to understand what would be the table sys.ora_temp_1_ds_120564 and for serving this insert.


      In attach we'll see a print of activity in my base:

      https://www.dropbox.com/s/qjc10xoag46gskn/CONSUMPTION_CPU2.JPG
      and where
      https://www.dropbox.com/s/04kqp80yu2w23u5/CONSUMPTION_CPU.jpg

      Thank you,

      Rafael Ceolim
        • 1. Re: CPU consumption by insert with APPEND clause
          Helios-GunesEROL
          Hi;

          What is DB version?


          Regard
          Helios
          • 2. Re: CPU consumption by insert with APPEND clause
            fjfranken
            Rafael Ceolim wrote:

            ++insert /+ append */ into sys.ora_temp_1_ds_120564 select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */"CHAVEUNICA","CHAVEACESSO","DTPROCESSAMENTO","NRRECIBO","NRPROTOCOLO","XMLNOTA","XMLPROTOCOLO" from "NFISCAL"."TB_NFISCALAUTORIZACAO" sample ( 10.0000000000) t where TBL$OR$IDX$PART$NUM("NFISCAL"."TB_NFISCALAUTORIZACAO",0,4,0,"ROWID") = :objn++*+

            The table "TB_NFISCALAUTORIZACAO" has 256 millions of lines. I would like to understand what would be the table sys.ora_temp_1_ds_120564 and for serving this insert.

            In attach we'll see a print of activity in my base:
            The table ora_temp_1_ds_120564 is i.m.o. nothing more than a temporary table used for calculating the statistics on this "TB_NFISCALAUTORIZACAO" table.
            Because that is what the statement is apparently busy with, calculating optimizer statistics


            Cheers
            FJFranken
            • 3. Re: CPU consumption by insert with APPEND clause
              rafael ceolim
              Helios, database 10.2.0.2
              Cluster ASM

              Tank you
              • 4. Re: CPU consumption by insert with APPEND clause
                rafael ceolim
                Helios- Gunes EROL wrote:
                Hi;

                What is DB version?


                Regard
                Helios
                Helios, database 10.2.0.2
                Cluster ASM

                Tank you
                • 5. Re: CPU consumption by insert with APPEND clause
                  rafael ceolim
                  fjfranken wrote:
                  Rafael Ceolim wrote:

                  ++insert /+ append */ into sys.ora_temp_1_ds_120564 select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */"CHAVEUNICA","CHAVEACESSO","DTPROCESSAMENTO","NRRECIBO","NRPROTOCOLO","XMLNOTA","XMLPROTOCOLO" from "NFISCAL"."TB_NFISCALAUTORIZACAO" sample ( 10.0000000000) t where TBL$OR$IDX$PART$NUM("NFISCAL"."TB_NFISCALAUTORIZACAO",0,4,0,"ROWID") = :objn++*+

                  The table "TB_NFISCALAUTORIZACAO" has 256 millions of lines. I would like to understand what would be the table sys.ora_temp_1_ds_120564 and for serving this insert.

                  In attach we'll see a print of activity in my base:
                  The table ora_temp_1_ds_120564 is i.m.o. nothing more than a temporary table used for calculating the statistics on this "TB_NFISCALAUTORIZACAO" table.
                  Because that is what the statement is apparently busy with, calculating optimizer statistics


                  Cheers
                  FJFranken
                  FJFranken tanks for your reply.
                  This process happens every minute. You know how to reduce it?

                  Tanks,
                  Rafael Ceolim
                  • 6. Re: CPU consumption by insert with APPEND clause
                    fjfranken
                    >

                    FJFranken tanks for your reply.
                    This process happens every minute. You know how to reduce it?

                    Tanks,
                    Rafael Ceolim
                    It looks like an external process/script called from SQLplus as this is the program showing up. Is it some kind of script, or warehose load?
                    Are there statistics calculated in that script?
                    Can these command be changed to estimates instead of calculations ??

                    More than that I can't do here. Find out using the session information where the script/program is coming from.

                    Success!
                    FJFranken
                    • 7. Re: CPU consumption by insert with APPEND clause
                      Iordan Iotzov
                      If last_analyzed column in DBA_TABLES is changed every minute then you are gathering stats every minute. You need to find out where DBMS_STATS in invoked and change the frequency, if appropriate.
                      If last_analyzed column is not changed , then you need to figure out what process calls that statement. A first steps would be to review when processes are running while this is happening. Please note that TBL$OR$IDX$PART$NUM is related to partitioning.

                      Iordan Iotzov
                      http://iiotzov.wordpress.com/
                      • 8. Re: CPU consumption by insert with APPEND clause
                        rafael ceolim
                        fjfranken wrote:
                        >

                        FJFranken tanks for your reply.
                        This process happens every minute. You know how to reduce it?

                        Tanks,
                        Rafael Ceolim
                        It looks like an external process/script called from SQLplus as this is the program showing up. Is it some kind of script, or warehose load?
                        Are there statistics calculated in that script?
                        Can these command be changed to estimates instead of calculations ??

                        More than that I can't do here. Find out using the session information where the script/program is coming from.

                        Success!
                        FJFranken
                        FJFranken, thats it!
                        I found the problem. External SQL. I found in a trace! Tank you for helped to solve my problem.
                        Best Regards

                        Rafael Ceolim