DBMS_SYS_SQL (line 1120)
J, October 11, 2006 - 2:26 pm UTC
Hello Tom.
Not sure if this question goes here or under a dbms_profiler question, but here goes: Here's an edited snippet of a profiler test run that took 143 seconds to run. Out of that run, DBMS_SYS_SQL (line 1120) took 61.42 seconds of the run and was by far the #1 time-consumer.
PCT UNIT_OWNER UNIT_NAME LINE# TEXT
------ ----------- -------------- ------ -----------------
42.0 SYS DBMS_SYS_SQL 1120
29.7 SMS PKG_ABC 5114 blah blah blah
3.5 SMS PKG_DEF 124 blah blah blah
Hmmm... I can dive into PKG_ABC to look for tuning opportunities, but I'm really uninformed as to what is going on with DBMS_SYS_SQL - the associated text column is null (and of course the package is gibberish - DON'T want to touch it), so I'm at sea...
Any thoughts or ideas about what the slow-up might be would be most appreciated.
Regards,
- J
October 11, 2006 - 4:06 pm UTC
likely the place where your sql is being executed there, you don't have any more insight into it than that, you cannot change what it does, you can only avoid calling it.
DBMS_SYS_SQL
Carlos, February 07, 2007 - 3:10 am UTC
Hi Tom,
I'm playing with the dbms_sql package to use it in a future development and i've found a little problem. When i try to create a table with this kind of dinamyc sql (i know its a very bad approach and huge expensive) i get differente results depending on the way i execute it. See the example please:
SQL> DECLARE
2 cursorPointer INTEGER := DBMS_SQL.OPEN_CURSOR;
3 cursorQuery VARCHAR2(500) := 'CREATE TABLE fooTable (OID NUMBER(18))';
4 BEGIN
5 DBMS_SQL.PARSE(cursorPointer,cursorQuery,dbms_sql.native);
6 DBMS_OUTPUT.PUT_LINE(DBMS_SQL.EXECUTE(cursorPointer));
7 DBMS_SQL.CLOSE_CURSOR(cursorPointer);
8 END;
9 /
Procedimiento PL/SQL terminado correctamente.
SQL>select * from fooTable;
ninguna fila seleccionada
Transcurrido: 00:00:00.00
SQL>drop table fooTable;
Tabla borrada.
SQL>CREATE OR REPLACE PROCEDURE fooProcedure AS
2 cursorPointer INTEGER := DBMS_SQL.OPEN_CURSOR;
3 cursorQuery VARCHAR2(500) := 'CREATE TABLE fooTable (OID NUMBER(18))';
4 BEGIN
5 DBMS_SQL.PARSE(cursorPointer,cursorQuery,dbms_sql.native);
6 DBMS_OUTPUT.PUT_LINE(DBMS_SQL.EXECUTE(cursorPointer));
7 DBMS_SQL.CLOSE_CURSOR(cursorPointer);
8 END;
9 /
Procedimiento creado.
SQL>exec fooProcedure;
BEGIN fooProcedure; END;
*
ERROR en línea 1:
ORA-01031: privilegios insuficientes
ORA-06512: en "SYS.DBMS_SYS_SQL", línea 906
ORA-06512: en "SYS.DBMS_SQL", línea 39
ORA-06512: en "TESTUSER.FOOPROCEDURE", línea 5
ORA-06512: en línea 1
I use a 10.2.0.1.0 version of oracle.
Thanks in advance
February 07, 2007 - 5:25 pm UTC