Oracle 11G: XMLQuery = eval startup 594127 1280

Oracle 11G: XMLQuery = eval

July 14, 2007 Database, IT, PL/SQL, XML 6 Comments

A nice little trick on Oracle 11G is using XMLQuery as an eval function:

SQL>l
  1  select substr( sys_connect_by_path( level, '*' ), 2 ) || ' = ' ||
  2         XMLQuery( substr( sys_connect_by_path( level, '*' ), 2 ) RETURNING CONTENT).getnumberval() product
  3       , substr( sys_connect_by_path( level, '+' ), 2 ) || ' = ' ||
  4         XMLQuery( substr( sys_connect_by_path( level, '+' ), 2 ) RETURNING CONTENT).getnumberval() sum
  5  from dual
  6* connect by level < 6
SQL>/
PRODUCT                   SUM
------------------------- -------------------------
1 = 1                     1 = 1
1*2 = 2                   1+2 = 3
1*2*3 = 6                 1+2+3 = 6
1*2*3*4 = 24              1+2+3+4 = 10
1*2*3*4*5 = 120           1+2+3+4+5 = 15


Anton   Marco’s first performance  investigation:


== First Time ==


Execution Plan

———————————————————-

Plan hash value: 1236776825


—————————————————————————–

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

—————————————————————————–

| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |

|* 1 | CONNECT BY WITHOUT FILTERING| | | | |

| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |

—————————————————————————–


Predicate Information (identified by operation id):

—————————————————


1 – filter(LEVEL<6)


Statistics

———————————————————-

2526 recursive calls

910 db block gets

1971 consistent gets

171 physical reads

16816 redo size

834 bytes sent via SQL*Net to client

407 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

191 sorts (memory)

0 sorts (disk)

5 rows processed


== Second Time ==


Execution Plan

———————————————————-

Plan hash value: 1236776825


—————————————————————————–

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

—————————————————————————–

| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |

|* 1 | CONNECT BY WITHOUT FILTERING| | | | |

| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |

—————————————————————————–


Predicate Information (identified by operation id):

—————————————————


1 – filter(LEVEL<6)


Statistics

———————————————————-

71 recursive calls

900 db block gets

169 consistent gets

0 physical reads

0 redo size

834 bytes sent via SQL*Net to client

407 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

5 rows processed


 


Disclaimer


The information demonstrated and shared here is based on Oracle beta software. The following is intended to outline Oracle’s general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.


															


												
				
				
								
				
								
				
								
				
				
											

										

										
Related Posts
					
										
										
					
Tags:, 
											

												
About The Author

																								
Anton Scheffer

												
Oracle Consultant at AMIS

											

																

							

		
6 Comments

		
    
					
  1. 
								
    
						
    
														Laurent Schneider															July 31, 2007
														
															
						
    
												
    
							
    
								
    Andre,
    
On the other hand, 11g documented the DBMS_ASSERT package to prevent sql injection 😎
    
							
    
							
    
															
    
						
    
					
    
					
    2. 
		
  2. 
								
    
						
    
														Andre															July 31, 2007
														
															
						
    
												
    
							
    
								
    I trust that 11g has a lot more attack surface than any other oracle version. EVAL functions are potential new sql injection opportunities, or am I wrong? Which would not make me happy, because I am not a pro hacker but a database customer.
    
Andre
    
							
    
							
    
															
    
						
    
					
    
					
    3. 
		
  3. 
								
    
						
    
														Marco Gralike															July 16, 2007
														
															
						
    
												
    
							
    
								
    Bases on Oracle 11g Beta autotrace output is as follows.
    

    == First Time ==
    

    Execution Plan
    
———————————————————-
    
Plan hash value: 1236776825
    

    —————————————————————————–
    
| Id  | Operation                    | Name | Rows  | Cost (%CPU)| Time     |
    
—————————————————————————–
    
|   0 | SELECT STATEMENT             |      |     1 |     2   (0)| 00:00:01 |
    
|*  1 |  CONNECT BY WITHOUT FILTERING|      |       |            |          |
    
|   2 |   FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
    
—————————————————————————–
    

    Predicate Information (identified by operation id):
    
—————————————————
    

       1 – filter(LEVEL
    
							
    
							
    
															
    
						
    
					
    
					
    4. 
		
  4. 
								
    
						
    
														Marco Gralike															July 16, 2007
														
															
						
    
												
    
							
    
								
    Nice post! I like it aswel. It gives and good example how you can use XMLDB functions in your day to day relational environment.
    
							
    
							
    
															
    
						
    
					
    
					
    5. 
		
  5. 
								
    
						
    
														Laurent Schneider															July 15, 2007
														
															
						
    
												
    
							
    
								
    I like this!
    
							
    
							
    
															
    
						
    
					
    
					
    6. 
		
  6. 
								
    
						
    
														Lucas Jellema															July 14, 2007
														
															
						
    
												
    
							
    
								
    You leave up it up to the reader to grasp the meaning of this – what the hack is an EVAL function. It is quite interesting of course: it allows in-place, immediate evaluation of dynamically constructed pieces of SQL – its like calling a PL/SQL function that uses EXECUTE IMMEDIATE or dbms_sql to process the string passed in and returns the result – without having to create the function. 
    

     Have you any comments on performance impact of using this eval wannabe?
    Lucas
    
							
    
							
    
															
    
						
    
					
    
					
    7.