9 Replies Latest reply on Aug 21, 2012 1:24 PM by 743201

    Row chaining issue in Oracle 10g

    Suddhasatwa_Bhaumik
      Hello All,

      I was seeing row chaining issue in one of our production DB. Row chaining was present in all tables having LONG RAW columns.
      As of now I am not supposed to change these to BLOB/CLOB, so I did exp/imp to solve the issue. However, we are repeating this excercise once every quarter, and now it is time we put a permanent fix to it.

      One of such tables has below storage parameters:
      PCTUSED    0
      PCTFREE    10
      INITRANS   1
      MAXTRANS   255
      STORAGE    (
                  INITIAL          40K
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                 )
      Can I be advised what would be the tuning options in above? Note: All of these tables are in GB's.
      For any inputs, please let me know.

      Thanks,
      Suddhasatwa
        • 1. Re: Row chaining issue in Oracle 10g
          sb92075
          SBhaumik_DBA wrote:
          Hello All,

          I was seeing row chaining issue in one of our production DB.
          post SQL & results so we can see it also.

          why is row chaining a problem that needs to be fixed?

          post SQL & results that show your solution fixes this problem.
          • 2. Re: Row chaining issue in Oracle 10g
            Suddhasatwa_Bhaumik
            This is how I find the tables having row chaining issue:
            select table_name,
            round((blocks*8)/1024/1024,2) "Physical Size (GB)",
            round((num_rows*avg_row_len/1024/1024/1024),2) "Actual Size (GB)",
            (round((blocks*8)/1024/1024,2)-round((num_rows*avg_row_len/1024/1024/1024),2)) "Wasted Space (GB)"
            from dba_tables
            where owner = 'SYSADM'
            and (round((blocks*8)/1024,2)-round((num_rows*avg_row_len/1024/1024),2)) > 20
            and table_name in (select table_name from dba_tab_columns where data_type in ('RAW','LONG RAW','LONG'))
            and table_name in (select table_name from dba_tab_columns where data_type like '%LONG%')
            order by (round((blocks*8)/1024,2)-round((num_rows*avg_row_len/1024/1024),2)) desc;
            Then these tables are exported, re-created, and imported back.
            Once done, above SQL shows no such tables with wasted space.

            Main concern of this thread was to seek advice for the STORAGE parameters I have given above.
            Are they looking good, considering these tables are 32-50G in size?

            Thanks.
            • 3. Re: Row chaining issue in Oracle 10g
              743201
              first a few questions
              -what version of oracle database is this?
              -what kind of application is this? OLTP/DW/OLAP
              -How do you access data (DML)?
              -Are you seeing performance impact?


              couple of things to think about

              -use different dedicated tablespace for long raw columns
              -for 11gR2 - http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_tables.htm#ADLOB45259
              -for 10g - http://docs.oracle.com/cd/B19306_01/appdev.102/b14249/adlob_tables.htm

              -use larger segment size for your tablespace so that you can fit more data into 1 segment
              1 person found this helpful
              • 4. Re: Row chaining issue in Oracle 10g
                sb92075
                SELECT table_name, 
                       Round(( blocks * 8 ) / 1024 / 1024, 2) 
                       "Physical Size (GB)", 
                       Round(( num_rows * avg_row_len / 1024 / 1024 / 1024 ), 2) 
                       "Actual Size (GB)", 
                       ( Round(( blocks * 8 ) / 1024 / 1024, 2) - Round(( 
                         num_rows * avg_row_len / 1024 / 1024 / 1024 ), 2) ) 
                       "Wasted Space (GB)" 
                FROM   dba_tables 
                WHERE  owner = 'SYSADM' 
                       AND ( Round(( blocks * 8 ) / 1024, 2) - Round( 
                                 ( num_rows * avg_row_len / 1024 / 1024 ) 
                                                                   , 2) ) > 20 
                       AND table_name IN (SELECT table_name 
                                          FROM   dba_tab_columns 
                                          WHERE  data_type IN ( 'RAW', 'LONG RAW', 'LONG' )) 
                       AND table_name IN (SELECT table_name 
                                          FROM   dba_tab_columns 
                                          WHERE  data_type LIKE '%LONG%') 
                ORDER  BY ( Round(( blocks * 8 ) / 1024, 2) - Round( 
                                      ( num_rows * avg_row_len / 1024 / 1024 ) 
                                                                        , 2) ) DESC; 
                is the air inside the gas tank on your car considered "Wasted Space"?
                would you car run any better if the size of the gas tank got reduced as gasoline was consumed?

                Realize & understand that Oracle & does reused FREE SPACE without any manual intervention.

                It appears you suffer from Complusive Tuning Disorder!
                • 5. Re: Row chaining issue in Oracle 10g
                  Suddhasatwa_Bhaumik
                  Hello Prakash,

                  It is a 10.2.0.3 DB running under PeopleSoft 8.49 with PeopleTools 8.8. I cannot migrate the LONG columns to LOG because it is not supported by PeopleSoft in this version. Moving them to a different tablespace or increasing the sgment size of the tablespace looks to be a good idea - Many thanks!. Platform is used real-time and is not OLTP. Performance impact is - the batch jobs which make use of these tables got slower and slower as amount of waster space increased over a period of time.

                  Any idea on how to properly decide the values for PCTFREE, PCTINCREASE in such cases?

                  Thanks
                  • 6. Re: Row chaining issue in Oracle 10g
                    743201
                    if you dont go back and update any of your long data you may want to pack it as much as you can in one segment.
                    • 7. Re: Row chaining issue in Oracle 10g
                      Suddhasatwa_Bhaumik
                      Hello Prakash,

                      Thanks for the above note. I would test it one of my databases and let you know.
                      However, please note that some of these tables are heavily updated using DMLs everyday, which might be one of the reasons of this problem.

                      Once I do some tests I would get back to this thread. Thanks.

                      Suddhasatwa.
                      • 8. Re: Row chaining issue in Oracle 10g
                        Suddhasatwa_Bhaumik
                        Hello Prakash,

                        This is the current settings of the tablespace where these tables (with LONG RAW) columns reside:
                        CREATE TABLESPACE HRLARGE DATAFILE 
                          '/oradata/eobtprd/hrlarge01.dbf' SIZE 28731M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED,
                          '/oradata/eobtprd/hrlarge02.dbf' SIZE 4363M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED,
                          '/oradata/eobtprd/hrlarge03.dbf' SIZE 3830M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
                        LOGGING
                        ONLINE
                        EXTENT MANAGEMENT LOCAL AUTOALLOCATE
                        BLOCKSIZE 8K
                        SEGMENT SPACE MANAGEMENT AUTO
                        FLASHBACK ON;
                        Please advice any improvements in the same.
                        Thanks.
                        • 9. Re: Row chaining issue in Oracle 10g
                          743201
                          i dont know if peoplesoft supports it but you also may want to look at larger block size may be 32K. you really have to do some calculations on what is the average row length and decide what would be the best segment size and block size.