Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

 
 

Maximizing Oracle 10g Block Space Usage

The RAM that is allocated to the data buffers will have to be carefully managed until memory becomes cheap enough to cache the entire database.  Properly allocating tables and indexes according to block size is a balancing act.  If the data blocks are set too large, valuable buffer space is wasted holding row data that will never be referenced.  If the blocks are set too small, Oracle is forced to perform more disk I/O to satisfy a query. 

 

The following are some further general guidelines that can be used for allocating data block sizes:

§       Segregate large-table full-table scans. Tables subject to large-table, full-table scans will benefit from the largest supported block size.  They should be placed in a tablespace with the largest block size.

§       Set db_recycle_cache_size carefully. If db_cache_size  is not set to the largest supported block size, the db_recycle_cache_size parameter should be used.  Instead, a db_32k_cache_size , or whatever the max size is, should be created, and then assign all tables and indexes subject to large-table, full-table scans to the largest data buffer in the database.

The data dictionary will use the default block size.  Make sure that the dictionary, the SYSTEM tablespace for example, is always fully cached in a data buffer pool.  The block size, per se, of the dictionary is less important than having enough RAM in the SYSTEM tablespace buffer to fully cache all of the dictionary blocks.

Finding Baselines

Oracle databases are always changing, and the databases that are examined at 10:00 AM may be completely different than the databases that exist at 3:00 PM.  Does this mean that a broad brush application of SSD is not valid?

 

When the performance of Oracle disk I/O is examined over different time periods, regular signatures appear when the I/O information is aggregated by hours of the day and day of the week as shown in Figure 14.11. 

 

Figure 14.11 – Average disk reads and writes by hour of the day

 

Most Oracle professionals will use Oracle9i STATSPACK or Oracle10g AWR information to gather these baselines.  Once the repeating I/O trends have been identified, the DBA will be able to apply a broad brush to the use of SSD, placing the fast I/O devices where they will do the most good.

 

I/O information can be captured at the file level and this can give insight into the best data files to place on super fast SSD.  The following script extracts the physical read information from the Oracle 10g dba_hist_filestatxs   view:

 

break on begin_interval_time skip 2

 

column phyrds  format 999,999,999

column begin_interval_time format a25

 

select

   begin_interval_time,

   filename,

   phyrds

from

   dba_hist_filestatxs

  natural join

   dba_hist_snapshot

;

 

The sample output below shows a running total of physical reads by datafile.  The snapshots are collected every half-hour.  Starting from this script, the DBA could easily add a WHERE clause criteria and create a unique time-series exception report.

 

SQL> @reads

 

BEGIN_INTERVAL_TIME       FILENAME                                 PHYRDS

------------------------- -------------------------------------- --------

24-FEB-04 11.00.32.000 PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF   164,700

                          E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF   26,082

                          E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF   472,008

                          E:\ORACLE\ORA92\FSDEV10G\USERS01.DBF      1,794

                          E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA     2,123

                                                                         

                                                                          

24-FEB-04 11.30.18.296 PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF   167,809

                          E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF   26,248

                          E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF   476,616

                          E:\ORACLE\ORA92\FSDEV10G\USERS01.DBF      1,795

                          E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA     2,244

                                                                         

                                                                          

25-FEB-04 12.01.06.562 AM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF   169,940

                          E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF   26,946

                          E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF   483,550

                          E:\ORACLE\ORA92\FSDEV10G\USERS01.DBF      1,799

                          E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA     2,248

 

A little tweaking to the reads.sql script and the DBA could report on physical writes, read time, write time, single block reads, and a host of other interesting metrics from the dba_hist_filestatxs view.

 

SEE CODE DEPOT FOR FULL SCRIPTS


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks