Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle Database Performance Optimization of memory disk     - Fedora 22 how to play rmvb mp4 mkv video files (Linux)

- Cobbler batch install Ubuntu / CentOS system (Linux)

- Linux Network Programming - non-blocking program (Programming)

- Install VLC player on Ubuntu 14.10 (Linux)

- About Samba certification process and permissions (Linux)

- GNU Linux system variables (sysctl configuration commands) integrated use (Linux)

- Workaround CentOS error message during compilation PHP5 common (Linux)

- Install and configure GO 1.2.1 under CentOS 6.5 (Linux)

- Household use Linux Security (Linux)

- OpenSSH server configuration file for each Common Definition (Server)

- 256 with rich colors decorate your terminal (Linux)

- Linux and SELinux Exploration Program Manager (Linux)

- Oracle 10046 Event (Database)

- CentOS 5.8 (64) Python 2.7.5 installation error resolved (Linux)

- Android custom ViewPager create kaleidoscopic image transition effects (Programming)

- IIS virtual host of safety knowledge (Linux)

- Oracle 11.2.0.3 and MySQL5.6 DDL comparison (Database)

- Use SVN to automatically deploy code under Apache (Server)

- Use in Linux ipmitool tool (Linux)

- Ubuntu How to install screen recording tool Simple Screen Recorder 0.3.1 (Linux)

 
         
  Oracle Database Performance Optimization of memory disk
     
  Add Date : 2018-11-21      
         
       
         
  1, Memory structure optimization overview
1.1 Buffer
Affect database performance include buffers that can be shared and server processes SGA private pga two categories, which also includes sga shared pool, large pool, java pool, the data buffer, the flow cell, redo log buffer.

1.2 Automatic Memory Management
Oracle generally use automatic memory management, memory management system, automatically manage and adjust the oracle database instance memory size. In automatic management mode, first initialization parameters MEMORY_TARGET (target memory size) and MEMORY_MAX_TARGET (the maximum memory size) can be configured to adjust the target database memory size, and constantly re-adjust the size of the buffer sga and pga needed.

1.3 automatically manages the shared memory sga
If the database instance is started when the dynamic initialization parameter SGA_TARGET to 0, Automatic Shared Memory Management is prohibited. The size of each buffer by the initialization parameter file to determine the appropriate parameters. If necessary, you can manually set the initialization parameter DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, STREAMS_POOL_SIZE, to adjust the size of each shared memory buffer area. Usually there are some memory components usually requires the administrator to manually set the size of the impact is not automatic memory management, such as the redo log buffer; KEEP, RECYCLE buffer and based on non-standard data blocks; Fixed SGA and other internal memory allocated Area. Manually adjust these memory areas when the initialization parameters need to be set are DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, DB_nK_CACHE_SIZE, LOG_BUFFER, these memory areas are allocated memory from SGA_TARGET get specified, the remaining portion of SGA_TARGET automatically for each shared buffer memory management use.

1.4 dynamically change the size of the buffer
If the system does not use automatic memory management or automatic shared memory memory management functions, you can use the ALTER SYSTEM statement initializes the configuration parameters DB_ACHE_SIZE, JAVA_POOL_SIZE, LARGE_POOL_SIZE, LOG_BUFFER, SHARED_POOL_SIZE like.

2, adjust the data buffer
By the following two ways to check the data buffer set is reasonable:

See the database buffer hit ratio, run:

select 1 - ((physical.value - direct.value -lobs.value) / logical.value)

"Buffer Cache Hit Ratio"

from v $ sysstat physical, v $ sysstat direct, v $ sysstat lobs, v $ sysstatlogical

where physical.name = 'physical reads'

anddirect.name = 'physical reads direct'

andlobs.name = 'physical reads direct (lob)'

andlogical.name = 'session logical reads';

The result:

SQL> select 1 - ((physical.value - direct.value - lobs.value) /logical.value)

2 "Buffer Cache HitRatio"

3 from v $ sysstatphysical, v $ sysstat direct, v $ sysstat lobs, v $ sysstat logical

4 where physical.name = 'physical reads'

5 and direct.name = 'physicalreads direct'

6 and lobs.name = 'physical readsdirect (lob)'

7 and logical.name = 'sessionlogical reads';

Buffer Cache Hit Ratio

----------------------

  0.958117758479709

SQL>

Practice, the above statement when> 0.9 real description of adjustment sufficient. Hit rate is high, there has been 0.95, so the hit rate is quite substantial. For database systems, DEFAULT buffer pool data buffer included sufficient application needs.

Query data buffer logical reads and physical reads information for each buffer, you can query the dynamic performance view v $ buffer_pool_statistics, for example:

