Another Day, Another Obscure Oracle Error September 11, 2009
Posted by mwidlake in internals, performance.Tags: data dictionary, rant, statistics
9 comments
I seem to be spending my life hitting odd bugs or errors in Oracle’s performance-related areas at the moment. I guess part of it is because I am using features not widely touched, part is I’m not working as the SYS or SYSTEM user {in my experience a lot of people working on such things do it when connected as sysdba or at least as SYSTEM} and part is that Larry Ellisson personally hates me {he fills in for God when it comes to Oracle}.
I’m seeing this a lot today,and it seems virtually unknown:-
ERROR at line 1:
ORA-20003: Unable to gather system statistics
ORA-06512: at “SYS.DBMS_STATS”, line 15882
ORA-06512: at line 1
I’m also seeing this, but this error is documented:-
ERROR at line 1:
ORA-20000: Unable to gather system statistics : insufficient privileges
ORA-06512: at “SYS.DBMS_STATS”, line 15822
ORA-06512: at line 1
The rest of this post will explain my path to only partial enlightenment. If you have hit the above errors and found this page via Google, this might help you out and you might like to skip towards the end.
If you just read my blog, regard this as a very long “tweet” or whatever the hell they are. Again, feel free to skip to the end. Or just skip.
The task I’m doing which is causing me to hit these issues is that I’m trying to assure myself of the ability to role back gathering system statistics before getting the DBA team to do it on a production system. {I am not in the DBA team on this one}.
No system statistics have been gathered yet so I have the default set, I’m sure many of you will recognise them:
SNAME PNAME PVAL1 PVAL2 --------------------------------------------------- SYSSTATS_MAIN CPUSPEED SYSSTATS_MAIN CPUSPEEDNW 1,567.798516 SYSSTATS_MAIN IOSEEKTIM 10.000000 SYSSTATS_MAIN IOTFRSPEED 4,096.000000 SYSSTATS_MAIN MAXTHR SYSSTATS_MAIN MBRC SYSSTATS_MAIN MREADTIM SYSSTATS_MAIN SLAVETHR SYSSTATS_MAIN SREADTIM SYSSTATS_INFO DSTART 07-25-2006 12:39 SYSSTATS_INFO DSTOP 07-25-2006 12:39 SYSSTATS_INFO FLAGS 1.000000 SYSSTATS_INFO STATUS COMPLETED
I decided to pull the current system statistics into a statistics table:-
exec dbms_stats.create_stat_table(‘DWPERF’,’AD_BF_STATS’,’DW_COMMON_MG’)
EXEC DBMS_STATS.EXPORT_SYSTEM_STATS
(‘AD_BF_STATS’,’SYSSTAT’||TO_CHAR(SYSDATE,’YYMMDDHH24MI’),’DWPERF’)
SELECT * FROM AD_BF_STATS;
SYSSTAT0909101309 S 4 1 COMPLETED
07-25-2006 12:39 07-25-2006 12:39 CPU_SERIO
SYSSTAT0909101309 S 4 1
PARIO
10 4096 1567.79852
Two records created, first is the header record about what this statid is all about, the second contains the stored info. I’ve highlighted the relevant records.
All well and good, I can save my stats. Why do I want to? Because we may gather system statistics on an infrequent but regular basis, and I want a record of them. The automtically stored history only goes back a month {by default}.
Of course, there is no need for me to explicitly export the stats to the table, it can be done as part of the gathering process, by stating the statistics table, owner and an ID when I do the gather, so I did:-
exec dbms_stats.gather_system_stats(gathering_mode => ‘INTERVAL’,interval => 15,stattab=> ‘
AD_BF_STATS’,statown=> ‘DWPERF’ ,statid=>’SYSSTAT’||TO_CHAR(SYSDATE,’YYMMDDHH24MI’))
I went off for a cup of coffee, came back, checked my system stats.
SNAME PNAME PVAL1 PVAL2 --------------------------------------------------- SYSSTATS_MAIN CPUSPEED SYSSTATS_MAIN CPUSPEEDNW 1,567.798516 SYSSTATS_MAIN IOSEEKTIM 10.000000 SYSSTATS_MAIN IOTFRSPEED 4,096.000000 SYSSTATS_MAIN MAXTHR SYSSTATS_MAIN MBRC SYSSTATS_MAIN MREADTIM SYSSTATS_MAIN SLAVETHR SYSSTATS_MAIN SREADTIM SYSSTATS_INFO DSTART 07-25-2006 12:39 SYSSTATS_INFO DSTOP 07-25-2006 12:39 SYSSTATS_INFO FLAGS 1.000000 SYSSTATS_INFO STATUS COMPLETED
Oh. No change. No system stats gathered. Damn.
Well, it’s a test box, there may be too little worload to detect anything – like none! I checked the SGA for recent code and the only SQL was that for recording the stats gathering :-).
So, repeated with me running some workload in another window for 15 minutes.
Still no change, still no system stats. Double Damn!
I remembered from reading around that if MREADTIME is less than SREADTIME the stats gathered could be ignored so I tried again.
And again.
And then decided it would not magically work, there was a problem.
I’ll gather the system stats with START and STOP and really hammer the box for a while, ensuring with autotrace
exec dbms_stats.gather_system_stats(gathering_mode => ‘START’,stattab=>’AD_BF_STATS’,statown
=> ‘DWPERF’ ,statid=>’SYSSTAT’||TO_CHAR(SYSDATE,’YYMMDDHH24MI’))
work-work-work-work-work
exec dbms_stats.gather_system_stats(gathering_mode => ‘STOP’);
BEGIN dbms_stats.gather_system_stats(gathering_mode => ‘STOP’); END;
*
ERROR at line 1:
ORA-20003: Unable to gather system statistics
ORA-06512: at “SYS.DBMS_STATS”, line 15882
ORA-06512: at line 1
Oh.
Oh Hell. Well, at least I now know that the chances are my interval systenm stats collection failed with this, but silently.
I bet if I dug around I would find a few failed jobs recorded where the INTERVAL version of the stats collection failed with this. I have not checked, I am just up to my eyes right now.
I found nothing at all via Google or Metalink about this exact error for gathering system stats. BUT maybe I lacked the correct priveleges on my non-SYS account. So I granted ANALYZE AND DICTIONARY and ANALYZE ANY to my user, as those or the priveleges mentioned for many of the DBMS_STATS management procedures.
Nope, did not help.
Blow it, If DBMS_STATS won’t let me keep the stats in my table, I’ll just rely on the default store of them, and not state my stats table in the call:
DWD2_DWPERF> exec dbms_stats.gather_system_stats(gathering_mode => ‘START’)
BEGIN dbms_stats.gather_system_stats(gathering_mode => ‘START’); END;
*
ERROR at line 1:
ORA-20000: Unable to gather system statistics : insufficient privileges
ORA-06512: at “SYS.DBMS_STATS”, line 15822
ORA-06512: at line 1
Oh. Well, that’s interesting.
If I don’t state a stats table the gather fails when initiated.
If I state a stats table the gather fails when I stop.
How frustrating.
I decided to give up. Then I decided I won’t be beaten and to grant myself DBA role and see if it works.
DWDEV2> exec dbms_stats.gather_system_stats(gathering_mode => ‘START’)
PL/SQL procedure successfully completed.
YEEEESSSSSS!!!
But will it now error when I stop the gather…
DWDEV2> exec dbms_stats.gather_system_stats(gathering_mode => ‘STOP’)
PL/SQL procedure successfully completed.
Deep Joy.
And it gathered stats.
SNAME PNAME PVAL1 PVAL2 ------------------------------------------ SYSSTATS_MAIN CPUSPEED 1,545.000000 SYSSTATS_MAIN CPUSPEEDNW 1,567.798516 SYSSTATS_MAIN IOSEEKTIM 10.000000 SYSSTATS_MAIN IOTFRSPEED 4,096.000000 SYSSTATS_MAIN MAXTHR SYSSTATS_MAIN MBRC SYSSTATS_MAIN MREADTIM SYSSTATS_MAIN SLAVETHR SYSSTATS_MAIN SREADTIM 5.000000 SYSSTATS_INFO DSTART 09-11-2009 13:02 SYSSTATS_INFO DSTOP 09-11-2009 13:02 SYSSTATS_INFO FLAGS 1.000000 SYSSTATS_INFO STATUS COMPLETED
{ignore the lack of mreadtime and sreadtime, nothing happend on the box in the few seconds I was collecting for}
SO, the lack of DBA role was a problem but had been hidden by my attempts to use a stats table. Now surely I can do what I wanted to and store the stats in my stats table…
exec dbms_stats.gather_system_stats(gathering_mode => ‘START’,stattab=>’AD_BF_STATS’,statown
=> ‘DWPERF’ ,statid=>’SYSSTAT’||TO_CHAR(SYSDATE,’YYMMDDHH24MI’))
PL/SQL procedure successfully completed.
Wehay.
work-work-work-work-work
exec dbms_stats.gather_system_stats(gathering_mode => ‘STOP’)
BEGIN dbms_stats.gather_system_stats(gathering_mode => ‘STOP’); END;
*
ERROR at line 1:
ORA-20003: Unable to gather system statistics
ORA-06512: at “SYS.DBMS_STATS”, line 15882
ORA-06512: at line 1
AAAAAGGGGHHHHHH!!!!!!!!!
Let’s check back.
exec dbms_stats.gather_system_stats(gathering_mode => ‘START’)
PL/SQL procedure successfully completed.
work-work-work-work-work
exec dbms_stats.gather_system_stats(gathering_mode => ‘STOP’)
PL/SQL procedure successfully completed.
That works, because I am not involving a stats table. If you look back at the two errors, they come from different (but very similar) line numbers. I’d missed that for a couple of hours, I blame dyslexia.
Conclusion:
Gathering and storing System Stats is as buggy and random as the rest of the dbms_stats functionality. {yes, I am peeved about this}. It Seems..
- If you gather system stats with the interval method, it can silently fail. This might only be if you involve a stats table.
- You need the DBA role to gather system stats, otherwise you get ORA-20000, via ORA-06512 at line 15822
- analyze and dictionary and analyze any are not enough to allow a non DBA account to gather system stats.
- If you try and store the previous system stats in your stats table as part of the gather you get ORA-20003, via ORA-06512 at line 15882 {different line}, even if you have DBA role.
- If you just rely on Oracle preserving the previous version of the stats, you can gather system stats fine.
- You can work around the issue by exporting the system stats to your stats table as a single step first, then gathering system stats without involving your stats table.
There is no metalink for the ORA-20003 via line 15882 in metalink and no google hit for it, so I reckon it is a damned rare event. And since the last time I blogged about a damned rare error, I see 1 or 2 hits on that blog page every day since :-).
Martin Widlake's Yet Another Oracle Blog 