Home IT Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ Oracle Database Performance Optimization of memory disk     - Linux basic introductory tutorial ---- regex basis (Linux)

- Kubernetes (k8s) environment to build combat (Server)

- The ActiveMQ JMS installation and application examples for Linux (Linux)

- Talk about Java in the collection (Programming)

- Some safety precautions of Linux servers (Linux)

- Close common port to protect server security (Linux)

- Normal start Lazarus 1.0.8 under Ubuntu (Linux)

- Ubuntu install Scala 2.10.x version (Linux)

- Awk include binding capacity larger than the specified size of all files directory (Linux)

- top command causes the system load increases (Linux)

- Linux security settings Notes (Linux)

- Puppet 3.5 Source package Installation and Configuration (Server)

- GROUP BY extension (Database)

- MySQL error: ERROR 1175: You are using safe update mode solution (Database)

- Ruby and Python simple comparison (Programming)

- Ubuntu 14.04 / 13.10 users how to install Ubuntu Touch core applications (Linux)

- Getting the Linux shell expr use (Programming)

- lack of SWAP space during installation of Oracle (Database)

- Nginx Load Balancing (standby) + Keepalived (Server)

- Linux environment variable configuration and save places (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




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:


2 from v $ buffer_pool_statisticst1



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

DEFAULT 44480354387 571096618 33595740186 -0.3018575


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;


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


BODY 28468616 28458242 1818 46

CLUSTER 114902 114198 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


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

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


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;





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'.;


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


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;


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

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


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

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


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


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)'


memory sort disk sort HitRatio

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

sorts (memory) sorts (disk) 99.9999420


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;



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.
- Server Security Analysis attack on Linux (Linux)
- CentOS system Amoeba + MySQL Master-slave configuration (Database)
- Common Linux System Troubleshooting (Linux)
- OpenStack / Dev stack with neutron on Ubuntu 14.04 (Server)
- Python programmers most often committed ten errors (Programming)
- Go constructed using an interpreted language (Programming)
- Install Java JDK 8 in CentOS 7 / 6.5 / 6.4 (Linux)
- To configure parameter configuration and software installation and uninstallation under Linux (Linux)
- Copy and paste in Linux terminal and Vim (Linux)
- Python Socket Network Programming (Programming)
- How to recover deleted files in Linux systems (Linux)
- When Vim create Python scripts, vim autocomplete interpreter and encoding method (Programming)
- Linux package management (Linux)
- Proficient in C / C ++ can also find a very good job (Programming)
- To install Ganglia (Linux)
- Using packet capture libpcap be reconciliation package in Ubuntu 14.04 64 bits (Linux)
- Fast Sort Algorithms (Programming)
- To install Xen in Ubuntu 12.04 (Linux)
- Getting the Linux shell expr use (Programming)
- Android use canvas board painting (Programming)
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.