Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Saturday, September 12, 2015

Configure the database environment to support optimal data access performance:


Configure the database environment to support optimal data access performance:

BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200, interval => 30, topnsql => 100, dbid => 3310949047);
END;
/

In this example, the retention period is specified as 43200 minutes (30 days), the interval between each snapshot is specified as 30 minutes, and the number of Top SQL to flush for each SQL criteria as 100. If NULL is specified, the existing value is preserved. The optional database identifier is 3310949047. If you do not specify a value for dbid, the local database identifier is used as the default value. You can check the current settings for your database instance with the DBA_HIST_WR_CONTROL view



ORA-00847: MEMORY_TARGET/MEMORY_MAX_TARGET and LOCK_SGA cannot be set together

You cannot configure lock_sga if you have configured AMM i.e memory_target for your database.

SHAIKDB>show parameter memory

NAME                    TYPE    VALUE
------------------------------------ ----------- ------------------------------
memory_max_target            big integer 452M
memory_target                big integer 452M



SHAIKDB>show parameter sga

NAME                    TYPE    VALUE
------------------------------------ ----------- ------------------------------
lock_sga                boolean    FALSE
pre_page_sga                boolean    FALSE
sga_max_size                big integer 452M
sga_target                big integer 0


SHAIKDB>alter system set lock_sga=true scope=spfile;

System altered.

SHAIKDB>shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SHAIKDB>startup
ORA-00847: MEMORY_TARGET/MEMORY_MAX_TARGET and LOCK_SGA cannot be set together


Either set lock_sga=false or memory_target=0


OPTIMIZER_DYNAMIC_SAMPLING:
In 11.2.0.4
optimizer automatically decides whether to gather dynamic statistics during table scans, index access, joins, and GROUP BY operations. The enhanced behavior is enabled only when the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter is set to the new value of 11


In-Memory Parallel Execution When using parallel query, you can configure the database to use the database buffer cache instead of performing direct reads into the PGA for a SQL statement. This configuration may be appropriate when database servers have a large amount of memory. Also, an Oracle Real Applications Cluster (Oracle RAC) database can aggregate the size of the buffer cache of all nodes, thereby caching larger objects and caching more queries.

Performance Tuning Guide:


The optimal size can be obtained by querying the OPTIMAL_LOGFILE_SIZE column from the V$INSTANCE_RECOVERY

SHAIKDB>alter system set fast_start_mttr_target=900;

System altered.



SHAIKDB>alter system archive log current;

System altered.

SHAIKDB>select optimal_logfile_size from v$instance_recovery;

OPTIMAL_LOGFILE_SIZE
--------------------
        1467


You should monitor temporary tablespace activity to check how many extents the database allocates for the temporary segment. If an application extensively uses temporary tables, as in a situation when many users are concurrently using temporary tables, then the extent size could be set smaller, such as 256K, because every usage requires at least one extent. The EXTENT MANAGEMENT LOCAL clause is optional for temporary tablespaces because all temporary tablespaces are created with locally managed extents of a uniform size. The default for SIZE is 1M.


V$DISPATCHER and V$DISPATCHER_RATE

If the current and average rates are significantly less than the maximums, then consider reducing the number of dispatchers. Conversely, if current and average rates are close to the maximums, then you might need to add more dispatchers. A general rule is to examine V$DISPATCHER_RATE statistics during both light and heavy system use periods

Steadily increasing wait times in the requests queue indicate contention for shared servers. To examine wait time data, use the dynamic performance view V$QUEUE


SHAIKDB>select * from v$queue;

PADDR        TYPE        QUEUED        WAIT     TOTALQ
---------------- ---------- ---------- ---------- ----------
00        COMMON         0        0      0
000000007B88F0D0 DISPATCHER        0        0      0

or

SHAIKDB>SELECT DECODE(TOTALQ, 0, 'No Requests',
WAIT/TOTALQ || ' HUNDREDTHS OF SECONDS') "AVERAGE WAIT TIME PER REQUESTS"
FROM V$QUEUE
WHERE TYPE = 'COMMON';  2    3    4

AVERAGE WAIT TIME PER REQUESTS
--------------------------------------------------------------
No Requests


You can also determine how many shared servers are currently running by issuing the following query:

SHAIKDB>SELECT COUNT(*) "Shared Server Processes" FROM V$SHARED_SERVER WHERE STATUS != 'QUIT';

Shared Server Processes
-----------------------
             1

SHAIKDB>show parameter shared_servers

NAME                    TYPE    VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers            integer
shared_servers                integer    1


Chapter-7 In Performance Guide:

Configuring and Using Memory:

Oracle Memory Caches The main Oracle Database memory caches that affect performance are:
■ Shared pool
■ Large pool
■ Java pool
■ Buffer cache
■ Streams pool size
■ Log buffer
■ Process-private memory, such as memory used for sorting and hash joins


Memory for the shared pool, large pool, java pool, and buffer cache is allocated in units of granules. The granule size is 4MB if the SGA size is less than 1GB. If the SGA size is greater than 1GB, the granule size changes to 16MB. The granule size is calculated and fixed when the instance starts up. The size does not change during the lifetime of the instance


SHAIKDB>col component for a30

SHAIKDB>show parameter target;

archive_lag_target            integer    0
db_flashback_retention_target        integer    1440
fast_start_io_target            integer    0
fast_start_mttr_target            integer    900
memory_max_target            big integer 452M
memory_target                big integer 300M
parallel_servers_target         integer    8
pga_aggregate_target            big integer 50M
sga_target                big integer 148M


SHAIKDB>select * from v$sga_dynamic_components;

COMPONENT              CURRENT_SIZE   MIN_SIZE    MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER GRANULE_SIZE
------------------------------ ------------ ---------- ---------- ------------------- ---------- ------------- --------- --------- ------------
shared pool             113246208  113246208    113246208            0          0 STATIC                 4194304
large pool                4194304    4194304     4194304            0          0 STATIC                 4194304
java pool                4194304    4194304     4194304            0          0 STATIC                 4194304
streams pool                 0        0        0            0          0 STATIC                 4194304
DEFAULT buffer cache          67108864   67108864    138412032            0           1 SHRINK        DEFERRED  10-SEP-15    4194304
KEEP buffer cache             0        0        0            0          0 STATIC                 4194304
RECYCLE buffer cache             0        0        0            0          0 STATIC                 4194304
DEFAULT 2K buffer cache          0        0        0            0          0 STATIC                 4194304
DEFAULT 4K buffer cache          0        0        0            0          0 STATIC                 4194304
DEFAULT 8K buffer cache          0        0        0            0          0 STATIC                 4194304
DEFAULT 16K buffer cache         0        0        0            0          0 STATIC                 4194304
DEFAULT 32K buffer cache         0        0        0            0          0 STATIC                 4194304
Shared IO Pool                 0        0        0            0          0 STATIC                 4194304
ASM Buffer Cache             0        0        0            0          0 STATIC                 4194304

14 rows selected.

You must then divide the resulting memory between the SGA and the PGA.

For OLTP systems, the PGA memory typically accounts for a small fraction of the total memory available (for example, 20%), leaving 80% for the SGA.

For DSS systems running large, memory-intensive queries, PGA memory can typically use up to 70% of that total (up to 2.2 GB in this example).
Good initial values for the parameter PGA_AGGREGATE_TARGET might be:
For OLTP: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%

For DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%


V$DB_CACHE_ADVICE, the parameter DB_CACHE_ADVICE should be set to ON, and a representative workload should be running on the instance. Allow the workload to stabilize before querying the V$DB_CACHE_ADVICE view.



COLUMN size_for_estimate FORMAT 999,999,999,999 heading 'Cache Size (MB)'
COLUMN buffers_for_estimate FORMAT 999,999,999 heading 'Buffers'
COLUMN estd_physical_read_factor FORMAT 999.90 heading 'Estd Phys|Read Factor'
COLUMN estd_physical_reads FORMAT 999,999,999 heading 'Estd Phys| Reads'

SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_ physical_reads FROM V$DB_CACHE_ADVICE WHERE name = 'DEFAULT' AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_ size') AND advice_status = 'ON';



The buffer pool hit ratio can be determined using the following formula

SHAIKDB>SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"
FROM V$BUFFER_POOL_STATISTICS;  

NAME            PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS  Hit Ratio
-------------------- -------------- ------------- --------------- ----------
DEFAULT              15138       121525       767633 .982974904


Determining Which Segments Have Many Buffers in the Pool

The V$BH view shows the data object ID of all blocks that currently reside in the SGA. To determine which segments have many buffers in the pool, you can use


SHAIKDB>
COLUMN OBJECT_NAME FORMAT A40
COLUMN NUMBER_OF_BLOCKS FORMAT 999,999,999,999

SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
FROM DBA_OBJECTS o, V$BH bh
WHERE o.DATA_OBJECT_ID = bh.OBJD
AND o.OWNER != 'SYS'
GROUP BY o.OBJECT_NAME
ORDER BY COUNT(*);

OBJECT_NAME                NUMBER_OF_BLOCKS
---------------------------------------- ----------------
BSLN_TIMEGROUPS_PK                   1
BSLN_BASELINES_PK2                   1
BSLN_BASELINES                       6


KEEP_POOL:

If there are certain segments in your application that are referenced frequently, then store the blocks from those segments in a separate cache called the KEEP buffer pool. Memory is allocated to the KEEP buffer pool by setting the parameter DB_KEEP_CACHE_ SIZE to the required size. The memory for the KEEP pool is not a subset of the default pool. Typical segments that can be kept are small reference tables that are used frequently

RECYCLE_POOL:
It is possible to configure a RECYCLE buffer pool for blocks belonging to those segments that you do not want to remain in memory. The RECYCLE pool is good for segments that are scanned rarely or are not referenced frequently.Memory is allocated to the RECYCLE buffer pool by setting the parameter DB_RECYCLE_ CACHE_SIZE to the required size. This memory for the RECYCLE buffer pool is not a subset of the default pool.

SHARED_POOL;
The main components of the shared pool are the library cache, the dictionary cache, and, depending on your configuration, the server result cache. The library cache stores the executable (parsed or compiled) form of recently referenced SQL and PL/SQL code. The dictionary cache stores data referenced from the data dictionary. The server result cache stores the results of queries and PL/SQL function results.

A cache miss on the data dictionary cache or library cache is more expensive than a miss on the buffer cache. For this reason, the shared pool should be sized to ensure that frequently used data is cached

Dictionary Cache
Information stored in the data dictionary cache includes usernames, segment information, profile data, tablespace information, and sequence numbers. The dictionary cache also stores descriptive information, or metadata, about schema objects. Oracle Database uses this metadata when parsing SQL cursors or during the compilation of PL/SQL programs.


Library Cache
The library cache holds executable forms of SQL cursors, PL/SQL programs, and Java classes. This section focuses on tuning as it relates to cursors, PL/SQL programs, and Java classes. These are collectively referred to as application code


Shared Cursors
Reuse of shared SQL for multiple users running the same application, avoids hard parsing. Soft parses provide a significant reduction in the use of resources such as the shared pool and library cache latches

V$LIBRARYCACHE
These statistics reflect all library cache activity after the most recent instance startup.

SHAIKDB>SELECT NAMESPACE, PINS, PINHITS, RELOADS, INVALIDATIONS
FROM V$LIBRARYCACHE
ORDER BY NAMESPACE;  2    3  

NAMESPACE                                  PINS    PINHITS      RELOADS INVALIDATIONS
---------------------------------------------------------------- ---------- ---------- ---------- -------------
BODY                                      1847      1620           16          0
CLUSTER                                   1447      1413       0          0
DBINSTANCE                                 0         0       0          0
DBLINK                                     0         0       0          0
DIRECTORY                                 2         0       0          0
EDITION                                143       117       0          0
INDEX                                      2057      1469           23          0
OBJECT ID                                 0         0       0          0
QUEUE                                     6         2       0          0
RULESET                                  3         2       0          0
SCHEMA                                     0         0       0          0
SQL AREA                                224818    204558         2303        426
SUBSCRIPTION                                 1         0       0          0
TABLE/PROCEDURE                              49248     35009         2887          0
TEMPORARY INDEX                            190         0           20          0
TEMPORARY TABLE                            533         0          389          0

16 rows selected.

To calculate the library cache hit ratio, use the following formula:
Library Cache Hit Ratio = sum(pinhits) / sum(pins)       
               
                   
Cache Recovery (Rolling Forward)
                   
