on 11-12-2013 10:17 AM
Hello
It's about a tricky problem I get with an undocumented Oracle parameter
I get that error on a Unix box with Oracle 11.2.0.3.7 SBP 2013-09
When accessing to any table through SE16 I get a dump:
Runtime Errors DBIF_RSQL_SQL_ERROR
Exception CX_SY_OPEN_SQL_DB
Date and Time 07.11.2013 14:01:45
UPDATE "PROGDIR" SET "RSTAT"=:A0 , "UNAM"=:A1 , "CNAM"=:A2 , "EDTX"=:A3 , "RLOAD"=:A4 ,
"OCCURS"=:A5\ [dbsc.c#2316],
"SUBC"=:A6 WHERE "NAME"=:A7 AND "STATE"=:A8 ; [dbsc.c#2318]
***LOG BY4=> sql error 1732 performing UPD on table PROGDIR
No error were reported at Oracle level.
It took a while before I discover that this was caused by parameter _simple_view_merging wrongly set to TRUE.
I did test this on a Windows system with Oracle 11.2.0.3 Bundle patch 22.
That time _simple_view_merging had to be set to FALSE to get the same Dump.
The thing is that I do not understand how that parameter can cause that problem has far as no IOTs are reported on my DB.
SELECT count(*) FROM DBA_INDEXES WHERE INDEX_TYPE = 'IOT - TOP' AND OWNER like 'SAP%';
COUNT(*)
0
I will be pleased to understand the root cause of that problem.
Anyway I think it is a good thing to know, that's why I did post this message.
Best regards
1915485 - Wrong results on Oracle Index Organized Table (IOT's)
A query involving IOT and secondary IOT indexes returns wrong results with the fix for bug 13705338 applied.
SELECT count(*) FROM DBA_INDEXES WHERE INDEX_TYPE = 'IOT - TOP' AND OWNER like 'SAP%';
COUNT(*)
0
If you see that your SAP user having a count > 0 then IOT's are created and you are affected by this bug.
If not, you don't need to apply the mentioned patch below.
1431798 - Oracle 11.2.0: Database Parameter Settings
September 30, 2013 => adjusted _simple_view_merging
September 19, 2013 => added _simple_view_merging = false for 11.2.0.3
_simple_view_merging FALSE (Note 1915485 - only whith IOT's)
UNIX SXD 11203x_date ( 2012 05 <= date <= 2013 10 )
WIN 11.2.0.3.nP ( 6 <= n <= 26 )
Hi Yves,
i think two things are mixed up here: Wrong result sets and an ORA-01732 (data manipulation operation not legal on this view) error. I don't know how the database object PROGDIR looks like in detail, but does this issue maybe occur on parsing?
For example on your unix box:
shell> sqlplus <SAP_SCHEMA_OWNER>/<PASS>
SQL> alter session set "_simple_view_merging"=TRUE;
SQL> explain plan for UPdATE "PROGDIR"
SET "RSTAT"=:A0 , "UNAM"=:A1 , "CNAM"=:A2 , "EDTX"=:A3 , "RLOAD"=:A4 , "OCCURS"=:A5, "SUBC"=:A6
WHERE "NAME"=:A7 AND "STATE"=:A8 ;
SQL> alter session set "_simple_view_merging"=FALSE;
SQL> explain plan for UpDATE "PROGDIR"
SET "RSTAT"=:A0 , "UNAM"=:A1 , "CNAM"=:A2 , "EDTX"=:A3 , "RLOAD"=:A4 , "OCCURS"=:A5, "SUBC"=:A6
WHERE "NAME"=:A7 AND "STATE"=:A8 ;
Regards
Stefan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Stefan,
Thank you for your help.
It is not a matter of a wrong result set.
It's a simple update query that is seen as incorrect when the _simple_view_merging parameter is changed from it's default value
I did run the query on sqlplus, replacing the bind variables and I get the same, error.
sqlplus "/as sysdba"
set lines 128
SELECT substr(a.ksppinm,1,20) "Parameter", substr(b.ksppstvl,1,5) "Session", substr(c.ksppstvl,1,5) "Instance" FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '%simple_view_merging%';
Parameter Session Instance
---------------------------------------- --------------- ----------
_simple_view_merging TRUE TRUE
UPDATE sapsr3."PROGDIR" SET rstat = 'S', unam = 'SAP*', cnam = 'SAP*', edtx = 'X' , rload = 'EN', occurs = '1', subc = '1' where name = '/1BCDWB/DBVARIS' and state = 'A';
1 row updated.
alter session set "_simple_view_merging"=FALSE;
SELECT substr(a.ksppinm,1,20) "Parameter", substr(b.ksppstvl,1,5) "Session", substr(c.ksppstvl,1,5) "Instance" FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '%simple_view_merging%';
Parameter Session Instance
---------------------------------------- --------------- ----------
_simple_view_merging FALSE TRUE
UPDATE sapsr3."PROGDIR" SET rstat = 'S', unam = 'SAP*', cnam = 'SAP*', edtx = 'X' , rload = 'EN', occurs = '1', subc = '1' where name = '/1BCDWB/DBVARIS' and state = 'A';
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
PROGDIR is a basic view on table Reposrc.
Best regards
SQL> select OBJECT_TYPE from dba_objects where owner = 'SAPSR3' and OBJECT_NAME = 'PROGDIR';
OBJECT_TYPE
-------------------
VIEW
SQL> Set lines 80 pages 500
SQL> SET LONG 2000
SQL> COLUMN VIEW_NAME FORMAT a20
SQL> COLUMN TEXT FORMAT a80 WORD_WRAPPED
SQL> select VIEW_NAME "View", TEXT "Code" from dba_views where view_name like 'PROGDIR';
View
------------------------------
Code
--------------------------------------------------------------------------------
PROGDIR
SELECT T1."PROGNAME", T1."R3STATE", T1."SQLX", T1."EDTX", T1."VARCL", T1."DBAPL"
, T1."DBNA", T1."CLAS", T1."TYPE", T1."OCCURS", T1."SUBC", T1."APPL", T1."SECU",
T1."CNAM", T1."CDAT", T1."UNAM", T1."UDAT", T1."VERN", T1."LEVL", T1."RSTAT", T
1."RMAND", T1."RLOAD", T1."FIXPT", T1."SSET", T1."SDATE", T1."STIME", T1."IDATE"
, T1."ITIME", T1."LDBNAME", T1."UCCHECK" FROM "REPOSRC" T1 WHERE ( T1."R3STATE"
= 'A' OR T1."R3STATE" = 'I' )
SQL> desc sapsr3.REPOSRC
Name Null? Type
----------------------------------------- -------- ----------------------------
PROGNAME NOT NULL VARCHAR2(120)
R3STATE NOT NULL VARCHAR2(3)
SQLX NOT NULL VARCHAR2(3)
EDTX NOT NULL VARCHAR2(3)
DBNA NOT NULL VARCHAR2(6)
CLAS NOT NULL VARCHAR2(12)
TYPE NOT NULL VARCHAR2(9)
OCCURS NOT NULL VARCHAR2(3)
SUBC NOT NULL VARCHAR2(3)
APPL NOT NULL VARCHAR2(3)
SECU NOT NULL VARCHAR2(24)
CNAM NOT NULL VARCHAR2(36)
CDAT NOT NULL VARCHAR2(24)
VERN NOT NULL VARCHAR2(18)
LEVL NOT NULL VARCHAR2(12)
RSTAT NOT NULL VARCHAR2(3)
RMAND NOT NULL VARCHAR2(9)
RLOAD NOT NULL VARCHAR2(3)
UNAM NOT NULL VARCHAR2(36)
UDAT NOT NULL VARCHAR2(24)
UTIME NOT NULL VARCHAR2(18)
DATALG NOT NULL NUMBER(10)
VARCL NOT NULL VARCHAR2(3)
DBAPL NOT NULL VARCHAR2(3)
FIXPT NOT NULL VARCHAR2(3)
SSET NOT NULL VARCHAR2(3)
SDATE NOT NULL VARCHAR2(24)
STIME NOT NULL VARCHAR2(18)
IDATE NOT NULL VARCHAR2(24)
ITIME NOT NULL VARCHAR2(18)
LDBNAME NOT NULL VARCHAR2(60)
UCCHECK NOT NULL VARCHAR2(3)
MAXLINELN NOT NULL NUMBER(5)
DATA BLOB
Hi Yves,
thanks for the detailed information.
I knew that your current issue is not about the wrong result set, but you have mentioned SAPnote #1915485 which explains this as a reason for setting this parameter - in reality there are some others as well
I am pretty sure that this is not an issue with the execution of the SQL - it is an issue with parsing. Can you please run my previous posted SQL example on your unix box and post the result.
Regards
Stefan
Stefan,
Sure that parameter must have other effects, but I was not able to find that much information about them on the Web, even on Metalink.
It is strange because the view & the update statement are very simple.
DST Upgrade using DBMS_DST.BEGIN_PREPARE fail with ORA-2014 (Doc ID 1407273.1)
The same issue has been reported under Bug 10138792 ORA-2014 ON SEGMENT ADVISOR SELECT STMT WHEN _SIMPLE_VIEW_MERGING=FALSE, closed as not a bug. When the SELECT FOR UPDATE statement applied to view, anything that prevents view merging from taking place would result in ORA-2014 error; "anything" could be no_merge hint, setting the parameter "_simple_view_merging" to false, or something in the query structure that prevents view merging. In this case, the database has "_simple_view_merging" set to FALSE and the DBMS_DST.BEGIN_PREPARE has similar calls to SELECT FOR UPDATE statements.
Bug 12537316 - ORA-600 / ORA-7445 for SQL with merged subquery (Doc ID 12537316.8)
When a query has a view with a select sub-query, after the view is merged and the select sub-query appears in places other than the select list then ORA-600 or ORA-7445 errors may be produced.
Find in attachment the command run on the Windows box.
I do not have a remote access to the Unix box, I'll try to have someone running it for me tomorrow and I will post the result.
Thanks for you help
Hi Yves,
i have done a little bit of researching about this with an adopted and stripped down example on Oracle 11.2.0.3.6.
SQL> create table TESTME (a VARCHAR2(10), B VARCHAR2(2));
SQL> insert into TESTME values ('TEST','A');
SQL> commit;
SQL> create view TESTME_V as select A from TESTME where B = 'A';
SQL> alter session set "_simple_view_merging"=TRUE;
SQL> explain plan for UPDATE TESTME_V SET A = 'TESTU' where A = 'TEST';
SQL> alter session set "_simple_view_merging"=FALSE;
SQL> explain plan for UPDATE TESTME_V SET A = 'TESTU' where A = 'TEST';
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
So far it is reproducible with that stripped exampled. It behaves the same way on 11g R2 and 12c R1. Parameter "_simple_view_merging" is described as "control simple view merging performed by the optimizer". As this issue occurs by parsing - let's do some CBO traces.
*** CBO Trace (with "_simple_view_merging"=TRUE;") ***
CVM - complex view merging
SPJ - select-project-join
…
OJE: Begin: find best directive for query block SEL$1 (#0)
OJE: End: finding best directive for query block SEL$1 (#0)
CVM: Merging SPJ view SEL$1 (#0) into UPD$1 (#0)
Registered qb: SEL$DA9F4B51 0x7b31eab8 (VIEW MERGE UPD$1; SEL$1)
…
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT 0 FROM SYS."TESTME" "TESTME" WHERE "TESTME"."A"='TEST' AND "TESTME"."B"='A'
kkoqbc: optimizing query block SEL$DA9F4B51 (#0)
…
*** CBO Trace (with "_simple_view_merging"=FALSE;") ***
OJE: Begin: find best directive for query block SEL$1 (#0)
OJE: End: finding best directive for query block SEL$1 (#0)
SVM: SVM bypassed: Parameter.
Take a look at the cost based query transformation in the example with simple view merging enabled (for UPDATE statement). At last let's take a look at the detailed execution plans in case of SELECTs and the corresponding working UPDATE.
SQL> alter session set "_simple_view_merging"=TRUE;
SQL> select A from TESTME_V where A = 'TEST';
-----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| TESTME | 1 | 10 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("A"='TEST' AND "B"='A'))
SQL> alter session set "_simple_view_merging"=FALSE;
SQL> select A from TESTME_V where A = 'TEST';
--------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | VIEW | TESTME_V | 1 | 7 | 2 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TESTME | 1 | 10 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("B"='A' AND "A"='TEST'))
SQL> alter session set "_simple_view_merging"=TRUE;
SQL> UPDATE TESTME_V SET A = 'TESTU' where A = 'TEST';
------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 2 (100)| |
| 1 | UPDATE | TESTME | | | | |
|* 2 | TABLE ACCESS FULL| TESTME | 1 | 10 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("A"='TEST' AND "B"='A'))
If simple view merging is enabled the view definition is resolved (eliminated) and a simple SELECT or UPDATE with the concatenated predicates is performed on the underlying table. Otherwise only the predicate is pushed down, but this works in a SELECT scenario only. In case of an UPDATE the optimizer took notice of that and stops at the point of SVM.
Regards
Stefan
P.S.: I can highly recommend the presentation of Jože Senegačnik about "Query Transformations", if you are interested into such query transformations in more detail.
A quick update, SAP has released a note on that problem...
1922481 - ORA-01732: data manipulation operation not legal on this view
Funny to notice that there is a mistake, (at least in version 2) of the note as undocumented (beginning with an underscore) parameters cannot be queried through view v$parameter...
select value from v$parameter where name='_simple_view_merging';
no rows selected
To find the value of hidden parameter, as stated few lines upper:
SELECT substr(a.ksppinm,1,20) "Parameter", substr(b.ksppstvl,1,5) "Session", substr(c.ksppstvl,1,5) "Instance" FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '%simple_view_merging%';
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.