Can you please tell how can we improve performance of an SQL query?
Printable View
Can you please tell how can we improve performance of an SQL query?
More effective measure of performance tuning results is response time. There are many factors which effect the performance of a SQL query. Identify the most common SQL statements, tune each one by carefully reviewing the execution plan for the SQL and adjust the execution plan using Oracle hints.
You can improve sql statement efficiency by following steps : <br>1.Verifying optimizer statisticsverifying optimizer statistics <br> 2.Reviewing the execution plan 3.restructuring the sql statements <br> 4.Creating indexes and restructuring the indexes <br> 5.Modifying or disabling triggers and constraints <br>6.Restructuring the data <br>7.Maintaining execution plans over time <br>8.Visiting data as few times as possible etc.
There is no single way to tune the sql query. no one can help you about it beacause it tunning depends on the execution the sql.
I suggest you that by using the explain plan check the execution of query and go well to it.
and also check the sga parameter.
[QUOTE=susarlasireesha;20750]You can improve sql statement efficiency by following steps : <br>1.Verifying optimizer statisticsverifying optimizer statistics <br> 2.Reviewing the execution plan 3.restructuring the sql statements <br> 4.Creating indexes and restructuring the indexes <br> 5.Modifying or disabling triggers and constraints <br>6.Restructuring the data <br>7.Maintaining execution plans over time <br>8.Visiting data as few times as possible etc.[/QUOTE]
can any one can explain about execution plan? what actually mean is there any specified execution plan to improve the performance of the query. Could you please help me?
[QUOTE=kalyan.naga;20800]can any one can explain about execution plan? what actually mean is there any specified execution plan to improve the performance of the query. Could you please help me?[/QUOTE]
Whenever you read or write data in Oracle, you do so by issuing an SQL statement. One of Oracle's task when it receives such a statement is to build a query execution plan. An execution plan defines how Oracle finds or writes the data. For example, an important decision that Oracle has to take is if it uses indexes or not. And if there are more indexes, which of these is used. All this is contained in an execution plan. If one wants to explore such an execution plan, Oracle provides the SQL statement EXPLAIN PLAN to determine this. The general syntax of EXPLAIN PLAN is: <br>
explain plan for your-precious-sql-statement;
[QUOTE=susarlasireesha;20834]Whenever you read or write data in Oracle, you do so by issuing an SQL statement. One of Oracle's task when it receives such a statement is to build a query execution plan. An execution plan defines how Oracle finds or writes the data. For example, an important decision that Oracle has to take is if it uses indexes or not. And if there are more indexes, which of these is used. All this is contained in an execution plan. If one wants to explore such an execution plan, Oracle provides the SQL statement EXPLAIN PLAN to determine this. The general syntax of EXPLAIN PLAN is: <br>
explain plan for your-precious-sql-statement;[/QUOTE]
yes i understood can you provide me with some example? so that i can have a clear picture on it.
[QUOTE=kalyan.naga;20922]yes i understood can you provide me with some example? so that i can have a clear picture on it.[/QUOTE]
Can any one provide me the Oracle best interview questions link or atleast Questions? can any one help me out...
[QUOTE=kalyan.naga;20922]yes i understood can you provide me with some example? so that i can have a clear picture on it.[/QUOTE]
For detail information about explain plan refer this [URL="http://www.adp-gmbh.ch/ora/explainplan.html"]link[/URL]
If you use sqldeveloper of oracle, you can just click on a button and see the explain plan as well as a the trace. The trace is also useful in tuning the queries.
Of course there are things beyond sql too that need more tweaking, e.g. the pga_agrregate_target that plays an important role in sorting.