This discussion is archived
8 Replies Latest reply: Nov 20, 2012 9:39 AM by rafael ceolim RSS

CPU consumption by insert with APPEND clause

rafael ceolim Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    Hi;

    What is DB version?


    Regard
    Helios
  • 2. Re: CPU consumption by insert with APPEND clause
    fjfranken Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Helios, database 10.2.0.2
    Cluster ASM

    Tank you
  • 4. Re: CPU consumption by insert with APPEND clause
    rafael ceolim Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    >

    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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