A potential bug (infinite loop) in Oracle: querying v$access
Peter Teoh
select distinct name from all_source;
.......
SYS_YOID0000052452$
......
3196 rows selected.
Ok no problem.
select distinct owner from v$access
SQL> /
select distinct owner from v$access
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
I had to terminate it as it become non-terminating.
"select owner from v$access" returns a mere 193 rows, and it cannot
sort it?
I suspect there is exists an infinite loop somewhere.
This was attempted on 10gR2, 11gR1 and both had the same problem.
Vladimir M. Zakharychev
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.dynamicpsp.com
shakespeare
"Vladimir M. Zakharychev" <vladimir.z...@gmail.com> schreef in
bericht
news:38ae4253-4519-44ed...@d70g2000hsb.googlegroups.com...
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
Peter Teoh
immediately - no hanging.
But for the earlier post - I forgotten to mentioned they are on Fedora
Core 7.
Thanks.
Steve Howard
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
Charles Hooper
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=5615168161
...
For an average 60 second interval interval, the session had the
following latch statistics:
Latch Child# Level Gets Misses Sleeps Sleeps1
LIBRARY CACHE 1 5 529418 25 0 25
LIBRARY CACHE 2 5 539720 36 0 36
LIBRARY CACHE 3 5 519189 15 0 15
LIBRARY CACHE 4 5 516501 55 0 55
LIBRARY CACHE 5 5 524907 1744 4 1740
On the waits, P1 is 13985, P2 is 214, P3 is 0, Buf is 99, SQL Hash is
3250939240.
SELECT
SQL_TEXT
FROM
V$SQL
WHERE
HASH_VALUE=3250939240
SQL_TEXT
-----------------------------------
select distinct owner from v$access
SELECT
ID,
SUBSTR(OPERATION,1,12) OPERATION,
SUBSTR(OPTIONS,1,12) OPTIONS,
SUBSTR(OBJECT_OWNER||'.'||OBJECT_NAME,1,20) OBJECT,
SUBSTR(OBJECT_TYPE,1,13) OBJECT_TYPE,
PARENT_ID,
DEPTH,
POSITION,
CPU_COST
FROM
V$SQL_PLAN_STATISTICS_ALL
WHERE
HASH_VALUE=3250939240
AND CHILD_NUMBER=1
ORDER BY
ID;
ID OPERATION OPTIONS OBJECT OBJECT_TYPE
PARENT_ID DEPTH POSITION CPU_COST
-- ------------ ------------ -------------------- -------------
---------- ---------- ---------- ----------
1 HASH
UNIQUE . 0
1 1 2142850
2 NESTED
LOOPS .
1 2 1 1115000
3 NESTED
LOOPS .
2 3 1 1080000
4 MERGE JOIN
CARTESIAN . 3
4 1 730000
5 FIXED TABLE FULL SYS.X$KSUSE TABLE
(FIXED) 4 5 1 380000
6 BUFFER
SORT . 4
5 2 350000
7 FIXED TABLE FULL SYS.X$KGLDP TABLE
(FIXED) 6 6 1 350000
8 FIXED TABLE FIXED INDEX SYS.X$KGLLK (ind:1) TABLE
(FIXED) 3 4 2 3500
9 FIXED TABLE FIXED INDEX SYS.X$KGLOB (ind:1) TABLE
(FIXED) 2 3 2 3500
SELECT
ID,
ACCESS_PREDICATES,
FILTER_PREDICATES
FROM
V$SQL_PLAN_STATISTICS_ALL
WHERE
HASH_VALUE=3250939240
AND CHILD_NUMBER=1
ORDER BY
ID;
ID ACCESS_PREDICATES FILTER_PREDICATES
-- ----------------- -----------------
1
2
3
4
5 "S"."INST_ID"=USERENV('INSTANCE')
6
7
8 ("L"."KGLLKUSE"="S"."ADDR" AND
"L"."KGLLKHDL"="D"."KGLHDADR" AND "L"."KGLNAHSH"="D"."KGLNAHSH")
9 ("O"."KGLNAHSH"="D"."KGLRFHSH" AND
"O"."KGLHDADR"="D"."KGLRFHDL")
Why are you attempting to run this SQL statement?
A quick Google search finds this page:
http://download-uk.oracle.com/docs/cd/B13789_01/server.101/b10755/dynviews_1003.htm
V$ACCESS displays objects in the database that are currently locked
and the sessions that are accessing them.
Owner: Owner of the object
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Charles Hooper
>
> V$ACCESS displays objects in the database that are currently locked
> and the sessions that are accessing them.
> Owner: Owner of the object
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
The query eventually completed on the 32 bit version of Oracle
10.2.0.2 with the Oracle October 2006 CPU.
PARSE 1|CPU S 0.000000|CLOCK S 0.006227|ROWs 0
EXEC 1|CPU S 0.000000|CLOCK S 0.000201|ROWs 0
FETCH 2|CPU S 13112.828125|CLOCK S 926.981803|ROWs 6
Rwo Source Execution Plan:
(Rows 6) HASH UNIQUE (cr=0 pr=0 pw=0 time=568347223 us)
(Rows 3463) NESTED LOOPS (cr=0 pr=0 pw=0 time=3464796755 us)
(Rows 3463) NESTED LOOPS (cr=0 pr=0 pw=0 time=3464592419 us)
(Rows 1613768) MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0
time=21019488 us)
(Rows 236) FIXED TABLE FULL X$KSUSE (cr=0 pr=0 pw=0
time=2376 us)
(Rows 1613768) BUFFER SORT (cr=0 pr=0 pw=0 time=12951356 us)
(Rows 6838) FIXED TABLE FULL X$KGLDP (cr=0 pr=0 pw=0
time=41073 us)
(Rows 3463) FIXED TABLE FIXED INDEX X$KGLLK (ind:1) (cr=0
pr=0 pw=0 time=13094082350 us)
(Rows 3463) FIXED TABLE FIXED INDEX X$KGLOB (ind:1) (cr=0 pr=0
pw=0 time=166548 us)
Note the merge Cartesian join between the 236 rows in X$KSUSE and the
1613768 rows from X$KGLDP.
The wait events:
0.03 seconds on latch: library cache
hpuxrac
Works fine for me hpux 64 bit 10.2.0.3
Frank van Bortel
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
Steve Howard
wrote:
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.
Eberhard Niendorf
I could reproduce the hanging for 10.2.0.3 on x86_64, but after using
SQL> alter session set optimizer_mode = RULE; or
SQL> alter session set optimizer_mode = FIRST_ROWS_n;
it works fine.
Eberhard Niendorf
joel garry
> On Jan 23, 12:23 am, Peter Teoh <htmldevelo...@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
Charles Hooper
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.
Frank van Bortel
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?!?
Frank van Bortel
>
> 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.
>
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.
Charles Hooper
wrote:
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.
fitzj...@cox.net
>
> 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
>
> - 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
Charles Hooper
> Oracle has published Metalink Note 549895.1, regarding this same
> problem. Their solution is different, and doesn't involve fixed
> object statistics.
>
> David Fitzjarrell
Thanks for the reference to the article.
It is an interesting article in that the author did not investigate
what was causing the bad execution plan, only that a "MERGE JOIN
CARTESIAN" operation appeared in the plan - and the plan indicated
that the join did (or should have) taken 0.01 seconds. Instead of
addressing the cause of the problem, they supplied a bandage to
something that "might" have been the problem (even though it was not
indicated in the plan) by making the following change:
alter session set "_optimizer_cartesian_enabled"=false;
Cartesian merge joins are not necessarily bad in all cases. Page 387
of "Cost-Based Oracle Fundamentals" contains a bit of information
about this type of join, and how it might be helpful.
I wonder who the author of that article is? Did you notice this
wording on the article: "This document is being delivered to you via
Oracle Support's Rapid Visibility (RaV) Rapid Visibility (RaV)
process, and therefore has not been subject to an independent
technical review." Maybe it should have been subject to review?
As far as I am aware, fixed object statistics are supposed to be
gathered on Oracle 10g databases.
On a side note, I found the source of the suggestion to use 'ALL' as
the parameter for GATHER_FIXED_OBJECTS_STATS.
"OCP Oracle Database 10g Exam Guide" by Alapati, who I believe is also
the author of teh very useful book "Expert Oracle Database 10g
Administation" by Sam Alapati:
http://www.oracle.com/technology/books/pdfs/exam_ch5.pdf
The suggestion to use 'ALL' can also be found here:
http://www.oracle.com/technology/books/pdfs/book_10g_chap9_ce1.pdf
fitzj...@cox.net
I. too, thought it odd that such a 'band-aid' was suggested by Oracle,
absent any real investigative work. My choice is to compute the fixed
objects statistics, as I'd rather not adversely affect every other
query using a MERGE JOIN CARTESIAN in order to fix one errant example.
David Fitzjarrell
tit...@gmail.com
I have same problem with fixed tables
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
OS AIX 5.3
SQL_TEXT
--------------------------------------------------------------------------------
SELECT * FROM V$DB_PIPES WHERE NAME NOT LIKE '%LOCK%' ORDER BY PIPE_SIZE DESC
Plan:
********************************************************************************
Rows Row Source Operation
------- ---------------------------------------------------
191 SORT ORDER BY (cr=0 r=0 w=0 time=32691509 us)
191 FIXED TABLE FULL X$KGLOB (cr=0 r=0 w=0 time=32678781 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 14 0.00 0.00
latch free 75 0.29 11.75
SQL*Net message from client 14 2.15 2.16
********************************************************************************
But I can't create fixed index on X$KGLOB, because this is 9.2.0.8.
What can I do to eliminate this problem?
tit...@gmail.com
> But I can't create fixed index on X$KGLOB, because this is 9.2.0.8.
SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS('ALL');
BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS('ALL'); END;
ERROR at line 1:
PLS-00302: component 'GATHER_FIXED_OBJECTS_STATS' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Mladen Gogala
> But I can't create fixed index on X$KGLOB, because this is 9.2.0.8.
> What can I do to eliminate this problem?
--
http://mgogala.byethost5.com
tit...@gmail.com
> On Wed, 26 Dec 2012 04:41:37 -0800, titan4 wrote:
>
> Upgrade sounds like a winner.
>
Deadly Dirk
aware of the fact that version 9i is long gone and unsupported? That means
no patches. Absolutely none, zero, nada, zilch. Even worse, if you have
corruption or data loss of any kind, you cannot open a service request
with the Oracle Support. You are on your own. If the database is so
critical to the company that you cannot upgrade it, then the possibility
of losing that database and being beyond help should scare your management
shootless. Lightning strike could put your company out of business. And
lightning strikes do happen. Hurricanes in New York City also do happen.
There is a building in Jersey City NJ, called "Harbourside Financial
Center", built right on the Hudson coast, less than a mile from
the place where Hudson flows into the Atlantic Ocean. Beautiful waterfront
property, right across the river from the Wall Street. In the basement of
that building was a server room with quite a few servers. Then came the
hurricane Sandy. Long story short, here is what happened:
http://ireport.cnn.com/docs/DOC-867312
I will leave to your imagination to guess what has happened to the servers
in the basement. Imagine your 9.2.0.8 server was sleeping with the fishes,
just like Luca Brasi from the "Godfather". Where would you get help? Your
server is unsupported and Oracle Corp. would probably require a ton of
money and your first born. Your company may go out of business because of
such thing.
On the other hand, if that database is not that important, I don't see why
wouldn't you take it down and upgrade? A bug in 9.2.0.8? Tough luck my
friend, you're on your own. That's what happens when you run unsupported
versions.
--
I love the smell of napalm in the morning.
tit...@gmail.com
> Oh? I would really like to hear the reasoning behind this one. Are you
Mladen Gogala
was discontinued and we don't want to give it up", but "I cannot disclose
the reason" is something new. There is nothing that anybody can do with
the 9i.
--
http://mgogala.byethost5.com
John Hurley
reasons...
paying oracle maintenance.
Saves a bunch of money for sure ... but hard to move onward / upward.
Sometimes means a company was purchased by someone else and no one
wants to touch any of the current "working" systems.
Mladen Gogala
from the Google groups and when I checked what was caught in the net,
your post was there.
Can you use a normal NNTP client like forte, T-Bird or Pan? I can
wholeheartedly recommend Pan (http://pan.rebelbase.com/)
which works on OSX, Windows and Linux. On Android tablets, there is
Groundhog. Anything but Google groups and web interface, pleaaseeeee!
--
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com
joel garry
wrote:
they are clients. It's like saying "Don't use any server based stored
procedures, you must put all your oracle programs on a fat client."
Sounds silly when you put it that way, eh?
http://callcenterinfo.tmcnet.com/analysis/articles/321265-oracle-boosts-its-customer-experience-cloud-with-eloqua.htm
Mladen Gogala
> Well, I guess you won't see this,
checked few posters on this group and figured out that my net is too
broad. I have since removed Google groups from my filter and limited
filtering to "after the fact" mode.
> but the problem with all those is they
> are clients. It's like saying "Don't use any server based stored
> procedures, you must put all your oracle programs on a fat client."
> Sounds silly when you put it that way, eh?
the purpose of putting things into the database is faster development and
better performance. The purpose of using a fat NNTP client is the
functionality which I don't have with web clients like Google groups. The
functionality that I am talking about is, of course, filtering. You
cannot filter out articles with the Google Groups interface. You can with
all of the fat clients I listed. Last but not least, I wish you a happy
New Year.
joel garry
I hear oracle-l has some good filtering
<g,d&r>
Figures, the one time in months I decide to take my kids down there:
http://www.utsandiego.com/news/2012/dec/29/tp-suicide-threats-cause-two-freeway-closures/
Mladen Gogala
> Happy New Year to you, too, Big Brother!
>
> I hear oracle-l has some good filtering
recommendations, another one has lost the disk and now cannot mount ASM
diskgroup, somebody is trying to stuff gazillion elements into the IN
list of the WHERE clause and somebody doesn't quite understand "audit
network" but is unwilling to read the manual. That's about it. I prefer no
posts at all to pile of c....useless stuff like that.
Mladen Gogala
>
> I hear oracle-l has some good filtering <g,d&r>
Steve Adams, being a prudish prick that he is and having not done any
work with Oracle for years, has nothing to do with what happened.
Establishing a new list or coming over here would be the easiest thing in
the world and yet it didn't happen.
The reason is that the majority of the consultants on the list see that
list as a perfect marketing tool for customer hunting. They have a
reputation to maintain, in order to justify rather large consulting fees,
and a list having just a few qualified professionals would not be very
useful for money.making purposes. We're not friends, we're not family,
they have to make money and they need guys like that guy who cannot find
5 of his drives, which means that ORADATA disk group in his ASM volume
manager is inaccessible, along with the company data. It's what I call
"Larry, Curly & Moe method of database administration". A perfect
candidate for a $400/hr consultant to rescue the company.
There is also another climate change which has rendered the list
useless: Oracle is more closed than ever. They're not dispensing any
internal information, unless they absolutely have to, or unless somebody
pays for it. They have the title of "Ace Director" who has access to some
privileged information and which is given to some consultants to bolster
their consulting fees. They will not dispense such information freely, on
a mailing list, they will do it for $400/hr.
That means that there isn't much to learn on the list. Metastink still
doesn't return any hits if you search for X$KSLHOT, several years after
K. Gopalakrishnan has published the table description in his RAC 10 book.
That should tell you everything you need to know about the information
sharing by the Oracle Corp. If Oracle support site doesn't have much of
the interesting stuff, what can you expect from a publicly available
mailing list?
I am not a gypsy consultant, I don't need that list. There is nothing
for me there. That is the same thing as with OTN forums. Too much
traffic, too much effort for no result whatsoever. I wanted to return to
the list for purely sentimental reasons, when I thought that the old
prick finally decided that policing the list is not worth the effort, but
unfortunately, it wasn't to be. However, I will not return to the list. I
have no use for such a forum any more.
This is much more to my liking: there are few of us here who post when we
have something, like with the Bloom filters, and do not generate much
traffic other than that.