 |
|
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:
CREATE OR
REPLACE FUNCTION get_sum(table_name VARCHAR2)
RETURN NUMBER AS
sum_bytes NUMBER;
BEGIN
SELECT SUM(bytes) INTO sum_bytes FROM dba_extents
WHERE segment_name=UPPER(table_name) AND
segment_type=‘TABLE’;
RETURN sum_bytes;
END;
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:
DB_2K_CACHE_SIZE
DB_4K_CACHE_SIZE
DB_8K_CACHE_SIZE
DB_16K_CAHCE_SIZE
DB_32K_CACHE_SIZE
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:
* DB_BLOCK_BUFFERS 0
* SGA_MAX_SIZE to whatever you want max size
* DB_CACHE_SIZE to SGA_MAX_SIZE -
SHARED_POOL_SIZE - LARGE_POOL_SIZE - LOG_BUFFERS -
DB_xK_CACHE_SIZE-JAVA_POOL_SIZE (x is 2, 4, 8, 16, 32)
* 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. |
|
