A potential bug (infinite loop) in Oracle: querying v$access

A potential bug (infinite loop) in Oracle: querying v$access

Post by Vladimir M » Thu, 24 Jan 2008 15:55:05



9.2.0.8/Win32 doesn't have this issue: select distinct owner from v
$access returns 3 rows instantaneously as expected. I don't have a
10gR2 or 11gR1 instance handy to test, but if this issue is definitely
reproducible on these releases (maybe on certain platforms only,) I
suggest that you open a SR with Oracle Support for it as it looks like
a regression or platform-dependent bug.

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.yqcomputer.com/
 
 
 

A potential bug (infinite loop) in Oracle: querying v$access

Post by shakespear » Thu, 24 Jan 2008 17:40:06


"Vladimir M. Zakharychev" < XXXX@XXXXX.COM > schreef in
bericht




Select distinct name from all_source: 6194 rows returned
Select distinct owner from v$access: 13 rows returned.
10.1.0.5.0 on Windows: no errors.

Shakespeare

 
 
 

A potential bug (infinite loop) in Oracle: querying v$access

Post by Peter Teo » Thu, 24 Jan 2008 21:32:32

I tried on Oracle9iR2 (Fedora Core Linux 5) no problem, it returned
immediately - no *** .

But for the earlier post - I forgotten to mentioned they are on Fedora
Core 7.

Thanks.
 
 
 

A potential bug (infinite loop) in Oracle: querying v$access

Post by Steve Howa » Thu, 24 Jan 2008 21:50:09


10.2.0.3 32 bit on RHAT returned immediately, as did 10.2.0.3 64 bit
on AIX 5.2 64 bit.

Regards,

Steve
 
 
 

A potential bug (infinite loop) in Oracle: querying v$access

Post by Charles Ho » Thu, 24 Jan 2008 21:58:09

n Jan 23, 12:23m, Peter Teoh < XXXX@XXXXX.COM > wrote:
> ERRO> at line 1:
> ORA-01013: user requested cancel of curre>t >peration
>
> I had to terminate it as it become non->er>inating.
>
> "select owner from v$access" returns a mere 193 rows, a>d it cannot> >>sort it?
>
> I suspect there is exists an infinite loo> s>mewhere.
>
> This was attempted on 10gR2, 11gR1 and both had the same problem.

I was able to reproduce this problem on Oracle 10.2.0.2 with the
Oracle October 2006 CPU on 64 bit Windows 2003.

From the udump trace file:
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION]
[__intel_new_memcpy+610] [PC:0x3236532] [ADDR:0x0] [UNABLE_TO_READ] []
Current SQL statement for this session:
select distinct owner from v$access
----- Call Stack Trace -----
calling call entry argument values in
hex
location type point (? means dubious
value)
-------------------- -------- --------------------
----------------------------
__intel_new_memcpy+ 0000000000000000 000000000 000000000
0118AF5A0
610 7FF970C7598
000007FF95D155F0 CALL??? __intel_new_memcpy+ 0000007FF 013DF42E8
001749686
610 000000000
...

From a 10046 trace at level 8:
*** 2008-01-23 06:51:57.901
WAIT #3: nam='latch: library cache' ela= 8566 address=1398554576
number=214 tries=1 obj#=-1 tim=5204643696
WAIT #3: nam='latch: library cache' ela= 859 address=1398554576
number=214 tries=1 obj#=-1 tim=5205047765
WAIT #3: nam='latch: library cache' ela= 2958 address=1398554576
number=214 tries=1 obj#=-1 tim=5205183748
WAIT #3: nam='latch: library cache' ela= 551 address=1398554576
number=214 tries=1 obj#=-1 tim=5205267198
*** 2008-01-23 06:54:39.117
WAIT #3: nam='latch: library cache' ela= 813 address=1398554576
number=214 tries=1 obj#=-1 tim=5365848854
*** 2008-01-23 06:55:06.288
WAIT #3: nam='latch: library cache' ela= 30 address=1398554576
number=214 tries=1 obj#=-1 tim=5393019180
*** 2008-01-23 06:55:30.006
WAIT #3: nam='latch: library cache' ela= 68 address=1398554576
number=214 tries=1 obj#=-1 tim=5416746379
*** 2008-01-23 06:55:50.584
WAIT #3: nam='latch: library cache' ela= 33 address=1398554576
number=214 tries=1 obj#=-1 tim=5437323921
*** 2008-01-23 06:57:09.536
WAIT #3: nam='latch: library cache' ela= 111 address=1398554576
number=214 tries=1 obj#=-1 tim=5516279642
*** 2008-01-23 06:57:20.895
WAIT #3: nam='latch: library cache' ela= 77 address=1398554576
number=214 tries=1 obj#=-1 tim=5527627340
*** 2008-01-23 06:57:36.082
WAIT #3: nam='latch: library cache' ela= 246 address=1398554576
number=214 tries=1 obj#=-1 tim=5542815685
*** 2008-01-23 06:57:57.957
WAIT #3: nam='latch: library cache' ela= 123 address=1398554576
number=214 tries=1 obj#=-1 tim=5564704225
*** 2008-01-23 06:58:14.644
WAIT #3: nam='latch: library cache' ela= 63 address=1398554576
number=214 tries=1 obj#=-1 tim=5581385020
*** 2008-01-23 06:58:26.269
WAIT #3: nam='latch: library cache' ela= 62 address=1398554576
number=214 tries=1 obj#=-1 tim=5593004724
*** 2008-01-23 06:58:48.346
WAIT #3: nam='latch: library cache' ela= 42 address=1398554576
number=214 tries=1 obj#=-1 tim=5615094241
WAIT #3: nam='latch: library cache' ela= 8 address=1398554576
number=214 tries=1 obj#=-1 tim=56151681
 
 
 

A potential bug (infinite loop) in Oracle: querying v$access

Post by Charles Ho » Fri, 25 Jan 2008 00:39:43

n Jan 23, 7:58m, Charles Hooper < XXXX@XXXXX.COM > wrote:
> > ERRO> >t line 1:
> > ORA-01013: user requested cancel of curre>t >p>ration
>
> > I had to terminate it as it become non->er>i>ating.
>
> > "select owner from v$access" returns a mere 193 rows, a>d>it cannot
>
> > I suspect there is exists an infinite loo> s>m>where.
>
> > This was attempted on 10gR2, 11gR1 and both had the s>me>problem.
>
> I was able to reproduce this problem on Oracle 10.2.>.2 with the
> Oracle October 2006 CPU on 64 bit W>nd>ws 2003.
>
> From the udump>trace file:
> ksedmp: internal or>fatal error
> ORA-07445: exception encountered: core dump [ACCES>_VIOLATION]
> [__intel_new_memcpy+610] [PC:0x3236532] [ADDR:0x0] [UNABLE>TO_READ] []
> Current SQL statement for t>is session:
> select distinct owner f>om v$access
> ----- Call Stack>Trace -----
> calling all entry > gt;rgument values in
> hex
> location typ> poin> ? means dubious
> value)
> ------->------------ -------- --------->----------
> ----------------------------
> __intel_new_memcpy+ > 000>000000000000 000000000 000000000
> 0>18AF5A0
> 610 FF970C7>98
> 000007>F95D155F0 CALL??? _intel_new_memc>y+ 0>00>7FF 013DF42E8
> 001749686
> gt;610 > 00000000
> ...
>
> From a 10046 trace at level >:
> *** 2008-01-23 06:51:57.901
> WAIT #3:>nam='latch: library cache' ela= 8566 address=1398554576
> number=>14 tries=1 obj#=-1 tim=5204643696
> WAIT #3> nam='latch: library cache' ela= 859 address=1398554576
> number=2>4 tries=1 obj#=-1 tim=5205047765
> WAIT #3:>nam='latch: library cache' ela= 2958 address=1398554576
> number=>14 tries=1 obj#=-1 tim=5205183748
> WAIT #3> nam='latch: library cache' el>= 551 address=1398554576
> number=214 tries=1 obj#=-1 tim=5205267>98
> *** 2008-01-23 06:54:39.117
> WAIT #3> nam='latch: library cache' el>= 813 address=1398554576
> number=214 tries=1 obj#=-1 tim=536584>854
> *** 2008-01-23 06:55:06.288
> WAIT #>: nam='latch: library cache' e>a= 30 address=1398554576
> number=214 tries=1 obj#=-1 tim=539301>180
> *** 2008-01-23 06:55:30.006
> WAIT #>: nam='latch: library cache' e>a= 68 address=1398554576
> number=214 tries=1 obj#=-1 tim=541674>379
> *** 2008-01-23 06:55:50.584
> WAIT #>: nam='latch: library cache' e>a= 33 address=1398554576
> number=214 tries=1 obj#=-1 tim=5437323>21
> *** 2008-01-23 06:57:09.536
> WAIT #3> nam='latch: library cache' el>= 111 address=1398554576
> number=214 tries=1 obj#=-1 tim=551627>642
> *** 2008-01-23 06:57:20.895
> WAIT #>: nam='latch: library cache' e>a= 77 address=1398554576
> number=214 tries=1 obj#=-1 tim=5527627>40
> *** 2008-01-23 06:57:36.082
> WAIT #3> nam='latch: library cache' el>= 246 address=1398554576
> number=214 tries=1 obj#=-1 tim=5542815>85
> *** 2008-01-23 06:57:57.957
> WAIT #3> nam='latch: library cache' el>= 123 address=1398554576
> number=214 tries=1 obj#=-1 tim=556470>225
> *** 2008-01-23 06:58:14.644
> WAIT #>: nam='latch: library cache' e>a= 63 address=1398554576
> number=214 tries=1 obj#=-1 tim=558138>020
> *** 2008-01-23 06:58:26.269
> WAIT #>: nam='latch: library cache' e>a= 62 address=1398554576
> number=214 tries=1 obj#=-1 tim=559300>724
> *** 2008-01-23 06:58:48.346
> WAIT #>: nam='latch: library cache' e
 
 
 

A potential bug (infinite loop) in Oracle: querying v$access

Post by hpuxra » Fri, 25 Jan 2008 01:00:05


> ERRO> at line 1:
> ORA-01013: user requested cancel of curre>t >peration
>
> I had to terminate it as it become non->er>inating.
>
> "select owner from v$access" returns a mere 193 rows, a>d it cannot> >>sort it?
>
> I suspect there is exists an infinite loo> s>mewhere.
>
> This was attempted on 10gR2, 11gR1 and both had the same problem.

Works fine for me hpux 64 bit 10.2.0.3
 
 
 

A potential bug (infinite loop) in Oracle: querying v$access

Post by Frank van » Fri, 25 Jan 2008 03:35:55


Odd...
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select distinct owner from v$access;
select distinct owner from v$access
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation



SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release
10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle102@cs-frank03 ~]$ uname -a
Linux cs-frank03 2.6.18-53.1.4.el5PAE #1 SMP Fri Nov 30 01:21:20 EST
2007 i686 i686 i386 GNU/Linux

--

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up
 
 
 

A potential bug (infinite loop) in Oracle: querying v$access

Post by Steve Howa » Fri, 25 Jan 2008 04:16:03

On Jan 23, 1:35 pm, Frank van Bortel < XXXX@XXXXX.COM >




