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.
> 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.
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.
> On Jan 23, 9:23 am, Peter Teoh <htmldevelo...@gmail.com> wrote: >> Making a long query:
>> 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.
> 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
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.
> 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.
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 ...
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;
> 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?
> 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.
Steve Howard wrote: > On Jan 23, 7:32 am, Peter Teoh <htmldevelo...@gmail.com> wrote: >> I tried on Oracle9iR2 (Fedora Core Linux 5) no problem, it returned >> immediately - no hanging.
>> But for the earlier post - I forgotten to mentioned they are on Fedora >> Core 7.
>> Thanks.
> 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
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: > > On Jan 23, 7:32 am, Peter Teoh <htmldevelo...@gmail.com> wrote: > >> I tried on Oracle9iR2 (Fedora Core Linux 5) no problem, it returned > >> immediately - no hanging.
> >> But for the earlier post - I forgotten to mentioned they are on Fedora > >> Core 7.
> >> Thanks.
> > 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
> 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
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.
> 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.
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;
On Jan 23, 4:58 am, Charles Hooper <hooperc2...@yahoo.com> wrote:
> 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
> On Jan 23, 4:58 am, Charles Hooper <hooperc2...@yahoo.com> wrote:
> > 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
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.
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.
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)
joel garry wrote: > On Jan 23, 4:58 am, Charles Hooper <hooperc2...@yahoo.com> wrote: >> 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
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
> 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)
> > 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)
> 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
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:500015404803594...
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.
> On Jan 24, 2:20 pm, Frank van Bortel <frank.van.bor...@gmail.com> > wrote:
> > Charles Hooper wrote:
> > > 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)
> > SQL> set timing on > > SQL> select distinct owner fromv$access;
> > OWNER > > ---------------------------------------------------------------- > > PUBLIC > > SYSTEM > > XDB > > SYS
> > Elapsed: 00:00:00.01
> > At least there's a workaround. > > --
> > Regards, > > Frank van Bortel
> 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:500015404803594...
> 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.- Hide quoted text -
> - 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.
On Feb 1, 12:38 pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
> On Jan 24, 2:13 pm, Charles Hooper <hooperc2...@yahoo.com> wrote: > > 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. > 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
> On Feb 1, 12:38 pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
> > On Jan 24, 2:13 pm, Charles Hooper <hooperc2...@yahoo.com> wrote: > > > 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. > > 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
> Charles Hooper > IT Manager/Oracle DBA > K&M Machine-Fabricating, Inc.
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.
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?
On Wednesday, December 26, 2012 6:41:37 PM UTC+6, tit...@gmail.com wrote:
> But I can't create fixed index on X$KGLOB, because this is 9.2.0.8.
Sorry, I mean that I can't collect statistics on the fixed objects:
SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS('ALL');
BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS('ALL'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 18:
PLS-00302: component 'GATHER_FIXED_OBJECTS_STATS' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
On Wed, 26 Dec 2012 04:41:37 -0800, titan4 wrote:
> 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?
On Wed, 26 Dec 2012 22:02:25 -0800, titan4 wrote:
> On Thursday, December 27, 2012 12:16:22 AM UTC+6, Mladen Gogala wrote:
>> On Wed, 26 Dec 2012 04:41:37 -0800, titan4 wrote:
>> Upgrade sounds like a winner.
> We will not be able to upgrade in the next 2 years, unfortunately...
Oh? I would really like to hear the reasoning behind this one. Are you 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:
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.
