If query taking long time then First will run the query in Explain Plan, The explain plan process stores data in the PLAN_TABLE.
it will give us execution plan of the query like whether the query is using the relevant indexes on the joining columns or indexes to support the query are missing.
If joining columns doesn’t have index then it will do the full table scan if it is full table scan the cost will be more then will create the indexes on the joining columns and will run the query it should give better performance and also needs to analyze the tables if analyzation happened long back. The ANALYZE statement can be used to gather statistics for a specific table, index or cluster using
ANALYZE TABLE employees COMPUTE STATISTICS;
If still have performance issue then will use HINTS, hint is nothing but a clue. We can use hints like
- ALL_ROWS
One of the hints that 'invokes' the Cost based optimizer
ALL_ROWS is usually used for batch processing or data warehousing systems.
(/*+ ALL_ROWS */)
- FIRST_ROWS
One of the hints that 'invokes' the Cost based optimizer
FIRST_ROWS is usually used for OLTP systems.
(/*+ FIRST_ROWS */)
- CHOOSE One of the hints that 'invokes' the Cost based optimizer
This hint lets the server choose (between ALL_ROWS and FIRST_ROWS, based on statistics gathered. - HASH Hashes one table (full scan) and creates a hash index for that table. Then hashes other table and uses hash index to find corresponding records. Therefore not suitable for < or > join conditions.
/*+ use_hash */
Hints are most useful to optimize the query performance.
There are mainly two ways to do performance tuning.
ReplyDelete1. 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());
the output looks like
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;
(orcl_ora_7176.trc file name has three parts orcl is database name, ora default in all trace files and 7176 is session id its keep on changing. We can not get file directly.. first we has to login as system/manager (your password). Check for USER_DUMP_DEST using command
Sql> SHOW PARAMETER USER_DUMP_DEST;
Then we get file source folder. We can get using latest time.
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.
You can go through below link for some some PL/SQL advanced concepts
ReplyDeletehttp://www.oracle-tf-world.in/p/oracle-plsql_20.html
Your comments are most welcome...!!!
above mentioned site is not availble
ReplyDeleteI really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Performance Tuning, kindly contact us http://www.maxmunus.com/contact
ReplyDeleteMaxMunus Offer World Class Virtual Instructor led training on Performance Tuning. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
For Demo Contact us:
Name : Arunkumar U
Email : arun@maxmunus.com
Skype id: training_maxmunus
Contact No.-+91-9738507310
Company Website –http://www.maxmunus.com