Another dynamic sql using dbms_sql.execute [message #599333] |
Wed, 23 October 2013 13:32 |
|
lott42_gmail
Messages: 146 Registered: June 2010
|
Senior Member |
|
|
I'm working with old code that uses dbms_sql.execute to build/execute dynamic sql. In our case, the user can select varying columns(I think up to 20) with different where conditions as needed.
After building the sql, here's an example
WITH ph AS
(SELECT ph.* FROM po_header ph WHERE 1 = 2),
pf AS
(SELECT DISTINCT pf.order_id, pf.fund
FROM po_fau pf, ph
WHERE 1 = 1
AND ph.order_id = pf.order_id
AND pf.po_line > 0)
SELECT /*+ FIRST_ROWS */
'zzz',
ph.order_id,
ph.control_number,
ph.transactor,
decode(ph.po_type, 'Q', 'View DAPOs', po_general.parseponumber(ph.delivery_code, ph.po_number, ph.po_suffix, ph.po_type)) new_po_number,
pf.fund
FROM ph,
agreement a,
status_ids pst,
(SELECT column_value po_type, code_descr
FROM code_type_descriptors
WHERE table_name = 'PO_HEADER'
AND column_name = 'PO_TYPE') ct,
pf
WHERE ph.agreement_id = a.agreement_id(+)
AND ph.status_id = pst.status_id(+)
AND ph.po_type = ct.po_type(+)
AND ph.order_id = pf.order_id(+)
ORDER BY 2
Where table records for
po_header = ~567746
po_fau = ~2153570
and PK "order_id" is a NUMBER(10) not null
and a snippet of the code looks like
nDDL_Cursor := dbms_sql.open_cursor;
dbms_sql.parse(nDDL_Cursor, sSQLStr, 2);
FOR x IN 1 .. nCols LOOP
sCols(x) := '';
dbms_sql.define_column(nDDL_Cursor, x, sCols(x), 100);
END LOOP;
nError := dbms_sql.execute(nDDL_cursor);
my question is I'm a little confused as to why when the "execute" statement is fired off the elapsed time takes ~4.5 seconds but If I change "1 = 1" above to "1 = 2" it takes ~.2 seconds. If I run the above query interactively it takes ~.2 seconds. Shouldn't the above query when joining
ph.order_id = pf.order_id
return zero rows back instantly or does the "dbms_sql_execute" do some other type of parsing internally that takes cpu time.
I hope I gave you enough info. Please let me know otherwise
Thanks
|
|
|
|
|
Re: Another dynamic sql using dbms_sql.execute [message #599352 is a reply to message #599351] |
Wed, 23 October 2013 16:05 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
lott42 wrote on Thu, 24 October 2013 02:28I use pl/sql developer. With pl/sql developer, I went to command window after I was logged into the correct DB and schema.
Here's what I tried. I'm missing something??
Please be advised, command window of PL/SQL developer is not an alternative to SQL*Plus.
|
|
|
|
Re: Another dynamic sql using dbms_sql.execute [message #599354 is a reply to message #599353] |
Wed, 23 October 2013 16:13 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
lott42 wrote on Thu, 24 October 2013 02:39Quote:
Please be advised, command window of PL/SQL developer is not an alternative to SQL*Plus.
Can you tell me what I'm doing wrong with the above sql plus commands??
sql plus commands? Used where?
Neither I, nor anybody could guarantee that command window in PL/SQL Developer is equal to SQL*Plus.
|
|
|
|
Re: Another dynamic sql using dbms_sql.execute [message #599356 is a reply to message #599355] |
Wed, 23 October 2013 16:18 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
lott42 wrote on Thu, 24 October 2013 02:45OK, I got you. Maybe, I'm not actually running sql*plus from pl/sql developer. I'll try to figure out how to do that.
You can never invoke a SQL*Plus session through a 3rd party tool.
To answer your question, it's quite simple, open a SQL*Plus session, execute your code, copy$paste the results.
|
|
|
|
|
|
|
|
|
Re: Another dynamic sql using dbms_sql.execute [message #599556 is a reply to message #599455] |
Fri, 25 October 2013 18:40 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
[oracle@localhost ~]$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use 'schema.tablename' with 'explain=' option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first 'integer' SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
waits=yes|no Record summary for any wait events found in the trace file.
sort=option Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor
|
|
|