Oracle sql tuning

=============Optimizer mode Parameter level: How to identify current Optimizer setting select value from v$parameter where name='optimizer_mode'; show parameter optimizer_mode; Session level: select substr(sql_text,1,50) from v$sql where sql_text like '%optimizer mode%' select optimizer_mode from v$sql where sql_text like '%1st query for optimizer mode%'; alter session set optimizer_mode=all_rows; select optimizer_mode from v$sql where sql_text like '%2nd query for optimizer mode%'; alter session set optimizer_mode=choose; select optimizer_mode from v$sql where sql_text like '%3rd query for optimizer mode%'; alter session set optimizer_mode=rule; select optimizer_mode from v$sql where sql_text like '%4th query for optimizer mode%'; alter session set optimizer_mode = first_rows; ------- Explain plan Create PLAN_TABLE using the script: select * from table(dbms_xplan.display); explain plan for select empno, empname from employee; explain plan for select empno, empname from employee where empno=206; explain plan for select emp.empno, emp.empname, dept.dname from employee emp, department dept where emp.deptno=dept.deptno; by default merge join - cost explain plan for select emp.empno, emp.empname, dept.dname from employee emp, department dept where dept.deptno=emp.deptno; ================ Query Transformations: 1) Simple View Merging: select e.first_name, e.last_name, dept_locs_v.street_address, dept_locs_v.postal_code from employees e, (select d.department_id, d.department_name, l.street_address, l.postal_code from departments d, locations l

where d.location_id = l.location_id) dept_locs_v where dept_locs_v.department_id = e.department_id and e.last_name = 'Smith'; -- select e.empname, dept_locs_v.street_address, dept_locs_v.postal_code from employee e, (select d.deptno, d.dname, l.street_address, l.postal_code from department d, locations l where d.locid = l.locid) dept_locs_v where dept_locs_v.deptno = e.deptno and e.empname = 'Susan'; post - Merge : select e.empname, l.street_address, l.postal_code from employee e, department d, locations l where d.locid = l.locid and d.deptno = e.deptno and e.empname = 'Susan'; 2) Predicate pushing: Oracle supports pushing of predicates into a given view. Assume we had a set of layered views, like this: -- View One CREATE VIEW vw_layer_one AS SELECT * FROM emp; -- view two CREATE VIEW vw_layer_two_dept_100 AS SELECT * FROM vw_layer_one WHERE deptno=100; Then assume we issued this query: SELECT * FROM vw_layer_two_dept_100 WHERE empid=100; The predicate in this statement is the where empid=100 statement. You may have one of tens or even hundreds of predicates (if you have hundreds, we don t want� to be supporting your SQL code!). Oracle will, in many cases, push those predicates down into the views being called. Thus, Oracle will transform the VW_LAYER_ONE view into a SQL statement that looks like this: CREATE VIEW vw_layer_one AS SELECT * FROM emp WHERE deptno=100 AND empid=100; Best perf - use bind variables instead of literals: SELECT * FROM vw_layer_two_dept_100 WHERE empid=:b100; 3) Subquery Unnesting select /*+ gather_plan_statistics */ ename from emp where sal > (select max(sal) from emp e where e.empno = emp.empno); Subqueries can be unnested in to a join. Above is shows that a subquery is unnested in to a view and then joined to other row sources. In this listing, a correlated subquery is moved in to a view VW_SQ_1, unnested and then joined using Nested Loops Join or hash join technique. There are many different variations of Subquery Unnesting possible, but the crux of the matter is that subqueries can be unnested, joined and then costed. Explain Plan: select * from table(dbms_xplan.display); =============Lesson 2 SELECT job,sum(sal)