SQL> selectt1.name, t1.PHYSICAL_READS, t1.DB_BLOCK_GETS, t1.CONSISTENT_GETS, 1- (t1.PHYSICAL_READS / (t1.DB_BLOCK_GETS + t1.CONSISTENT_GETS)) "Hit Ratio"

2 from v $ buffer_pool_statisticst1

3;

NAME PHYSICAL_READS DB_BLOCK_GETSCONSISTENT_GETS Hit Ratio

-------------------- --------------------------- --- ------------ ----------

DEFAULT 44480354387 571096618 33595740186 -0.3018575

SQL>

Adjust the data buffer size

If the data buffer is less than 90%, you should consider adjusting the data buffer size. Before increasing the data buffer, take a look at V $ DB_CACHE_SIZE (select * from v $ db_cache_advice), determines how much data buffer is increased to be able to significantly reduce the number of physical I / O, and then dynamically modify the initialization parameters DB_CACHE_SIZE. DB_CACHE_SIZE initialization parameters corresponding to the standard data buffer size, if you want to modify nonstandard data buffer size, you need to modify the initialization parameters DB_nK_CACHE_SIZE (n value of 2,4,8,16,32, and n can not be a standard data block size)

3, shared pool adjustment
3.1 library cache hit ratio
You can query the dynamic performance view v $ librarycache (select * from v $ librarycache;), this view holds statistics since the database last started library cache activity, each row reflect library cache an entry type of statistics, by namespace column values to identify each type of entry. E.g:

selectt1.NAMESPACE, t1.PINS, t1.PINHITS, t1.RELOADS, t1.INVALIDATIONS from v $ librarycachet1 order by t1.NAMESPACE;

SQL> selectt1.NAMESPACE, t1.PINS, t1.PINHITS, t1.RELOADS, t1.INVALIDATIONS from v $ librarycachet1 order by t1.NAMESPACE;

NAMESPACE PINS PINHITS RELOADS INVALIDATIONS

-------------------------------------------------- ------------------------ ---------- ---------- ------ -------

APP CONTEXT 2 1 0 0

BODY 28468616 28458242 1818 46

CLUSTER 114902 114198 0 0

DBINSTANCE 0 0 0 0

DBLINK 0 0 0 0

DIRECTORY 371 81 0 0

EDITION 10577860 10577856 0 0

INDEX 403451 315690 11194 0

JAVA DATA 3161 1876 0 0

JAVA RESOURCE 3413 2124 0 0

JAVA SOURCE 3413 2121 0 0

OBJECT ID 0 0 0 0

PIPE 2940 2936 0 0

QUEUE 755296 754776 218 0

RULE 732 365 82 0

RULESET 18358 17693 26 0

SCHEMA 0 0 0 0

SQL AREA 1252576540 1223849814 1430167 926624

SUBSCRIPTION 50182 49948 6 0

TABLE / PROCEDURE 162038767 161228629 429539 263

NAMESPACE PINS PINHITS RELOADS INVALIDATIONS

-------------------------------------------------- ------------------------ ---------- ---------- ------ -------

TEMPORARY INDEX 25602 0 7514 0

TEMPORARY TABLE 70046 0 55020 0

TRIGGER 12753767 12747803 1146 0

XDB CONFIG 1 0 0 0

XML SCHEMA 27 6 4 0

25 rows selected

SQL>

PINS: NAMESPACE specific target number of requests

PINHITS: NAMESPACE specific target number of requests, the number exists in the library cache

RELOADS: the number of objects need to be loaded from disk

INVALIDATIONS: for a particular namespace, due to the dependence of the change in the object is identified as the number of failed object.

Library cache hit ratio is calculated (sum (t1.PINHITS) / sum (pins) calculation), for example:

SQL> select sum (t1.PINHITS) / sum (pins) "Lib Ratio" from v $ librarycache t1;

LibRatio

----------

0.97973692

SQL>

You can also check the dynamic performance view v $ sgastat shared pool to obtain the number of free memory. E.g:

SQL> select t1 * from v $ sgastat t1 wheret1.NAME = 'free money' and pool = 'shared pool'.;

POOL NAME BYTES

------------ ------------

SQL>

3.2 Data Dictionary cache hit ratio
Typically, if sufficient space shared pool library cache, the data dictionary cache will be sufficient, in some cases, the data buffer will fail, such as the database instance has just started, the database dictionary cache does not contain any data, any sql statement the execution will result in the data dictionary cache misses, and with the increase in the data dictionary cache, cache misses will be less, and finally reach a stable hit rate, then the data dictionary cache failure rate will be very low.

You can query the database dynamic performance view v $ rowcache (select * from v $ rowcache;) to obtain information about different types of statistical data dictionary entry. Here the response data from the database since the last start of the dictionary usage.

