Does Dynamic SQL (DBMS_SQL) performance lacks?

Does Dynamic SQL (DBMS_SQL) performance lacks?

Post by Jez Kewl » Sat, 25 Jul 1998 04:00:00



Hi folks!

We encountered the following phenomena with dynamic SQL insert-statements
using ORACLE 7.3.2.2 on DEC ALPHA VMS 7.1.

A table with aprox. 30 attributes, rowsize aprox. 160 bytes, 4 indexes
aprox 100 bytes.

A double loop like

        for i in 1..100
                for j in 1..100
                        INSERT INTO TABLE
                                ...

is aprox. 3 TIMES FASTER

than a loop that isses a call to another package that uses DBMS_SQL to
insert the data via a dynamic statement.

To prevent the first question - OF COURSE the statement is PARSED only
once. All calls after the first one only use DBMS_SQL.BIND_VALUE and
.EXECUTE

What I found out so far is that the BIND-Statements (and there are 30 of
them) take a HELL LOT OF CPU - the dynamic SQL insert is CPU-bounded.          

I wonder if this is the designed behaviour or if any of you ever
encountered a similar CPU-eating performance problem.

Do you have any clues for speeding up this dynamic PL/SQL stuff or do we
have to go back to OCI ???

Regards and thank in advance,
Jez Kewler

 
 
 

1. dynamic SQL with DBMS_SQL

Hi ,
I am attempting to use DBMS_SQL to dynamically create tables or views.
These are my sample codes:

PROCEDURE DYNSQL AS
  cur integer;
  rc  integer;
BEGIN
  cur := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.V7);
  rc := DBMS_SQL.EXECUTE(cur);
  DBMS_SQL.CLOSE_CURSOR(cur);
END;

It compiles fine but when I try to run it, I get the following errors:

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
ORA-06512: at "SYS.DBMS_SQL", line 25
ORA-06512: at "HUM_WWW00.DYNSQL", line 6
ORA-06512: at line 4

I have usd to select and update using DBMS_SQL dynamically without any
problem
I am pretty sure I have privileges create table and views.  Would I need
higher privs  to dynamically create tables or views?

Thanks in advance for your help

SH

2. (Q)Pl/sql cartriger id/passwd

3. dbms_sql & dynamic sql problem

4. dbcombo

5. dynamic SQL with dbms_sql, example?

6. Trap or locked agent when try create stored procedure

7. Reguarding DBMS_SQL and Dynamic SQL

8. HELP WITH DYNAMIC SQL (DBMS_SQL PACKAGE)

9. Dynamic SQL with DBMS_SQL cursor

10. Dynamic Sql (DBMS_SQL package) Question

11. DBMS_SQL, native dsql, static sql performance