Home » RDBMS Server » Server Administration » Gathering Satistics
Gathering Satistics [message #207929] Thu, 07 December 2006 07:17 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
I'm running the following script

logged in to schema - TESTUSER1

Begin
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'TESTUSER1',options=>'GATHER AUTO');
End;

this does not work, it comes out immediately without giving error.

It works if I give ..
logged in to TESTUSER1
Begin
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'TESTUSER1');
End;

But the script 1 works fine in another schema

i,e.
Logged to TESTUSER2

Begin
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'TESTUSER2',options=>'GATHER AUTO');
End;


I do not understand why it is so..

Brian.
Re: Gathering Satistics [message #207938 is a reply to message #207929] Thu, 07 December 2006 08:05 Go to previous messageGo to next message
bwetkstr
Messages: 114
Registered: August 2005
Senior Member
TESTUSER1 and TESTUSER2 have the same rights?
Re: Gathering Satistics [message #207949 is a reply to message #207929] Thu, 07 December 2006 08:36 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
As usual, No Oracle version specified and no real session posted.
So this could take a little while to guess things out.Smile
How are you saying that is working and not working?
Delete the stats. gather the stats. check num_rows or somehing like that. Accuracy of the stats depends on size of your bucket.
how many objects in testuser1 and testuser2?
Are those two schema's identically same?
By specifying AUTO, you are leaving the control to oracle.

[Updated on: Thu, 07 December 2006 08:36]

Report message to a moderator

Re: Gathering Satistics [message #208054 is a reply to message #207929] Thu, 07 December 2006 23:52 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

Orcl Version - 9.2.0.7

I'm submitting DBMS_JOB. If I issue following within no time it finishes.

Declare
job number;
Begin
DBMS_JOB.SUBMIT(job,
'Begin
DBMS_STATS.GATHER_SCHEMA_STATS(ownname =>''TESTUSER1'',
options => ''GATHER AUTO''
);
End;',
Sysdate + 2/1440, 'NULL' );
End;


My doubt was why it is finishing within 1-2 seconds(I'm not sure it is doing the job or not). 2 days back it I've gathered stats with the same script.

It worked fine with

Declare
job number;
Begin
DBMS_JOB.SUBMIT(job,
'Begin
DBMS_STATS.GATHER_SCHEMA_STATS(ownname =>''TESTUSER1'' );
End;',
Sysdate + 2/1440, 'NULL' );
End;


Brian.
Re: Gathering Satistics [message #208265 is a reply to message #208054] Fri, 08 December 2006 23:09 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hello,


First delete old statistics.

gather new stats with ur script.

and check dba_tables.num_rows. values for you script run or not.

and for dbms_job.

you can check .
select job, failures from dba_job where job = '&jobno';


regards
Taj
Previous Topic: Rebuild Index - Oracle 10g Rel2
Next Topic: tablespace
Goto Forum:
  


Current Time: Sat Dec 03 18:00:52 CST 2016

Total time taken to generate the page: 0.12486 seconds