3 Replies Latest reply: Feb 21, 2013 2:34 PM by user1983440 RSS

    Rapid and Huge growth of of used space of temporary tablespace

    rr*427140*rm
      Hi,

      Have a query (select) that run quick (no more than 10 seconds).
      As soon I insert the data into a temporary table or even on physical table, the temporary table used space starts to growth very fast. The used space is totally used and the query crash since e reach the limit (65GB), or even more if I add more table files to temporary tablespace!

      The problem also happen only if the period (dates) is one year (2013). If the period is the first trimestre of 2013 (same amount of data), the problem does not happen!!


      I also confirm that on another instance ( a test one), even with less resources this ORACLE behavior do not happen. I confirm differente execution plan queries, between the two instances .

      What I really do not understant is the behavior of the ORACLE with the huge and rapid growth!!!

      Any one experiment such a similiar situation?

      Thanks in advance,Rui


      Plan
      INSERT STATEMENT ALL_ROWSCost: 15.776 Bytes: 269 Cardinality: 1
      28 LOAD TABLE CONVENTIONAL MIDIALOG_OLAP.MED_INVCOMP_FACTTMP_BEFGROUPBY
      27 FILTER
      26 NESTED LOOPS
      24 NESTED LOOPS Cost: 15.776 Bytes: 269 Cardinality: 1
      22 NESTED LOOPS Cost: 15.775 Bytes: 255 Cardinality: 1
      19 NESTED LOOPS Cost: 15.774 Bytes: 205 Cardinality: 1
      17 NESTED LOOPS Cost: 15.773 Bytes: 197 Cardinality: 1
      14 NESTED LOOPS Cost: 15.770 Bytes: 180 Cardinality: 1
      11 NESTED LOOPS Cost: 15.767 Bytes: 108 Cardinality: 1
      9 HASH JOIN Cost: 15.757 Bytes: 8.346.500 Cardinality: 83.465
      7 HASH JOIN Cost: 13.407 Bytes: 6.345.012 Cardinality: 83.487
      5 HASH JOIN Cost: 11.163 Bytes: 5.010.550 Cardinality: 100.211
      3 HASH JOIN Cost: 5.642 Bytes: 801.288 Cardinality: 22.258
      1 INDEX RANGE SCAN INDEX MIDIALOG.IX_INSCOMP_DTCEIDICIDLCPECIDOP Cost: 120 Bytes: 489.676 Cardinality: 22.258
      2 INDEX FAST FULL SCAN INDEX (UNIQUE) MIDIALOG.IX_LINHACOMPRADA_IDLCIDOPSEQ Cost: 5.463 Bytes: 123.975.530 Cardinality: 8.855.395
      4 INDEX FAST FULL SCAN INDEX (UNIQUE) MIDIALOG.IX_LINHACOMPRADA_IDLCIDOPSEQ Cost: 5.463 Bytes: 123.975.530 Cardinality: 8.855.395
      6 TABLE ACCESS FULL TABLE MIDIALOG.ITEM_AV Cost: 1.569 Bytes: 6.963.736 Cardinality: 267.836
      8 TABLE ACCESS FULL TABLE MIDIALOG.ITEM_AV Cost: 1.572 Bytes: 7.713.672 Cardinality: 321.403
      10 INDEX UNIQUE SCAN INDEX (UNIQUE) MIDIALOG.IX_BOFINALBO_IDBOIDFINALBO Cost: 0 Bytes: 8 Cardinality: 1
      13 TABLE ACCESS BY INDEX ROWID TABLE MIDIALOG.INSERCAO_COMPRADA Cost: 3 Bytes: 72 Cardinality: 1
      12 INDEX RANGE SCAN INDEX (UNIQUE) MIDIALOG.IX_INSCOMPRADA_IDLCDATAPECAINS Cost: 2 Cardinality: 1
      16 TABLE ACCESS BY INDEX ROWID TABLE MIDIALOG.INSERCAO_ITEMFACTURA Cost: 3 Bytes: 17 Cardinality: 1
      15 INDEX RANGE SCAN INDEX MIDIALOG.IX_INSITFACT_INSCOMPRADA Cost: 2 Cardinality: 1
      18 INDEX RANGE SCAN INDEX (UNIQUE) MIDIALOG.UQ_ITEMFACTURA_IDITF_IDFACT Cost: 1 Bytes: 8 Cardinality: 1
      21 TABLE ACCESS BY INDEX ROWID TABLE MIDIALOG.FATURA Cost: 1 Bytes: 50 Cardinality: 1
      20 INDEX UNIQUE SCAN INDEX (UNIQUE) MIDIALOG.PK_FATURA Cost: 0 Cardinality: 1
      23 INDEX UNIQUE SCAN INDEX (UNIQUE) MIDIALOG.PK_TIPO_ESTADO Cost: 0 Cardinality: 1
      25 TABLE ACCESS BY INDEX ROWID TABLE MIDIALOG.TIPO_ESTADO Cost: 1 Bytes: 14 Cardinality: 1

      Edited by: rr**** on 19/Fev/2013 15:25
        • 1. Re: Rapid and Huge growth of of used space of temporary tablespace
          user1983440
          Can you post the INSERT statement?
          • 2. Re: Rapid and Huge growth of of used space of temporary tablespace
            rr*427140*rm
            I run the select with sucess, no more that 1 minute from on year of data. Few temporary used space used.
            As soon I plug the insert (global temporary table, also experiment with physical table) the used space of temporary table space start to grow crazy!!


            insert into midialog_olap.med_invcomp_facttmp_befgroupby
            select fac.numefatura,
            fac.codpessoa,
            fac.dtemiss,
            tef.nome as estado_factura,
            opsorig.demid,
            opsorig.anoplano,
            opsorig.numplano,
            opsorig.numplanilha,
            ops.nome as ordem_publicidade,
            ops.external_number as numero_externo,
            ops.estado,
            lic.seq,
            inc.data,
            inc.peca,
            fac.id_versao_plano,
            fac.ano_proforma || '.' || fac.numrf as num_proforma,
            iif.tipo_facturacao,
            opsorig.codveiculo as id_veiculo,
            opsorig.codfm as id_fornecedor_media,
            icorig.chkestado as id_estado_checking,
            0 as percentagem_comissao_agencia,
            0 as valor_pbv,
            0 as valor_stxtv,
            0 as valor_ptv,
            0 as valor_odbv,
            0 as valor_pbbv,
            0 as valor_dnv,
            0 as valor_pbnv,
            0 as valor_stxv,
            0 as valor_pbtv,
            0 as valor_dav,
            0 as valor_plv,
            0 as valor_odlv,
            0 as valor_pllv,
            0 as valor_ca,
            0 as valor_trv,
            0 as valor_txv,
            0 as valor_base_facturacao,
            decode(ops.estado, :WKFOPR_BOOKINGORDER_CANCELED, 0,
            decode(iif.tipo_facturacao, :BILLING_TYPE_ONLYCOMISSION, 0,
            inc.total_pb_compra * fac.percentagem_facturada / 100))
            as valor_pbc,
            decode(ops.estado, :WKFOPR_BOOKINGORDER_CANCELED, 0,
            decode(iif.tipo_facturacao, :BILLING_TYPE_ONLYCOMISSION, 0,
            inc.total_stxt_compra * fac.percentagem_facturada / 100))
            as valor_stxtc,
            decode(ops.estado, :WKFOPR_BOOKINGORDER_CANCELED, 0,
            decode(iif.tipo_facturacao, :BILLING_TYPE_ONLYCOMISSION, 0,
            inc.total_pt_compra * fac.percentagem_facturada / 100))
            as valor_ptc,
            decode(ops.estado, :WKFOPR_BOOKINGORDER_CANCELED, 0,
            decode(iif.tipo_facturacao, :BILLING_TYPE_ONLYCOMISSION, 0,
            inc.total_odb_compra * fac.percentagem_facturada / 100))
            as valor_odbc,
            decode(ops.estado, :WKFOPR_BOOKINGORDER_CANCELED, 0,
            decode(iif.tipo_facturacao, :BILLING_TYPE_ONLYCOMISSION, 0,
            inc.total_pbb_compra * fac.percentagem_facturada / 100))
            as valor_pbbc,
            decode(ops.estado, :WKFOPR_BOOKINGORDER_CANCELED, 0,
            decode(iif.tipo_facturacao, :BILLING_TYPE_ONLYCOMISSION, 0,
            inc.total_dn_compra * fac.percentagem_facturada / 100))
            as valor_dnc,
            decode(ops.estado, :WKFOPR_BOOKINGORDER_CANCELED, 0,
            decode(iif.tipo_facturacao, :BILLING_TYPE_ONLYCOMISSION, 0,
            inc.total_pbn_compra * fac.percentagem_facturada / 100))
            as valor_pbnc,
            decode(ops.estado, :WKFOPR_BOOKINGORDER_CANCELED, 0,
            decode(iif.tipo_facturacao, :BILLING_TYPE_ONLYCOMISSION, 0,
            inc.total_stx_compra * fac.percentagem_facturada / 100))
            as valor_stxc,
            decode(ops.estado, :WKFOPR_BOOKINGORDER_CANCELED, 0,
            decode(iif.tipo_facturacao, :BILLING_TYPE_ONLYCOMISSION, 0,
            inc.total_pbt_compra * fac.percentagem_facturada / 100))
            as valor_pbtc,
            decode(ops.estado, :WKFOPR_BOOKINGORDER_CANCELED, 0,
            decode(iif.tipo_facturacao, :BILLING_TYPE_ONLYCOMISSION, 0,
            inc.total_da_compra * fac.percentagem_facturada / 100))
            as valor_dac,
            decode(ops.estado, :WKFOPR_BOOKINGORDER_CANCELED, 0,
            decode(iif.tipo_facturacao, :BILLING_TYPE_ONLYCOMISSION, 0,
            inc.total_pl_compra * fac.percentagem_facturada / 100))
            as valor_plc,
            decode(ops.estado, :WKFOPR_BOOKINGORDER_CANCELED, 0,
            decode(iif.tipo_facturacao, :BILLING_TYPE_ONLYCOMISSION, 0,
            inc.total_odl_compra * fac.percentagem_facturada / 100))
            as valor_odlc,
            decode(ops.estado, :WKFOPR_BOOKINGORDER_CANCELED, 0,
            decode(iif.tipo_facturacao, :BILLING_TYPE_ONLYCOMISSION, 0,
            inc.total_pll_compra * fac.percentagem_facturada / 100))
            as valor_pllc,
            decode(ops.estado, :WKFOPR_BOOKINGORDER_CANCELED, 0,
            decode(iif.tipo_facturacao, :BILLING_TYPE_ONLYCOMISSION, 0,
            inc.total_transcricoes * fac.percentagem_facturada / 100))
            as valor_trc,
            decode(ops.estado, :WKFOPR_BOOKINGORDER_CANCELED, 0,
            decode(iif.tipo_facturacao, :BILLING_TYPE_ONLYCOMISSION, 0,
            inc.total_tx_compra * fac.percentagem_facturada / 100))
            as valor_txc,
            --nvl((select cfm.total_comprado
            -- from fin_custos_facturados_media cfm
            -- where cfm.id_factura = fac.id_factura and
            - -- cfm.id_op = ops.id_op
            -- ), 0) / opsorig.number_of_bought_insertions as custos_associados,
            0 as custos_associados,
            fac.iss as percentagem_iva,
            fac.percentagem_facturada,
            fac.currency_exchange as taxa_cambio,
            iif.associated_code as insertions_associated_code
            from fatura fac, item_fatura itf, insercao_itemfactura iif,
            insercao_comprada icorig, linha_comprada lcorig, item_av opsorig,
            med_bookingorder_finalbo opfin,
            insercao_comprada inc,
            linha_comprada lic, item_av ops,
            --veiculo vei,
            tipo_estado tef
            where fac.id_factura = itf.id_factura and
            itf.id_itemfactura = iif.id_itemfactura and
            iif.id_ic = icorig.id_ic and
            icorig.id_lc = lcorig.id_lc and
            lcorig.id_op = opsorig.id_op and
            opsorig.id_op = opfin.id_booking_order and
            opsorig.number_of_bought_insertions > 0 and
            opfin.id_final_booking_order = ops.id_op and
            -- ops.id_op = (
            -- select max(ops.id_op)
            -- from item_av ops
            -- start with ops.id_op = opsorig.id_op
            -- connect by prior ops.id_opsubstituicao = ops.id_op) and
            ops.id_op = lic.id_op and
            lic.seq = lcorig.seq and
            lic.id_op = inc.id_op and
            lic.id_lc = inc.id_lc and
            inc.data = icorig.data and
            inc.peca = icorig.peca and
            --opsorig.codveiculo = vei.codveiculo and
            fac.estado = tef.estado and
            fac.estado != 305 and
            ops.estado != 223 and
            iif.tipo_facturacao != 'SO_CA' and
            icorig.data between :dtBeginDate and :dtEndDate and
            (fac.codagenciafat = :iIdAgency or :iIdAgency is null);
            • 3. Re: Rapid and Huge growth of of used space of temporary tablespace
              user1983440
              Global temp tables (GTT's) use the temporary tablespace by default, so it would make sense to me that your temporary table space would increase in size as your GTT is populated by the INSERT. Your last post, however, suggests that the temporary tablespace also significantly grows when you insert the rows into a "normal" table (that resides in a permanent [non-temporary] tablespace). Is this correct?

              Edited by: user1983440 on Feb 21, 2013 3:33 PM