What steps should a programmer should follow for better tunning of the PL/SQL blocks?Difference between procedure and function?What is the use of ref cursor return type?
The main difference between procedure and function,function should return a value.procedure need not
Antony Gubert
Mar 20th, 2006
Ref cursor return type can be used when it's required that a procedure / function need to return set of records.
Savithri
Apr 2nd, 2006
Tuning can be taken care by using the Correct Index on the table.We should not use Not equal to, Distinct on the Indexed columns.
Keshav
May 22nd, 2006
SQL Queries ? Best Practices
1.
Always use the where clause in your select statement to narrow the number of rows returned.
If we don?t use a where clause, the Oracle performs a full table scan on our table and returns all of the rows.
2.Use EXISTS clause instead of IN clause as it is more efficient than IN and performs faster.
Ex:
Replace
SELECT * FROM DEPT WHERE DEPTNO IN
(SELECT DEPTNO FROM EMP E)
With
SELECT * FROM DEPT D WHERE EXISTS
(SELECT 1 FROM EMP E WHERE D.DEPTNO = E.DEPTNO)
Note: IN checks all rows. Only use IN if the table in the sub-query is extremely small.
3.When you have a choice of using the IN or the BETWEEN clauses in your SQL, use the BETWEEN clause as it is much more efficient than IN.
Depending on the range of numbers in a BETWEEN, the optimizer will choose to do a full table scan or use the index.
4.Avoid WHERE clauses that are non-sargable. Non-sargable search arguments in the WHERE clause, such as "IS NULL", "OR", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE %500" can prevent the query optimizer from using an index to perform a search. In addition, expressions that include a function on a column, or expressions that have the same column on both sides of the operator, are not sargable.
Convert multiple OR clauses to UNION ALL.
5.Use equijoins. It is better if you use with indexed column joins.For maximum performance when joining two or more tables, the indexes on the columns to be joined should have the same data type.
6.Avoid a full-table scan if it is more efficient to get the required rows through an index. It decides full table scan if it has to read more than 5% of the table data (for large tables).
7. Avoid using an index that fetches 10,000 rows from the driving table if you could instead use another index that fetches 100 rows and choose selective indexes.
8. Indexes can't be used when Oracle is forced to perform implicit datatype conversion.
9. Choose the join order so you will join fewer rows to tables later in the join order.
luse smaller table as driving table
lhave first join discard most rows
10. Set up the driving table to be the one containing the filter condition that eliminates the highest percentage of the table.
11. In a where clause (or having clause), constants or bind variables should always be on the right hand side of the operator.
12.Do not use SQL functions in predicate clauses or WHERE clauses or on indexed columns, (e.g. concatenation, substr, decode, rtrim, ltrim etc.) as this prevents the use of the index. Use function based indexes where possible
SELECT * FROM EMP WHERE SUBSTR (ENAME, 1, 3) = ?KES?
Use the LIKE function instead of SUBSTR ()
13.If you want the index used, don?t perform an operation on the field.
Replace
SELECT * FROM EMPLOYEE WHERE SALARY +1000 = :NEWSALARY
With
SELECT * FROM EMPLOYEE WHERE SALARY = :NEWSALARY ?1000
14. All SQL statements will be in mixed lower and lower case.All reserve words will be capitalized and all user-supplied objects will be lower case. (Standard)
15.Minimize the use of DISTINCT because it forces a sort operation.
16. Try joins rather than sub-queries which result in implicit joins
Replace
SELECT * FROM A WHERE A.CITY IN (SELECT B.CITY FROM B)
With
SELECT A.* FROM A, B WHERE A.CITY = B.CITY
17. Replace Outer Join with Union if both join columns have a unique index:
Replace
SELECT A.CITY, B.CITY FROM A, B WHERE A.STATE=B.STATE (+)
With
SELECT A.CITY, B.CITY FROM A, BWHERE A.STATE=B.STATE
UNION
SELECT NULL, B.CITY FROM B WHERE NOT EXISTS
(SELECT 'X' FROM A.STATE=B.STATE)
18.Use bind variables in queries passed from the application (PL/SQL) so that the same query can be reused. This avoids parsing.
19. Use Parallel Query and Parallel DML if your system has more than 1 CPU.
20. Match SQL where possible. Applications should use the same SQL statements wherever possible to take advantage of Oracle's Shared SQL Area. The SQL must match exactly to take advantage of this.
21. No matter how many indexes are created, how much optimization is done to queries or how many caches and buffers are tweaked and tuned if the design of a database is faulty, the performance of the overall system suffers. A good application starts with a good design.
22.The following operations always require a sort:
SELECT DISTINCT
SELECT UNIQUE
SELECT ....ORDER BY...
SELECT....GROUP BY...
CREATE INDEX
CREATE TABLE.... AS SELECT with primary key specification
Use of INTERSECT, MINUS, and UNION set operators
Unindexed table joins
Some correlated sub-queries
Poojitha Somareddy
May 26th, 2006
Also the order in which the conditions are given in the 'WHERE' cluase are very important while performing a 'Select' query. The Performance Difference is unnoticed ifother wise the query is run on a Massive Database.
For example for a select statement,
SELECT Emp_id FROM Emp_table WHERE Last_Name = 'Smith' AND Middle_Initial = 'K' AND Gender = 'Female';
The look up for matches in the table is performed by taking the conditions in the WHERE cluase in the reverse order i.e., first all the rows that match the criteria Gender = 'Female' are returned and in these returned rows, the conditon Last_Name = 'Smith' is looked up.
There fore, the order of the conditions in the WHERE clause must be in such a way that the last condition gives minimum collection of potential match rows and the next condition must pass on even little and so on. So, if we fine tune the above query, it should look like,
SELECT Emp_id FROM Emp_table WHERE Gender = 'Female' AND Middle_Initial = 'K' AND Last_Name = 'Smith' ; as Last_Name Smith would return far more less number of rows than Gender = 'Female' as in the former case.
Ref cursor is used to declare a pointer variable. When you return a ref cursor, you are returning a pointer variable.
Aniruddha Das
Jun 11th, 2012
If you want to take care of performance tuning you need to take care of sub-queries as they takes a lot of time to executing in side the pl-sql blocks.
What steps should a programmer should follow for better tunning of the PL/SQL blocks?Difference between procedure and function?What is the use of ref cursor return type?
Questions by aseemnaithani answers by aseemnaithani
Related Answered Questions
Related Open Questions