| Gathering Satistics [message #207929] |
Thu, 07 December 2006 07:17  |
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 #207949 is a reply to message #207929] |
Thu, 07 December 2006 08:36  |
 |
Mahesh Rajendran
Messages: 10707
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.
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 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
