This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Feb 25, 2013 2:40 AM by UW (Germany) RSS

Empty blocks are not used for inserts

UW (Germany) Explorer
Currently Being Moderated
In our Data-Warehouse about 150 OWB-Mappings are used to fill roughly the same number of tables every night. But only a few tables show a strange behavior, and they have grown much bigger, than needed for the number of records.

I hoped that this is a bug in an older version of the database after I got a reply in this thread: OWB Mappings write data extremely sparse But a while ago we changed from database version 10.2.0.3 to 11.2.0.3 and the problem still exists.

And it seems to me, that there are two different types of the problem. On the one hand we have two logging tables, where only inserts happen and even if the length of a record is less than 100 bytes the database uses a new block with a blocksize of 8k for nearly every new record. So the number of records is more or less equal to the number of blocks, what should not be for two tables with this structure:
 
desc DWH_ADMIN.LOAD_LOG
Name       Null     Typ          
---------- -------- ------------ 
ID         NOT NULL NUMBER(22)   
WORKFLOW            VARCHAR2(50) 
WF_REF              NUMBER(22)   
STARTTIME           DATE         
ENDTIME             DATE         
STATUS              NUMBER(1)    
MAP_RUN_ID          NUMBER(22)   

desc DWH_ADMIN.MSG_LOG
Name       Null     Typ           
---------- -------- ------------- 
ID         NOT NULL NUMBER(28)    
LOAD_ID             NUMBER(28)    
MAP_RUN_ID          NUMBER(22)    
MAP_NAME            VARCHAR2(255) 
MODULE              VARCHAR2(255) 
REF_ID              NUMBER(22)    
MSG                 VARCHAR2(255) 
TS                  DATE          
DBA_TABLES gives me this information about the two tables:
 
OWNER      TABLE_NAME  NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED      
---------- ----------- -------- ------ ------------ ----------- ----------- -------------------
DWH_ADMIN  LOAD_LOG        1646   1696            0          38        1646 21.02.2013 08:48:02
DWH_ADMIN  MSG_LOG        34239  29507            0          49       34239 21.02.2013 08:48:04
The dbms_space-package gives me the following information:

For LOAD_LOG:
 
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................              10
Full Blocks        .....................           1,646
Total Blocks............................           1,712
Total Bytes.............................      14,024,704
Total MBytes............................              13
Unused Blocks...........................              16
Unused Bytes............................         131,072
Last Used Ext FileId....................              11
Last Used Ext BlockId...................          43,392
Last Used Block.........................             112
For MSG_LOG:
 
anonymer Block abgeschlossen
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               2
FS2 Blocks (25-50) .....................               5
FS3 Blocks (50-75) .....................               6
FS4 Blocks (75-100).....................             207
Full Blocks        .....................          29,068
Total Blocks............................          29,696
Total Bytes.............................     243,269,632
Total MBytes............................             232
Unused Blocks...........................             189
Unused Bytes............................       1,548,288
Last Used Ext FileId....................              11
Last Used Ext BlockId...................          44,032
Last Used Block.........................             835
How can it be, that nearly all blocks are regarded as “Full” even if, when all varchar2-fields were used with their maximum size, the blocks would be filled to less then 25%.

On the other hand we have a table where all records are dropped every night and created anew. Here the dbms_space package shows a large number of empty blocks but they are not used for the inserts. This is the structure of the table:
desc ODS.OVPRD