SQL> selectt1.PARAMETER, sum (t1.GETS), sum (t1.GETMISSES), 100 * sum (t1.GETS-t1.GETMISSES) / sum (t1.GETS) pct_succ_gets, sum (t1.MODIFICATIONS) updates from v ! $ rowcache t1 where t1.GETS = 0group by t1.PARAMETER;

PARAMETER SUM (T1.GETS) SUM (T1.GETMISSES) PCT_SUCC_GETS UPDATES

-------------------------------------------- ------ ----------- ------------- ----------

dc_constraints 39950 16050 59.8247809762 39942

qmc_app_cache_entries 1 1 0 0

sch_lj_oids 33696 1452 95.6908831908 0

dc_tablespaces 994784326 74 99.9999925612 2

dc_awr_control 271427 3 99.9988947304 6322

dc_object_grants 41203543 6097 99.9852027288 0

dc_histogram_data 649621988 1065618 99.8359633725 1069946

dc_rollback_segments 25496538 235 99.9990783062 825

dc_sequences 156506 1536 99.0185679782 156506

sch_lj_objs 4018 796 80.1891488302 0

dc_segments 130394426 139507 99.8930115310 77938

dc_objects 225599535 310991 99.8621490952 96415

dc_histogram_defs 217299674 1852549 99.1474681181 646908

dc_table_scns 4801 4801 0 0

dc_users 1300331635 10744 99.9991737492 789

qmtmrcin_cache_entries 1 1 0 0

outstanding_alerts 413246 2893 99.2999327277 6092

dc_files 36345 49 99.8651809052 6

dc_global_oids 28777369 6959 99.9758178032 235

dc_profiles 5282207 1 99.9999810685 0

PARAMETER SUM (T1.GETS) SUM (T1.GETMISSES) PCT_SUCC_GETS UPDATES

-------------------------------------------- ------ ----------- ------------- ----------

global database name 12620366 16 99.9998732207 0

qmtmrctn_cache_entries 13 13 0 0

qmtmrciq_cache_entries 1 1 0 0

qmtmrctq_cache_entries 586 585 0.17064846416 0

qmrc_cache_entries 7 7 0 0

25 rows selected

SQL>

PARAMETER: identifies a particular type of data dictionary entries to dc_ prefixed.

GETS: the characteristics of the data dictionary entry for the total number of requested data

GETMISSES: Due to display the data dictionary cache failure, the need for data on the disk I / O operations number

MODIFICATIONS: displays the number of times the data dictionary cache data is updated

3.3 Adjustment shared pool size
Typically, the library cache hit rate should be higher than 99%, while the data dictionary cache hit rate should be higher than 90%, otherwise need to adjust the size of the shared pool. Because of the size of the library cache and data dictionary cache size does not separately provided, but Oracle automatically according to certain allocation algorithm in the shared pool, according to the oracle of the memory allocation algorithm, if the library cache hit rate, then the data dictionary cache hit ratio It will be high.

3.4 redo log buffer adjustment
Redo log buffer is used to store data modification information, redo logs, redo log buffer is written first, under certain conditions, by the LGWR process will redo the message buffer is written redo log file; if redo logs larger buffer capacity, which can ensure that there is enough space to store new generation of redo records, but also to LGWR redo log files provided the conditions for the efficient writing process; if the redo log buffer is full, there is no room for new redo log, redo log waiting in the newly created state, known as redo log buffer write failed. Excessive redo log write fails, the redo log buffer is too small, the impact database performance.

You can check the following manner redo log buffer is written to the failure rate.

select t1.NAME "request", t2.NAME, t1.VALUE / t2.VALUE "Fail Ratio"

from v $ sysstat t1, v $ sysstat t2

where t1.name = 'redo log space requests' andt2.NAME = 'redo entries';

The results are as follows:

SQL> select t1.NAME "request", t2.NAME, t1.VALUE / t2.VALUE "Fail Ratio"

 2 from v $ sysstat t1, v $ sysstat t2

 3 where t1.name = 'redo log spacerequests' and t2.NAME = 'redo entries';

request NAME Fail Ratio

-------------------------------------------------- -------------------------------------------------- ---------------------------- ----------

redo log space requests redoentries 0.00355035

SQL>

Write failure rate is usually the log buffer should be close to zero, if the failure rate of greater than 1%, then the log buffer is too small, it should increase the size of LOG_BUFFER.

See write failure rate of 0.003, far less than 1 log buffer proved fairly appropriate.

4, PGA adjustment
PGA is located server process private data and control information memory area, each server process can access only its own PGA area. In the PGA area may be included at these workspaces.

Under normal circumstances, you can set the value of the first parameter PGA_AGGREGATE_TARGET is 20% SGA, and then run the database in a representative workload statistics, check the PGA operation, and make the appropriate adjustments.

In the oracle database, the sort can be sorted in a temporary segment pga sorting area and the temporary table space. Due to the use of temporary sections require the disk io operation will reduce the efficiency of the sort, so oracle recommended to perform the sorting operation in the sorting area.

According to the data volume and disk sort sort sort amount of area can be calculated sort sort area hit rate,

select memory1.NAME "memorysort", disk1.name "disk sort", (1-disk1.value / memory1.value) * 100 "HitRatio"

from v $ sysstat memory1, v $ sysstat disk1

where memory1.NAME = 'sorts (memory)' anddisk1.NAME = 'sorts (disk)';

The results are as follows:

SQL> select memory1.NAME "memorysort", disk1.name "disk sort", (1-disk1.value / memory1.value) * 100 "HitRatio"

 2 from v $ sysstat memory1, v $ sysstat disk1

 3 where memory1.NAME = 'sorts (memory)' and disk1.NAME = 'sorts (disk)'

 4;

memory sort disk sort HitRatio

-------------------------------------------------- -------------------------------------------------- ---------------------------- ----------

sorts (memory) sorts (disk) 99.9999420

SQL>

Under normal circumstances, the hit rate sorting area should be greater than 95%, the vast majority of which is to ensure the sort operation in memory, otherwise, you should adjust the size of the sort area, namely to increase the size of SORT_AREA_SIZE. See the hit rate of 99.99 or right from here.

5, database defragmentation
5.1 table space defragmentation
        Check the size of each table space: SelectTablespace_Name, Sum (bytes) / 1024/1024 From Dba_Segments Group By Tablespace_Name

View the remaining table space: SELECTtablespace_name table space, sum (blocks * 8192/1000000) space M FROMdba_free_space GROUP BY tablespace_name;

Table space debris level segment is due to the establishment, expansion, and remove the cause.

It can be used to operate the two schemes

1 is altertablespace PLCRM coalesce command.

 SQL> alter tablespace PLCRM coalesce;

Tablespacealtered.

SQL>

2 is to be exported through the export program data, and then use the data delete truncate the table, and finally import the data can be imported.

Table 5.2 Idle finishing debris
       

Assigned to the physical space for the number of tables: SelectSegment_Name, Sum (bytes) / 1024/1024 From User_Extents Group By Segment_Name;

Space actually used: selectnum_rows * avg_row_len from user_tables where table_name = 'EMP';

Recycling table free space: altertable tablename deallocate unused;

5.3 index defragmentation
(1) Recreateindex

(2) alterindex skate.idx_test rebuild nologging parallel 4 online;

(3) If the leaf blocks are half-emptyor near empty, you can run the "alter index coalesce" to merge

6 disk I / O optimization and adjustment
        You can sort according to the following rules

(1) physical database files, including data files, and log files control files, try to spread to a different disk, disk to avoid competition between each other, but also can achieve a balanced disk load.

(2) the same table space data file to try to balance the different disk, load balancing between disks.

(3) as far as possible all log files across different disks above, to reduce competition for the log file disk.

(4) try to tables and indexes into different table space, the store table data and index data to a different disk, reducing data files and index files on disk competition.

(5) Create a different table space for different applications, and storage space corresponding to the table data files to different disks to reduce disk competition between different applications.

(6) Table space allocated to the system do not use other applications, systems and construction db ing way competition between disk.

(7) Create undo tablespace for non-management system rollback to prevent disk-competitive effects of the transaction is completed.

(8) table space management to maximize the use of local management, automatic allocation of storage space management, and try to avoid debris line connection and migration occurs row.

(9) According to the characteristics of the table and the amount of data size, etc., using the partition table, partition index Index Tuning table, clustered and other structures, reasonable data into different data files, providing I / O performance of the system.
     
         
       
         
  More:      
 
- Based AutoYaST automated installation of SUSE practice (Linux)
- Linux Network Programming - raw socket programming (Programming)
- MongoDB 3.2 Cluster Setup (Database)
- Python calls the API interface in several ways (Programming)
- How to install with JSON support in Ubuntu 15.04 SQLite 3.9.1 (Database)
- Use lsof restore accidentally deleted log files or database (Linux)
- Linux_Logo - output color ANSI Linux distributions logo command-line tool (Linux)
- Linux 10 useful examples of command-line completion (Linux)
- Linux kernel source tree to establish load module hello (Linux)
- How the program is executed (Programming)
- Use Visual Studio Code Development TypeScript (Linux)
- ASM required disk format process in Solaris platforms (Linux)
- Use Docker containers (Linux)
- CentOS yum install LAMP (Server)
- Linux system firewall defense network attacks (Linux)
- In-depth summary of the PHP core of object-oriented (Programming)
- Https (SSL / TLS) Detailed principles (Server)
- GlusterFS distributed storage deployment (Server)
- Linux Getting Started tutorial: hard disk partition and to deal with traps (Linux)
- Each catalog Detailed Linux (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.