Showing posts with label OPTIMZATION. Show all posts
Showing posts with label OPTIMZATION. Show all posts

Data Base Optimizer part two

Posted by Isha | 10:24 AM

Continued from the last post data base optimizer part one.

Unsuitable access path :

If the single object analysis establishes that an expensive SQL statement has an unsuitable access path, you can narrow (and thus optimize) the search.

The search range is the number of data records that needs to be checked in a table in order to satisfy an SQL statement. When the SQL statement is processed on the database, the search range is dynamically determined by the WHERE clause, the database index used, and the access strategy selected.

You can narrow the search by:

Solving technical problems

Changing the ABAP coding

Changing the index design


Suitable access path:

If the single object analysis establishes that the SQL statement analyzed has a suitable access path, you can optimize it by:

Reducing the rows to be transferred

Reducing the columns to be transferred

Reducing data transfer

Avoiding unnecessary SQL statements

You can reduce the number of rows to be transferred, for example, by optimizing the WHERE clause or by using aggregate functions.

You can reduce the number of columns to be transferred, for example, by formulating a suitable field list or, in the case of database changes, by using UPDATE .. SET field = value.

You can reduce data transfer between database server and application server by formulating your SQL statements in such a way that data is transferred in as large blocks as possible (a full fetch is 32 KB). You can do this by avoiding nested SELECT statements, for example.

Unnecessary SQL statements are either similar or identical SQL statements. Similar SQL statements can often be grouped together. Identical SQL statements can be avoided by buffering data either in an internal table in the program or on the application server.

Data Base Optimizer

Posted by Isha | 8:52 AM

Cost Based Data Base Optimizer

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

Subscribe to: Posts (Atom)