Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Aman.

Asked: December 09, 2007 - 6:39 am UTC

Last updated: December 10, 2007 - 12:19 pm UTC

Version: 10.2.01

Viewed 1000+ times

You Asked

Hi sir,
(1)how in actualy parsing happens?I mean to ask that the process like Oracle breaks the sql into ASCII codes than sums them up and then passes to hash function.I want to get teh complete life circle of parsing of a query happens till the time when the parse id is cached in the library cache?
(2) Whats the use ofthe parse id in real?I know it sounds a very stupid but I am just lost thinking about this.Whats the real use of the parse id?Oracle can match the text also and than can decide whether to go for soft or hard parse?
(3) Yesterday I had a talk with some one.He proposed that when we will have a sql statement a part of a stored pl/sql object than it will always makes sure that statements are soft parsed.Thats what the advantage of pl/sql is.Is it true sir?Can you please approve/disapprove this by a practical example?
(4)When a statement uses a literal, its going to be hard parsed all teh time when it will run.So we have to use the bind variables.Is the same is true for the pl/sql enclosed statements?I mean to say that if I have a statement that is going to update some records based on the value supplied by the procedure,this will make it a hard parse query.So without using a bind variable,will we be getting the soft parsng automatically from pl/sql or the same rule is applied thatwe have to use a bind variable?I shall bereally thankfull if you make me understand by a demo.
Thanks and best regards,
Aman....

and Tom said...

1) http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2588723819082

you do not need to know the hash algorithm, I do not need to know it. It is not relevant. You just need to know "the query is hashed to provide an almost unique id for it"

2) if the parsing user ids are different - we need to verify that we can in fact reuse the query. That is, if user A parses "select * from t" and user B parses "select * from t" - we need to verify that they are in fact the same query - they might be very different (user A might have a table T, user B might be using a public synonym T that points to a view that accesses 50 tables)

3) it is that the query is

a) parsed as infrequently as possible because PLSQL is very good at caching sql in a session for us.

b) soft parsed almost all of the time - since the query is consistently formed (always the same string) and parsed by the same user (the owner of the plsql routine) and uses bind variables (all STATIC sql in plsql is bound PERFECTLY, optimally - no way around it)

c) only hard parsed when it is not actually in the shared pool - due to a restart, insufficient memory in the shared pool to cache it, gathering statistics - whatever...

4) if you execute:

select * from t where x=5;

5,000,000 times - it will likely be hard parsed once and soft parsed 4,999,999 times. It is not that literals themselves are EVIL, rather it is that if you execute

select * from t where x = 1;
select * from t where x = 2;
...
select * from t where x = 5000000;

the "same" statement only vary the literal values - that you are doing something horribly wrong.

In plsql - you would have to go out of your way and use dynamic sql to do this. If you stick with static sql in PLSQL - you have NO CHOICE but to bind correctly, the language FORCES you to do that.



If you are interested in lots of examples, in addition to the hundreds/thousands of them already present on this site - any of my books go into laborious detail on this.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Parsing schema/parsing user

Gary, December 09, 2007 - 9:15 pm UTC

"if the parsing user ids are different - we need to verify that we can in fact reuse the query. That is, if user A parses "select * from t" and user B parses "select * from t" - we need to verify that they are in fact the same query - they might be very different (user A might have a table T, user B might be using a public synonym T that points to a view that accesses 50 tables) "

Isn't this actually parsing_schema_id (eg if someone has done an ALTER SESSION SET CURRENT_SCHEMA) ? Won't parsing_user_id just relate to the security aspect (eg to force a reparse of a query if a revoke occurs).
Tom Kyte
December 10, 2007 - 11:05 am UTC

they did not qualify anything - "parse id" is very vague, I just went on that.


but part of the signature of the query is the optimizer environment, the users involved in parsing and such

Aman Sharma, December 10, 2007 - 12:02 pm UTC

hi sir,
thanks alot for the reply.There are couple of doubts still.
(2) I guess I didnt use the term properly.I meant that what do we really use parsing id of the statment, I meant to say that where do we usethe parsed statement?Every statement when parsed gets a hash value which is displayed in the V$sql and so on ,so what is the exact use of that?
Thanks alot for making the parsing user id concept clear too.I shall add it to my list of steps of parsing :).
3)
This is sort of clear but not properly sir.
a)So with pl/sql , we on-the-fly get caching of the cursors which is otherwise given by session_cached_cursors is it?
b)I didnt get the last part of the statement sir.
----and uses bind variables (all STATIC sql in plsql is bound PERFECTLY, optimally - no way around it)----
Can you please explain it?
4)
I am sorry but I didnt get the part of dynamic sql and static sql with pl/sql here.Can you please explain it?
Thanks and best regards,
Aman....


Tom Kyte
December 10, 2007 - 12:19 pm UTC

2) the hash is used to find the statement faster. the hash value is just a surrogate key used to identify the sql statement.

3)

a) no, plsql just caches cursors - when you say "close cursor C", plsql says "No, I won't, I'll pretend I did to make you happy, but I'm keeping this open in case you execute it again - it is more efficient that way"

session cached cursors is used by plsql to limit the number of cursors it keeps physically open, but - it keeps them open.

b) I don't know how else to say it. In plsql, if you use static sql (EG: NOT DYNAMIC) it is impossible to not use bind variables correctly. Therefore, you get the best possible chances for reuse of SQL - shared SQL.


4) in plsql, in order to execute

where x = 5
where x = 6
where x = 7
...
where x = 500000000

you would either

a) have to type in 500000000 sql statements and compile it. You won't do that.

b) use dynamic sql and build the SQL on the fly.


If you use dynamic sql, you can bind incorrectly (by not binding). If you use STATIC SQL (sql known at compile time), you have to bind (you will reference a plsql variable.


eg:

for i in 1 .. 5000000
loop
    execute immediate 'select count(*) from t where x='||i into N;
    select count(*) into N from t where x=i;
end loop;


the first select is dynamic sql, it will generate 5,000,000 UNIQUE sql statements, flood the shared pool and kill the system.

the second is "static sql" in plqsl, it will generate ONE statement and just use it over and over and over....

the second approach is correct, the first is very very very bad.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library