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

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Use Views to Prebuild Queries

Oracle Tips by Burleson

I have already discussed this trick. Essentially, if you have a standard data set that is repeatedly selected against, create a view to preprocess this dataset and select against the view. This ensures that processing is pushed to the server, not to the client.

Use MTS When Connections Exceed 50 to 100

The multithreaded server (MTS) allows for large numbers of users to connect through a limited number of database connections. This is great for large environments where it would be impossible for everyone to connect if they had to use individual connect processes. However, unless you normally run with at least 50 to 100 concurrent processes accessing Oracle at the same time, MTS can hurt your performance. Using parallel query just about guarantees that you should use MTS.

In a test using a multigig database and 10 users, a standard set of queries generated over 200 separate processes using dedicated connections. Some queries required over 30 minutes to complete. After switching on MTS and running the same queries, none took over five minutes; the SGA utilization (it had been running 100 percent for DB block buffers) dropped to 75 percent (as shown by the Q monitor system from Savant); and login times dropped to zero (using dedicated server resulted in up to five-minute delays logging in to the machine).  The machine was an E6000 from Sun with 9 CPUs, 3 gigabytes of memory, and a 600-gig disk farm using RAID0-1 and RAW disks. Access was over a normal Ethernet type network from PC clients using TCPIP protocols.

MTS is a queuing system for database connections; it allows multiple users to share the same single connection to the database by a time-sharing mechanism. If only 5 to 10 users are connecting, they may actually see delays in statement execution and processing due to this queuing mechanism.

Use PL/SQL Blocks, Procedures and Functions on Both Server and Client

Always look at multistep SQL scripts, whether they are standalone or embedded in an application, and ask yourself if they could be changed into a stored procedure, function, or anonymous PL/SQL block. Even with 3GL programs running from a client to a database server, if you encapsulate the SQL with BEGIN-END block construction (assuming this can be done; some statements can’t be done this way), then they will be passed as a single network transaction to the server.

As demonstrated above, a complex set of SQL statements can be converted into a PL/SQL procedure or function, and the procedure or function stored on the server, allowing a simple EXECUTE or direct function call. For example, about the only way for SQL to get the bytes of a table’s records is to issue a SUM(bytes) type statement against the tables entry in DBA_EXTENTS.  If you want to include a count of the rows in a report, you either have to ANALYZE the table and pull the count from out of DBA_TABLES as a join or use a local variable and do the SELECT COUNT into the local variable. This results in more network round trips and server work. However, if you create a function that does this type of operation for you, then you can issue the call to the function directly from a BEGIN-END block or even from the SELECT itself. For example:

sum_bytes NUMBER;
  SELECT SUM(bytes) INTO sum_bytes FROM dba_extents
  WHERE segment_name=UPPER(table_name) AND
  RETURN sum_bytes;

Using the above function (compiled and stored on the server), we can now select the sum of bytes used by any table, just like a regular column (purity restrictions were relaxed in Oracle8i):

SELECT table_name, get_sum(table_name) tab_size FROM dba_tables;

Techniques like this can reduce network traffic substantially. Use of functions and procedures force processing to the server and return only results to the client. Your goal as a DBA tuning in a multitier environment is to pack as much content into each piece of network traffic as possible. To do this, you have to move more into the object paradigm by passing messages (such as just a procedure or function call), rather than an entire procedural structure such as an SQL routine. This also ensures proper use of the shared pool and SGA resources, since multiple “almost virtually” identical statements won’t end up being stuffed into your SGA.

Other Oracle8i Tuning Features

I have already discussed the use of function-based indexes and reverse-key indexes in Chapter 6; refer to the index if you haven’t read those sections yet.

Oracle Tuning Features

Oracle offers many new tuning features, which I touch on here. For the details of their usage,  refer to the Oracle Database Performance Guide and Reference.

Using Multiple-Sized DB Block Buffer

Oracle allows the use of multiple database block sizes. Set the old DB_BLOCK_BUFFERS parameter should to 0 and instead set the DB_CACHE_SIZE to the desired buffer size. The _DB_BLOCK_BUFFERS parameter is then calculated by dividing the DB_CACHE_SIZE by the DB_BLOCK_SIZE parameter. The SGA_MAX_SIZE parameter controls the maximum size the SGA may reach; any unallocated space goes into the shared pool as unused.

To prepare for using multiple blocksizes, you set one or more of the following parameters:


You can set any of them except the one that corresponds to the default blocksize for your database; for example, if you have 8K- (8192) size blocks as set in DB_BLOCK_SIZE, you are not allowed to set the DB_8K_CACHE_SIZE parameter.

First, verify that DB_CACHE_SIZE+SHARED_POOL_SIZE+LARGE _POOL_SIZE+ JAVA_ POOL_SIZE+LOG_BUFFER is less than SGA_MAX_SIZE; if it is not, either increase SGA_MAX_SIZE or deallocate memory from the other structures until the sum of the parameters plus the size set for the new cache is less than SGA_MAX_SIZE. Remember that if SGA_MAX_SIZE is less than 128 megabytes, then the sizes specified must be in 4-megabyte multiples; otherwise they must be in 16-megabyte multiples or Oracle will round up to the next appropriate size.

Next, set DB_xK_CACHE_SIZE to the size you desire. Note: You may have to set it at a multiple of the increment size minus 1 byte, as it seems to round up to the next value at the current value. For example, I tried setting it exactly at 4194304 bytes (4 megabytes) and it rounded it to 8 megabytes.

If you have upgraded the database you are using from a previous versions, make sure and track through all the ifiles in the initialization file(s); for example, in Windows 2000, there were three different ifiles, and in the middle one (created by ODMA) was a DB_BLOCK_BUFFER setting. You cannot have DB_BLOCK_BUFFERS set to anything but 0, or not set at all, or you will get a conflict between the new settings and the old--you can’t have both. Make sure and check all of the files. I suggest merging them into one central file.

Once the initialization file settings are correct, then you can shut down and restart the database to have them take effect. I didn’t have much luck setting them dynamically due to the aforementioned problems with the initialization files.

Once the database has been restarted with the proper buffer settings, then you simply create a tablespace, adding the BLOCKSIZE parameter and setting it to the desired size. In OEM, this process would be:

1.    Log in to OEM console as SYS AS SYSDBA to your database.

2.    Select the Instance icon.

3.    Select the Configuration icon

4.    Select the All Initialization parameters button.

5.    Set:


* SGA_MAX_SIZE to whatever you want max size


* DB_xK_CACHE_SIZE to 1 less than a multiple of 4m or 16m (if SGA_MAX_SIZE > 128m 16 m)

6.    Shut down and restart. Note that for a migrated instance, there may be up to three ini files that need to be looked at to eliminate all settings for DB_BLOCK_BUFFERS; again, you cannot have DB_BLOCK_BUFFERS set to anything but null or 0.

7.    Restart OEM, if needed, and log in as SYSTEM to your database.

8.    Select Object ? Create ? Tablespace ? Create from the top menu bar

9.    Fill in general tablespace parameters.

10.   Select the Storage tab and select DB blocksize (if the parameters took, you should see a down arrow to the right of the DB blocksize button; if not, return to step 6. You can use see SQL text button to see actual SQL text used.

11.   Select the Create button.

12.   Voila! A xK tablespace in a yK database.


See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

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

Oracle performance tuning software 

Oracle performance tuning book


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



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

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter