The database optimizer is the most important part of a relational database system. For each SQL statement, the database optimizer determines the strategy for accessing data records. Access can be with database indexes (index access), or without database indexes (full table scan).
The cost-based database optimizer determines the access strategy on the basis of:
conditions in the WHERE clause of the SQL statement
Database indexes of the relevant table(s)
Selectivity of the table fields contained in the database indexes
size of the relevant table(s)
The table and index statistics supply information about the selectivity of table fields, the selectivity of combinations of table fields, and table size.
Before a database access is performed, the database optimizer cannot calculate the exact cost of a database access. It uses the information described above to estimate the cost of the database access.
The optimization calculation is the amount by which the data blocks to be read (logical read accesses) can be reduced. Data blocks show the level of detail in which data is written to the hard disk or read from the hard disk.
Data Base SQL Catche
To avoid repeating the time-consuming and costly procedure of parsing an SQL statement and determining an access strategy, SQL statements are buffered with the chosen access strategy in the DB SQL cache (shared memory area) of the database server.
For a received SQL statement, the DBMS first checks whether the statement already exists in the DB SQL cache. If it does exist, it can be used immediately. If it does not exist, the SQL statement must be parsed and the access strategy must be determined.
Next, the DBMS attempts to read the data blocks required by the SQL statement from the data buffer (logical read access). If this is possible, physical read accesses are not necessary. If this is not possible, the missing data blocks are read from the database files on the hard disk (physical read accesses).
With an appropriate access path:
Here, the SQL statement reads many data blocks in the database and is expensive because it transfers many data records from the database to the application server. Database performance is satisfactory according to the criterion that less than 5 data blocks are read per data record.
Expensive SQL statements with a suitable access path are listed at the top of the Database SQL Cache if they are executed frequently. A problem with the application logic is usually indicated. This problem can be fixed through changes to the ABAP code or to the business process.
With no appropriate access path:
Here, the SQL statement reads many data blocks in the database but does not transfer many data records from the database to the application server. Database performance is not optimal according to the criterion that more than 5 data blocks are read per data record.
Expensive SQL statements with no appropriate access paths can be optimized either by by creating or improving the design of an index, or modifying the ABAP code to improve a poorly designed WHERE clause.
8
No comments:
Post a Comment