This post shows you how you should write your SQL statement more effective and efficiency.
As a DBA/DB developer
Identifying poorly perform query
• Validate with the requirement against implemented logic.
• Check correct joins, predicates, sorting are implemented.
• Check the execution plan, will give details about how SQL execution happen on database engine.
• Cross check with the elapse time agreement (SLA) , and set the goal for tuning.
(tuning is continues process)
• Check data growth strategy and purging plans.
• Validate the query structure and the way that has been implemented, carefully check the
WHERE clause operations. Try to avoid calculations , user define or oracle built in functions, core related sub queries etc...
General check list for Query tuning
• Every table should have a PK defined and there should be a unique index belong to it, try to choose it as numeric type.since it's more effective.
• Generally there should be indexes for joining keys (FKs), this would help to improve performance while joining more tables.
• When passing parameter values, try to match data types.
e .x
1) Pass a number values if the predicate column data type is numeric, instead passing string values.
2) If the table column data type is DATE , don’t pass TIMESTAMP data type in where clause, Oracle will never use index it will always do FTS , instead of that either pass value as DATE data type in where clause or change table column to TIMESTAMP data type.
Consider the below facts when Identifying the required indexes
• Put the most frequently queried columns (also called the “leading part” of the index) first.
• Put the most restrictive column first. like most unique fields
• Check indexes are in place for joining key (generally, not all the case)
• Try to create combined (covering) indexes, including more than one column.this would help Index skip operation too.
• Check the execution plan
1) Try to minimize full table scan (FTS ). ( for small tables FTS is good) , normally Oracle does ( more effitive ) FTS for small tables and if the 90% of records going to retrieve from a table.
2) Consider the cost of the query, it should be minimize
• Let oracle optimize to pick the appropriate options to execute the query
Tyr to avoid push hits, index, join sequence etc..
• Optimizer statistics should be up to date.
• In order to use function based indexes, QUERY_REWRITE_ENABLED parameter should be set to TRUE
As a DBA/DB developer
Identifying poorly perform query
• Validate with the requirement against implemented logic.
• Check correct joins, predicates, sorting are implemented.
• Check the execution plan, will give details about how SQL execution happen on database engine.
• Cross check with the elapse time agreement (SLA) , and set the goal for tuning.
(tuning is continues process)
• Check data growth strategy and purging plans.
• Validate the query structure and the way that has been implemented, carefully check the
WHERE clause operations. Try to avoid calculations , user define or oracle built in functions, core related sub queries etc...
General check list for Query tuning
• Every table should have a PK defined and there should be a unique index belong to it, try to choose it as numeric type.since it's more effective.
• Generally there should be indexes for joining keys (FKs), this would help to improve performance while joining more tables.
• When passing parameter values, try to match data types.
e .x
1) Pass a number values if the predicate column data type is numeric, instead passing string values.
2) If the table column data type is DATE , don’t pass TIMESTAMP data type in where clause, Oracle will never use index it will always do FTS , instead of that either pass value as DATE data type in where clause or change table column to TIMESTAMP data type.
Consider the below facts when Identifying the required indexes
• Put the most frequently queried columns (also called the “leading part” of the index) first.
• Put the most restrictive column first. like most unique fields
• Check indexes are in place for joining key (generally, not all the case)
• Try to create combined (covering) indexes, including more than one column.this would help Index skip operation too.
• Check the execution plan
1) Try to minimize full table scan (FTS ). ( for small tables FTS is good) , normally Oracle does ( more effitive ) FTS for small tables and if the 90% of records going to retrieve from a table.
2) Consider the cost of the query, it should be minimize
• Let oracle optimize to pick the appropriate options to execute the query
Tyr to avoid push hits, index, join sequence etc..
• Optimizer statistics should be up to date.
• In order to use function based indexes, QUERY_REWRITE_ENABLED parameter should be set to TRUE
No comments:
Post a Comment