The Database Server is responsible for the storage and management of data. There are a number of practices that sites find useful for maintaining the health of the Database Server.
7 In laymans terms, older cached entries that are not reused are removed from the cache automatically to
Regularly Calculate Database Statistics
Database statistics are important for the performance of all SQL in the product. Keeping them up to date ensures the database has the most up to date information to make the appropriate access path decisions.
When any table in the system grows (or shrinks) by a larger than normal rate, the access paths to that table may change causing inefficiencies. For the database to make the correct decision, it uses a set of statistics to assess all available paths. This is an important factor in performance. It is therefore recommended that database statistics be recalculated, using dbmsstats, on a regular basis to
maintain up to date statistics.
The frequency will depend on the volume and size of your database. It is recommended that statistics most tables be calculated once a week at minimum unless their growth factors do not affect the path chosen by the DBMS.
Note: CISADM is used as an example in the guidelines below. If your site uses another schema owner, then substitute that owner in the examples below.
The following guidelines can be used to assist:
• It is possible to check what is the Last Analyzed Date on product tables are current (or not) by running the following SQL.
SELECT table_name, last_analyzed FROM dba_tables WHERE owner = 'CISADM';
• It is possible to check check what is the Last Analyzed Date on indexes are current (or not) by running the following SQL.
SELECT index_name, last_analyzed FROM dba_indexes WHERE owner = 'CISADM';
• If the Indexes are older by a week or more than consider gathering Statistics on them. You can also use the below SQL which tells approximate number of INSERTs, UPDATEs, and DELETEs for that table, as well as whether the table has been truncated, since the last time statistics were gathered.
SELECT * FROM USER_TAB_MODIFICATIONS;
Note: The MONITORING attribute must be set on individual objects to use this facility.
• It is recommended to gather statistics while no active purging activities are occurring on the database.
• It is recommended to use the dbms_stats package for collecting statistics. An estimate
percentage of 10 percent is generally sufficient. Set the degree parameter to a higher level to enable parallel collection of statistics. It is suggested to set the block_sample parameter to false.
• The Method option while gathering statistics on tables should be set to FOR ALL COLUMNS SIZE AUTO. This will make sure that Oracle automatically determines which
columns require histograms and the number of buckets (size) of each histogram.
• Gathering statistics separately for indexes is generally faster than the cascade=true
option while gathering table statistics. • It is recommended to not
• Depending on the stability of the query performance, it is suggested that the statistics collection frequency can be altered to maintain query performance.
collect statistics on all the tables at a single batch run at a single point of time. Dividing the tables into multiple groups and then executing statistics calculation for each group at different time frames will minimize any disruption due to statistics calculation.
A discussion on the statistics calculation is also discussed in Performance Troubleshooting Guide.
Ensure I/O is spread evenly across available devices
Ensuring I/O across devices is becoming less of a problem with progressive versions of the database handling this automatically and the introduction of intelligent SAN technology.
One of the key practices that are key to performance of a database is the elimination of hot spots in the disk architecture by ensuring that I/O is spread across all available devices. This is known as the Database Topology. For example, placing the database physical files on a single disk is not optimal as multiple concurrent requests queue to use the disk and would result in higher than expected disk wait times. By spreading the load across disks, the opportunity for wait times is minimized and increases throughput. It is therefore recommended that the disk architecture be designed for the physical database files so that as much I/O as possible is spread across all disks.
A discussion on the database topology and its implications is outlined in Performance Troubleshooting Guide.
Use the Correct NLS settings (Oracle)
One of the configuration settings that can affect the sorting and processing of date data is setting the correct language for the connection to the database. For Oracle customers it is recommended that the NLS setting is correct for your region is set correctly at installation time. Refer to the NLS documentation (Globalization Support Guide) for Oracle for details of the valid settings for your region.
Note: Additionally for UTF8 customers ensure that the spl.runtime.cobol.encoding in the spl.properties file, is set correctly to display the correct character set.
Monitoring database connections
Note: This facility is generally available for customers using Oracle Utilities Application Framework V4.0 and above. Facets of this are also available for customers using Oracle Utilities Application Framework V2.2 using Patch 10215923.
By default, the product uses a common pool managed database user for accessing the database. Whilst in Oracle Utilities Application Framework V4.0 and above, it was possible to use a different user per access method (online, batch, etc) it is limited to a single user per access method. This can be limiting when trying to track individual sessions at the database level as the connections can be difficult to distinguish.
It is possible to track individual connections using two attributes of the v$session system view:
• CLIENT_IDENTIFIER – In Oracle Utilities Application Framework V4.0 and
above, the application user used for the duration of the transaction is now placed in the
CLIENT_IDENTIFIER for the duration of the transaction using the connection. For
compatibility purposes, the short userid is placed in this column (not the Login Id). If the connection is idle, the column is blank.
• MODULE – In Oracle Utilities Application Framework V2.2 and above, the module
that is executing and using the database connection is now populated in the MODULE field of v$session. If the connection is active, the MODULE will contain the text TUGBU Idle to
denote it as an idle connection using by the product.
Note: To use the MODULE feature the hibernate.connection.release_mode must be
set to on_close in the hibernate.properties file.
Consider changing Bit Map Tree parameter
Some sites have reported that in Oracle Database 10g and above, the default for the hidden oracle parameter _b_tree_bitmap_plans changed from false to true.
Setting the parameter to true enables bitmap plans to be generated for tables with only B-Tree indexes. The Cost Based Optimizer can choose to use bitmap access paths without the existence of bitmap indexes and in order to do so; it uses BITMAP CONVERSION FROM ROWIDS and BITMAP CONVERSION TO ROWIDS operations. Those operations are CPU intensive. If a query in the product for which those operations are performed selects a small number of rows, then there should not be much of an impact. However, if those queries select a large number of rows, there may be a negative impact on performance. In order to prevent issues, if you are facing any such issues, this parameter should be explicitly set to false either at the database level.
OraGenSec command line Parameters
Most sites use the OraGenSec utility in interactive mode but there are command line options that
can be used for silent installation. The command line is as follows:
OraGenSec -d <Owner,OwnerPswd,DbName> -u <Database Users> -r <ReadRole,UserRole> -l <logfile> -h
Where:
-d <Owner,OwnerPswd,DbName> Database connect information for the target
database. e.g. spladm,spladm,DB200ODB. -u <Database Users> A comma-separated list of database users where
synonyms need to be created. e.g. spluser, splread
-r <ReadRole,UserRole> Optional. Names of database roles with read and
read-write privileges. Default roles are
SPL_READ, SPL_USER. e.g.
spl_read,spl_user -l <logfile> Optional. Name of the log file.
-h Help
This command line can be used in site specific DBA scripts or as a standalone command line. Executing the utility without any options starts interactive mode.
SetEnvId command line Parameters
Each environment at a site can have an unique environment identifier (if desired). This identifier is used by numerous utilities for environmental management such as ConfigLab or Archiving. While setting this value can be done as part of the installation process, it is also possible after installation. Typically, DBA's run the SetEnvId utility provided with the installation media to set the
environment identifier interactively. As with OraGenSec this can also be executed on the command line with various options to support silent installation. The command line is as follows:
SetEnvID -d <Owner,OwnerPswd,DbName> -r -u -q -l <logfile> -h
Where:
-d <Owner,OwnerPswd,DbName> Database connect information for the target
database. e.g. spladm,spladm,DB200ODB. -u Apply the generated SQL to the databases. By
default, the generated SQL is written to the log file only.
overwrite the existing environment identifiers
-q Silent Installation mode (no confirmations) -l <logfile> Optional. Name of the log file.
-h Help
This command line can be used in site specific DBA scripts or as a standalone command line. Executing the utility without any options starts interactive mode.
Building the Data Model
One of the common questions regarding product is the availability of the total data model in a particular tool (such as Oracle Data Modeler or similar). The product contains a large number of tables and it is generally impractical to display a full model due to its size (to make it legible).
There are a number of sources of information that can replace a full data model and present the data mode information into bite sized chunks:
• The data model information is contained in the Data Dictionary component of the Application Viewer.
• The Conversion documentation, available in Microsoft Word as well as online help contain a summary set of data models that basically outline the major entities in the product.
Note: Not all Oracle Utilities Application Framework products include a conversion capability
• Each of the Business Process manuals for the product outlines the functionality and contain data models specifically for that component.
Why is there no referential integrity built into the database?
Note: The scripts in this section have been designed for Oracle database only. Sites using DB2 or SQL Server should use the language equivalents of these scripts.
Typically referential integrity of a database is managed by the database itself. In product this is not so as the Maintenance Objects contain ALL the business logic including referential integrity. The reasons for this are varied:
• From a maintenance cost point of view, all the code is in one place. This reduces maintenance effort.
• Databases implement all or nothing referential integrity. This means that referential integrity is checked whether the data has changed or not. From a performance point of view this is potentially wasting time. The Maintenance Objects in product decide when to enforce referential integrity rules.
• Most of the referential rules in product are optional. If there is a value in the foreign key field it is checked, if there is no value (blanks, zero or nulls) then the referential integrity is not
checked unless it is a mandatory column. This is not possible in database imposed referential integrity.
• If the database controlled referential integrity then the application has no control on when it is imposed in the course of a transaction. Maintenance Object controlled referential integrity allows finer levels of control on when referential integrity is enforced in the transaction flow. • Each database implements referential integrity in a slightly different way. To reduce
maintenance costs, code differences are kept to a minimum.
• Maintenance Object enforced referential integrity is more efficient as far as product is concerned and translates to superior performance across many database types.
Building the Data Model
All is not lost though. The Oracle Utilities Application Framework maintains its own data dictionary in the form of meta-data that is used by the Oracle Utilities Software Development Kit, ConfigLab and Archiving.
If you insist that you want the data model in a tool or adorning a large wall then the following is recommended process to be used to generate the data model using the meta-data:
• Export the CISADM schema as a backup using the database export utility.
• Create constraints from the meta-data structure. The two Oracle pl/sql scripts below can be used to achieve this. The names of the constraints is already documented in the meta data as well. Run the utility and created the constraints in the database.
Function to join
create or replace function join ( p_cursor sys_refcursor, p_del varchar2 := ',' ) return varchar2 is l_value varchar2(32767); l_result varchar2(32767); begin loop
fetch p_cursor into l_value; exit when p_cursor%notfound; if l_result is not null then
l_result := l_result || p_del; end if;
l_result := l_result || l_value; end loop;
return l_result; end join;
/
Script to Create Constraints
SET serverout ON size 1000000 SET echo OFF
SET feedback OFF SET linesize 300 spool constraints.sql DECLARE CURSOR c1 IS SELECT tbl_name, CONST_ID, REF_CONST_ID, table_name FROM ci_MD_CONST, user_indexes WHERE CONST_TYPE_FLG='FK' AND TRIM(index_name)=SUBSTR(REF_CONST_ID,5,7) AND TRIM(tbl_name) IN
(SELECT TRIM(table_name) FROM user_tables ) ORDER BY tbl_name, CONST_ID; stmt VARCHAR2(400); field_list VARCHAR2(300); BEGIN FOR r1 IN c1 LOOP
stmt := 'alter table ' || trim(r1.tbl_name) || ' add constraint ' || trim(r1.const_id); dbms_output.put_line(stmt); SELECT JOIN(CURSOR (SELECT trim(fld_name) FROM ci_md_const_fld
WHERE const_id = r1.const_id ORDER BY seq_num
))
INTO field_list FROM dual;
stmt := 'foreign key (' || field_list || ')'; dbms_output.put_line(stmt);
SELECT JOIN(CURSOR
(SELECT trim(fld_name) FROM ci_md_const_fld
WHERE const_id = r1.ref_const_id ORDER BY seq_num )) INTO field_list FROM dual; stmt := 'references ' || trim(r1.table_name) || ' (' || field_list || ');'; dbms_output.put_line(stmt);
END; /
spool OFF; EXIT;
• Empty a copy of the database (truncated the tables). None of the relationships are expressed as constraints in the physical database; this is because ALL the referential integrity (RI) and validation is done in the code based Maintenance Objects. More importantly most of the constraints are data conditional (if there is data in the column, then RI applies; no value, no RI) so a loaded database might actually break "database strict" RI rules. Remove the data to prevent constraint violations using a valid method for the database (for example, TRUNCATE TABLE <tablename> REUSE STORAGE for Oracle et al).
• Run the constraints.sql file created in the previous step to create the RI using the CISADM user.
• Load the data model in the tool of your choice. Load the data model with the constraints in the desired tool. This should build the data model.
Note: This may take a while for the WHOLE data model.
• Removed the newly created constraints. This is to return the database back to the original condition.
set serverout on size 1000000 set echo off
set feedback off set linesize 300
spool drop_constraints.sql
select 'ALTER ' || tbl_name || ' drop constraint ' || CONST_ID || ';' from ci_MD_CONST where CONST_TYPE_FLG='FK' order by tbl_name, CONST_ID;
spool off;
@drop_constraints.sql exit;
• Reload the database. You then have the data model in your tool and the database returned to its original state.
Technical Best Practices June 2011
Author: Anthony Shorten, Principal Product Manager Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA 94065 U.S.A. Worldwide Inquiries: Phone: +1.650.506.7000 Fax: +1.650.506.7200 oracle.com
Copyright © 2007-2011, Oracle and/or its affiliates. All rights reserved. This document is provided for information purposes only and the contents hereof are subject to change without notice. This document is not warranted to be error-free, nor subject to any other warranties or conditions, whether expressed orally or implied in law, including implied warranties and conditions of merchantability or fitness for a particular purpose. We specifically disclaim any liability with respect to this document and no contractual obligations are formed either directly or indirectly by this document. This document may not be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without our prior written permission.
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. UNIX is a registered trademark licensed through X/Open Company, Ltd. 1010