Results 1 to 10 of 10

Thread: Oracle Question

  1. #1
    Junior Member
    Join Date
    Nov 2007
    Answers
    5

    Oracle Question

    Can you please tell how can we improve performance of an SQL query?


  2. #2
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Oracle Question

    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.


  3. #3
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Oracle Question

    You can improve sql statement efficiency by following steps :
    1.Verifying optimizer statisticsverifying optimizer statistics
    2.Reviewing the execution plan 3.restructuring the sql statements
    4.Creating indexes and restructuring the indexes
    5.Modifying or disabling triggers and constraints
    6.Restructuring the data
    7.Maintaining execution plans over time
    8.Visiting data as few times as possible etc.


  4. #4
    Junior Member
    Join Date
    Nov 2007
    Answers
    22

    Re: Oracle Question

    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.


  5. #5
    Junior Member
    Join Date
    Nov 2007
    Answers
    5

    Re: Oracle Question

    Quote Originally Posted by susarlasireesha View Post
    You can improve sql statement efficiency by following steps :
    1.Verifying optimizer statisticsverifying optimizer statistics
    2.Reviewing the execution plan 3.restructuring the sql statements
    4.Creating indexes and restructuring the indexes
    5.Modifying or disabling triggers and constraints
    6.Restructuring the data
    7.Maintaining execution plans over time
    8.Visiting data as few times as possible etc.
    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?


  6. #6
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Oracle Question

    Quote Originally Posted by kalyan.naga View Post
    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?
    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:


    explain plan for your-precious-sql-statement;


  7. #7
    Junior Member
    Join Date
    Nov 2007
    Answers
    5

    Re: Oracle Question

    Quote Originally Posted by susarlasireesha View Post
    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:


    explain plan for your-precious-sql-statement;


    yes i understood can you provide me with some example? so that i can have a clear picture on it.


  8. #8
    Junior Member
    Join Date
    Nov 2007
    Answers
    5

    Re: Oracle Question

    Quote Originally Posted by kalyan.naga View Post
    yes i understood can you provide me with some example? so that i can have a clear picture on it.
    Can any one provide me the Oracle best interview questions link or atleast Questions? can any one help me out...


  9. #9
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Oracle Question

    Quote Originally Posted by kalyan.naga View Post
    yes i understood can you provide me with some example? so that i can have a clear picture on it.
    For detail information about explain plan refer this link


  10. #10
    Junior Member
    Join Date
    May 2007
    Answers
    4

    Re: Oracle Question

    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact