SQL write Query with Tuning tips

By
Query Performance Tuning
====================

 In this post i will give few tips while writing sql query, how we can write better query with less cost
 If you follow below steps keep in mind while writing query you can tune up your sql while writning
 time. Below are some tips which can be used as guideline.

  Make sure am not expert, But what i learn have been post here to help some one. 
  If you know more points or you want to add on more please post in comment session it help to me and also others to learn. 
  Even though i can miss some silly points or known point as well. 
  so please help to remind them !!!


  1. DISTINCT:-  Do not use, If the objective can be achieved otherwise. DISTINCT incurs an extra sort operation and therefore slows your queries down.  
  2. Indexed columns:- Do not modify indexed columns with functions like RTRIM, TO_CHAR, UPPER, TRUNC, As this will prevent the optimizer from identifying the index. If possible perform the modification on the constant side of the condition. If the indexed column is usually accessed through a function, consider creating a function based index. (many cases people use and functions and complain my query not using index).
  3. UNION:- Do not use UNION, if the objective can be achieved through an UNION ALL. UNION incurs an extra sort operation which can be avoided.
  4. EXISTS :- use if selective predicate in parent query .
  5. IN : use if selective predicate is in the sub query.
  6. joins from one complex view to another not recommended.
  7. Do not use HINTS (as oracle new version using CBO(cost based optimizer).
  8. statistics for the objects used in the query are up to date or not, need to check.
  9. Read explain plan and try  tune your query.
  10. Commented lines are not good in between query lines.
  11. Instead of using NULL some cases DECODE better to use.



still have more point will add on later .

Thanks for your visit, please help to post in comment session if you have more points or any correction from my statements. it help to me &others!!!

Comments

Post a Comment

Popular posts from this blog

Shareplex some commands

By
Shareplex some commands i will post here and update in this post with more commands later, if i provide with example so post become lengthy so please to understand. =====================================================================   sp_ctrl  :- Shareplex provides a command line utility called    'sp_ctrl'   this  sp_ctl located in  <product_directory>/bin  ($SP_HOME/bin)  , if you set profile in os level you easy redirect location. Below i will give commands run under  sp_ctrl  sp_ctrl (xxx)>   show config   ==> To check the active configuration (source side). sp_ctrl (xxx)>   show log reverse  ==> To check the log file sp_ctrl (xxx)>  show  ==>   command to displays status of the replication processes (Capture, Read, Export, Import and Post) . sp_ctrl (xxx)>  status  ==> command from sp_ctrl utility that provides an overview on the state of Shareplex replication. sp_ctrl (xxx)>  qstatus   ==> We can monitor, t
Read more

SQL Tuning Task Creation ORA-13780: SQL statement does not exist.

By
SQL Tuning Task Creation ORA-13780: SQL statement does not exist. ================================================= example:- declare  l_sql_tune_task_id  varchar2(100); begin  l_sql_tune_task_id := dbms_sqltune.create_tuning_task (         sql_id      => 'your sql_id',         scope       => dbms_sqltune.scope_comprehensive,         time_limit  => 10800,         task_name   => 'tuning task name',         description => 'tuning task description');  dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); end; / declare * ERROR at line 1: ORA-13780: SQL statement does not exist. ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79 ORA-06512: at "SYS.PRVT_SQLADV_INFRA", line 125 ORA-06512: at "SYS.DBMS_SQLTUNE", line 655 solution :----  SELECT SNAP_ID FROM DBA_HIST_SQLSTAT WHERE SQL_ID='sql_id' ORDER BY SNAP_ID; ===========now create task============ declare  l_sql
Read more

ORA 700 [kskvmstatact: excessive swapping observed]

By
ORA 700 [kskvmstatact: excessive swapping observed] ======================================== One of my database down suddenly. Reason:  ORA-700 SWAP error, as the dump it's taking more that 10 minutes pmon killed the process and the database was down. this database running with less memory . Solution alter system set "_kse_die_timeout"=XX or 0 if we want to disable the pmon killer during dumping . note: The error is documented in:  -  Note:1919850.1  - 12c: Warning Message About Heavy Swapping Observation and ORA-700 [kskvmstatact: excessive swapping observed]  Because your swap memory has low values of swap in / swap out, this warning message can be safely ignored.  Given both the alert log message and ORA-700 are expected behavior for 12c, no action is required.  An enhancement request (ER) was created to change the threshold in future releases in  Bug 19495842  - CHANGE THRESHOLD IN THE SWAP WARNING IN THE ALERT_<SID>.LOG  ==============
Read more