Tuning Database Performance

The database performance is highly dependent on the structure of queries you perform from your DirX Audit Manager or DirX Audit Manager Classic and DirX Audit Server and on the audit message types you have collected or the history entries you have synchronized. There is no general recommendation on indexes structure and so specific configuration should be performed individually.

Without any indexing, database performance will most likely be insufficient. Therefore, the DirX Audit installation provides some SQL scripts for index creation. These scripts are in the folder install_path/conf/sql/mssql/creation for SQL Server or install_path/conf/sql/oracle/creation for Oracle Database. These scripts contain the recommended default indexes. You should create additional or different indexes if there are performance problems with executing queries. The Configuration Wizard will create these default indexes if a new empty database is selected. If you use a database that already contains some tables (that is, from a previous version), the indexes are not created or updated automatically. You should follow the instructions in the DirX Audit Migration Guide and then check, create or update the indexes manually.

The rest of this chapter provides some useful hints on tuning database performance in your environment.

Tracing SQL Queries in DirX Audit Manager Classic

If you need to trace performed SQL queries in Audit analysis in DirX Audit Manager Classic, follow these steps:

  • Uncomment the following section in the install_path\web\audit-manager-classic.war\WEB-INF\classes\log4j2.properties configuration file:

    logger.org-hibernate-sql.name = org.hibernate.SQL
    logger.org-hibernate-sql.level = DEBUG

  • Restart the Apache Tomcat service. Now you can search for performed-SQL-query-logged events in the tomcat_install_path\logs\dirxaudit-manager log file. Run the Search operation with your DirX Audit Manager Classic in Audit analysis. The log records look like this:

2020-07-01 11:24:50,914 [http-nio-8080-exec-3] DEBUG org.hibernate.SQL- WITH query AS (select count(auditevent0_.DAT_AUDITEVENTS_ID) as col_0_0_, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__ from DAT_AUDITEVENTS auditevent0_) SELECT * FROM query WHERE __hibernate_row_nr__ >= ? AND __hibernate_row_nr__ < ?

2020-07-01 11:24:52,789 [http-nio-8080-exec-7] DEBUG org.hibernate.SQL- WITH query AS (select auditevent0_.DAT_AUDITEVENTS_ID as DAT1_234_, auditevent0_.DAT_AUDITMESSAGES_ID as DAT5_234_, auditevent0_.DETAIL as DETAIL234_, auditevent0_.OP as OP234_, auditevent0_.TYPE as TYPE234_, ROW_NUMBER() OVER (order by auditmessa1_.IDENTIFICATION_WHEN DESC) as __hibernate_row_nr__ from DAT_AUDITEVENTS auditevent0_ inner join DAT_AUDITMESSAGES auditmessa1_ on auditevent0_.DAT_AUDITMESSAGES_ID=auditmessa1_.DAT_AUDITMESSAGES_ID ) SELECT * FROM query WHERE __hibernate_row_nr__ >= ? AND __hibernate_row_nr__ < ?

Some queries are available in quasi SQL in the DirX Audit installation folder. You can adapt these queries to SQL and further analyze them.

  • Data processing
    install_path\conf\defaults\sql\

  • Report definitions
    install_path\conf\defaults\report-definitions\

If you are considering optimizing data processing performance, please contact the DirX support unit to get a reference on the SQL queries that relate to the feature you are optimizing.

Tuning the SQL Server

The SQL Server provides several administration tools that can help you with tuning DirX Audit Database performance:

  • SQL Server Management Studio

  • SQL Server Profiler

  • Database Engine Tuning Advisor

To get advice on statistics and indexes, perform the following steps:

  1. Open SQL Server Profiler.

  2. Create new trace and log in to your database server. The new trace window is open.

  3. Start the selected trace.

  4. Open SQL Server Management Studio and log in to your database server.

  5. Create a new query based on the statement you caught with the logger. Use your audit messages database for performing the query.

  6. Execute the query.

  7. When the query is finished, return to the SQL Server Profiler. The performed database activities should be traced. Stop the selected trace and then save the collected data into a trace file.

  8. Open the Database Engine Tuning Advisor and then create a new session. Provide the session name, your trace file path as the workload file, the database for analyses and the database to be tuned.

  9. Start Analysis.

  10. When the analysis is finished, you can view recommendation advised by the tool on the Recommendations tab.

  11. Get the SQL script for each recommendation you want to apply in the Definition field.

  12. Run a batch of the SQL statements in the SQL Server Management Studio to apply the recommendations.

Tuning the Oracle Database

