Topic: Performance & Tuning >> Tuning SQL query
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| mohamed riaz |
Posted: Dec 22, 2007 04:17:20 PM |
Total Post: 91
Joined: Sep, 2006
|
Dear all,
Can any one pls help me how will you tune SQL query ? Please explain step by step Looking forward to hearing from you as soon as possible.
Regards
Riaz |
|
|
 Vinod |
| Posted: Dec 22, 2007 10:27:23 PM |  |
|
Total Post: 54
Joined: Oct, 2007
|
Hi Riaz
You can write SQL efficiency by good cursor usage ,reducing hard parses since they are most resource-intensive , because they perform all the operations involved in a parse.In soft parsing a sql statement is submitted for the first time, and a match is found in the shared pool. The match can be the result of previous execution by
another user. The SQL statement is shared, which is good for performance.However, soft parses are not ideal, because they still require syntax and security checking, which consume system resources.
A SQL statement can be executed in many different ways, such as full table scans,index scans, nested loops, and hash joins. The query optimizer determines the most efficient way to execute a SQL statement after considering many factors related to the objects referenced and the conditions specified in the query. This determination is an important step in the processing of any SQL statement and can greatly affect execution time.
To begin with you can trace your query and find the execution plans moreover make use of explain plan as well .
Due to time constraint none of the diagnostic phase are explained here.
Gather trace for the session running the SQL using <Event: >.
To gather 10046 trace at the session level:
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set events '10046 trace name context forever,level 12';
--DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION can also be used
A formatted explain plan
explain plan for <your query goes here>
set lines 130
set head off
spool <spool file>
alter session set cursor_sharing=EXACT;
select plan_table_output from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));
spool off
Plan hash value: 1126297136
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 177 | 15 (7)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 177 | 15 (7)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID | BOM_STRUCTURES_B | 1 | 37 | 3 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 177 | 14 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 140 | 11 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 4 | 484 | 11 (0)| 00:00:01 |
| 6 | MERGE JOIN CARTESIAN | | 4 | 196 | 3 (0)| 00:00:01 |
| 7 | NESTED LOOPS OUTER | | 1 | 37 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | MRP_PLANS_U1 | 1 | 15 | 0 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | MRP_PLAN_ORGANIZATIONS_U1 | 1 | 22 | 1 (0)| 00:00:01 |
| 10 | BUFFER SORT | | 4 | 48 | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | BOM_SUBSTITUTE_COMPONENTS | 4 | 48 | 2 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID| BOM_COMPONENTS_B | 1 | 72 | 2 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | BOM_COMPONENTS_B_U2 | 1 | | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | MRP_SYSTEM_ITEMS_U1 | 1 | 19 | 0 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | BOM_STRUCTURES_B_N1 | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
,
Thanks & Regards
Vinod
|
|
|
|
|
| mohamed riaz |
| Posted: Dec 22, 2007 11:35:22 PM | |
|
Total Post: 91
Joined: Sep, 2006
|
Hi vinod,
1) can you give me some tips how will you handle Execeptions in PLSQL
with example?
2) Difference between oracle 9i and 10g? explain ?
Regards
Riaz
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 814 |
| Mohammed Taj | 694 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| positive fanatic | 361 |
| Gitesh Trivedi | 322 |
| Gopu Gopi | 239 |
| neeraj sharma | 228 |
| Ramesh Jois | 226 |
| snehalatha p | 169 |
|
|
