| Previous | Table of Contents | Next |
Here is the output of the script:
SQL> @t1
Fri Feb 23 page 1
dbname Database
Data Dictionary Hit Ratios
PARAMETER GETS GETMISSES COUNT USAGE HITRATE
------------- ----- ---------- ------- ------- -------
dc_object_ids 136 136 12 0 0
dc_free_extents 1978 1013 67 48 48.8
dc_used_extents 1930 970 63 5 49.7
dc_database_links 4 2 3 2 50
dc_sequence_grants 101 18 121 18 82.2
dc_synonyms 527 33 34 33 93.7
dc_objects 18999 947 389 387 95
dc_columns 163520 6576 2261 2247 96
dc_segments 8548 314 127 117 96.3
dc_constraint_defs 7842 250 218 210 96.8
dc_table_grants 26718 792 772 763 97
dc_sequences 4179 75 11 7 98.2
dc_users 1067 14 20 14 98.7
dc_tables 49497 261 272 271 99.5
dc_tablespace_quotas 957 4 5 4 99.6
dc_indexes 59548 172 329 328 99.7
dc_tablespaces 1162 3 7 3 99.7
dc_tablespaces 1201 4 27 4 99.7
dc_user_grants 9900 14 24 14 99.9
dc_usernames 18452 18 20 18 99.9
dc_users 14418 17 18 17 99.9
dc_column_grants 0 0 1 0 100
dc_constraint_defs 0 0 1 0 100
dc_constraints 0 0 1 0 100
dc_files 0 0 1 0 100
dc_histogram_defs 0 0 1 0 100
dc_profiles 0 0 1 0 100
dc_rollback_segments 18560 6 17 7 100
28 rows selected.
Multithreaded Server Tuning
Remember, if you are using SQL*Net version 2 with the multithreaded server, Oracle will allocate storage in the library cache to hold session information. As new connections are established through the MTS, Oracle will allocate memory, and the amount of memory can be measured with the v$sessstat table. Listing 3.6 shows a sample query.
Listing 3.6 A sample query.
SELECT (sum(value) || ' bytes' "Total memory for all sessions" FROM v$sessstat, v$statname WHERE NAME = 'session memory' AND v$sessstat.statistics# = v$statname.statistic#; SELECT (sum(value) || ' bytes' "Total maximum memory for all sessions" FROM v$sessstat, v$statname WHERE NAME = 'max session memory' AND v$sessstat.statistics# = v$statname.statistic#;
The output might look as follows:
Total memory for all sessions ------------------------------------------ 203460 bytes Total maximum memory for all sessions ------------------------------------------ 712473 bytes
Based on this instant in time, this report shows that 204 K is allocated to sessions, while the maximum memory for all sessions is 712 K. When deciding whether or not to increase the shared_pool_size parameter, the total memory for all sessions is the best guideline, since it is unlikely that all sessions will reach maximum memory allocation at the same moment in time.
Tuning The db_block_buffers Parameter
When a request is made to Oracle to retrieve data, Oracle will first check the internal memory structures to see if the data is already in the buffer. In this fashion, Oracle avoids doing unnecessary I/O. It would be ideal if we could create one buffer for each database page, ensuring that Oracle would read each block only once. However, the costs of memory in the real world make this prohibitive.
At best, we can only allocate a small number of real-memory buffers, and Oracle will manage this memory for us. Oracle utilizes a least-recently-used algorithm to determine which database pages are to be flushed from memory. Another related memory issue emerges that deals with the size of the database blocks. In most Unix environments, database blocks are sized to only 2 K. Unlike the mainframe ancestors that allowed blocks of up to 16,000 bytes, large blocks are not possible because of the way Unix handles its page I/O. Remember, I/O is the single most important slowdown in a client-server system, and the more relevant the data that can be grabbed in a single I/O, the better the performance. The cost of reading a 2 K block is not significantly higher than the cost of reading an 8 K block. However, the 8 K block read will be of no benefit if we only want a small row in a single table. On the other hand, if the tables are commonly read front-to-back, or if you make appropriate use of Oracle clusters (as described in Chapter 2), you can reap dramatic performance improvements by switching to large block sizes.
For batch-oriented reporting databases, very large block sizes are always recommended. However, many databases are used for online transaction processing during the day, while the batch reports are run in the evenings. Nevertheless, as a general rule, 8 K block sizes will benefit most systems.
Fortunately, Oracle does allow for large block sizes, and the db_block_size parameter is used to control the physical block size of the data files. Unlike other relational databases, Oracle allocates the data files on your behalf when the CREATE TABLESPACE command is issued. One of the worst things that can happen to a buffer cache is the running of a full-table scan on a large table.
Predicting The Benefit Of Additional Block Buffers
As database blocks are retrieved from disk into the database, they are stored in RAM memory in an area called a buffer. The block remains in the buffer until it is overwritten by another database request. At read time, the database first checks to see if the data already resides in the buffer before incurring the overhead of a disk I/O (see Figure 3.3).
)
Figure 3.3 Oracle’s data buffer operation.
| Previous | Table of Contents | Next |