Oracle Database contains several tuning methods accessible in various database tools that can be used to determine the performance problems and perform database tuning. The relevant Oracle Database tools are:

  • Oracle SQL Plus – the “old fashioned” command line interface that can be used for execution of SQL commands and SQL scripts.

  • Oracle SQL Developer – a PL/SQL development environment that includes tools for PL/SQL development (editor, compiler, debugger) as well as tools for database administration and tuning (execution plan, autotrace, PL/SQL advisor).

  • Oracle Enterprise Manager – the Oracle web portal designed for database administration.

For tuning purposes, you need to log in with DBA privileges (for example, user SYSTEM).

Database performance bottlenecks cannot be fully predicted as the performance depends on many factors and exact data distribution.

Response time or high resource needs typically indicate the performance problems.

Detecting High-Load Queries

Typical tuning consists of the following steps:

  1. Identifying a high-load query. The Top-SQL page in the Oracle Enterprise Manager contains information about the most resource-consuming SQL statements in the system.

  2. Analyzing the query. The main methods here for performance analysis and tuning are SQL execution plans, SQL Advisor reports and application tracing. The next sections provide more information about these methods.

  3. Taking corrective action.

Generating an Execution Plan

You can obtain an execution plan for any SQL statement even without the execution of the statement. The execution plan depends on database statistics used by the Oracle cost-based optimizer (CBO). The statistics must be up to date and they are typically calculated by a batch process. Statistics can be gathered, calculated, or estimated for object, schema or complete database instances.

An execution plan describes the strategy of the optimizer and often can identify the performance problems, typically full table scans. The execution plan can be obtained from a lot of DB tools; for example, SQL*Plus. Command set auto-trace on is the easiest way to turn on execution plan generation. Execution plan is displayed together with optimizer predicates and execution statistics.

The execution plan feature is also available in the Oracle SQL Developer.

Using SQL Advisor

SQL Advisor is an Oracle tool that analyzes the given SQL statement and creates a detailed report with recommendations for performance improvements such as query optimization, additional index creation and so on.

SQL Advisor reports can be obtained, for example, from the Oracle SQL Developer.

Generating SQL Trace Information

You can generate an application trace for an entire session. The resulting trace file includes a detailed report on SQL statements. Tracing must be enabled or disabled with the alter session command. The appropriate trace file is generated on the database server. The tkprof utility must be used to format the trace file. The trace file location is stored in the database parameter dump.

To obtain the SQL trace information, follow these steps:

In SQL*Plus, perform the following steps:

  • Run show parameter dump – the user_dump_dest parameter is the location of the trace files.

  • Enable tracing with alter session set sql_trace=true.

  • Execute the SQL statement(s).

  • Disable tracing with alter session set sql_trace=false.

    The command sequence could look like this:

conn SYS/_password_@_host:port_/orclpdb as SYSDBA;
show parameter dump;
alter session set sql_trace=true;
select _IDENTIFICATION_UID from DXT_DATA.DAT_AUDITMESSAGES_;
alter session set sql_trace=false;
disc;

  • Run disc – this action disconnects the current session and closes trace file.

In the database server operating system, perform the following steps:

  • Locate the trace and the appropriate *.trc file.

  • Run the tkprof utility and create an output text file.

  • Check the results.

The information about one statement is organized in a table. The meaning of the sections is as follows:

Parse – translates the SQL statement into an execution plan.

Execute – executes the statement and modifies data (INSERT, UPDATE, DELETE).

Fetch – retrieves the row returned (only for SELECT).

Updating Database Indexes

Fragmented indexes can cause low performance because additional input and output operations are required to locate data. Depending on the degree of fragmentation, reorganizing or rebuilding the index should be considered. Index reorganization should be applied for a lower degree of fragmentation. The process physically reorganizes nodes of the index. Index rebuild should be applied for a high degree of fragmentation. The process drops the existing index and recreates it.

If you wish to reorganize an index, you can use the following command:

  • For SQL Server:
    alter index index_name on table_name reorganize;

  • For Oracle Database:
    alter index index_name coalesce nologging;

If you wish to rebuild an index, you can use the following command:

  • For SQL Server:
    alter index index_name on table_name rebuild;

  • For Oracle Database:
    alter index index_name rebuild online nologging;`

Besides indexes, statistics is another structure that needs to be up to date for optimal performance. The query optimizer uses statistics to prepare a cost-effective execution plan. Statistics helps the query optimizer to estimate number of rows. Outdated statistics may mislead the query optimizer to choose costly operations.

If you wish to update statistics, you can use the following command:

  • For SQL Server:
    exec sp_updatestats;

  • For Oracle Database:
    exec DBMS_STATS.GATHER_SCHEMA_STATS(null);

For the complete syntax of all commands, see the database server documentation.

A sample quasi SQL script for rebuilding indexes and updating statistics for History DB is located in the DirX Audit installation folder:

install_path\conf\defaults\sql\common\adm\rebuild_history_indexes.txt