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.

How can you fetch common records from two tables?

1099

---

How to run pl/sql statements in sql*plus?

1135

---

What is an inner join sql?

1097

---

Does oracle roll back the transaction on an error?

1067

---

What is a Mapplet?

1194

---

What is a heap in sql?

1137

---

1. is it possible to use the cursor atttibutes (%found ,% rowcount , %isopen , %notfound ) to our user defined cursor names ....... cursor cursor_name is select * from scott.emp if you use... cursor_name%found , %rowcount ,%isopen,%notfound...will it work... -------------------------- 2.what is the difference between the varray and index by table .. -------- 3. type type_name is table of number(8,3) index by binary_integer; identifier_name type_name; first , last , prior , next ,trim are the methods we can use it for the above type...simillary is there any way to apply for cursors... with thanks and regards..sarao...

2495

---

What are few of the schema objects that are created using PL/SQL?

1079

---

What is the maximum database size for sql express?

1071

---

What are field types?

1051

---

Is it possible to remove child records without removing master table records...the two having pk,fk relationship?

1300

---

What's the difference between inner join and left join?

1039

---

Can you skip header records while loading? : aql loader

1100

---

Is left join same as join?

1035

---

What is difference between ms sql and mysql?

1012

---