After an upgrade from 10.2.0.2 to 10.2.0.3 for a client, I encountered with the following error :
SQL> startup nomount
ORA-00940: invalid ALTER command
Interestingly, after removing spfile and creating a pfile with simple option, the issue was resolved.
It turned out to be because of a hidden parameter which disable/enable a bug in 10.2.0.2 database which does not exist in 10.2.0.3. Parameter was _fix_control=5385629:on which turns on the fix for bug 5385629 in 10.2.0.2 but since this bug does not exist in 10.2.0.3, having this parameter in spfile caused a problem as Oracle can not enable this bug in 10.2.0.3.
Please be advised to make sure spfile is good and healthy and it has not this parameter if you get this error.
TIP 73#: How to have execution plan with run-time
As you may know, the normal execution plan does not give you run-time of each pieces of any execution plan, Time which is reported is not exactly real elapsed time of SQL statement.
There are 2 ways to find out run-time of SQL statement which could be used greatly in SQL tuning :
Option 1 :
Trace SQL statement when statistics set to All and find out run-time from the trace file.
Obviously this option is traditional/old way which uses resources and takes time to scan trace file and find out run-time.
Option 2 :
Use gather_plan_statistics in SQL statement which let Oracle to gather most required stats for SQL tuning.
This option is simpler and faster.
I demonstrate it with the following example :
Step 1 : Enable sqlplus trace :
In order to prevent SQL statement to output the result on screen, it is better of to enable trace in order to just run and report statistics.
set autotrace traceonly stat;
Step 2 : Run query with hint:
My sample runs a simple query against test1 table.
SQL> desc test1
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(30 CHAR)
SQL> select /*+ gather_plan_statistics */ * from test1 where id=:myid;
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
648 bytes sent via SQL*Net to client
488 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
3. Find SQL statement in shared pool
SQL> select sql_id,executions from v$sql where sql_text='select /*+ gather_plan_statistics */ * from test1 where id=:myid';
SQL_ID EXECUTIONS
------------- ----------
82u1sypmr2k1f 1
4. Find execution plan with all run-time :
SQL> select * from table(dbms_xplan.display_cursor('82u1sypmr2k1f',null,'ALL IOSTATS LAST'));
As you see, E-Time shows execution plan which definitely is helpful to find out bottleneck in SQL execution and to find out at which part SQL statement is taking most of the time.
There are 2 ways to find out run-time of SQL statement which could be used greatly in SQL tuning :
Option 1 :
Trace SQL statement when statistics set to All and find out run-time from the trace file.
Obviously this option is traditional/old way which uses resources and takes time to scan trace file and find out run-time.
Option 2 :
Use gather_plan_statistics in SQL statement which let Oracle to gather most required stats for SQL tuning.
This option is simpler and faster.
I demonstrate it with the following example :
Step 1 : Enable sqlplus trace :
In order to prevent SQL statement to output the result on screen, it is better of to enable trace in order to just run and report statistics.
set autotrace traceonly stat;
Step 2 : Run query with hint:
My sample runs a simple query against test1 table.
SQL> desc test1
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(30 CHAR)
SQL> select /*+ gather_plan_statistics */ * from test1 where id=:myid;
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
648 bytes sent via SQL*Net to client
488 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
3. Find SQL statement in shared pool
SQL> select sql_id,executions from v$sql where sql_text='select /*+ gather_plan_statistics */ * from test1 where id=:myid';
SQL_ID EXECUTIONS
------------- ----------
82u1sypmr2k1f 1
4. Find execution plan with all run-time :
SQL> select * from table(dbms_xplan.display_cursor('82u1sypmr2k1f',null,'ALL IOSTATS LAST'));
As you see, E-Time shows execution plan which definitely is helpful to find out bottleneck in SQL execution and to find out at which part SQL statement is taking most of the time.
Subscribe to:
Posts (Atom)