what are the methods using performance tunning in sql and
pl/sql
- 2 Answers
- 5546 Views
- I also Faced
- E-Mail Answers
Answer Posted / yogish d naik
There are mainly two ways to do performance tuning.
1. Using Explain plan: it is an method of getting how our
query will execute without actually executing the query. It
is used for only sql. (but not pl/sql).
Steps: 1. Here first Create query by following syntax
EXPLAIN PLAN FOR
SELECT A.ename,A.sal,nvl(A.comm,0),B.dname,B.loc
FROM emp A ,dept B
WHERE A.deptno=B.deptno;
Steps: 2. DBMS_XPLAN is a inbuilt package provided by
oracle we just call that package to get explain plan for
that query.
select * from table(DBMS_xplan.display());
here we has to observe two things cost and operation, if
it is taken TABLA ACCESS FULL then we has to make indexes
for columns mentioned in where clause. And note down the
cost before and check after indexing.
2. Sql Tracing and TKprof:
Sql Tracing: is used to generate execution plan and time
related statistics after executing query or program.
Steps: 1. Enable Trace:
ALTER SESSION SET SQL_TRACE = TRUE;
Steps: 2. Run some pl sql program
Steps: 3. Disable trace
ALTER SESSION SET SQL_TRACE = FALSE;
Use TKPROF Utility:
The trace file generated from previous step will be in non
readable format. TKPROF is a command line utility (os level)
converts non-readable trace file into readable text file.
C:\users\server> tkprof orcl_ora_7176.trc newfile.txt
Analyze output 1.CPU & Elapsed time should be less
2. Disk reads should be less
3. plan should have lesser cost , path should not be full
table scan.
| Is This Answer Correct ? | 2 Yes | 0 No |
Post New Answer View All Answers
What is the file extension for sql database?
If the application is running very slow? At what points you need to go about the database in order to improve the performance?
What does cursor do in sql?
How do you write a subquery?
What do you understand by pl/sql records?
What is the plv (pl/vision) package offers?
I need a function for a train ticket reservation please answer it thanks in advance
What is sql select statement?
what are the authentication modes in sql server? How can it be changed? : Sql dba
How to select 10 records from a table?
what are the maximum number of rows that can be constructed by inserting rows directly in value list? : Transact sql
Mention what is the function that is used to transfer a pl/sql table log to a database table?
What is the difference among union, minus and intersect?
what is the difference between mysql_fetch_array and mysql_fetch_object? : Sql dba
Is sqlexception checked or unchecked?
