This content has been marked as final. Show 4 replies
As far as I knows, there are no answer of any single line question in Oracle (and I think also not in any tech). We must ask/frame our question as much as we can do. For your these questions there are many If and buts like first and foremost Oracle Version, OS info (These two almost applies in each those questions which are address to the Oracle architecture, so please always quote them) etc.
Ok, now come to the your question.
What is hard parse ?
What is soft parse ?
What is child cursor ?
What is parent cursor ?
What is execution plan ?
Where oracle stores execution plan ?
Why oracle generates execution plan ?
At which stage oracle generates execution plan i.e. soft parse or hard parse ?
What is mutex/latch in this context i.e. parent and child cursor ?
What is bind peeking / variable ?
What are relationship of Parent and child cursors, bind variables and library cache ?
What about curosr_sharing parameters, i mean how it influence all these ?
What dict object should be see for this question ?
Are these questions applies to each oracle version/OS info or they have different functionality/features ?
...May be more !
My intention to raise these question is to simplify the question, break the question in small parts, then understand each of them (from docs and great books). Because we can not understand a question in one go until and unless we are in the dark of that related sub-sub-sub-question(s). Once if you are able to get the answer for above questions, then I am sure you will the pleasure which is worthy. At this moment, what we will do... we will simply give you couple of links to answer your this question; thats it. But in really your question is not answered because you are in in dark (if you really are !) for above questions.
Re: Notion of child cursor
Parent and child cursors, bind variables and library cache
Re: Parsing sql - parent and child cursor
may be more...!
each child cursor has an individual plan. A parent cursor is just the sql_id and the text of the query (same query text will always give the same sql_id), everything else is in a child cursor.
When new SQL arrives, the database tries to find a suitable child cursor on the library cache. If there is no parent cursor, then there will be hard parse. If there is a parent cursor, but it's existing children cannot be reused by this call (e.g. because of different size of bind variables, or because of different optimizer settings, or because of different NLS setting etc.), there will be hard parse. If existing child cursors can be reused by this call, there will be a soft parse (unless the cursor is in session cache -- then there won't be any parse at all).