Identifying Bad SQL and Indexing techniques

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

Query writing techniques

1) SELECT only the columns and rows needed.
SQL query becomes faster if you use the actual columns names in SELECT statement instead of than '*‘ and the fewer rows. Also this would  reduce the amount of data needs to be transferred through the network   ,also optimizer pickup the correct indexes otherwise it’ll do a full table scan.

For Example: Write the query as
           SELECT id, first_name, last_name, age, subject FROM student_details;
           Instead of:
           SELECT * FROM student_details; 

2) Try to minimize the number of sub query block in your query
Sometimes you may have more than one sub queries in your main query ( may be correlated subquery ). Try to minimize the number of sub query block in your query. this would give better performance.
For Example: Write the query as
         SELECT name   FROM employee   WHERE (salary, age ) = (SELECT MAX (salary), MAX (age)          FROM employee_details)  AND dept = 'Electronics';
         Instead of:
         SELECT name FROM employee   WHERE salary = (SELECT MAX(salary) FROM employee_details)          AND age = (SELECT MAX(age) FROM employee_details)   AND emp_dept = 'Electronics'; 


3) Perform order by operation as required
Perform order by operation as required try to use numeric field for sort operation.it would give better performance than use of string field.

For Example: Write the query as
        Select e_id,name ,age,salary from employee order by e_id;

4) Use operator EXISTS, IN and table joins appropriately in your query.

• Usually IN has the slowest performance.
• IN is efficient when most of the filter criteria is in the sub-query.
• EXISTS is efficient when most of the filter criteria is in the main query.

For Example: Write the query as
        Select * from product p  where EXISTS (select * from order_items o where o.product_id = p.product_id)
Instead of:
       Select * from product p  where product_id IN (select product_id from order_items )


      
5) Try to use UNION ALL in place of UNION. unless you need to retrieve distinct set of records
For Example: Write the query as
       SELECT id, first_name FROM student_details_class10
       UNION ALL
       SELECT id, first_name FROM sports_team;
Instead of:
        SELECT id, first_name, subject FROM student_details_class10
        UNION
        SELECT id, first_name FROM sports_team;  


6) Write ANSCI complaint SQL, it's more clarity
 
User INNER JOIN,OUTER JOINS etc. in the query,
 
SELECT ename, dname FROM emp left outer join  dept  on emp.deptno   = dept.deptno;
SELECT ename, dname FROM emp inner join  dept  on emp.deptno   = dept.deptno;
Instead of:
SELECT ename, dname FROM emp, dept WHERE emp.deptno (+) = dept.deptno;
SELECT ename, dname FROM emp, dept WHERE emp.deptno  = dept.deptno;

7) Be careful while using <>,!= operators in WHERE clause.
Even though indexes are in pleased for the columns, above operators (<> != ) are ignored by oracle optimizer , therefore try to implement positive conditions as possible.
For Example: Write the query as
SELECT id, first_name, age FROM student_details WHERE age != 10; 


8) Be careful while using functions/calculations in WHERE clause.
Usage of functions in predicates will ignore the indexes, try to avoid it or add function base index for those predicates.
For Example: Write the query as
------------------------------------------------------------------------------------------------------------
SELECT id, name, salary FROM employee WHERE dept = 'Electronics' AND location = 'Bangalore';
Instead of:
SELECT id, name, salary FROM employee WHERE dept || location= 'ElectronicsBangalore';
------------------------------------------------------------------------------------------------------------
SELECT id, name, salary FROM employee WHERE salary < 25000;
Instead of:
SELECT id, name, salary FROM employee WHERE salary + 10000 < 35000;

9) Be careful while using  LIKE operators in WHERE clause.

Where using LIKE in WHERE clause ,NEVER use % in beginning of where condition. Oracle will not use index .Always put some value in beginning then use %.
ename like ‘%CO%’ ; — Index will not be used ename like ‘SC%’; — Index will be used

12C

12c architecture diagram

Seed is the template for all plugable databases.
12C has only one SGA for all databases
It allows easy way to clone databases with in same CDB or different CDBs.
Need to open CDB first and then open all the PDBs