Re: A potential bug (infinite loop) in Oracle: querying v$access
Date: Wed, 23 Jan 2008 18:14:56 -0800 (PST)
Message-ID: <2d2a67f4-12cf-470a-baa8-a90aea42f2e5@v46g2000hsv.googlegroups.com>
On Jan 23, 5:07 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Jan 23, 4:58 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
>
> > On Jan 23, 12:23 am, Peter Teoh <htmldevelo..._at_gmail.com> wrote:
>
> > > 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
>
> 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
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) | 16721 | 1 | 16721 |00:00:00.21 | | | | | 7 | BUFFER SORT | | 16721 | 1 | 2842K|00:00:02.91 | 4096 | 4096 | 4096 (0)| |* 8 | FIXED TABLE FULL | X$KSUSE | 1 | 1 | 170 |00:00:00.01 | | | | |* 9 | FIXED TABLE FIXED INDEX | X$KGLLK (ind:1) | 2842K| 1 | 2342 |00:00:15.49 | | | | --------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
6 - filter(("O"."KGLNAHSH"="D"."KGLRFHSH" AND
"O"."KGLHDADR"="D"."KGLRFHDL"))
8 - filter("S"."INST_ID"=USERENV('INSTANCE'))
9 - filter(("L"."KGLLKUSE"="S"."ADDR" AND
"L"."KGLLKHDL"="D"."KGLHDADR" AND "L"."KGLNAHSH"="D"."KGLNAHSH"))
The above executed more quickly, and the plan is slightly different,
but the MERGE JOIN CARTESIAN is still present, as is the difference
between the estimated and actual number of rows.
The fixed object stats must be wrong (I recall having a problem with
that a couple years ago when perfoming the following)...
SQL> CONNECT / AS SYSDBA
SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS('ALL');
BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS('ALL'); END;
*
ERROR at line 1:
ORA-20000: Insufficient privileges to analyze an object in Fixed Ob ORA-06512: at "SYS.DBMS_STATS", line 17951 ORA-06512: at "SYS.DBMS_STATS", line 18404 ORA-06512: at "SYS.DBMS_STATS", line 18951 ORA-06512: at line 1
The same error occurs on Oracle 10.2.0.2, 10.2.0.3, and 11.1.0.6 as the internal user, SYS AS SYSDBA, and SYSTEM.
There must be another way:
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS',GATHER_FIXED=>TRUE)
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
The new DBMS_XPLANs:
10.2.0.3:
| Id | Operation | Name | Starts | E-Rows| A-Rows | A-Time | OMem | 1Mem | Used-Mem |
| 1 | HASH UNIQUE | | 1 | 7 | 4 |00:00:00.09 | | | | | 2 | NESTED LOOPS | | 1 | 1822 | 1003 |00:00:00.08 | | | | |* 3 | HASH JOIN | | 1 | 1822 | 1003 |00:00:00.05 | 898K| 898K| 1099K (0)| |* 4 | HASH JOIN | | 1 | 1822 | 1897 |00:00:00.03 | 1010K| 1010K| 639K (0)| |* 5 | FIXED TABLE FULL | X$KSUSE | 1 | 236 | 236 |00:00:00.01 | | | | | 6 | FIXED TABLE FULL | X$KGLLK | 1 | 1822 | 1897 |00:00:00.01 | | | | | 7 | FIXED TABLE FULL | X$KGLDP | 1 | 2892 | 649 |00:00:00.01 | | | | |* 8 | FIXED TABLE FIXED INDEX| X$KGLOB (ind:1) | 1003 | 1 | 1003 |00:00:00.01 | | | | ------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
3 - access("L"."KGLLKHDL"="D"."KGLHDADR" AND "L"."KGLNAHSH"="D"."KGLNAHSH")
4 - access("L"."KGLLKUSE"="S"."ADDR")
5 - filter("S"."INST_ID"=USERENV('INSTANCE'))
8 - filter(("O"."KGLNAHSH"="D"."KGLRFHSH" AND
"O"."KGLHDADR"="D"."KGLRFHDL"))
The MERGE JOIN CARTESIAN is gone and the execution time dropped from 2
minutes, 51 seconds to 0.09 seconds.
11.1.0.6:
| Id | Operation | Name | Starts | E-Rows| A-Rows | A-Time | OMem | 1Mem | Used-Mem |
| 1 | HASH UNIQUE | | 1 | 19 | 1 |00:00:00.04 | 1037K| 1037K| 368K (0)| | 2 | NESTED LOOPS | | 1 | 1139 | 134 |00:00:00.04 | | | | | 3 | NESTED LOOPS | | 1 | 1139 | 134 |00:00:00.03 | | | | |* 4 | HASH JOIN | | 1 | 1139 | 1144 |00:00:00.02 | 1010K| 1010K| 1205K (0)| |* 5 | FIXED TABLE FULL | X$KSUSE | 1 | 170 | 170 |00:00:00.01 | | | | | 6 | FIXED TABLE FULL | X$KGLLK | 1 | 1139 | 1144 |00:00:00.01 | | | | |* 7 | FIXED TABLE FIXED INDEX| X$KGLDP (ind:1) | 1144 | 1 | 134 |00:00:00.01 | | | | |* 8 | FIXED TABLE FIXED INDEX | X$KGLOB (ind:1) | 134 | 1 | 134 |00:00:00.01 | | | | -------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
4 - access("L"."KGLLKUSE"="S"."ADDR")
5 - filter("S"."INST_ID"=USERENV('INSTANCE'))
7 - filter(("L"."KGLLKHDL"="D"."KGLHDADR" AND
"L"."KGLNAHSH"="D"."KGLNAHSH"))
8 - filter(("O"."KGLNAHSH"="D"."KGLRFHSH" AND "O"."KGLHDADR"="D"."KGLRFHDL")) The MERGE JOIN CARTESIAN is gone and the execution time dropped from 40.28 seconds to 0.04 seconds.
The OP may be able to run the same DBMS_STATS.GATHER_SCHEMA_STATS procedure to work around the problem.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Wed Jan 23 2008 - 20:14:56 CST