Follow Our FB Page << CircleMedia.in >> for Daily Laughter. We Post Funny, Viral, Comedy Videos, Memes, Vines...


what are the methods using performance tunning in sql and
pl/sql

Answers were Sorted based on User's Feedback



what are the methods using performance tunning in sql and pl/sql..

Answer / 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

what are the methods using performance tunning in sql and pl/sql..

Answer / vyshak

1.tracing
2.explain plan
3.auto tracing
4.AWR reports
5.using v$views

Is This Answer Correct ?    1 Yes 0 No

Post New Answer

More SQL PLSQL Interview Questions

how can we find the number of rows in a table using mysql? : Sql dba

0 Answers  


What is the purpose of design view?

0 Answers  


What is the difference between cluster and non-cluster index?

0 Answers  


Is primary key always clustered index?

0 Answers  


How to fix oracle error ora-00942: table or view does not exist

0 Answers  






Why does %isopen return false for an implicit cursor?

0 Answers  


what is a unique key ? : Sql dba

0 Answers  


What is the reports view in oracle sql developer?

0 Answers  


Which join is like inner join?

0 Answers  


Can two tables have same primary key?

0 Answers  


Give the order of sql select?

0 Answers  


What is partition by in sql?

0 Answers  






Categories