During the cache recovery step, Oracle Database applies all committed and uncommitted changes in the redo log files to the affected data blocks. The work required for cache recovery processing is proportional to the rate of change to the database (update transactions each second) and the time between checkpoints.
                   
Transaction Recovery (Rolling Back)
                   
To make the database consistent, the changes that were not committed at the time of the crash must be undone (in other words, rolled back). During the transaction recovery step, Oracle Database applies the rollback segments to undo the uncommitted changes.       
                   
FAST_START_MTTR_TARGET (cache recovery)           
The Fast-Start Fault Recovery feature reduces the time required for cache recovery, and makes the recovery bounded and predictable by limiting the number of dirty buffers and the number of redo records generated between the most recent redo record and the last checkpoint

With the Fast-Start Fault Recovery feature, the FAST_START_MTTR_TARGET initialization parameter simplifies the configuration of recovery time from instance or system failure. FAST_START_MTTR_TARGET specifies a target for the expected mean time to recover (MTTR), that is, the time (in seconds) that it should take to start up the instance and perform cache recovery. After FAST_START_MTTR_TARGET is set, the database manages incremental checkpoint writes in an attempt to meet that target. If you have chosen a practical value for FAST_START_MTTR_TARGET, you can expect your database to recover, on average, in approximately the number of seconds you have chosen.   
               
You must disable or remove the FAST_START_IO_TARGET, LOG_CHECKPOINT_INTERVAL, and LOG_CHECKPOINT_TIMEOUT initialization parameters when using FAST_START_MTTR_TARGET. Setting these parameters interferes with the mechanisms used to manage cache recovery time to meet FAST_START_MTTR_TARGET
                                   
The maximum value for FAST_START_MTTR_TARGET is 3600 seconds (one hour). If you set the value to more than 3600, then Oracle Database rounds it to 3600.
                                   
           
               
                   
Reducing Checkpoint Frequency to Optimize Run-Time Performance
                   
To reduce the checkpoint frequency and optimize run-time performance, you can do the following:
               
                           
  • Set the value of FAST_START_MTTR_TARGET to 3600. This enables Fast-Start checkpointing and the Fast-Start Fault Recovery feature, but minimizes its effect on run-time performance while avoiding the need for performance tuning of FAST_START_MTTR_TARGET.                       
           
  • Size your online redo log files according to the amount of redo your system generates. Try to switch logs at most every twenty minutes. Having your log files too small can increase checkpoint activity and reduce performance. Also note that all redo log files should be the same size.       
                                   
  • As part of the ongoing monitoring of your database, you can periodically compare V$INSTANCE_RECOVERY.TARGET_MTTR to your FAST_START_MTTR_TARGET. The two values should generally be the same if the FAST_START_MTTR_TARGET value is in the practical range. If TARGET_MTTR is consistently longer than FAST_START_MTTR_TARGET, then set FAST_START_MTTR_TARGET to a value no less than TARGET_MTTR. If TARGET_MTTR is consistently shorter, then set FAST_START_MTTR_TARGET to a value no greater than TARGET_MTTR.
  •                        
           
       
   
SHAIKDB>SELECT TARGET_MTTR, ESTIMATED_MTTR FROM V$INSTANCE_RECOVERY;

TARGET_MTTR ESTIMATED_MTTR
----------- --------------
    36        9


SHAIKDB>select * from V$MTTR_TARGET_ADVICE;

MTTR_TARGET_FOR_ESTIMATE ADVIC DIRTY_LIMIT ESTD_CACHE_WRITES ESTD_CACHE_WRITE_FACTOR ESTD_TOTAL_WRITES ESTD_TOTAL_WRITE_FACTOR ESTD_TOTAL_IOS ESTD_TOTAL_IO_FACTOR
------------------------ ----- ----------- ----------------- ----------------------- ----------------- ----------------------- -------------- --------------------
            29 ON          9528           14969               1        16125                1       47074            1
            35 ON         12518           14969               1        16125                1       47074            1
            11 ON          1098           17167             1.1468        18323           1.1363       49272           1.0467
            17 ON          3947           14969               1        16125                1       47074            1
            23 ON          6738           14969               1        16125                1       47074            1

No comments: