OraFAQ Forum: Performance Tuning » DBMS

Home » RDBMS Server » Performance Tuning » DBMS_STATS Problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DBMS_STATS Problem [message #285319]

Tue, 04 December 2007 04:24

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

Hi,

Actually i am taken backup (hot backup) from my production database. And successfully restored in my standby machine.

When collecting statistics from schema i am facing the below error message.

Quote:

Errors in file d:\oracle\admin\samidb\udump\samidb_ora_2440.trc:
ORA-12012: error on auto execute of job 123
ORA-06521: PL/SQL: Error mapping function
ORA-06512: at "SYS.DBMS_STATS", line 9339
ORA-06512: at "SYS.DBMS_STATS", line 9821
ORA-06512: at "SYS.DBMS_STATS", line 10005
ORA-06512: at "SYS.DBMS_STATS", line 10059
ORA-06512: at "SYS.DBMS_STATS", line 10036
ORA-06512: at line 1

Even all parameter properly configured. like

SQL> show parameter job

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
job_queue_processes                  integer     10

Trace File information:

Quote:

Dump file d:\oracle\admin\samidb\udump\samidb_ora_2440.trc
Tue Dec 04 15:48:47 2007
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Instance name: samidb

Redo thread mounted by this instance: 1

Oracle process number: 16

Windows thread id: 2440, image: ORACLE.EXE

*** SESSION ID:(21.11) 2007-12-04 15:48:47.000
*** 2007-12-04 15:48:47.000
ORA-12012: error on auto execute of job 123
ORA-06521: PL/SQL: Error mapping function
ORA-06512: at "SYS.DBMS_STATS", line 9339
ORA-06512: at "SYS.DBMS_STATS", line 9821
ORA-06512: at "SYS.DBMS_STATS", line 10005
ORA-06512: at "SYS.DBMS_STATS", line 10059
ORA-06512: at "SYS.DBMS_STATS", line 10036
ORA-06512: at line 1

May i know, what's the problem ?

[Updated on: Tue, 04 December 2007 05:39]

Report message to a moderator

Re: DBMS_STATS Problem [message #285393 is a reply to message #285319]

Tue, 04 December 2007 06:44

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>When collecting statistics from schema i am facing the below error message.
Post what you did. I mean, the exact statement used to collect statistics.
If you can afford, delete the existing statistics for the concerned table and collect again.

Report message to a moderator

Re: DBMS_STATS Problem [message #285407 is a reply to message #285393]

Tue, 04 December 2007 07:22

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

DECLARE
   v_job   NUMBER;  -- Job Number 110
BEGIN
   DBMS_JOB.SUBMIT(v_job,
                   'DBMS_STATS.GATHER_SCHEMA_STATS
                       (ownname=>''AFLMIS'', 
                        estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, 
                        method_opt=>''FOR ALL INDEXED COLUMNS SIZE AUTO'', 
                        cascade=>TRUE);',
                   SYSDATE,
                   'TRUNC(SYSDATE)+1+0.5/24');
   COMMIT;
END;

>>If you can afford, delete the existing statistics for the concerned table and collect again.

I will try and let you know

Report message to a moderator

Re: DBMS_STATS Problem [message #285410 is a reply to message #285407]

Tue, 04 December 2007 07:28

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

This could be a known issue and the recent versions may have this fixed.
Instead of estimate, use compute. Early versions of Oracle 9i had some random problems with estimate when used with GATHER_SCHEMA_STATS/GATHER_DATABASE_STATS

[Updated on: Tue, 04 December 2007 07:28]

Report message to a moderator

Re: DBMS_STATS Problem [message #285416 is a reply to message #285410]

Tue, 04 December 2007 07:58

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

Yes. Your correct. Just now only i found the problem.

Quote:

GATHER_SCHEMA_STATS/GATHER_DATABASE_STATS - Fixed in 9.2.0.2.1

I installed this patch. Now everything fine.

Thanks

Babu

[Updated on: Tue, 04 December 2007 07:59]

Report message to a moderator

Previous Topic:	Shows difference in EXPLAIN PLAN result for same query.
Next Topic:	Faceing Database Performance Related Problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 30 03:22:19 CST 2024