|
Where ever possible, oracle cursors should be declared at the start of the program. It is also good practice to tell the system the maximum number of cursors you want, at the start of the program. This can be done in DB by opening the cursors you require, in Pro*c set MAXCURSORS. There are two 'expensive' operations during the execution of a query, apart from returning the rows. These are associated with the 'parse' and 'bind' phase of query execution. What is meant by the parse phase. This is the time when the sql text that the programmer has written is translated into an internal representation, that the Oracle kernel can understand. The following activities happen during the parse phase. a) Data dictionary look up and security checking. This activity causes the oracle kernel to produce internal sql queries, which are called recursive queries, to check such things as whether this user is allowed access to this column etc. b) Query optimisation. Part of the query optimisation which deals with views and sub queries is performed. Performing all of the above can take a considerable time. Therefore they should be done as infrequently as possible. The binding phase takes place after the parse phase. It is at this stage that the user variables are decided. For instance in the query select 'x' from person where name = :1 the host variable 1 would have its value 'bound' into the query during binding by having the address of :1 associated with the host variable. Under normal circumstances, wms programs should perform the following steps, for a query that is the be executed more than once. Step 1: During program start-up set up and parse the queries with calls to sql_set-up. Step 2: Execute the query with a call to sql_execute supplying the necessary bind variables. Step 3: Fetch and process the returned rows by using sql_fetch. To execute the query again, return to step 2 and supply new bind variables.
Have a Oracle Question
Oracle Books
Oracle Application
Oracle Home
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.
|