Name                   Null Typ           
---------------------- ---- ------------- 
DOC_NO                      VARCHAR2(20)  
LINE_NO                     NUMBER(10)    
O_NO                        VARCHAR2(20)  
O_LINE_NO                   NUMBER(10)    
O_DATE                      DATE          
P_DATE                      DATE          
R_DATE                      DATE          
M_DATE                      DATE          
E_DATE                      DATE          
S_DATE                      DATE          
BFV_NO                      VARCHAR2(20)  
BFV_NAME                    VARCHAR2(50)  
LOAD_ID                     NUMBER(22)    
MAP_RUN_ID                  NUMBER(22)    
MAP_NAME                    VARCHAR2(255)
Meanwhile about 85000 rows with an average length of 140 Bytes use nearly half a million of 8k blocks, where more than 99% of the blocks are empty. This is not only a waste of storage, it makes full table scans extremely slow. During the last days I’ve got the following results from dba_tables and dbms_space-package, always with a DBMS_STATS.GATHER_TABLE_STATS done directly before.
from DBA_TABLES:      | Monday         | Tuesday        Change| Wednesday      Change| Thursday       Change
----------------------+----------------+----------------------+----------------------+----------------------
OWNER                 |             ODS|             ODS      |             ODS      |             ODS      
TABLE_NAME            |           OVPRD|           OVPRD      |           OVPRD      |           OVPRD      
NUM_ROWS              |           84479|           84541    62|           84624    83|           84659    35
BLOCKS                |          445977|          447686  1709|          449397  1711|          451116  1719
EMPTY_BLOCKS          |               0|               0     0|               0     0|               0     0
AVG_ROW_LEN           |             140|             140     0|             140     0|             140     0
SAMPLE_SIZE           |           84479|           84541    62|           84624    83|           84659    35
LAST_ANALYZED         |18.02.2013 07:19|19.02.2013 08:45      |20.02.2013 08:08      |21.02.2013 07:54      
                      |                |                      |                      |                      
