| Parsing of sql-statements [message #451727] |
Fri, 16 April 2010 07:15  |
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
|
Senior Member |
|
|
Hi all,
(I'm not quite sure whether this topic belongs in this forum, so don't hesitate to move it to a more appropriate forum.)
I have some understanding of the parse (soft/hard) process, but maybe you can clarify something:
Session1 : as sysdba
SQL> alter system flush shared_pool;
System altered.
SQL> select sql_text, executions, parse_calls from v$sql where lower(sql_text) like '%dummy_a%' and lower(sql_text) not like '%sql_text%';
no rows selected
SQL>
Now in a other session as a ordinary user:
SQL> DECLARE
2 teller integer;
3 r dual.dummy%TYPE;
4 BEGIN
5 teller:=10;
6 WHILE (teller>=1) LOOP
7 BEGIN
8 execute immediate 'select dummy dummy_a from dual where dummy='''||to_char(teller)||'''' into r;
9 EXCEPTION
10 WHEN NO_DATA_FOUND
11 THEN NULL;
12 END;
13 teller:=teller-1;
14 END LOOP;
15 END;
16 /
PL/SQL procedure successfully completed.
SQL>
Back to the sys session:
SQL> col sql_text for a50
SQL> select sql_text, executions, parse_calls from v$sql where lower(sql_text) like '%dummy_a%' and lower(sql_text) not like '%sql_text%';
SQL_TEXT EXECUTIONS PARSE_CALLS
-------------------------------------------------- ---------- -----------
select dummy dummy_a from dual where dummy='9' 2 2
select dummy dummy_a from dual where dummy='6' 2 2
select dummy dummy_a from dual where dummy='1' 2 2
select dummy dummy_a from dual where dummy='2' 2 2
select dummy dummy_a from dual where dummy='4' 2 2
select dummy dummy_a from dual where dummy='8' 2 2
select dummy dummy_a from dual where dummy='7' 2 2
select dummy dummy_a from dual where dummy='10' 2 2
select dummy dummy_a from dual where dummy='3' 2 2
select dummy dummy_a from dual where dummy='5' 2 2
DECLARE teller integer; r dual.dummy%TYPE; BEG 1 1
IN teller:=10; WHILE (teller>=1) LOOP BEGI
N execute immediate 'select dummy dummy_a fr
om dual where dummy='''||to_char(teller)||'''' int
o r; EXCEPTION WHEN NO_DATA_FOUND
THEN NULL; END; teller:=teller-1; END
LOOP; END;
11 rows selected.
SQL>
This I understand...all the "generated" queries are different so they are parsed. So...I have a lot of parsing and a lot of sql in my shared_pool.
(Actually I find it kind of strange that all querys are executed twice allready. I executed the anonymous block just once.)
What I don't understand is the following:
Running the anonymous pl/sql block again:
SQL> /
PL/SQL procedure successfully completed.
SQL>
Back to the sys session:
SQL> select sql_text, executions, parse_calls from v$sql where lower(sql_text) like '%dummy_a%' and lower(sql_text) not like '%sql_text%';
SQL_TEXT EXECUTIONS PARSE_CALLS
-------------------------------------------------- ---------- -----------
select dummy dummy_a from dual where dummy='9' 3 3
select dummy dummy_a from dual where dummy='6' 3 3
select dummy dummy_a from dual where dummy='1' 3 3
select dummy dummy_a from dual where dummy='2' 3 3
select dummy dummy_a from dual where dummy='4' 3 3
select dummy dummy_a from dual where dummy='8' 3 3
select dummy dummy_a from dual where dummy='7' 3 3
select dummy dummy_a from dual where dummy='10' 3 3
select dummy dummy_a from dual where dummy='3' 3 3
select dummy dummy_a from dual where dummy='5' 3 3
DECLARE teller integer; r dual.dummy%TYPE; BEG 2 2
IN teller:=10; WHILE (teller>=1) LOOP BEGI
N execute immediate 'select dummy dummy_a fr
om dual where dummy='''||to_char(teller)||'''' int
o r; EXCEPTION WHEN NO_DATA_FOUND
THEN NULL; END; teller:=teller-1; END
LOOP; END;
11 rows selected.
SQL>
Why are all the "generated" sql's parsed again?
I just assume that a statement with literal values will allways be parsed. Is this correct?
Oh....cursor sharing is : exact
(The question arose because I was playing around with it  )
--edit : I have shamelessly used and then altered code from oracle-base
[Updated on: Fri, 16 April 2010 07:18] Report message to a moderator |
|
|
|
| Re: Parsing of sql-statements [message #451732 is a reply to message #451727] |
Fri, 16 April 2010 07:40  |
 |
Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
"execute immediate" internally explicitly calls parse.
This is also why you have 2 parses in the first execution:
- "execute immediate" calls parse (including to verify you gave the correct parameters and to know how to receive the result)
- then call SQL engine which executes itself a parse.
After the first execution, still the parse from "execute immediate" but SQL engine needs it no more.
I don't understand the 2 executions on first call.
Regards
Michel
[Updated on: Fri, 16 April 2010 07:46] Report message to a moderator |
|
|
|
| Re: Parsing of sql-statements [message #451734 is a reply to message #451732] |
Fri, 16 April 2010 07:48 |
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
|
Senior Member |
|
|
Ah .... great.
Thank you very much.
That explains it all.
The other test I created, the same but then with bind-variables, is now clear to me also.
thanks again
|
|
|
|
| Re: Parsing of sql-statements [message #451785 is a reply to message #451734] |
Fri, 16 April 2010 14:32 |
mnitu
Messages: 159
Registered: February 2008
Location: Reims
|
Senior Member |
|
|
Sorry, but I don't reproduce your results
Session 1
mni@DIANA> select banner from v$version
2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
mni@DIANA> alter system flush shared_pool;
SystÞme modifiÚ.
mni@DIANA> select sql_text, executions, parse_calls from v$sql where lower(sql_text) like '%dummy_a%' and lower(sql_text) not like '%sql_text%';
aucune ligne sÚlectionnÚe
Session 2
mni@DIANA> DECLARE
2 teller integer;
3 r dual.dummy%TYPE;
4 BEGIN
5 teller:=10;
6 WHILE (teller>=1) LOOP
7 BEGIN
8 execute immediate 'select dummy dummy_a from dual where dummy='''||to_char(teller)||'''' into r;
9 EXCEPTION
10 WHEN NO_DATA_FOUND
11 THEN NULL;
12 END;
13 teller:=teller-1;
14 END LOOP;
15 END;
16 /
ProcÚdure PL/SQL terminÚe avec succÞs.
Session 1
mni@DIANA> r
1* select sql_text, executions, parse_calls from v$sql where lower(sql_text) like '%dummy_a%' and lower(sql_text) not like '%sql_text%'
SQL_TEXT
-----------------------------------------------------------------------------------------------------------------------------------
EXECUTIONS PARSE_CALLS
---------- -----------
select dummy dummy_a from dual where dummy='9'
1 1
DECLARE teller integer; r dual.dummy%TYPE; BEGIN teller:=10; WHILE (teller>=1) LOOP BEGIN execute i
mmediate 'select dummy dummy_a from dual where dummy='''||to_char(teller)||'''' into r; EXCEPTION WHEN NO_DATA_FOUN
D THEN NULL; END; teller:=teller-1; END LOOP; END;
1 1
select dummy dummy_a from dual where dummy='6'
1 1
select dummy dummy_a from dual where dummy='1'
1 1
select dummy dummy_a from dual where dummy='2'
1 1
select dummy dummy_a from dual where dummy='4'
1 1
select dummy dummy_a from dual where dummy='8'
1 1
select dummy dummy_a from dual where dummy='7'
1 1
select dummy dummy_a from dual where dummy='10'
1 1
select dummy dummy_a from dual where dummy='3'
1 1
select dummy dummy_a from dual where dummy='5'
1 1
11 ligne(s) sÚlectionnÚe(s).
That's one parse, one execution! There is no trace for a parsing due to execute immediate and another parsing due to the Sql engine.
|
|
|
|
|
|
| Re: Parsing of sql-statements [message #451898 is a reply to message #451791] |
Sun, 18 April 2010 08:29 |
mnitu
Messages: 159
Registered: February 2008
Location: Reims
|
Senior Member |
|
|
Hope that's enough readable for you.
mni@DIANA> column sql_text format a60 wrapped
mni@DIANA> select sql_text, executions, parse_calls from v$sql where lower(sql_text) like '%dummy_a%' and lower(sql_text) not like '%sql_text%';
aucune ligne sÚlectionnÚe
mni@DIANA> r
1* select sql_text, executions, parse_calls from v$sql where lower(sql_text) like '%dummy_a%' and lower(sql_text) not like '%sql_text%'
SQL_TEXT EXECUTIONS PARSE_CALLS
------------------------------------------------------------ ---------- -----------
DECLARE teller integer; r dual.dummy%TYPE; BE 1 1
GIN teller:=10; WHILE (teller>=1) LOOP BEGI
N execute immediate 'select dummy dummy_a from dual
where dummy='''||to_char(teller)||'''' into r; EXC
EPTION WHEN NO_DATA_FOUND THEN NULL;
END; teller:=teller-1; END LOOP; END;
select dummy dummy_a from dual where dummy='9' 1 1
select dummy dummy_a from dual where dummy='6' 1 1
select dummy dummy_a from dual where dummy='1' 1 1
select dummy dummy_a from dual where dummy='2' 1 1
select dummy dummy_a from dual where dummy='4' 1 1
select dummy dummy_a from dual where dummy='8' 1 1
select dummy dummy_a from dual where dummy='7' 1 1
select dummy dummy_a from dual where dummy='10' 1 1
select dummy dummy_a from dual where dummy='3' 1 1
select dummy dummy_a from dual where dummy='5' 1 1
11 ligne(s) sÚlectionnÚe(s).
mni@DIANA>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
