Home » RDBMS Server » Performance Tuning » EXECUTE IMMEDIATE 'set autotrace on'; ORA-00922: missing or invalid option (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod || TNS for 32-bit Windows: Version 10.2.0.3.0 - Production)
icon4.gif  EXECUTE IMMEDIATE 'set autotrace on'; ORA-00922: missing or invalid option [message #422335] Tue, 15 September 2009 09:03 Go to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

Hi All,

Tried the below block with

EXECUTE IMMEDIATE 'set autotrace on';

But I'm getting
Error at line 1
ORA-00922: missing or invalid option
ORA-06512: at line 5


DECLARE
V_MONTH_END_DATE date:= '31-jan-2009';
BEGIN

EXECUTE IMMEDIATE 'set autotrace on'; 

   UPDATE bis.dds_issue_fclty
      SET acc_rk =
             (SELECT sf.orig_acc_rk
                FROM rcdwstg.stg_fin_acc sf
               WHERE sf.acc_rk = dds_issue_fclty.acc_rk
                 AND sf.source_system = 'CLASS'
                 AND sf.orig_acc_rk IS NOT NULL),
         update_time = sysdate
    WHERE dds_issue_fclty.source_system = 'PCASAV'
      AND dds_issue_fclty.valid_from_dt = (v_month_end_date)
      AND EXISTS (
             SELECT sf1.orig_acc_rk
               FROM rcdwstg.stg_fin_acc sf1
              WHERE sf1.acc_rk = dds_issue_fclty.acc_rk
                AND sf1.orig_acc_rk IS NOT NULL
                AND sf1.source_system = 'CLASS');

END;


Any help will be appreciated.

regards,
Nataraj
Re: EXECUTE IMMEDIATE 'set autotrace on'; ORA-00922: missing or invalid option [message #422336 is a reply to message #422335] Tue, 15 September 2009 09:05 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
"set autotrace on" is a SQL*Plus client command.

You can't use it in PL/SQL.
Re: EXECUTE IMMEDIATE 'set autotrace on'; ORA-00922: missing or invalid option [message #422339 is a reply to message #422336] Tue, 15 September 2009 09:16 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

This is a sample & i have so many queries like this inside the procedure.

i'll create & drop indexes as below inside dynamically.

CREATE INDEX RCDWSTG.LIE_STG_FIN_ACC_3 ON RCDWSTG.STG_FIN_ACC (ACC_RK, SOURCE_SYSTEM, ORIG_ACC_RK) LOCAL PCTFREE 0 NOLOGGING


I need to get the explain plan for the query inside dynamically the procedure to know whether it is using the relevant indexes.

regards,
Nataraj
Re: EXECUTE IMMEDIATE 'set autotrace on'; ORA-00922: missing or invalid option [message #422344 is a reply to message #422339] Tue, 15 September 2009 09:32 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
i'll create & drop indexes as below inside dynamically.


That's the worst idea I've heard this week.

Anyway, you can't get an explain plan back from a procedure.

You could do an

EXECUTE IMMEDIATE ('alter session set sql_trace=true');


And then process the trace-file that is generated on the server with tkprof.

Re: EXECUTE IMMEDIATE 'set autotrace on'; ORA-00922: missing or invalid option [message #422421 is a reply to message #422344] Wed, 16 September 2009 04:57 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:

Quote:
i'll create & drop indexes as below inside dynamically.


That's the worst idea I've heard this week.


You must be having a bad week.
I'd got to a month minimum on that one.

The reasons it's such a bad idea are:
1) It will prevent any inserts/updates on the indexed columns while the create index is running.
2) If another session has an uncommited change to that table, then your create index will error with an 'ORA-00054 Resource busy and acquired with NOWAIT'
3) It will invalidate all the current query plans for that table and require them to be reparsed

You'll get problems 2 and 3 again when you try to drop the index.

Just create the index and leave it there.
Previous Topic: Error Creating STATSPACK on Oracle10g
Next Topic: Use of Bind variable
Goto Forum:
  


Current Time: Sat Oct 01 09:14:52 CDT 2016

Total time taken to generate the page: 0.18841 seconds