There are a Lot of options to DBMS_STATS .. And be Judicious in using
the same. Read the docs before attempting it.
BTW .. What is the session waiting for .. Just Check v$session_wait.
HTH
Best Regards,
Ganesh R
DID : +65-6215-8413
HP : +65-9067-8474
===========================================
Live to learn... forget... and learn again.
===========================================
-----Original Message-----
Keshava Murthy (Cognizant)
Sent: Wednesday, June 04, 2003 8:55 PM
To: Multiple recipients of list ORACLE-L
Hi all,
Everyone is recommending DBMS_STATS to be used for
computing the statistics. But even after specifying the parallel option
, DBMS_STATS is taking lots of time ! in comparison to Analyze...
In case of tables with a million records the query just hangs when i
use the stats package...
Can anybody tell me whts happening ?
Regards
kesh
-----Original Message-----
Sent: Tuesday, June 03, 2003 8:55 PM
To: Multiple recipients of list ORACLE-L
Sorry JP, I should have clarified that DBMS_STATS is the way to go, when
on a 9i or above release. One of the simple factors being that ANALYZE
is being deprecated.. There seemed to be a number of bugs/quirks,
whatever you want to call them, in certain 8.1.x versions, which are now
fixed within 9i..
There's a number of threads about the issues, available in the list
archives (you can find them at http://www.faqchest.com), which I dug
around - as I remembered seeing a post by Connor McDonald, where he gave
away an alternate script to gather stats, here's the mail:
http://www.faqchest.com/prgm/oracle-l/ora-02/ora-0210/ora-021011/ora0210
0802
_06150.html
I think the general consensus is that DBMS_STATS is quicker. I've never
personally done any comparative benchmarks to corroborate this sheep
following attitude though.. ;)
Regards
Mark
-----Original Message-----
Sent: 03 June 2003 14:55
To: Mark Leith
Cc: [EMAIL PROTECTED]
Thanx a lot Mark.
Sure , your code has given me a starting point.
let me change accordingly to use DBMS_STATSe and give a try :-)
So,can i conclude that DBMS_STATS is better than ANALYZE ?!
Someone over the list mentioned that DBMS_STATS is slower.
is it so ? What is your opinion regarding this ?!
just curious to know !
Regards,
Jp.
3-6-2003 22:01:32, "Mark Leith" <[EMAIL PROTECTED]> wrote:
>Prem,
>
>Use DBMS_JOBS to run the analyze:
>
>===================================
>
>undef username
>undef password
>grant analyze any to &&username
>/
>
>connect &&username/&&password
>
>CREATE or REPLACE PROCEDURE analyze_tables (
> v_stat_type IN VARCHAR2 := 'COMPUTE') AS
> CURSOR c IS
> SELECT DISTINCT owner
> FROM all_tables
> WHERE owner not in ('SYS','SYSTEM');
> BEGIN
> FOR any_row IN c LOOP
> dbms_utility.analyze_schema(
> any_row.owner,v_stat_type);
> END LOOP;
> END;
>/
>
>===================================
>
>variable jobno number
>declare jobno number;
> BEGIN
> dbms_job.submit(:jobno,
> 'begin &&username.analyze_tables; end;',
> to_date('03jun0304:00','DDMONYYHH24:MI'),
> 'trunc(sysdate)+(1+(4/24))');
> END;
>
>===================================
>
>The above will run a COMPUTE analyze on all schemas, except SYS and
SYSTEM,
>at 4:00am every day. Modify it to your own needs, but it should give
you a
>starting point..
>
>I would also recommend using DBMS_STATS to generate your statistics.
>
>Have fun! ;0)
>
>Mark
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mark Leith
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ganesh Raja
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
