Home » RDBMS Server » Performance Tuning » EXECUTE IMMEDIATE Command (Oracle 8i)
EXECUTE IMMEDIATE Command (Oracle 8i) [message #64826] Tue, 17 February 2004 03:26 Go to next message
Patrick Tahiri
Messages: 119
Registered: January 2004
Senior Member
Hi,

I have just started as a DBA in a telecom company, and I can see many times in procedures (inside packages) the EXECUTE IMMEDIATE command with the USING option to update, insert some tables or other look-up tables. Some of these tables have 200 000 rows and 2 tables have more than 3 million rows.

We are expreriencing some performance problems and I have been attributed the task to improve the packages code!

I'm wondering at performance level if using such commands (DML) inside an EXECUTE IMMEDIATE (UPDATE, INSERT) is good or bad!?? Isn't better at performance level to use the DML as it is inside the procedure (inside the package) without the EXECUTE IMMEDIATE ... USING..?

Does the DML (SELECT, INSERT; UPDATE, DELETE) embeded in an EXECUTE IMMEDIATE ... USING makes use of the indexes on the targeted tables? Or we have always a table scan with EXECUTE IMMEDIATE??

Thank you for your precious help and tips.

Regards,

Patrick Tahiri.

PS: I'm using Oracle 8i
Re: EXECUTE IMMEDIATE Command (Oracle 8i) [message #64830 is a reply to message #64826] Tue, 17 February 2004 07:48 Go to previous messageGo to next message
Michel Bartov
Messages: 35
Registered: February 2003
Member
To improve performance, you need to use bind variables. In this case use PREPARE Statement then EXECUTE with bind variables rather than using EXECUTE IMMEDIATE.

Michel.
http://www.barsoft.net/
Re: EXECUTE IMMEDIATE Command (Oracle 8i) [message #64834 is a reply to message #64826] Tue, 17 February 2004 09:26 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Patrick,
if you are using static sql, then you dont need( and preferably shouldnt) to use Execute immediate. Use Execute Immediate for Dynamic SQL.

Yes,DML inside of EXECUTE IMMEDIATE should use available indexes.Eventually its the same optimizer that devices the execution plan.

-Thiru
Re: EXECUTE IMMEDIATE Command (Oracle 8i) [message #64836 is a reply to message #64834] Tue, 17 February 2004 21:58 Go to previous messageGo to next message
Patrick Tahiri
Messages: 119
Registered: January 2004
Senior Member
Hi Thiru,

Thank you for all your support these last days!!
I'm quite busy as I have several tasks to be finish with asap here...
I find just now the time to say thank you for all the tips and theory you gave to me!! Very useful!!
I always look first in my Oracle documents and on forums, but it's not always "exactely" what I'm looking for! Sometimes I'm looking for some technics or "cook" technics to resolve some specific problems and I can't find it neither in books or on the net!

I'm feeling comfortable only when I fell that I understood deeply, generally speaking, the concepts, methods that I'm applying to my servers! And not only applying what it's written and advised without understanding more than that...
And you give me that + !!! GREAT!!

Best regards,

Patrick Tahiri.
Re: EXECUTE IMMEDIATE Command (Oracle 8i) [message #65278 is a reply to message #64830] Sat, 17 July 2004 00:18 Go to previous message
pk
Messages: 12
Registered: September 2000
Junior Member
dear sir,

i am executing below code :

DECLARE
v_sql VARCHAR2(100);
v_date DATE;
BEGIN
v_sql := 'SELECT Sysdate FROM dual';
EXECUTE IMMEDIATE v_sql INTO v_date;
END;

It giving the error :

ERROR at line 6:
ORA-06550: line 6, column 11:
PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following:
:= . ( @ % ;
ORA-06550: line 7, column 1:
PLS-00103: Encountered the symbol "END" The symbol "END" was ignored.

Pls tell me <execute immediate> command execute on oracle8i, if yes then tell me solution. how i can use it

thanks,
Previous Topic: Nested loop Vs Inlist iterator
Next Topic: Problem in Trace
Goto Forum:
  


Current Time: Sun Mar 03 02:11:08 CST 2024