Data Base Tuning -Performance Tuning
Generally Database is the repository where we can store the data in different forms like tables, stored procedures, and triggers...etc
Most of the times we are came across the database performance problems as listed
1) Dead locks (Using Sql Profiling we can fix the issues of dead lock)
2) Queries consuming high response times (Using AWR report we can identify bottleneck in Queries high time by CPU and wait time can fix the issues.)
We can fix the most of the problems using following sql tuning techniques.
Indexes should be applied for tables which involves the joins and where clauses for corresponding columns of data.
- Avoid the use of loops instead use the cursors for the requirement to handle.
- Execution plan should be optimized for any query.
- Do not use Update Instead of Trigger which leads to performance degradation for particular trigger.
- Display number of columns instead of using wildcard (*) character for displaying all columns
- Replace in and not in with Exits and Not Exists which will improve the performance of query
- Use with no lock for tables when creating indexes which helps for supporting concurrent users.
- Avoid using functions on the right hand side, it is always best practice to use the functions at beginning of the query.
- Avoid using correlated sub-queries instead use Joins and normal sub queries.
- Remove unnecessary temp tables at end of the each procedures, sometimes temp data will get full up and causes performance issues.
- Use the select statements limited, it is always best practice to get the required data in single select statement instead using multiple select statements for retrieving the data.
Index:
An index is used to speed up the performance of queries. It does this by reducing the number of database data pages that have to be visited/scanned.
Clustered Index:
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
- It is present on primary key of the table.
- It follows binary structure.
- The leaf node contains key and actual data.
Non-Clustered Index:
A Non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a Non-clustered index does not consist of the data pages.
- It also follows Binary Structure.
- Leaf node contains Key and reference data.
Defragmentation generally makes the index in exact working state by making it active.
- Rebuild index: it will recreate the existing index by dropping it, to make sure index should be working properly for corresponding columns.
- Reorganize index: it will just refresh the existing index without dropping them, it also helps to make the index working properly.
- Whenever a query is written in the database by the developer. While executing the load test we may observe that query will consume more response time for its execution.
How do the database manage the execution process for different queries and stored procedures?
- Data reads and writes happen in the SGA(Shared Global Area) are known as logical reads and logical writes.
- Data reads and writes happen in the PGA(Programmable Global Area) are known as physical reads and physical writes.
- For a most optimizing maintaining of the database servers, we need to maintain the high percentage of logical reads and writes than the physical.
Parsing:
Parsing is one stage in the processing of a SQL statement. When an application issues a SQL statement, the application makes a parse call to Database.
During the parse call, Database
1. Checks the statement for syntactic and semantic validity.
2. Determines whether the process issuing the statement has privileges to run it.
3. Allocates a private SQL area for the statement.
Also tuning can be done using following tools or commands or options too
- Sp_who2 – Used to identify any process or stored procedure which is halting performance at that particular point of time, using the stored procedure id or corresponding operation id we can kill the halting process.
- Profiler –Using profiler we can identify the particular tables or stored procedures performance issue or dead lock kind of problems.
- Tuning Advisor – It automatically suggests us for corresponding query where to add indexes for making query optimized performance.
- Activity Monitor – It monitors the database level server monitoring when load test is in progress, for viewing the database related issues.
- Execution plan – It is one of the key component to makes sure the execution plan should be optimized by seeing the cost operation of query by re-writing it in best way.
- Dynamic Management views –Properly managing the dynamic management views will add the performance improvement for any database.
