Foglight 5.9.2 - Performance Tuning Field Guide

Block Size

At this time, we are not aware of any specific effect caused by running with different block sizes. 8 K blocks, in particular, should be fine. Due to the way in which the database is primarily used, larger block sizes are not expected to perform better. For information on how the database is primarily used, see How the Management Server Uses the Database .

Oracle Striping

Oracle® can utilize striping to increase the throughput of the database. A responsible Oracle DBA will analyze the I/O numbers of our sizing spreadsheet and then set up striping if they feel that faster disk access is warranted. In testing, we have not seen any need for striping for the supported load numbers. Use striping at your own discretion.

Oracle Tablespaces

You can improve database performance by using multiple tablespaces that are located on different physical hard disks. This setup can yield much better I/O throughput than a setup with a single tablespace on one disk.

Foglight® Management Server basically manages two sets of tables:

NOTE: There is no benefit to separating configuration data from observation data (that is, in setting up multiple destinations for the different types of data, potentially on different physical disks). Quest does not recommend any tablespace separation for data, index, and LOB. Quest does not expect such separation to have any impact on real performance. Your DBA can decide how to configure such things based on his or her own specific requirements about where to store data, index and LOB. This is typically done according to database administration conventions.

During the installation, you can select a tablespace for group A (advanced database setup, by default USERS). The configuration tables are mostly read, and are not modified (inserts/updates) very frequently because configuration changes are not happening all the time.

The topology tables (topology_*) in the same tablespace will see more activity (inserts for the most part, but also a fair amount of updates), depending on the agent data. Changes should only occur periodically, as changes in the monitored environment trickle into the Management Server.

The alarm tables (alarm_*) will, for the most part, receive a constant stream of alarms (inserts) along with the occasional time-based read as the user looks at alarm history. Features like alarm annotations do not cause frequent updates.

For group B, you can specify a list of tablespaces at install time (advanced database setup, by default USERS). The observation tables are used for insertion only. The server creates new tables (obs_*) at runtime when it needs to store data. When the data in a table is no longer required, the table truncates and is then used for another set of data.

The configuration of multiple tablespaces enables the Management Server to spread the observation tables over multiple hard drives (if configured that way) and, generally, to achieve higher throughput for incoming data, retrieval, and rollup operations. At present, we are not aware of a bottleneck in Oracle® performance with the supported load numbers and known hardware. For higher supported load numbers, and, depending on the hardware, this setup should be considered by the DBA.

For more information, consult Oracle’s documentation on tablespaces.

Undo tablespace

With Oracle9i, Oracle introduced the UNDO_RETENTION parameter, which specifies how far back in time Oracle retains undo information. Oracle8i made use of rollback segments instead. With Oracle8i, you had to have a rollback segment large enough (as suggested by your DBA) to be able to complete the longest possible transaction, and you had to make sure you had enough rollback segments to process several concurrent transactions. When a transaction is finished, a rollback segment can be reused for another transaction.

With Oracle9i and beyond, the system should be able to accomplish what it needs to without you having to add rollback segments, and it should be able to go as far back in time as the UNDO_RETENTION parameter specifies. When UNDO_RETENTION is equal to 900 (the default value), the undo retention time is 15 minutes. If the database is transaction-intensive (as is the case with Foglight®), Oracle9i creates a lot of rollback segments for each transaction on the undo table space. When the transaction is finished, it keeps the rollback segments to satisfy the UNDO_RETENTION value. To be able to serve new transactions, it creates new rollback segments. If the autoextend parameter is on, the UNDO_TABLESPACE keeps growing. In comparison with Oracle8i, if UNDO_RETENTION=20, the Oracle9i UNDO_TABLESPACE size has to be 20 times more than all rollback segments in Oracle8i, given the same transaction rate. It is possible to have a fixed UNDO_TABLESPACE size, like in Oracle8i. In that case, it has to initially be large enough to be able to process all transactions, and, at the same time, keep all used rollback segments to satisfy the UNDO_RETENTION. So, in our case, we need to reduce UNDO_RETENTION and estimate the transaction rate. Oracle recommends that you use the following formula:

UndoSpace = UR * UPS + overhead

where:

The original size of the UNDO_TABLESPACE on creation was a default of around 400MB. Oracle does not reuse the space on this tablespace, so it keeps growing. We only know of one way to reclaim the space, which is to create a new undo tablespace (CREATE UNDO TABLESPACE “UNDO2” datafile.....), issue the alter system command to point to the new tablespace (ALTER SYSTEM set undo_tablespace = UNDO2), and drop the original undo tablespace, including the datafile. You should not restrict the max size of the datafile, because you will get an ‘unable to extend’ error. It does not seem to be supported.

Example instructions (full set):

(for example, NEWUNDOTABLESPACENAME] = UNDOTBS2, [TABLESPACEDATAFILE] = /data02/oracle10g/oradata/FGL/undo0201.dbf, [OLDUNDOTABLESPACENAME] = UNDOTBS1 )