This content has been marked as final. Show 9 replies
SBhaumik_DBA wrote:post SQL & results so we can see it also.
I was seeing row chaining issue in one of our production DB.
why is row chaining a problem that needs to be fixed?
post SQL & results that show your solution fixes this problem.
This is how I find the tables having row chaining issue:
Then these tables are exported, re-created, and imported back.
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;
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?
first a few questions1 person found this helpful
-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
is the air inside the gas tank on your car considered "Wasted Space"?
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;
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!
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?
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.
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.
This is the current settings of the tablespace where these tables (with LONG RAW) columns reside:
Please advice any improvements in the same.
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;
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.