Oracle Database - EcoSys - Help - Hexagon PPM

EcoSys Performance Tuning

Language
English
Product
EcoSys
Search by Category
Help
EcoSys Version
8.7

Oracle Database Processes

Set the Oracle processes (the number of concurrent tasks executing) at least as high as the number of database connections in the application server’s database connection pool.

  • Run query while logged in as SYS or SYSTEM:

    SHOW PARAMETER PROCESSES;

  • You can export all parameters to a file for additional analysis:

    SET LINESIZE 500

    COLUMN name FORMAT A50

    COLUMN value FORMAT A60

    SELECT p.name,

    p.value,

    p.isdefault "Default"

    FROM v$parameter p

    ORDER BY p.name;

Verify the following nominal processes:

  • Processes roughly corresponds to the number of concurrent sessions that the database will support including the typical set of Oracle background processes. The recommended minimum is 2,000. The database will need to be bounced when modifying this parameter.

  • Open_cursors specifies the number of cursors a session can have open at any one time. The recommended value is 1,500.

Perform Regular Oracle Maintenance

Analyze statistics off peak hours on a weekly basis or after a bulk insert or delete. You can query when tables were last analyzed by running the following as SYS or SYSTEM.

select table_name, monitoring, num_rows,

to_char(last_analyzed, 'HH24-mi-ss') ANALYZED

from dba_tables where owner = 'FMADMIN' order by 1;

Update statistics for EcoSys schema objects

Analyze statistics off peak hours on a weekly basis or after a bulk insert or delete.

EXEC DBMS_STATS.gather_schema_stats (ownname => 'FMADMIN',
cascade =>true,estimate_percent => NULL);

Identifying/resolving chained rows

Row chaining occurs when a row is too large to fit into an empty data block. As a result, Oracle stores the DATA for the row in a CHAIN of one or more Data BLOCKS. Below are the steps required to address this issue.

  1. Create chained_row table.

    $ORACLE_HOME\rdbms\admin\utlchain.sql

  2. Truncate chained_row table from Step 1.

    truncate table chained_rows.

  3. Analyze tables for chained count

    SELECT 'ANALYZE TABLE '||table_name||' LIST CHAINED ROWS INTO CHAINED_ROWS;'
    FROM DBA_TABLES WHERE OWNER = 'FMADMIN';

  4. List tables with chained rows.

    SELECT owner_name, table_name, count(head_rowid) row_count
    FROM chained_rows
    GROUP BY owner_name,table_name;

  5. Alter table move increase PCTFREE for tables returned in Step 4.

    ALTER TABLE Table_Name MOVE PCTFREE 20;

  6. Rebuild indexes.

    select 'alter index '||owner||'.'||index_name||' rebuild online ;' from dba_indexes
    where status = 'UNUSABLE' and owner = 'FMADMIN';

  7. Truncate chained_row table.

    ANALYZE TABLE FMADMIN.Table_Name LIST CHAINED ROWS INTO CHAINED_ROWS;

  8. List tables with chained rows.

    SELECT owner_name, table_name, count(head_rowid) row_count
    FROM chained_rows
    GROUP BY owner_name,table_name;

Optimizing Oracle Configuration

To implement the recommended database configuration:

  1. Execute the compatible set_db_params script of the Oracle database version from \EcoSys\database\oracle\setup directory

    For example, if the Oracle database version is 12.2, execute fm_or_12_2_set_db_params.sql script.

  2. Restart the database.

Enabling automatic memory management

The simplest way to manage instance memory is to allow the Oracle Database instance to automatically manage and tune it for you. After it is enabled, the instance tunes to the target memory size, redistributing memory as needed between the system global area (SGA) and the instance program global area (instance PGA).

Automatic Workload Repository (AWR)

AWR is used to collect performance statistics including wait events used to identify performance problems and along with object usage statistics.

Automatic Database Diagnostic Monitor (ADDM)

The Automatic Database Diagnostic Monitor (ADDM) analyzes the AWR data on a regular basis, then locates the root causes of performance problems and provides recommendations.

Optimizing Oracle Table Spaces and Partitioning

The main criteria for determining which tables will be partitioned or moved to a separate table space , will be the size of the table in bytes. Generally, tables above 1G should be considered and attempted first, as these should give the greatest gains.

Optimizing Storage for Oracle

Regardless of whether you use operating system striping or manual I/O distribution, if the I/O system or I/O layout is not able to support the I/O rate required, then you need to separate files with high I/O rates from the remaining files. You can identify such files either at the planning stage or after the system is live.

The decision to segregate files should only be driven by I/O rates, recoverability concerns, or manageability issues. Before segregating files, verify that the bottleneck is truly an I/O issue. The data produced from investigating the bottleneck identifies which files have the highest I/O rates.

Examine disk access to database files through the dynamic performance view V$FILESTAT. This view shows the following information for database I/O (but not for log file I/O):

  • Number of physical reads and writes

  • Number of blocks read and written

  • Total I/O time for reads and writes

Calculate I/O timing values for data files by running the following query. We recommended that you limit this to 10 milliseconds for a disk read.

col name format a65

col READTIM/PHYRDS heading 'Avg|Read Time' format 9,999.999

col WRITETIM/PHYWRTS heading 'Avg|Write Time' format 9,999.999

set lines 132 pages 45

start title132 'IO Timing Analysis'

spool C:\Scripts\EcoSys\io_time.txt

select f.FILE# ,d.name,PHYRDS,PHYWRTS,READTIM/PHYRDS,WRITETIM/PHYWRTS

from v$filestat f, v$datafile d

where f.file#=d.file#

and phyrds>0 and phywrts>0

union

select a.FILE# ,b.name,PHYRDS,PHYWRTS,READTIM/PHYRDS,WRITETIM/PHYWRTS

from v$tempstat a, v$tempfile b

where a.file#=b.file#

and phyrds>0 and phywrts>0

order by 5 desc;

Disk configuration:

For optimum performance and assurance against disk failure, use hardware-based RAID1+0 with multi-read capability (the ability to read/write from any disk in the array that is not busy). To minimize disks purchased but maximize protection from failure, use RAID5.

RAID

Type of Raid

Control File

Database File

Redo Log File

Archive Log File

0

Striping

Avoid

OK

Avoid

Avoid

1

Shadowing

Best

OK

Best

Best

1+0

Striping and shadowing

OK

Best

Best

Best

3

Striping with static parity

OK

OK

Avoid

Avoid

5

Striping with rotating parity

OK

Best if RAID0+1 not available

Avoid

Avoid

Oracle recommends a middle-of-the-road stripe size of 1 megabyte (1M). This size is large enough to make full-table scans efficient but is a small enough stripe size to prevent hot disk problems.

Recommendation is RAID1+0, that is, striped and mirrored. This provides nearly all of the dependability of RAID5 and gives much better write performance. Usually, you take at least a 20 percent write performance hit using RAID5. For read-only applications, RAID5 is a good choice. However, in high-transaction/high-performance environments the write penalties may be too high.