How to improve the performance of a pl/sq stored procedures
or functions or triggers and packages ?
- 12 Answers
- 24214 Views
- IBM, TCS, UHD, I also Faced
- E-Mail Answers
Answer Posted / 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 |
Post New Answer View All Answers
column A column b | output 10 7 | 10 5 8 | 8 7 -9 | 7 3 5 | 5 0 6 | 6 Write a sql query to print such output.
Can we insert in view in sql?
What is the difference between a subquery and a join?
What are the different operators available in sql?
What does count (*) mean?
Define SQL and state the differences between SQL and other conventional programming Languages?
What are the parts of a basic sql query?
What is forward declaration in pl sql?
what is 'trigger' in sql? : Sql dba
How do I edit a trigger in sql developer?
Hi am new to PLSQL & facing problems in writing code like in SP, Functions, so any one having some SP coding with in depth explanation please share with me my Email ID suvarnaatsuvarna@rediffmail.com Or taking tanning on this please do contact me
What is sql clause?
What is program debugging?
Who is the owner of mysql database?
What are the different dml commands in sql?
