Interested to Buy Any Domain ? << Click Here >> for more details...
How to improve the performance of a pl/sq stored procedures
or functions or triggers and packages ?
- 12 Answers
- 29620 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
Table A Table B 1 1 2 1 3 1. Union & union all --> A Union B , A Union all B 2. Minus , Intersect --> A minus B , B Minus A , A Intersect B 3. Joins A join B , A Left Join B A Right Join B , A full Join B 4. %Type - Uses & Benifit 5. Truncate & Delete 6. Pragma Autonomus Transaction 7. how to Perform DDL from function or procedure 8. Can we have DML inside Function 9. Rank & Dense Rank diffrence 10. Water Mark in Oracle 11. Index , Can we have index in all column of table if no then why ?
what is the difference between mysql_fetch_array and mysql_fetch_object? : Sql dba
What is a dirty read sql?
Is it possible to read/write files to-and-from PL/SQL?
Is record in pl sql?
What are the rules to be applied to nulls whilst doing comparisons?
What is compiled query?
What are conditional predicates?
how many ways to get the current time? : Sql dba
who introduced sql?
what is the difference between group by and order by in sql? : Sql dba
What is db journal file?
What is the use of sql trace?
What does cursor do in sql?
What is varchar used for?
