The most important task of computer control systems is data storage and processing.Sybase performance management it`s one of the reasons for good work and less problems. To solve some possible errors, specialized software was created - Database Management Systems (DMS), which allow to structure, systematize and organize data for their computer storage and processing.
It is impossible to imagine the activity of a modern enterprise or institution without using professional DBMS. They form the basis of information activities in all areas - from production to finance and telecommunications.
The quality of work is composed of both hardware and software level equipment. One of the ways to ensure the DBMS operation efficiency is equipping it with a professional monitoring system.
Today, the most effective are relational databases. The most popular in the world is the relational database management system (RDBMS) - Oracle, which is designed for simultaneous access to large volumes of stored information. It is used by large enterprises engaged in information technologies.
A DBMS consists of two components: a database (information) and an instance or an instance (a specific system implementation). The database consists of the physical files stored in the system and the logical parts.
Disk array (physical layer component) of Oracle:
-relational database files - one or more data files in which the information is stored in the database (one can have both one data file and hundreds of such files), the information from one table can be scattered over several data files (and several tables can share the data file space with each other), tables are distributed over several data files, which can significantly increase the system performance;
-redo log files - two or more operation log files that contain the information required for the system recovery process. These files store all the changes that occurred in the database. Using them, the alterations that were made but not committed before the system crash are restored. Journaling files must be well protected against hardware failures (both on the software and hardware levels);
-archive log - an exact copy of the operation logging files.
Oracle background processes:
Database writer - writing modified (dirty) blocks from database buffers blocks to disk. When a transaction changes the information in a data block, this data block does not have to be immediately written to disk. It means that you can write data to disk in a more efficient way than writing all the changes individually. Usually data is written when it is already needed for reading. Data that has been recently used is also written. For systems with asynchronous I/O, one process is sufficient. For others, you can significantly increase productivity by creating multiple processes;
-driver (redo log writer) - writes data from the log buffer into the change log;
-the archiver driver - copies the change log files when they are filled in. It is active only if the DBMS operates in the ARCHIVELOG mode.
System global are (or SGA) is a shared memory area that Oracle uses to store data and control information of one particular Oracle instance. SGA is placed in memory when the Oracle instance starts and frees memory when it stops. Each running Oracle instance has its own SGA. The SGA components (each of which is created in the instance startup memory):
-database cache buffer - stores the last open data blocks. These blocks may contain data that has changed but not yet been written to disk (dirty blocks); data that has not changed or was written to disk after the change (clean blocks). Since the database cache buffer stores data blocks based on the algorithm of the last blocks used, the most actively used blocks remain in memory permanently (reducing disk I/O and increasing system performance);
-change log buffer - stores the database change data. The change log buffer is written to the change log file as quickly and efficiently as possible;
-a shared pool is an SGA area that stores such shared memory structures as shared SQL areas in the library cache and internal data dictionary information. Insufficient size of the shared pool can lead to performance degradation of the whole system. It consists of library cache and data dictionary cache. The library cache is a memory area where all SQL and PL/SQL operators are stored. The database processor understands only the SQL commands. Regardless of what means are used to ensure user interaction with the database, only SQL statements are sent to the processing software.
Server processes are the mechanisms of program code execution. Several processes can work simultaneously. The DBMS works with two types of processes: user processes and Oracle processes.
-user (client) processes - user connections to the DBMS (manages the input and interacts with Oracle server processes through the Oracle program interface). They can be both single-user (dedicated) and multi-user (shared).
-dispatcher - a process responsible for each communication protocol. They organize the interaction between user and shared server processes.
QL*Net is the client network components and administrative utilities.
The proposed diagram shows the necessary structural components and communications to be monitored.
Disk array components - relational database - in the percentage ratio employment by the data files of the total disc space; operation logging - employment by the files with operation logging; log archive - the percentage of employment by the backup device.
The arrows indicate the links between the DBMS components the monitoring of which is also mandatory.
The performance of the whole system depends on the database cache buffer functioning; it consists of memory blocks of the same size as the Oracle blocks. All the data is loaded into the cache-buffer. Any data update is executed in them as well, so it is very important to set the buffer size correctly, if something is wrong https://www.enteros.com/services/ will help you.Oracle transfers data to disk (swap data is used) according to the order in which it is placed in the LRU list (least recently used - most recently used).
This list tracks access to data blocks and takes into account how often they are accessed. When addressing a block of data stored in cache buffer, it is placed at the end of the list - MRU (most recently used - recently used). Thus, if a server needs a place in a cache-buffer for loading of the new block from a disk it addresses to list LRU and decides what of blocks to transfer on a disk to release space for the new block. The blocks most removed from the list from the MRU are the most likely to be removed from the cache buffer. The blocks which are most frequently accessed remain in the cache buffer the longest.
Each time the counter value increases. Blocks are moved through the list naturally over time, as the changed blocks are moved to the "dirty" list (to be written to disk by the DBWn process). If in spite of repeated use of blocks the cache-buffer has filled up, and the block with small value "counter of references" is removed from the list, it comes back with new data approximately in the middle of the list. Intensively used blocks are cached for a long time, and seldom used - long there are no delays.
The size of a cache buffer is defined by two parameters of adjustment DB_BLOCK_SIZE and DB_BLOCK_BUFFERS (are located in a file init.ora). Total volume of cache buffer is defined as product of these two parameters DB_BLOCK_SIZE*DB_BLOCK_BUFFERS.
Enabling the subsystem - detailed monitoring of the database cache buffer work in the general system will allow optimally determining the cache buffer size, which in its turn will increase the DBMS system administrator work efficiency and prevent the possibility of the client and system application procedures work failures and increase the overall performance.