FROM emp e, dept d WHERE e.deptno=d.deptno GROUP BY job; The tabular representation is a top-down, left-to-right traversal of the execution tree. When you read a plan tree you should start from the bottom left and work across and then up. In the above example, begin by looking at the leaves of the tree. In this case the leaves of the tree are implemented using a full table scans of the EMP and the DEPT tables. The rows produced by these table scans will be consumed by the join operator. Here the join operator is a hash-join (other alternatives include nested loop or sort-merge join). Finally the group-by operator implemented here using hash (alternative would be sort) consumes rows produced by the join-operator, and return the final result set to the end user. ============ Example of Dynamic Sampling: create table demo_ds as select owner, object_type from all_objects; select count(*) from demo_ds; set autotrace traceonly explain select /*+ dynamic_sampling(demo_ds 0) */ * from demo_ds --------------- Hash ex SELECT deptno, job, SUM(sal) FROM employee GROUP BY deptno, job; uses sort merge select a.dname,b.empname,b.empno from department a,employee b where a.deptno = b.deptno; Use nested loop select /*+ USE_NL(b a) */ a.dname,b.empname,b.empno from department a,employee b where a.deptno = b.deptno; =============Generic 1. SQL Performance Tuning team recommends using COUNT(1) instead COUNT(*) for SQL query performance optimization. Example: Do not use: SELECT COUNT(*) FROM master; Use: SELECT COUNT(1) FROM master; 2. Never compare NULL with NULL. Consider that NULL is not like an empty string or like the number 0. Also NULL can not be not equal to NULL (NULL != NULL). SELECT COUNT(1) FROM all_users WHERE 1 = 1; Result: With condition 1 = 1 you will get result. But in this case you can also omit condition 1 = 1.

SELECT COUNT(1) FROM all_users WHERE NULL = NULL; Result: With condition NULL = NULL you will get result 0. SELECT COUNT(1) FROM all_users WHERE NULL != NULL; Result: With condition NULL != NULL you will also get result 0. 3. If you are using more than one table, make sure to use table aliases. SELECT COUNT(1) FROM master m, detail d WHERE m.id = d.master_id; 4. It is good practice to use table column names in an SQL query. This way the SQL statements will be more readable, but that is not the main reason. Example: If in INSERT statement you use SELECT * FROM x and at some point you add a new column in table x, SQL will return an error. The third reason why it is better to use table column names is to reduce network traffic. 5. In WHERE statements make sure to compare string with string and number with number, for optimal SQL query performance. Example: Note: Column id is NUMBER Data Type. Do not use: SELECT id, apn, charging_class FROM master WHERE id = '4343'; Use: SELECT id, apn, charging_class FROM master WHERE id = 4343; 6. Do not change column values in WHERE statements if possible, recommended by SQL Performance Tuning. Example: Do not use: WHERE SUBSTR(a.serial_id, INSTR(b.serial_id, ',') - 1) = SUBSTR(b.serial_id, INSTR(b.serial_id, ',') - 1) Use: WHERE a.serial_id = b.serial_id 7. Avoid using complex expressions. Examples: Avoid: WHERE serial_id = NVL(:a1, serial_id) WHERE NVL(serial_id,-1) = ( 1, etc...) 8. If you need to use SQL functions on join predicates that is okay, but do not use them with indexed table columns. 9. EXISTS vs. IN for sub queries If the selective predicate is in the sub query, then use IN. If the selective predicate is in the parent query, then use EXISTS. 10. RETURNING clause

INSERT, UPDATE or DELETE can be used with RETURNING clause when appropriate. This way the number of calls to the database are reduced. 11. SQL Performance Tuning recommends using CASE statements. It is more efficient to run a single SQL statement, rather than two separate SQL statements. Example: Do not use: SELECT COUNT (1) FROM emp WHERE salary <= 1000; SELECT COUNT (1) FROM emp WHERE salary BETWEEN 1000 AND 2000; Use: SELECT COUNT (CASE WHEN salary <= 1000 THEN 1 ELSE null END) count_1, COUNT (CASE WHEN salary BETWEEN 1001 AND 2000 THEN 1 ELSE null END) count_2 FROM emp; 12. Use UNION ALL instead of UNION, if possible Example: Do not use: SELECT id, name FROM emp_bmw UNION SELECT id, name FROM emp_bmw_welt Use: SELECT id, name FROM emp_bmw UNION ALL SELECT id, name FROM emp_bmw_welt 13. SQL Performance Tuning recommends to use minimal number of sub queries, if possible. Example: Do not use: SELECT id, manufacturer, model FROM cars WHERE price = ( SELECT MAX(price) FROM cars_bmw ) AND year = ( SELECT MAX(year) FROM cars_bmw ) Use: SELECT id, manufacturer, model FROM cars WHERE (price, year) = ( SELECT MAX(price), MAX(year) FROM cars_bmw ) 14. SQL Performance Tuning recommends when cretin data are used frequently, it is a good idea to store them into intermediate tables. 15. SQL Performance Tuning OR vs. IN. Our tests showed that using IN in WHERE condition is a little faster then using OR.