Sorry, that will teach me to post prior to 8AM and my third cuppa'
joe :(. I ran it without the distinct, and mine times out as well, on
both Linux and AIX.
 
 
 

A potential bug (infinite loop) in Oracle: querying v$access

Post by joel garr » Fri, 25 Jan 2008 07:07:14


Looks like the fix for Note:353663.1 doesn't hit all the
possibilities. But check out Note:415450.1, perhaps a windows-
specific problem, since there is that "intel" argument. Looks like
there are some "unable to duplicate" similar problems, too.

No problem for me with hp-ux 9206.

jg
@home.com is bogus.
"When the pin is pulled, Mr. Grenade is not our friend." - U.S.
Marine Corps
 
 
 

A potential bug (infinite loop) in Oracle: querying v$access

Post by Charles Ho » Fri, 25 Jan 2008 11:14:56

n Jan 23, 5:07m, joel garry < XXXX@XXXXX.COM > wrote:

I will have to take a look at those bug reports.

I may have found something that may help the OP - it hit me when I
found very slow performance with the same SQL statement on 32 bit
Oracle 10.2.0.3 and 11.1.0.6, after looking at the DBMS_XPLANs.

The DBMS_XPLAN on 10.2.0.3:
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows
| A-Rows | A-Time | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
| 1 | HASH UNIQUE | | 1 | 105
| 5 |00:02:51.06 | | | |
| 2 | NESTED LOOPS | | 1 | 105
| 1131 |00:02:51.04 | | | |
| 3 | NESTED LOOPS | | 1 | 10
| 1131 |00:02:50.39 | | | |
| 4 | MERGE JOIN CARTESIAN | | 1 | 100
| 180K|00:00:01.27 | | | |
|* 5 | FIXED TABLE FULL | X$KSUSE | 1 | 1
| 236 |00:00:00.01 | | | |
| 6 | BUFFER SORT | | 236 | 100
| 180K|00:00:00.55 | 36864 | 36864 |32768 (0)|
| 7 | FIXED TABLE FULL | X$KGLDP | 1 | 100
| 763 |00:00:00.01 | | | |
|* 8 | FIXED TABLE FIXED INDEX| X$KGLLK (ind:1) | 180K| 1
| 1131 |00:02:48.31 | | | |
|* 9 | FIXED TABLE FIXED INDEX | X$KGLOB (ind:1) | 1131 | 10
| 1131 |00:00:00.64 | | | |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("S"."INST_ID"=USERENV('INSTANCE'))
8 - filter(("L"."KGLLKUSE"="S"."ADDR" AND
"L"."KGLLKHDL"="D"."KGLHDADR" AND "L"."KGLNAHSH"="D"."KGLNAHSH"))
9 - filter(("O"."KGLNAHSH"="D"."KGLRFHSH" AND
"O"."KGLHDADR"="D"."KGLRFHDL"))

Note the MERGE JOIN CARTESIAN, and how the estimated rows compares
with the actual rows.

The DBMS_XPLAN on 11.1.0.6:
select distinct owner from v$access
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-
Rows | A-Rows | A-Time | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
| 1 | HASH UNIQUE | | 1 |
1 | 6 |00:00:40.28 | 951K| 951K| 860K (0)|
| 2 | NESTED LOOPS | | 1 |
1 | 2342 |00:00:40.27 | | | |
| 3 | MERGE JOIN CARTESIAN | | 1 |
1 | 2842K|00:00:11.37 | | | |
| 4 | NESTED LOOPS | | 1 |
1 | 16721 |00:00:00.38 | | | |
| 5 | FIXED TABLE FULL | X$KGLDP | 1 |
100 | 16721 |00:00:00.05 | | | |
|* 6 | FIXED TABLE FIXED INDEX| X$KGLOB (ind:1)
 
 
 

A potential bug (infinite loop) in Oracle: querying v$access

Post by Frank van » Sat, 26 Jan 2008 04:14:52


Reproduces on Linux as well. But I'm on Intel, yes.
Don't know what hardware Steve Howard uses for AIX - is
AIX 5.2 available for intel?!?
--

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up
 
 
 

A potential bug (infinite loop) in Oracle: querying v$access

Post by Frank van » Sat, 26 Jan 2008 04:20:31


Charles to the rescue:

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS',GATHER_FIXED=>TRUE);

PL/SQL procedure successfully completed.

SQL> set timing on
SQL> select distinct owner from v$access;

OWNER
----------------------------------------------------------------
PUBLIC
SYSTEM
XDB
SYS

Elapsed: 00:00:00.01

At least there's a workaround.
--

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up
 
 
 

A potential bug (infinite loop) in Oracle: querying v$access

Post by Charles Ho » Sat, 26 Jan 2008 05:13:11

On Jan 24, 2:20m, Frank van Bortel < XXXX@XXXXX.COM >



Thanks for the feedback Frank.

That call solved the problem on 64 bit Windows, no more ORA-07445, and
the SQL statement executes quickly. A little more testing to
determine why the following failed:
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS('ALL');

I found Metalink doc ID 272479.1, which suggests to use:
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NULL);

