Results 1 to 4 of 4

Thread: Internal process of an execute query

  1. #1
    Geek_Guest
    Guest

    Internal process of an execute query

    What are the internal process ORACLE (in internal server side) do when we execute any query lets take example "SELECT * FROM EMP" ?

    Thanks in Advance.
    Question asked by visitor Nihar Ranjan Behera


  2. #2
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Internal process of an execute query

    You can set the environment variable auto trace to ON to get the internal execution process. You must have plan table in your schema to do so. To create teh plan table run the script UTLXPLAN.SQL


  3. #3
    Contributing Member
    Join Date
    Apr 2006
    Answers
    46

    Re: Internal process of an execute query

    go through the link

    sorry go to this link

    http://www.adp-gmbh.ch/ora/concepts/sql_stmt.html

    Last edited by bhaski; 11-22-2007 at 04:40 AM.

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

    Re: Internal process of an execute query

    Step 1: oracle create a cursor
    for every sql, first a cursor is created (cursor creation can occur implicitly, or explicitly by declaring a cursor).
    step 2: oracle parse sql statement
    a sql statement is parsed only if an identical sql statement does not exist in the library cache (shared pool-system global area). In this case, a new shared sql area is allocated and the statement is parsed (hard parse), otherwise existing stored information in library cache is used (soft parse).
    hard parsing is the process of
    • translating a sql statement and verify the syntax.
    • checking data dictionary to check table and column definitions
    • acquiring parse locks on required objects so that their definitions do not change during the statement’s parsing
    • checking privileges to access referenced schema objects
    • determining the optimal execution plan for the statement
    • loading it into a shared sql area
    • for distributed statements, routing all or part of the statement to remote nodes that contain referenced data
    stage 3: describe results (for select statement only)
    the describe phase is used to determine the characteristics (datatypes, lengths, and names) of a query’s result.
    stage 4: defining output (for select statement only)
    specify the location, size, and datatype of variables defined to receive each fetched value. Oracle performs datatype conversion if necessary.
    stage 5: bind any variables
    at this point, oracle needs values for any variables listed in the statement; for example, in sql statement “select * from emp where empno = p_empno”, oracle needs a value for p_empno. This process is called binding variables. A program must specify the location (memory address) where the value can be found.
    stage 6: execute the statement
    at last, statement is executed.oracle checks if the data it needs for the query are already in the buffer cache. If not, it reads the data off the disk into the buffer cache.the record(s) that are changed are locked i.e. In case of delete or update statement. Also, before and after images describing the changes are written to the redo log buffer and the rollback segments. The original block receives a pointer to the rollback segment. Then, the data is changed.for some statements you can specify a number of executions to be performed. This is called array processing. Given n number of executions, the bind and define locations are assumed to be the beginning of an array of size n.
    stage 7: parallelize the statement
    when using the parallel query option, oracle can parallelize queries and certain ddl operations. Parallelization causes multiple query servers to perform the work of the query so that the query can complete faster.
    stage 8: fetch rows of a query result (for select statement only) rows are selected and ordered (if requested by the query), and each successive fetch retrieves another row of the result, until the last row has been fetched.



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