Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Tips by Burleson

Chapter 5 Oracle Tablespace Design

;

We now have a table named test_frag in a 2-KB tablespace. The next step is to populate 4,000 rows, with only a single spec in the VARCHAR2 column:

declare
myint integer := 1;
begin
loop
 
  insert into test_frag
  values
  (
     test_frag_seq.nextval,
    ' '
   );
 
  myint := myint+1;
 
  if myint > 4000 then exit; end if;
 
end loop;
end;
/

Now that we have the rows inserted, let’s take a look at how many rows are stored on the data block in DBA_TABLES:

Table                    % Free   NUM_ROWS AVG_ROW_LEN  CHAIN_CNT
-------------------- ---------- ---------- ----------- ----------
TEST_FRAG                    10       4000           9          0


In DBA_SEGMENTS, we see that the table is in a single extent. We also see that we used 32 data block, at 2 KB per block, to store 4,000 rows. This works out to 500 data rows per block.

Table             Tablespace      Buffer                       
name              Name            Pool          Bytes   Blocks  Extents
----------------- --------------- ---------- -------- -------- -----   
TEST_FRAG         ASM_TEST        DEFAULT      65,536       32     1


Now let’s make a mess and expand a large VARCHAR2 column from one byte to 2,000 bytes. After the update, we see in DBA_SEGMENTS that the table is much larger:

Table         Tablespace      Buffer                                 
name          Name            Pool              Bytes   Blocks Extents 
------------- --------------- ---------- ------------ -------- --------
TEST_FRAG     ASM_TEST        DEFAULT       9,437,184    4,608     24 

Now our table is on 4,608 blocks, and the table has taken 24 extents. When we examine DBA_TABLES, we see that the table now has an average row length of 1,378, and every single row has chained!

Table                    % Free   NUM_ROWS AVG_ROW_LEN  CHAIN_CNT
-------------------- ---------- ---------- ----------- ----------
TEST_FRAG                    10       4000        1378       4000


Row chaining is a serious problem for the database administrator (DBA), and it appears that Automatic Space Management is not appropriate for tables where you need to reserve space for large row expansions with PCTFREE.


Got Oracle Scripts?

BC shares their personal arsenal of Oracle data dictionary scripts in this comprehensive download. With decades of experience using Oracle monitoring scripts and Oracle tuning scripts, we shares our secrets for navigating the Oracle data dictionary.

Packed with more than 690 ready-to-use Oracle scripts, this is the definitive collection for every senior Oracle DBA.

It would take man-years to develop these scripts from scratch, making this download the best value in the Oracle industry.

http://www.dba-oracle.com/bp/bp_elec_adv_mon_tuning.htm

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.