From dbms_space:      |                |                      |                      |                      
----------------------+----------------+----------------------+----------------------+----------------------
Unformatted Blocks    |               0|               0      |               0      |               0      
FS1 Blocks (0-25)     |               0|               0      |               0      |               0      
FS2 Blocks (25-50)    |               0|               0      |               0      |               0      
FS3 Blocks (50-75)    |               0|               0      |               0      |               0      
FS4 Blocks (75-100)   |         443.341|         445.049  1708|         446.758  1709|         448.469  1711
Full Blocks           |           1.708|           1.709     1|           1.711     2|           1.711     0
Total Blocks          |         449.664|         449.664     0|         449.664     0|         457.856  8192
Total Bytes           |   3.683.647.488|   3.683.647.488     0|   3.683.647.488     0|   3.750.756.352      
Total MBytes          |           3.513|           3.513     0|           3.513     0|           3.577    64
Unused Blocks         |           3.687|           1.978 -1709|             267 -1711|           6.740  6473
Unused Bytes          |      30.203.904|      16.203.776      |       2.187.264      |      55.214.080      
Last Used Ext FileId  |               7|               7     0|               7     0|              12     5
Last Used Ext BlockId |         529.792|         529.792     0|         529.792     0|         551.680 21888
Last Used Block       |           4.505|           6.214  1709|           7.925  1711|           1.452 -6473
Is there any explanation, why the database behaves this way? Should I open a service request at Oracle support?
  • 1. Re: Empty blocks are not used for inserts
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    What is the PCTFREE value for these tables ?

    Are the INSERTs executed as Direct Path INSERTs (with the APPEND) Hint ? Such INSERTs would cause new blocks to be used without reusing existing blocks.

    Is there a particular pattern to how the rows are inserted, updated and deleted ?

    Are multiple sessions issuing INSERTs concurrently ? How many ?



    Hemant K Chitale

    Edited by: Hemant K Chitale on Feb 21, 2013 4:43 PM
    Added question on multiple sessions.
  • 2. Re: Empty blocks are not used for inserts
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Post
    SQL> SELECT pct_free FROM user_tables WHERE table_name = 'LOAD_LOG';
  • 3. Re: Empty blocks are not used for inserts
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    It looks as if your loading process empties a table using a delete, then refills it with a direct path load (which goes above the highwater mark).
    If this were SQL Loader, the control file would look something like:
    OPTIONS (direct = true)
    LOAD DATA
    REPLACE
    INTO table heap_load
    (
         id          position(01:10)     char,
         name          position(12:50) char,
         padding          position(51:100) char
    )
    I'd guess that OWB has some sort of control mechanism that lets you speciify the generic "empty" and "fill" mechanisms.

    Regards
    Jonathan Lewis
  • 4. Re: Empty blocks are not used for inserts
    UW (Germany) Explorer
    Currently Being Moderated
    Thank you for helping me. Here are my answers:

    1. PCT_FREE is "just default":
    OWNER      TABLE_NAME   PCT_FREE
    ---------- ------------ --------
    DWH_ADMIN  LOAD_LOG           10 
    DWH_ADMIN  MSG_LOG            10 
    ODS        OVPRD              10 
    2. I looked into the packages, generated by OWB: And yes, there are lines like:
    INSERT /*+ APPEND PARALLEL("MSG_LOG") */ 
    INTO
            "DWH_ADMIN"."MSG_LOG"  "MSG_LOG"
            ("ID",
            "LOAD_ID",
            "MAP_RUN_ID",
            "MAP_NAME",
            "TS")
            (SELECT
    /* INPUT_PARAMETER.OUTGRP1, EXP_LOAD.OUTGRP1 */ ...
    or
    FORALL i IN "OVPRD_si".."OVPRD_i" - 1
            INSERT 
            /*+ APPEND PARALLEL("OVPRD") */
            INTO
              "OVPRD" ...
    but I can't imagine, that this causes Oracle to use one block per record.

    3. That's difficult to say. The code is generated by OWB and I didn't study it in detail. But many Mappings are structured similar and we have this problem only with a few tables. But about the next question ...

    4. Especially for the two logging tables this might be true. They are used in most mappings and it might be, that mappings are executed at the same time. But in general the mappings are executed sequential and I don't think that more than two sessions try to write into those tables at the same time.
  • 5. Re: Empty blocks are not used for inserts
    Martin Preiss Expert
    Currently Being Moderated
    only a (blind) guess: perhaps every INSERT APPEND into the log table contains only a single row? So Oracle would have to use a new block for every new row. You could check the executions and row_processed in v$sql.

    Regards

    Martin
  • 6. Re: Empty blocks are not used for inserts
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    UW (Germany) wrote:
    INSERT /*+ APPEND PARALLEL("MSG_LOG") */ 
    INTO
    "DWH_ADMIN"."MSG_LOG"  "MSG_LOG"
    ("ID",
    "LOAD_ID",
    "MAP_RUN_ID",
    "MAP_NAME",
    "TS")
    (SELECT
    /* INPUT_PARAMETER.OUTGRP1, EXP_LOAD.OUTGRP1 */ ...
    This is sufficient to explan your stats on msg_log if the select usually returns just one or two rows.

    FORALL i IN "OVPRD_si".."OVPRD_i" - 1
    INSERT 
    /*+ APPEND PARALLEL("OVPRD") */
    INTO
    "OVPRD" ...
    Is the rest of this code showing a values() clause - presumably so, or the code would crash on a SELECT clause unless the IN calculation made the code execute just once.
    Is there any other code to insert into ovprd ? And what does the code that deletes from it look like ?
    The block stats you show suggest that "yesterday's" full blocks are added to "today's" empty blocks, while the volume of data grows slightly. I'd hazard a guess that something manages to delete and recreate the data every day, with an /*+ append */ on the recreation.

    Regards
    Jonathan Lewis
  • 7. Re: Empty blocks are not used for inserts
    UW (Germany) Explorer
    Currently Being Moderated
    Thank you all for the answers. Now I have an idea, where I have to look. It seems that the "append" is wrong especially for the log-tables and that it is also a problem for the "ovprd"-table. I'll compare the less problematical mappings with the one that fills "ovprd". It might need some time because the mappings were done by an other person and I'm busy with other topics as well. But I'll come back and tell you the results.

    To answer the questions: Here is the delete in the code, generated by OWB:
      IF NOT "OVPRD_St" THEN
      -- Delete all data from the target table
        IF get_use_hc THEN
          EXECUTE IMMEDIATE 'DELETE FROM "OVPRD"';
        ELSE
         EXECUTE IMMEDIATE 'DELETE /*+ PARALLEL("OVPRD", DEFAULT, DEFAULT) */ FROM "OVPRD"';
        END IF;
      END IF;
    and yes, the insert code posted above contiunues with a values() clause. And also yes: In the same mapping there is an other insert into the same table, looking like this:
    INSERT
       /*+ APPEND PARALLEL("OVPRD") */
       INTO
            "OVPRD"  "OVPRD"
            (...)
            (SELECT
    /* JOINER_4.OUTGRP1 */
       ...
      ("MAP_ORDER_VALUES_INIT"."LOAD_ID"/* ATTRIBUTE INPUT_PARAMETER.OUTGRP1.LOAD_ID */)/* ATTRIBUTE EXP_LOAD.OUTGRP1.LOAD_ID: EXPRESSION */ "LOAD_ID$3",
      get_runtime_audit_id/* ATTRIBUTE CONSTANT_LOAD.OUTGRP1.MAP_NAME *//* ATTRIBUTE EXP_LOAD.OUTGRP1.MAP_RUN_ID: EXPRESSION */ "MAP_RUN_ID$2",
      get_model_name/* ATTRIBUTE CONSTANT_LOAD.OUTGRP1.MAP_NAME *//* ATTRIBUTE EXP_LOAD.OUTGRP1.MAP_NAME: EXPRESSION */ "MAP_NAME$2"
    FROM
        "STAGE"."NH"  "NH1"   
     JOIN   "STAGE"."NL"  "NL1" ON ( ( "NH1"."NO_" = "NL1"."DOCUMENT_NO_" ) )
      WHERE 
      ("NL1"."TYPE" <> 0/* OPERATOR FILTER_1: FILTER CONDITION */) 
            )
          ;
  • 8. Re: Empty blocks are not used for inserts
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    The INSERT APPEND would cause one row per block if, at every INSERT, it was inserting only one row. The next INSERT APPEND would take a new block and insert the next row into the new block.


    Hemant K Chitale
  • 9. Re: Empty blocks are not used for inserts
    rp0428 Guru
    Currently Being Moderated
    >
    Thank you all for the answers. Now I have an idea, where I have to look. It seems that the "append" is wrong especially for the log-tables and that it is also a problem for the "ovprd"-table. I'll compare the less problematical mappings with the one that fills "ovprd". It might need some time because the mappings were done by an other person and I'm busy with other topics as well. But I'll come back and tell you the results.
    >
    Doubt if it is your problem but just for kicks run this query (using your table) to see what the HAKAN factor is for that table
    select object_name,to_char(spare1,'0xxxxx') spare1_hex,bitand(spare1,32767) hakan_factor
    from all_objects o,sys.tab$ t
    where object_name ='HAKAN' and object_type='TABLE'
    and o.object_id=t.obj#;
    
    OBJECT_NAME     SPARE1_HEX     HAKAN_FACTOR
    HAKAN      008001     1
    Sometimes we play tricks on the new guys by rebuilding one of their tables in DEV and setting thei Hakan factor to 1 or 2.

    Then we ask them why they are using up so much space. Drives them nuts when they see what you describe.

    Here is a simple demo you can do to see how using 'minimize records_per_block' can lock in the maximum records you can insert into a block.

    You can see the record and block counts go up and even though 4 or 8 blocks get allocated at a time the math shows records per block is clearly being limited.
    Setup - 
    
    drop table hakan
    / 
    create table hakan as select 'ABCDE' char5 from dual
    / 
    exec dbms_stats.gather_table_stats(ownname=>'SCOTT', tabname=>'HAKAN', estimate_percent=>null, cascade=>true, method_opt=>'FOR ALL COLUMNS SIZE 1')
     
    select table_name, num_rows, blocks, empty_blocks from user_tables where table_name = 'HAKAN'
    / 
    alter table HAKAN minimize records_per_block
    / 
    
    
    Now repeat the following and watch the block count after each run
    
    insert into HAKAN values ('ABCDE')
    / 
    / 
    exec dbms_stats.gather_table_stats(ownname=>'SCOTT', tabname=>'HAKAN', estimate_percent=>null, cascade=>true, method_opt=>'FOR ALL COLUMNS SIZE 1')
     
    select table_name, num_rows, blocks, empty_blocks from user_tables where table_name = 'HAKAN'
    / 
    
    TABLE_NAME     NUM_ROWS     BLOCKS     EMPTY_BLOCKS
    HAKAN     10     16     0
    NOTE: the double slash is intentional to insert two rows at a time.

    Ten rows using 16 blocks!
  • 10. Re: Empty blocks are not used for inserts
    UW (Germany) Explorer
    Currently Being Moderated
    I have no idea what it means, but I can tell you, that the result is
    spare1_hex = 0002e0 and hakan_factor = 736
    for all three tables as well as for most other tables in the two schemas.

    Looking at all tables we have this distribution:
    OWNER                          SPARE1_HEX HAKAN_FACTOR COUNT(*)
    ------------------------------ ---------- ------------ --------
    DWH_ADMIN                      0002e0              736       26 
    ODS                            0001fa              506        1 
    ODS                            000117              279        2 
    ODS                            000151              337        1 
    ODS                            000195              405        1 
    ODS                            0002a3              675        2 
    ODS                            0002e0              736      133 
    ODS                            00021c              540        1 
    ODS                            000243              579        1 
    ODS                            00026f              623        1 
  • 11. Re: Empty blocks are not used for inserts
    UW (Germany) Explorer
    Currently Being Moderated
    In the table source$ I analyzed the code created by OWB and I found, that 1190 of 1389 INSERT-statements and also 84 of 88 UPDATE-statements and 21 of 22 MERGE-statements have a hint /*+ APPEND PARALLEL("…") */ in the next line of the code.

    And in the Oracle Warehouse Builder documentation I found this information at
    http://docs.oracle.com/cd/E14072_01/owb.112/e10935/ref_maps_processflows.htm#i1113036

    “By default, commonly used hints such as APPEND and PARALLEL are added. For all loading modes other than INSERT, the APPEND hint causes no effect and you can choose to remove it.”

    In the moment I’m surprised that we don’t have much more tables with such a bad ratio from used blocks to empty blocks in our data warehouse as for most tables the inserts are always done with an APPEND hint. I have to find out, what is different in the problematical mappings and where I can switch of this default hint in OWB for certain mappings.

    I also wonder whether other Data Warehouse in the world, that were constructed with OWB, don't have a such problem with meager filled blocks :-)
  • 12. Re: Empty blocks are not used for inserts
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    UW (Germany) wrote:

    In the moment I’m surprised that we don’t have much more tables with such a bad ratio from used blocks to empty blocks in our data warehouse as for most tables the inserts are always done with an APPEND hint. I have to find out, what is different in the problematical mappings and where I can switch of this default hint in OWB for certain mappings.

    I also wonder whether other Data Warehouse in the world, that were constructed with OWB, don't have a such problem with meager filled blocks :-)
    The /*+ append */ hint only applies to INSERT AS SELECT (although for a brief period there was a bug that allowed it to apply to the values() clause). You would only notice an extreme amount of wasted space if the SELECT produced a very small number of rows each time you ran the statement. If, for example, you ran a statement that always inserted about 1,000 rows each time it ran you might not notice that you had repeated sequences of 49 full blocks followed by one half-full block. A table where you insert one logging or message row after each 100,000 rows inserted into the fact table would stand out as "nearly empty" - but then you might notice that it was much bigger than it ought to be because you might only be checking the very large tables.

    I often notice all sorts of errors on client sites which have not been noticed by the local DBA because they are insignificant compared to the large-scale activity of the system - and it's often not worth doing anything subtle to address them because "subtle" can mean 10 minutes coding but 6 weeks on change management and testing.


    Regards
    Jonathan Lewis
  • 13. Re: Empty blocks are not used for inserts
    Mohamed Houri Pro
    Currently Being Moderated
    Jonathan
    The /*+ append */ hint only applies to INSERT AS SELECT (although for a brief period there 
    was a bug that allowed it to apply to the values() clause)
    Starting from 11g there is a special hint /*+ append_values */ which applies for INSERT-VALUES

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#BABFIHGA

    Best regards

    Mohamed Houri

    Edited by: Mohamed Houri on 22 févr. 2013 06:41
  • 14. Re: Empty blocks are not used for inserts
    rp0428 Guru
    Currently Being Moderated
    >
    I have no idea what it means, but I can tell you, that the result is
    spare1_hex = 0002e0 and hakan_factor = 736
    for all three tables as well as for most other tables in the two schemas.
    >
    As I had said I didn't expect that to be an issue but thanks for confirming.

    The distribution you posted is what you would expect. It means that no one has artificially set the Hakan factor by manually manipulating the setting.

    The value of '736' is typical for many newly created tables. Oracle will reset the Hakan as part of the process of creating a bitmap index on a table to ensure that there can't be any blocks that have more than a known number of rows. A user would likely never need to set it.
1 2 Previous Next

Legend

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