| Bind Variables [message #64952] |
Tue, 16 March 2004 23:31  |
Patrick Tahiri
Messages: 119
Registered: January 2004
|
Senior Member |
|
|
I have several theoric questions about it:
1. Soft parses: finds the DML statement in the Shared Pool & Verify we can use it!
What do this sentenct means by "verify the dml statement?
2. V$SESSION_EVENT.<I style="mso-bidi-font-style: normal">latch_free[/i]: a latch on the Shared SQL Area. Admitting by not using bind variables two users are using the same SQL statements à since we have two sessions now doing “hard parses”, contention for the Shared SQL Area is introduced. They both need to modify a shared data structure, and only one session at a time can do this.
Isn't it the Shared Pool instead of the Shared SQL Area? Hard parses does affect the Shared Pool?
Not using bind variables: if those user use the same query they will affect the Shared Pool by consuming its memory space: So if 1000 of user or processes are using the same query without bind variables the Shared Pool can run out of memory allocated (to the Shared Pool) space? Is that right?
What do they mean with shared data structure?
When you submit any SQL or PL/SQL to the database, Oracle will first search the <I style="mso-bidi-font-style: normal">shared pool[/i] to see if it already exists (already parsed and optimized). If it finds the query and it can be reused, you are ready to go. If it cannot find it, Oracle must go to the arduous process of parsing the query fully, optimizing the plan, performing the security checks, and so on…
3. <I style="mso-bidi-font-style: normal">Cursor[/i]: Don’t be so fast to close a cursor - The overhead of having it remain open during your program execution if you might reuse it, is overshadowed by the performance increase you will receive by keeping it open.
In the book they showed an exemple with just a select statment whithin a Loop batch:
DECLARE ....
BEGIN
FOR ...
...
LOOP
...
END LOOP;
COMMIT;
END;
It is not an explicit cursor?? Wich means that theses kind of Loop are
implicit loops? Are they closed automatically at the end?
4. CURSOR_SHARING=FORCE:
In my book, I have some exemples with just a select statment like:
select * from emp where ename = 'KING'
which with this parameter set to FORCE is interpreted by Oracle like:
select * from emp where ename =:"SYS_B_0"
But his is a normal DML not a cursor??
Thank you a lot for your help!
Regards,
Patrick Tahiri.
|
|
|
|
| Re: Bind Variables [message #64978 is a reply to message #64952] |
Mon, 22 March 2004 08:58  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Patrick,
I believe you are quoting somebody or some book ?
Which book is this ? . I'd appreciate if you can split the questions into multiple threads ,making it easier to answer.
I'll just answer your 4th question in this thread :-)
When you implement cursor_sharing,Oracle converts the literal values found in your sql statements(both queries and DML statements(insert,update,delete) into dummy variables before parsing. Cursors are created to process sql statements(select,insert,update,delete).
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
