How to improve the performance of a pl/sq stored procedures
or functions or triggers and packages ?
Answers were Sorted based on User's Feedback
Answer / shailesh
We can improve the performance by create indexes on the use
tables which is used in the procedure,function and packages
And with the help of these indexes we can improve the
performance.
Is This Answer Correct ? | 14 Yes | 3 No |
Answer / kapil
By using index (not always)
by using bulk fetch
by using exists and not exists in place of in and not in
clause.
by avoiding implicit conversion by oracle
ex: if in employee table emp_no is varchar then
if we query the data from employee table by
where cluase like employee_number = 123 then oracle will
have to implictly convert the number 123 to
varhcar '123'and will require time
Is This Answer Correct ? | 11 Yes | 0 No |
Answer / hrishi
To reduce the context switches between the SQL Engine and
PL/SQL engine. We can use Bulk Bind Concept to this...
Is This Answer Correct ? | 6 Yes | 1 No |
Answer / rajnish chauhan
Follow the steps for performance SQL Tunning.
1) First of all tables structure should be in normalization form.
2) Then Index and tables Statistics should be upto date.
3) make it different tables table space for tables and index.
4) Avoid unnecessary joins from the query.
5) Avoid Full Table Scan and Index Skip scan.if query fetching less then 15% records from the table then index scan faster then FTS.and FTS is better then index scan if tables consist larg no of data because Index scan read multiple time on each row where as FTS read single time for each row.
6)monitor Plan through Explain plan or TKPROFF.
7)Table ordering also improve the performance of the query like.Master table should take first place then after Transaction table.
8) Check index path used or not in explain plan .if not then check weather index enable or not.then use Index Hint to forcefully used.
9)Avoid function on index column.
10) other small things you can apply like use Having instead of Where clause , use Exists then IN , use Substring instead of <> caluse.
Thanks
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / shakir
keeping the pl/sql blocks in keep pool in sga that is
called pinning of packages
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / dipti
By using Indexs we can improve the performace,
also use exist and not exist in place of in and not in as
it plays the same role as Index.
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / anand reddy
1)by using index
2)by using bulk collect
3)by using exist operator
4)by using hints
5)by using nocopy option
6)by using collection types
Is This Answer Correct ? | 1 Yes | 0 No |
SELECT *
FROM employees
ORDER BY EMPLOYEE_ID
OFFSET 99 ROWS
FETCH NEXT 21 ROWS ONLY;
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / syam
Hi Shailesh,
let me know that how can i improve performence of
procedures by useing indexes
Is This Answer Correct ? | 0 Yes | 1 No |
Which join is default?
What is recursive stored procedure?
What is procedure and function in sql?
how to retrive only second row from table?
What are the basic sql commands?
how to find the First and Last Observation from the table: Ex: OBS Name Sal Ans like: OBS Name Sal 105 E 5000--> 105 E 5000 102 B 2000 104 D 4000 103 C 3000 101 A 1000--> 104 D 4000
How many types of cursors supported in pl/sql?
how to find 5th row ?
What is clustered and nonclustered index in sql?
how to escape special characters in sql statements? : Sql dba
can we call a procedure from a function?
Why are cursors used?