Then I started to wonder, did I find a work around a couple years ago
for this problem when I migrated to Oracle 10.2.0.2? How to determine
if I collected statistics on the fixed tables? A search on Metalink
found this article from 2004:
https://metalink.oracle.com/metalink/plsql/f?p=200:27:5000154048035945504::::p27_id,p27_show_header,p27_show_help:525959.996,1,1

In the article, Jonathan Lewis mentioned that tab_stats$ could be
checked.
Test database on 64 bit Windows:
SELECT
*
FROM
SYS.TAB_STATS$

The above returned about 582 rows after running:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS',GATHER_FIXED=>TRUE);

I then checked a production database, and found no rows returned.
Using the suggestion from the Metalink article:
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NULL);

The stats collection completed much more quickly, as it did not
analyze the full SYS schema, and there were 582 rows returned by the
query on SYS.TAB_STATS$. It looks like this simple query may be used
to determine if fixed object statistics need to be collected.

I am still looking for the source of the suggestion to use 'ALL' as
the parameter for GATHER_FIXED_OBJECTS_STATS - it was apparently not
from "Expert Oracle Database 10g Administation", as that book shows
nothing after GATHER_FIXED_OBJECTS_STATS.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
 
 
 

A potential bug (infinite loop) in Oracle: querying v$access

Post by fitzjarrel » Sun, 03 Feb 2008 02:38:17

n Jan 24, 2:13m, Charles Hooper < XXXX@XXXXX.COM > wrote:
>> > Then I started to wonder, did I find a work around a couple years ago> > for this problem when I migrated to Oracle 10.2.0.2? ow to determine> > if I collected statistics on the fixed tables? search on Metalink> > found this article from 2004:https://metalink.oracle.com/metalink/plsql/f?p=200:27:500015404803594...> >> > In the article, Jonathan Lewis mentioned that tab_stats$ could be> > checked.> > Test database on 64 bit Windows:> > SELECT> > >
> FRO>
> SYS.TAB_STAT>$ >>
> The above returned about 582 rows after runnin>:
> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS',GATHER_F>XED=>TRU>);> >
> I then checked a production database, and found no rows return>d.
> Using the suggestion from the Metalink artic>e:
> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NU>L)>
>
> The stats collection completed much more quickly, as it did>not
> analyze the full SYS schema, and there were 582 rows returned by>the
> query on SYS.TAB_STATS$. t looks like this simple query may be >sed
> to determine if fixed object statistics need to be collec>ed>
>
> I am still looking for the source of the suggestion to use 'ALL> as
> the parameter for GATHER_FIXED_OBJECTS_STATS - it was apparently>not
> from "Expert Oracle Database 10g Administation", as that book s>ows
> nothing after GATHER_FIXED_OBJECTS_ST>TS>
>
> Charles Ho>per
> IT Manager/Oracle>DBA
> K&M Machine-Fabricating, Inc.- Hide quoted te>t >
>
> - Show quoted text -

Oracle has published Metalink Note 549895.1, regarding this same
problem. Their solution is different, and doesn't involve fixed
object statistics.


David Fitzjarrell