Example: Do not use: SELECT * FROM CDRS_NR WHERE RECORD_TYPE = 'MTC' OR RECORD_TYPE = 'MOC' OR RECORD_TYPE = 'SMSO' Use: SELECT * FROM CDRS_NR WHERE RECORD_TYPE IN ('MTC', 'MOC', 'SMSO') ============== Which departments have at least one employee? --Looks for every dept from dept table in employee table, collects to remove the duplicate; SELECT DISTINCT d.department_name FROM departments d, employees e WHERE e.department_id = d.department_id; Oracle performs an existence check (semi-join) for each record in the department table; no duplicates are produced. SELECT d.dname FROM department d WHERE not EXISTS ( SELECT * FROM employee e WHERE deptno = d.deptno ); below qry uses hash SELECT DISTINCT d.dname FROM department d, employee e WHERE e.deptno = d.deptno; to make it use sort merge SELECT /*+ USE_MERGE(d e) */ DISTINCT d.dname FROM department d, employee e WHERE e.deptno = d.deptno; SELECT /*+ NO_USE_HASH USE_MERGE(d e) */ DISTINCT d.dname FROM department d, employee e WHERE e.deptno = d.deptno; =====Use Exists SELECT DISTINCT o.order_id, o.order_date FROM orders o, order_items ol WHERE LOWER(o.order_state) = : order_state AND ol.order_id = o.order_id AND ol.item_id IN ( SELECT item_id FROM items WHERE item_state = : item_state ) ORDER BY o.order_date; --prevents oracle from using any index on order_state column, Below is the query re-written: SELECT order_id,

order_date FROM orders o WHERE order_state = : order_state AND EXISTS ( SELECT * FROM order_items ol WHERE order_id = o.order_id AND EXISTS ( SELECT * FROM items WHERE item_id = ol.item_id AND item_state = : item_state ) ) ORDER BY order_date; Note: 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. == Usage of Having clause Do not use: SELECT subject, count(subject) FROM student_details GROUP BY subject HAVING subject!= 'Vancouver' AND subject!= 'Toronto'; Use: SELECT subject, count(subject) FROM student_details WHERE subject != 'Science' AND subject != 'Maths' GROUP BY subject; ===Try to Minimize number of subqueries; Do not use: SELECT first_name , salary FROM employees WHERE salary = (SELECT MAX(salary) FROM employees) AND comm1 = (SELECT MAX(comm1) FROM employees) ; AND emp_dept = 'Electronics'; Use: SELECT first_name FROM employees WHERE (salary, comm1 ) = (SELECT MAX (salary), MAX (comm1) FROM employees) ; AND dept = 'Electronics'; ---use Exists instead of distinct Do not use: SELECT DISTINCT d.dept_id, d.dept FROM dept d,employee e WHERE e.dept = e.dept; Use: SELECT d.dept_id, d.dept FROM dept d WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);

--Other Where conditions Do: SELECT id, first_name, age FROM student_details WHERE age > 10; Don't: SELECT id, first_name, age FROM student_details WHERE age != 10; or SELECT id, first_name, age FROM student_details WHERE age NOT = 10 Do: SELECT id, first_name, age FROM student_details WHERE first_name LIKE 'Chan%'; Don't: SELECT id, first_name, age FROM student_details WHERE SUBSTR(first_name,1,3) = 'Cha'; Do: SELECT id, first_name, age FROM student_details WHERE first_name LIKE NVL ( :name, '%'); Don't: SELECT id, first_name, age FROM student_details WHERE first_name = NVL ( :name, first_name); Do: SELECT product_id, product_name FROM product WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price) Don't: SELECT product_id, product_name FROM product WHERE unit_price >= MAX(unit_price) and unit_price <= MIN(unit_price) Do: SELECT id, name, salary FROM employee WHERE dept = 'Electronics' AND location = 'Bangalore'; Don't: SELECT id, name, salary FROM employee WHERE dept || location= 'ElectronicsBangalore'; Use non-column expression on one side of the query because it will be processed earlier. Do: SELECT id, name, salary FROM employee WHERE salary < 25000 Don't: SELECT id, name, salary FROM employee WHERE salary + 10000 < 35000; Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause.

Eg: Write like SELECT id FROM employee WHERE name LIKE 'Ramesh%' and location = 'Bangalore'; Don't: SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee WHERE name LIKE 'Ramesh%'; --Order of Predicates in Where clause place predicates first , the one which eliminates more rows first. Where Gender = 'Male' and Designation = 'SSE' --Order of Tables names Low selectivity - does not use index High selectivity - uses index ---Indexes - Create index for columns that are repeatedly used - Creating index on Foreign Key - helps as most of the join conditions arebased on PK & FK - Don't create Index for columns which are frequently updated - Get rid of overlapping indexes - Consider deleting indiex when loading huge voume of data into table To store large binary objects, first place them in the file system and add the file path in the database. --Follow general rules - Tips for writing more efficient SQL: a) Use single case for all SQL verbs b) Begin all SQL verbs on a new line c) Separate all words with a single space d) Right or left aligning verbs within the initial SQL verb e) Rewrite complex subqueries with temporary tables - Oracle created the global temporary table (GTT) and the SQL WITH operator to help divide-and-conquer complex SQL sub-queries (especially those with with WHERE clause subqueries, SELECT clause scalar subqueries and FROM clause in-line views). Tuning SQL with temporary tables (and materializations in the WITH clause) can result in amazing performance improvements. f) Use minus instead of EXISTS subqueries - Some say that using the minus operator instead of NOT IN and NOT Exists will result in a faster execution plan. g) Use SQL analytic functions - The Oracle analytic functions can do multiple aggregations (e.g. rollup by cube) with a single pass through the tables, making them very fast for reporting SQL. h) Re-write NOT EXISTS and NOT EXISTS subqueries as outer joins - In many cases of NOT queries (but ONLY where a column is defined as NULL), you can rewrite the uncorrelated subqueries into outer joins with IS NULL tests. Note that this is a non-correlated sub-query, but it could be re-written as an outer join. select book_key from book where book_key NOT IN (select book_key from sales); Below we combine the outer join with a NULL test in the WHERE clause without using a sub-query, giving a faster execution plan.

select b.book_key from book b, sales s where b.book_key = s.book_key(+) and s.book_key IS NULL; i)Index your NULL values - If you have SQL that frequently tests for NULL, consider creating an index on NULL values. To get around the optimization of SQL queries that choose NULL column values (i.e. where emp_name IS NULL), we can create a function-based index using the null value built-in SQL function to index only on the NULL columns. j)Leave column names alone - Never do a calculation on an indexed column unless you have a matching function-based index (a.k.a. FBI). where salary*5 > :myvalue where substr(ssn,7,4) = "1234" where to_char(mydate,mon) = "january" k) Avoid the use of NOT IN or HAVING. Instead, a NOT EXISTS subquery may run faster (when appropriate). l) Avoid the LIKE predicate = Always replace a "like" with an equality, when appropriate. m) Never mix data types - If a WHERE clause column predicate is numeric, do not to use quotes. For char index columns, always use quotes. There are mixed data type predicates: where cust_nbr = "123" where substr(ssn,7,4) = 1234 n) Use decode and case - Performing complex aggregations with the "decode" or "case" functions can minimize the number of times a table has to be selected. o) Don't fear full-table scans - Not all OLTP queries are optimal when they uses indexes. If your query will return a large percentage of the table rows, a full-table scan may be faster than an index scan. This depends on many factors, including your configuration (values for db_file_multiblock_read_count, db_block_size), query parallelism and the number of table/index blocks in the buffer cache. p)Use those aliases - Always use table aliases when referencing columns.

Oracle sql tuning

More Related Content

What's hot

Viewers also liked

Similar to Oracle sql tuning

Recently uploaded

Oracle sql tuning