Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Strange behavior with dbms_stats...

Re: Strange behavior with dbms_stats...

From: anu <anu_77_d_at_yahoo.com>
Date: Thu, 11 Dec 2003 08:54:35 -0800
Message-ID: <F001.005D98FB.20031211085435@fatcity.com>
--0-658437726-1071160184=:78021


The proc generates the 'exec dbms_stats ' statements for all the users. Are you saving the output and running it manually or not. IT would have the same statements that you run one by one.

Jose Luis Delgado <joseluis_delgado_at_yahoo.com> wrote:List...

SunOS 5.8, Oracle 8.1.6 (and 8.1.7 too).

I use the proc at the bottom to generate statistics.

It seems to work, but if I check statistics with:

select owner, table_name, num_rows, blocks, av_row_len,
to_char(last_analyzed, 'MM/DD/YYYY HH24:MI:SS') from dba_tables;

the tables have the OLD last_analyzed time!

but... If I execute ONE by ONE:

exec dbms_stats.gather_schema_stats(ownname => 'PERFSTAT', cascade => TRUE);

it works fine!!...

So, am I doing something wrong?

Any help?

TIA
JL

create or replace procedure get_statistics as cursor get_users_list is
select username
from dba_users
where username != 'SYS'
and username != 'SYSTEM';

begin
for i in get_users_list
loop
dbms_output.put_line('exec
dbms_stats.gather_schema_stats(ownname => '||chr(39)||i.username||chr(39)||', cascade => TRUE);');
end loop;
end;
/



Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jose Luis Delgado
INET: joseluis_delgado_at_yahoo.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services



To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

Do you Yahoo!?
Free Pop-Up Blocker - Get it now
--0-658437726-1071160184=:78021

Content-Type: text/html; charset=us-ascii

<DIV>The proc generates the 'exec dbms_stats ' statements for all the users. Are you&nbsp;saving the&nbsp;output and running it manually or not.&nbsp;IT would have the same statements that you run one by one. <BR><BR><B><I>Jose Luis Delgado &lt;joseluis_delgado_at_yahoo.com&gt;</I></B> wrote: <BLOCKQUOTE class=replbq style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">List...<BR><BR>SunOS 5.8, Oracle 8.1.6 (and 8.1.7 too).<BR><BR>I use the proc at the bottom to generate statistics.<BR><BR>It seems to work, but if I check statistics with:<BR><BR>select owner, table_name, num_rows, blocks,<BR>av_row_len,<BR>to_char(last_analyzed, 'MM/DD/YYYY HH24:MI:SS')<BR>from dba_tables;<BR><BR>the tables have the OLD last_analyzed time!<BR><BR>but... If I execute ONE by ONE:<BR><BR>exec dbms_stats.gather_schema_stats(ownname =&gt;<BR>'PERFSTAT', cascade =&gt; TRUE);<BR><BR>it works fine!!...<BR><BR>So, am I doing something wrong?<BR><BR>Any help?<BR><BR>TIA<BR>JL<BR><BR>create or replace procedure get_statistics as<BR>cursor get_users_list is<BR>select username<BR>from dba_users<BR>where username != 'SYS' <BR>and username != 'SYSTEM';<BR><BR>begin<BR>for i in get_users_list<BR>loop<BR>dbms_output.put_line('exec<BR>dbms_stats.gather_schema_stats(ownname

 =&gt;<BR>'||chr(39)||i.username||chr(39)||', cascade =&gt;<BR>TRUE);');<BR>end loop;<BR>end;<BR>/<BR><BR><BR><BR>__________________________________<BR>Do you Yahoo!?<BR>New Yahoo! Photos - easier uploading and sharing.<BR>http://photos.yahoo.com/<BR>-- <BR>Please see the official ORACLE-L FAQ: http://www.orafaq.net<BR>-- <BR>Author: Jose Luis Delgado<BR>INET: joseluis_delgado@yahoo.com<BR><BR>Fat City Network Services -- 858-538-5051 http://www.fatcity.com<BR>San Diego, California -- Mailing list and web hosting services<BR>---------------------------------------------------------------------<BR>To REMOVE yourself from this mailing list, send an E-Mail message<BR>to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in<BR>the message BODY, include a line containing: UNSUB ORACLE-L<BR>(or the name of mailing list you want to be removed from). You may<BR>also send the HELP command for other information (like subscribing).</BLOCKQUOTE></DIV><p><hr SIZE=1>
Do you Yahoo!?<br>
<a href="http://us.rd.yahoo.com/slv/mailtag/*http://companion.yahoo.com/">Free Pop-Up Blocker - Get it now</a>
--0-658437726-1071160184=:78021--
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: anu
  INET: anu_77_d_at_yahoo.com
Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services

---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Dec 11 2003 - 10:54:35 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US