Home » SQL & PL/SQL » SQL & PL/SQL » Strange experince with Oracle 10g ! (Oracle10g, 10.2.0.1.0, RHL 5.1 SE)
Strange experince with Oracle 10g ! [message #410130] Thu, 25 June 2009 07:18 Go to next message
msmallya77
Messages: 28
Registered: June 2009
Location: Ahmedabad
Junior Member
Hi,

I have a trigger ddltrigger which logs any DDL statements issued in a ddl_log table. The trigger code is
create or replace trigger DDLTRIGGER  
AFTER DDL ON DATABASE

declare
     v_program varchar2(100);
     v_terminal varchar2(100);
BEGIN
    select 
	a.program, a.terminal into v_program, v_terminal 
	from v$session a, (Select Distinct Sid from V$mystat) b
	where
	audsid=sys_context('USERENV','SESSIONID') and
	a.sid=b.sid and (trim(terminal) not like 'UNK%'
	or trim(terminal) is not null);

    procins(ora_login_user,ora_sysevent,ora_dict_obj_type,
                 ora_dict_obj_owner,ora_dict_obj_name,v_program) ;
    if v_terminal not in ('MURLIDHARM') then
       if (ora_sysevent='DROP' and substr(v_program,1,3) <> 'exp') then
          raise_application_error(-20000,'ACTION DENIED');
       end if;
    end if;
END ;

and procedure procins code is (which is called by the above trigger)
create or replace PROCEDURE procins 
	(ologin 	varchar2,
 	 oevent 	varchar2,
 	 objtype 	varchar2,
 	 oobjowner 	varchar2,
 	 oobjname 	varchar2,
	 v_program 	varchar2) IS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN 
insert into
   sys.ddl_log 
	(username, ddl_date, ddl_type, object_type,
	 owner, object_name, terminal, program) 
   values
	(ologin, sysdate, oevent, objtype, oobjowner, oobjname,
	 sys_context('USERENV','TERMINAL'), v_program);
   COMMIT; 
END;

One of the user issued the Drop statement and it did not allow to drop the table.

But after the activity I have noticed that
SQL> select * from tab where tname='TEMP_YP'
select * from temp_yp
              *
ERROR at line 1:
ORA-00942: table or view does not exist

AND
SQL> select * from tab where tname='TEMP_YP';
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEMP_YP                        TABLE

Also
SQL> select table_name, column_name, data_type
from user_tab_columns where table_name='TEMP_YP';
TABLE_NAME                     COLUMN_NAME                      DATA_TYPE
------------------------------ -------------------------------- ---------
TEMP_YP                        D                                CHAR

So I tried to drop the table, it gives
SQL> drop table temp_yp;
drop table temp_yp
           *
ERROR at line 1:
ORA-00942: table or view does not exist


I suspect because of the trigger this has happened i.e some of the dictionary tables it has applied the changes and some it has rolled back(?), As procedure (procins autonomous transaction) commits and ddltrigger raised application error which has rolled back changes(?)

I have also experinced same problem with another user process, which drops the table and recreates the table. But after some workaround, I tried to insert a row in that table for one column which was known to the users and it started behaving properly means I could select, describe and all.

But the TEMP_YP has only one column and I tried this tactis i.e. to insert a row. But again it gives

SQL> insert into temp_yp values('A');
insert into temp_yp values('A')
            *
ERROR at line 1:
ORA-00942: table or view does not exist

In Oracle 9i this was working without any side effects!!!

Please help me to get rid of this problem and solve the existing disintegrity.

Thanking you in advance,

MSMallya
Re: Strange experince with Oracle 10g ! [message #410135 is a reply to message #410130] Thu, 25 June 2009 07:29 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you creating tables in sys?
Re: Strange experince with Oracle 10g ! [message #410137 is a reply to message #410130] Thu, 25 June 2009 07:35 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I'd second the notion to get out and stay out of the sys schema.

Also, why are you running 10.2.0.1, the base/buggy version of 10gR2?
Re: Strange experince with Oracle 10g ! [message #422792 is a reply to message #410137] Sat, 19 September 2009 04:50 Go to previous messageGo to next message
msmallya77
Messages: 28
Registered: June 2009
Location: Ahmedabad
Junior Member
Hi All,

I have some queries. I am continuing with same topic, ie strange behaviour in 10g.

I have experienced same query giving different results at different time.

Some times in group by query. Also selection from 2-3 tables join. Some times returning correct results some times wrong results. I am getting confused, worried whether I have done a mistake by switching from 9i to 10g.

Also planning to switch back and then do the followup with Metalink. Any suggestions?

Thanks and Regards,

MSMallya

[Updated on: Sat, 19 September 2009 04:51]

Report message to a moderator

Re: Strange experince with Oracle 10g ! [message #422802 is a reply to message #422792] Sat, 19 September 2009 09:26 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
then do the followup with Metalink. Any suggestions?

Useless.
The first thing Oracle will ask is do you do this with SYS? If yes, go back with something supported.
The second will be to say you to upgrade to 10.2.0.4 and check if the same thing happens.

- Never ever use SYS (or SYSDBA) but for maintenance purpose (startup, shutdown, backup, recover)
- SYS/SYSDBA is special
- SYS/SYSDBA is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS/SYSDBA" and you'll see the immediate answer)
- SYS/SYSDBA does not act like any other user
- When you use SYS/SYSDBA Oracle deactivates some code path and activates others
- Whatever you do with SYS/SYSDBA will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS/SYSDBA for anything that can be done by another user.
Use SYS/SYSDBA ONLY for something that can't be done by someone else.


Regards
Michel
Re: Strange experince with Oracle 10g ! [message #422873 is a reply to message #422802] Mon, 21 September 2009 00:13 Go to previous messageGo to next message
msmallya77
Messages: 28
Registered: June 2009
Location: Ahmedabad
Junior Member
Hi,

Thanks for the suggestions.

By the way, I had only one trigger in sys schema. After facing problem (first time), I have dropped that trigger and also I am very well aware and also not using sys schema and not allowing any one to have thier objects on sys/system schema as well.

But that objects disintegrity experienced after switching from 9i to 10g, and in the meantime the problem was resolved.

But now, I am facing problems in SQL queries, as mentioned in my earlier post, (in group by query and in select query from 3 tables - wake results sometimes, correct results sometimes from the same query).

As you have suggested to switchover to 10.2.0.4, I am not finding any version which can be installed on -
Red Hat Enterprise Linux Server release 5.1 (Tikanga)
Kernel 2.6.18-53.el5PAE on an i686. If any please suggest.

Thanks and Regards,

MSMallya
Re: Strange experince with Oracle 10g ! [message #422880 is a reply to message #422873] Mon, 21 September 2009 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
As you have suggested to switchover to 10.2.0.4, I am not finding any version which can be installed on -

Go to Metalink site and click on "Patches and Updates" tab.

Regards
Michel
Re: Strange experince with Oracle 10g ! [message #422912 is a reply to message #410130] Mon, 21 September 2009 02:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't think this is a bug at all - I think this is just a privileges problem.
I think that the user you created the procedues as has access to them via a role, and doesn't have a direct grant to let them insert into the table.

That would make the problem look like this:

1) You issue the DROP TABLE temp_yp command
2) The DDL trigger fires, and runs the stored procedure Procins.
3) Procins attemtps to insert into the table sys.ddl_log, but doesn't have the required privs, and so raises and ORA-00942, and the drop also fails.

Re: Strange experince with Oracle 10g ! [message #422927 is a reply to message #422912] Mon, 21 September 2009 04:40 Go to previous messageGo to next message
msmallya77
Messages: 28
Registered: June 2009
Location: Ahmedabad
Junior Member
Hi,

Thanks for your views / possibilities. But this I have already resolved. I would like you to comment on my latest post dated 19th Sept.2009, where I have some problems in running queries - Same Query giving different results at different time.

Thanks and Regards,

MSMallya
Re: Strange experince with Oracle 10g ! [message #422930 is a reply to message #422927] Mon, 21 September 2009 04:50 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
When a query gives different results then either the data has changed, or the query is written wrong.

Since we can see neither the data nor the query, there is not much to comment on.
Re: Strange experince with Oracle 10g ! [message #422931 is a reply to message #422927] Mon, 21 September 2009 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or there is a bug in the optimizer and there are many in 10.2.0.1.
Search for "wrong results" in Metalink.

Regards
Michel

[Updated on: Mon, 21 September 2009 04:57]

Report message to a moderator

Re: Strange experince with Oracle 10g ! [message #422934 is a reply to message #422930] Mon, 21 September 2009 05:16 Go to previous messageGo to next message
msmallya77
Messages: 28
Registered: June 2009
Location: Ahmedabad
Junior Member
Hi ThomasG,

The same query when run gives different result against static data.

Example : I have a query when run it sometimes inserts 78330 Recs
sometimes (same query without modification) inserts 2920 recs!

The data is static and query is selecting recs from 4 tables for the selected date range.

If you want to analyze I can post the query, but as far as data
concerened 4 tables contains 1) 77000 2) 12lacs 3) 61000 4) 37000 recs and will not be able to provide the data.

Thanks and Regards,

MSMallya
Re: Strange experince with Oracle 10g ! [message #422937 is a reply to message #422934] Mon, 21 September 2009 05:24 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, either it is an optimizer bug as Michael mentioned, or you rely on some implicit conversions and/or order of data in the query that change the outcome from one run to the other somehow.

The very least you should post is the actual query, and the SQL session where you run it two times with different row counts.
Re: Strange experince with Oracle 10g ! [message #423185 is a reply to message #422937] Tue, 22 September 2009 08:14 Go to previous messageGo to next message
msmallya77
Messages: 28
Registered: June 2009
Location: Ahmedabad
Junior Member
Hi,

I was not aware of the bugs that were surfaced, while switching from 9i to 10gR2 (As I was relying on Oracle software and till 9i I did not face any problems / bugs. Even on 10.2.0.2.0 worked for 3 months (on solaris x86) and not experinced any problems).

Now I am planning to go back to 9iR2 and then Resolve 10g Bugs.
Because I have to immediately attack the problems.

My only worry is can I goback to 9.2.0.4 on
Red Hat Enterprise Linux Server release 5.1 (Tikanga)
Kernel 2.6.18-53.el5PAE on an i686

Please advise.

Thanks and Regards,

MSMallya
Re: Strange experince with Oracle 10g ! [message #423186 is a reply to message #423185] Tue, 22 September 2009 08:24 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You posted your version originally as 10.2.0.1.0.

If that really is your server version, than that is the first released version of 10gR2, which has a number of bugs. (As most people expect)

You should upgrade to the latest 10.2.0.4.0 patch set.
Re: Strange experince with Oracle 10g ! [message #423188 is a reply to message #423185] Tue, 22 September 2009 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, 9.2.0.4 also has bugs but maybe you are not aware of them.

Regards
Michel
Re: Strange experince with Oracle 10g ! [message #423232 is a reply to message #422937] Tue, 22 September 2009 23:25 Go to previous messageGo to next message
msmallya77
Messages: 28
Registered: June 2009
Location: Ahmedabad
Junior Member
ThomasG wrote on Mon, 21 September 2009 15:54
Well, either it is an optimizer bug as Michael mentioned, or you rely on some implicit conversions and/or order of data in the query that change the outcome from one run to the other somehow.


Will You please explain more on this.

I have pasted here sql session where it is giving diffrent results for the same query.

Session altered.

Elapsed: 00:00:00.00

SYSDATE
--------------------
23 sep 2009 09:41:33

Elapsed: 00:00:00.00
aims>get y3
  1  INSERT INTO pord2
  2  (seqno, compid, ordno, productid, psize, ordpcs, dsppcs,
  3  invno, invdate, regionid, collection_id)
  4  SELECT
  5     12347, a.compid, a.ordno, b.productid, b.psize, 0, b.pkgpcs,
  6     d.invno, d.invdt, d.regionid, a.collection_id
  7  FROM
  8     atsordht a, atdespdt b, atdespht c, atsinvht d
  9  WHERE
 10     a.compid IN ('01', '05')
 11     AND a.lockind = 'N'
 12             AND a.orddate >= '01-Sep-2009'
 13             AND a.orddate <= '18-Sep-2009'
 14             AND a.compid = b.compid
 15             AND a.lockind = b.lockind
 16             AND a.ordno = b.ordno
 17             AND b.compid = c.compid
 18             AND b.lockind = c.lockind
 19             AND b.pkgno = c.pkgno
 20             AND c.compid = d.compid
 21             AND c.lockind = d.lockind
 22*            AND c.invno = d.invno
aims>/

78475 rows created.

Elapsed: 00:00:02.03
aims>l
  1  INSERT INTO pord2
  2  (seqno, compid, ordno, productid, psize, ordpcs, dsppcs,
  3  invno, invdate, regionid, collection_id)
  4  SELECT
  5     12347, a.compid, a.ordno, b.productid, b.psize, 0, b.pkgpcs,
  6     d.invno, d.invdt, d.regionid, a.collection_id
  7  FROM
  8     atsordht a, atdespdt b, atdespht c, atsinvht d
  9  WHERE
 10     a.compid IN ('01', '05')
 11     AND a.lockind = 'N'
 12             AND a.orddate >= '01-Sep-2009'
 13             AND a.orddate <= '18-Sep-2009'
 14             AND a.compid = b.compid
 15             AND a.lockind = b.lockind
 16             AND a.ordno = b.ordno
 17             AND b.compid = c.compid
 18             AND b.lockind = c.lockind
 19             AND b.pkgno = c.pkgno
 20             AND c.compid = d.compid
 21             AND c.lockind = d.lockind
 22*            AND c.invno = d.invno
aims>/

78475 rows created.

Elapsed: 00:00:02.09
aims>l
  1  INSERT INTO pord2
  2  (seqno, compid, ordno, productid, psize, ordpcs, dsppcs,
  3  invno, invdate, regionid, collection_id)
  4  SELECT
  5     12347, a.compid, a.ordno, b.productid, b.psize, 0, b.pkgpcs,
  6     d.invno, d.invdt, d.regionid, a.collection_id
  7  FROM
  8     atsordht a, atdespdt b, atdespht c, atsinvht d
  9  WHERE
 10     a.compid IN ('01', '05')
 11     AND a.lockind = 'N'
 12             AND a.orddate >= '01-Sep-2009'
 13             AND a.orddate <= '18-Sep-2009'
 14             AND a.compid = b.compid
 15             AND a.lockind = b.lockind
 16             AND a.ordno = b.ordno
 17             AND b.compid = c.compid
 18             AND b.lockind = c.lockind
 19             AND b.pkgno = c.pkgno
 20             AND c.compid = d.compid
 21             AND c.lockind = d.lockind
 22*            AND c.invno = d.invno
aims>/

78475 rows created.

Elapsed: 00:00:02.03
aims>l
  1  INSERT INTO pord2
  2  (seqno, compid, ordno, productid, psize, ordpcs, dsppcs,
  3  invno, invdate, regionid, collection_id)
  4  SELECT
  5     12347, a.compid, a.ordno, b.productid, b.psize, 0, b.pkgpcs,
  6     d.invno, d.invdt, d.regionid, a.collection_id
  7  FROM
  8     atsordht a, atdespdt b, atdespht c, atsinvht d
  9  WHERE
 10     a.compid IN ('01', '05')
 11     AND a.lockind = 'N'
 12             AND a.orddate >= '01-Sep-2009'
 13             AND a.orddate <= '18-Sep-2009'
 14             AND a.compid = b.compid
 15             AND a.lockind = b.lockind
 16             AND a.ordno = b.ordno
 17             AND b.compid = c.compid
 18             AND b.lockind = c.lockind
 19             AND b.pkgno = c.pkgno
 20             AND c.compid = d.compid
 21             AND c.lockind = d.lockind
 22*            AND c.invno = d.invno
aims>/

5691 rows created.

Elapsed: 00:00:00.06
aims>/

5691 rows created.

Elapsed: 00:00:00.06
aims>rollback;

Rollback complete.

Elapsed: 00:00:00.04
aims>


Where 5691 rows created is correct one.

Thanks and Regards

MSMallya
Re: Strange experince with Oracle 10g ! [message #423235 is a reply to message #410130] Wed, 23 September 2009 00:03 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
I tried something similar see what I got
SQL> create table test_log
  2  (
  3  a varchar2(200)
  4  );

Table created.

SQL> create or replace PROCEDURE procins 
  2   (ologin  varchar2,
  3     oevent  varchar2,
  4     objtype  varchar2,
  5     oobjowner  varchar2,
  6     oobjname  varchar2,
  7    v_program  varchar2) IS
  8  
  9  PRAGMA AUTONOMOUS_TRANSACTION;
 10  
 11  BEGIN 
 12  insert into
 13  test_log values('1');
 14  commit;
 15  end;
 16  /

Procedure created.

SQL> create or replace trigger DDLTRIGGER  
  2  AFTER DDL ON DATABASE
  3  
  4  declare
  5       v_program varchar2(100);
  6       v_terminal varchar2(100);
  7  BEGIN
  8      select 
  9   a.program, a.terminal into v_program, v_terminal 
 10   from v$session a, (Select Distinct Sid from V$mystat) b
 11   where
 12   audsid=sys_context('USERENV','SESSIONID') and
 13   a.sid=b.sid and (trim(terminal) not like 'UNK%'
 14   or trim(terminal) is not null);
 15  
 16      procins(ora_login_user,ora_sysevent,ora_dict_obj_type,
 17                   ora_dict_obj_owner,ora_dict_obj_name,v_program) ;
 18      if v_terminal not in ('MURLIDHARM') then
 19         if (ora_sysevent='DROP' and substr(v_program,1,3) <> 'exp') then
 20            raise_application_error(-20000,'ACTION DENIED');
 21         end if;
 22      end if;
 23  END ;
 24  /

Trigger created.

SQL> select * from test;

       TID STATUS                                     PRIORITY INSERT_DA
---------- ---------------------------------------- ---------- ---------
UPDATE_DA
---------
         1 PROCESSED                                         1 22-SEP-09
22-SEP-09


SQL> drop table test;
drop table test
           *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: ACTION DENIED
ORA-06512: at line 17


SQL> select * from test_log;

A
--------------------------------------------------------------------------------
1
1
1
1

SQL> delete from test_log;

4 rows deleted.

SQL> commit;

Commit complete.

SQL>  drop table test;
 drop table test
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from test;
select * from test
              *
ERROR at line 1:
ORA-00942: table or view does not exist
 

Sorry I copy pasted the trigger
Modified my trigger as below see the result
SQL> create or replace trigger DDLTRIGGER  
  2  AFTER DDL ON DATABASE
  3  
  4  declare
  5       v_program varchar2(100);
  6       v_terminal varchar2(100);
  7  BEGIN
  8      select 
  9   a.program, a.terminal into v_program, v_terminal 
 10   from v$session a, (Select Distinct Sid from V$mystat) b
 11   where
 12   audsid=sys_context('USERENV','SESSIONID') and
 13   a.sid=b.sid and (trim(terminal) not like 'UNK%'
 14   or trim(terminal) is not null);
 15  
 16      procins(ora_login_user,ora_sysevent,ora_dict_obj_type,
 17                   ora_dict_obj_owner,ora_dict_obj_name,v_program) ;
 18      if v_terminal not in ('WXP-1Q37X1S') then
 19         if (ora_sysevent='DROP' and substr(v_program,1,3) <> 'sql') then
 20            raise_application_error(-20000,'ACTION DENIED');
 21         end if;
 22      end if;
 23  END ;
 24  /

Trigger created.
SQL> drop table test;
drop table test
           *
ERROR at line 1:
ORA-00600: internal error code, arguments: [15264], [], [], [], [], [], [], []
SQL> select * from test_log;

A
--------------------------------------------------------------------------------
1
1



I think drop table in 10g does some DDL itself

[Updated on: Wed, 23 September 2009 00:16]

Report message to a moderator

Re: Strange experince with Oracle 10g ! [message #423240 is a reply to message #423232] Wed, 23 September 2009 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
a.orddate >= '01-Sep-2009'

This is implicit conversion. '01-Sep-2009' is NOT a date it is a string. Note that '10-Jan-1900' is greater than '01-Sep-2009'.

Regards
Michel

Re: Strange experince with Oracle 10g ! [message #423241 is a reply to message #423235] Wed, 23 September 2009 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-00600: internal error code, arguments: [15264], [], [], [], [], [], [], []

ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink and/or call Oracle support

Regards
Michel
Re: Strange experince with Oracle 10g ! [message #423257 is a reply to message #423241] Wed, 23 September 2009 02:50 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Or you just accept that a .1 release version is usually quite buggy (I would never run a production system on a .1 version) and apply the latest patch set.

And, in your code you indeed rely on some implicit conversions. Which might somehow change the result if the execution plan changes after the query is run a few times.

[Updated on: Wed, 23 September 2009 02:51]

Report message to a moderator

Re: Strange experince with Oracle 10g ! [message #423259 is a reply to message #423240] Wed, 23 September 2009 03:05 Go to previous messageGo to next message
msmallya77
Messages: 28
Registered: June 2009
Location: Ahmedabad
Junior Member
Hi,

Yes, I had also doubt that it is treating as string implicitly, then I had modified to convert that to date, but still the results are different. Attached session outputs.

aims>@y5
 23  /

5893 rows created.

aims>l
  1  INSERT INTO pord2
  2  (seqno, compid, ordno, productid, psize, ordpcs, dsppcs,
  3  invno, invdate, regionid, collection_id)
  4  SELECT
  5     12347, a.compid, a.ordno, b.productid, b.psize, 0, b.pkgpcs,
  6     d.invno, d.invdt, d.regionid, a.collection_id
  7  FROM
  8     atsordht a, atdespdt b, atdespht c, atsinvht d
  9  WHERE
 10     a.compid IN ('01', '05')
 11     AND a.lockind = 'N'
 12             AND a.orddate >= to_date('01-Sep-2009','dd-Mon-yyyy')
 13             AND a.orddate <= to_date('18-Sep-2009','dd-Mon-yyyy')
 14             AND a.compid = b.compid
 15             AND a.lockind = b.lockind
 16             AND a.ordno = b.ordno
 17             AND b.compid = c.compid
 18             AND b.lockind = c.lockind
 19             AND b.pkgno = c.pkgno
 20             AND c.compid = d.compid
 21             AND c.lockind = d.lockind
 22*            AND c.invno = d.invno
aims>/

78795 rows created.

aims>/

78795 rows created.

aims>/

5893 rows created.

aims>


Thanks and Regards,

MSMallya
Re: Strange experince with Oracle 10g ! [message #423260 is a reply to message #423259] Wed, 23 September 2009 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So patch.

Regards
Michel

[Updated on: Wed, 23 September 2009 03:08]

Report message to a moderator

Re: Strange experince with Oracle 10g ! [message #423262 is a reply to message #423232] Wed, 23 September 2009 03:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
With regards to the Insert problem:

Are there any triggers on the PORD2 table?

Are all the objects that are being selected from tables, or are some of them views?

Can you post the plans for the correct and incorrect versions of the query?

If you run the query as a SELECT rather than as an INSERT, do you still get a different number of rows returned?


Re: Strange experince with Oracle 10g ! [message #423318 is a reply to message #423262] Wed, 23 September 2009 06:17 Go to previous messageGo to next message
msmallya77
Messages: 28
Registered: June 2009
Location: Ahmedabad
Junior Member
Hi,

There are no triggers on pord2. Selections are from Tables, no views involved.

Took little time, because I was unable to get different results at one go (explain plan, run sql, explain plan, run sql) and
finally I got, which is pasted as follows.

aims>@altdt

Session altered.

SYSDATE
--------------------
23 sep 2009 16:24:29

aims>explain plan set statement_id='Y5-1' for
  2  INSERT INTO pord2
  3  (seqno, compid, ordno, productid, psize, ordpcs, dsppcs,
  4  invno, invdate, regionid, collection_id)
  5  SELECT
  6       12349, a.compid, a.ordno, b.productid, b.psize, 0, b.pkgpcs,
  7       d.invno, d.invdt, d.regionid, a.collection_id
  8    FROM
  9       atsordht a, atdespdt b, atdespht c, atsinvht d
 10    WHERE
 11       a.compid IN ('01', '05')
 12       AND a.lockind = 'N'
 13       AND a.orddate >= to_date('01-Sep-2009','dd-Mon-yyyy')
 14       AND a.orddate <= to_date('18-Sep-2009','dd-Mon-yyyy')
 15       AND a.compid = b.compid
 16       AND a.lockind = b.lockind
 17       AND a.ordno = b.ordno
 18       AND b.compid = c.compid
 19       AND b.lockind = c.lockind
 20       AND b.pkgno = c.pkgno
 21       AND c.compid = d.compid
 22       AND c.lockind = d.lockind
 23       AND c.invno = d.invno
 24  /

Explained.

aims>@utlxplp

Plan Table
--------------------------------------------------------------------------------------------
| Operation                  |  Name    |  Rows | Bytes|  Cost  |  TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------
| INSERT STATEMENT           |          |     1 |  137 |   1629 |      |      |            |
|  TABLE ACCESS BY INDEX ROWI|ATSINVHT  |     1 |   31 |      2 |      |      |            |
|   NESTED LOOPS             |          |     1 |  137 |   1629 |      |      |            |
|    HASH JOIN               |          |    15 |    1K|   1599 |      |      |            |
|     HASH JOIN              |          |   139 |   10K|   1531 |      |      |            |
|      INLIST ITERATOR       |          |       |      |        |      |      |            |
|       TABLE ACCESS BY INDEX|ATSORDHT  |    57 |    1K|     16 |      |      |            |
|        INDEX RANGE SCAN    |IDX_1_ATS |    57 |      |      3 |      |      |            |
|      TABLE ACCESS FULL     |ATDESPDT  |   150K|    7M|   1513 |      |      |            |
|     INDEX FAST FULL SCAN   |IDX_1_ATD |     8K|  256K|     67 |      |      |            |
|    INLIST ITERATOR         |          |       |      |        |      |      |            |
|     INDEX RANGE SCAN       |ATSINVHT_ |     1 |      |      1 |      |      |            |
--------------------------------------------------------------------------------------------

15 rows selected.

aims>INSERT INTO pord2
  2  (seqno, compid, ordno, productid, psize, ordpcs, dsppcs,
  3  invno, invdate, regionid, collection_id)
  4  SELECT
  5       12349, a.compid, a.ordno, b.productid, b.psize, 0, b.pkgpcs,
  6       d.invno, d.invdt, d.regionid, a.collection_id
  7    FROM
  8       atsordht a, atdespdt b, atdespht c, atsinvht d
  9    WHERE
 10       a.compid IN ('01', '05')
 11       AND a.lockind = 'N'
 12       AND a.orddate >= to_date('01-Sep-2009','dd-Mon-yyyy')
 13       AND a.orddate <= to_date('18-Sep-2009','dd-Mon-yyyy')
 14       AND a.compid = b.compid
 15       AND a.lockind = b.lockind
 16       AND a.ordno = b.ordno
 17       AND b.compid = c.compid
 18       AND b.lockind = c.lockind
 19       AND b.pkgno = c.pkgno
 20       AND c.compid = d.compid
 21       AND c.lockind = d.lockind
 22       AND c.invno = d.invno
 23  /

6044 rows created.

aims>explain plan set statement_id='Y5-2' for
  2  INSERT INTO pord2
  3  (seqno, compid, ordno, productid, psize, ordpcs, dsppcs,
  4  invno, invdate, regionid, collection_id)
  5  SELECT
  6       12349, a.compid, a.ordno, b.productid, b.psize, 0, b.pkgpcs,
  7       d.invno, d.invdt, d.regionid, a.collection_id
  8    FROM
  9       atsordht a, atdespdt b, atdespht c, atsinvht d
 10    WHERE
 11       a.compid IN ('01', '05')
 12       AND a.lockind = 'N'
 13       AND a.orddate >= to_date('01-Sep-2009','dd-Mon-yyyy')
 14       AND a.orddate <= to_date('18-Sep-2009','dd-Mon-yyyy')
 15       AND a.compid = b.compid
 16       AND a.lockind = b.lockind
 17       AND a.ordno = b.ordno
 18       AND b.compid = c.compid
 19       AND b.lockind = c.lockind
 20       AND b.pkgno = c.pkgno
 21       AND c.compid = d.compid
 22       AND c.lockind = d.lockind
 23       AND c.invno = d.invno
 24  /

Explained.

aims>@utlxplp

Plan Table
--------------------------------------------------------------------------------------------
| Operation                  |  Name    |  Rows | Bytes|  Cost  |  TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------
| INSERT STATEMENT           |          |     1 |  137 |   1629 |      |      |            |
|  TABLE ACCESS BY INDEX ROWI|ATSINVHT  |     1 |   31 |      2 |      |      |            |
|   NESTED LOOPS             |          |     1 |  137 |   1629 |      |      |            |
|    HASH JOIN               |          |    15 |    1K|   1599 |      |      |            |
|     HASH JOIN              |          |   139 |   10K|   1531 |      |      |            |
|      INLIST ITERATOR       |          |       |      |        |      |      |            |
|       TABLE ACCESS BY INDEX|ATSORDHT  |    57 |    1K|     16 |      |      |            |
|        INDEX RANGE SCAN    |IDX_1_ATS |    57 |      |      3 |      |      |            |
|      TABLE ACCESS FULL     |ATDESPDT  |   150K|    7M|   1513 |      |      |            |
|     INDEX FAST FULL SCAN   |IDX_1_ATD |     8K|  256K|     67 |      |      |            |
|    INLIST ITERATOR         |          |       |      |        |      |      |            |
|     INDEX RANGE SCAN       |ATSINVHT_ |     1 |      |      1 |      |      |            |
--------------------------------------------------------------------------------------------

15 rows selected.

aims>INSERT INTO pord2
  2  (seqno, compid, ordno, productid, psize, ordpcs, dsppcs,
  3  invno, invdate, regionid, collection_id)
  4  SELECT
  5       12349, a.compid, a.ordno, b.productid, b.psize, 0, b.pkgpcs,
  6       d.invno, d.invdt, d.regionid, a.collection_id
  7    FROM
  8       atsordht a, atdespdt b, atdespht c, atsinvht d
  9    WHERE
 10       a.compid IN ('01', '05')
 11       AND a.lockind = 'N'
 12       AND a.orddate >= to_date('01-Sep-2009','dd-Mon-yyyy')
 13       AND a.orddate <= to_date('18-Sep-2009','dd-Mon-yyyy')
 14       AND a.compid = b.compid
 15       AND a.lockind = b.lockind
 16       AND a.ordno = b.ordno
 17       AND b.compid = c.compid
 18       AND b.lockind = c.lockind
 19       AND b.pkgno = c.pkgno
 20       AND c.compid = d.compid
 21       AND c.lockind = d.lockind
 22       AND c.invno = d.invno
 23  /

78845 rows created.

aims>


Count(*) shows correct result, but still I will watch this behaviour.

Thanks and Regards,

MSMallya
Re: Strange experince with Oracle 10g ! [message #423321 is a reply to message #423318] Wed, 23 September 2009 06:34 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
So you have the options of keep watching it for a few weeks or months, or install a patch set. Choose now.
Re: Strange experince with Oracle 10g ! [message #423326 is a reply to message #423321] Wed, 23 September 2009 07:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Patch set - no choice about it.

There is the 3rd choice, of raising an SR with Oracle, but given your oracle version, theres a 95%+ chance thatthe first thing they tell you to do will be to upgrade.

Are any of these tables remote (ie accessed over a database link)?

Can you identify what the cause of the additional rows is?
Ie are you getting duplicate rows, or are you getting some rows that don't match some of the join conditions?
Re: Strange experince with Oracle 10g ! [message #423337 is a reply to message #423326] Wed, 23 September 2009 08:21 Go to previous message
msmallya77
Messages: 28
Registered: June 2009
Location: Ahmedabad
Junior Member
Hi,

Yes, I have to go for Patches and initiated the process.

Unwanted rows are not selected, but duplicates records are created.

Thanks and Regards to all for giving valuable inputs,

MSMallya
Previous Topic: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Next Topic: Query tunning
Goto Forum:
  


Current Time: Thu Sep 29 20:59:04 CDT 2016

Total time taken to generate the page: 0.46524 seconds