RE: analyze estimate sample size

From: Thomas Jeff <ThomasJe_at_tce.com>
Date: Wed, 6 Sep 2000 18:50:21 -0500
Message-Id: <10611.116358@fatcity.com>

Ruth,

What version of Oracle are you on? If you have 8.1, you can use DBMS_STATS,
e.g;

PROCEDURE gather_schema ( in_schema_owner IN VARCHAR2,
                          in_estimate     IN NUMBER DEFAULT 30,
                          in_degree       IN NUMBER DEFAULT NULL,
                          in_block        IN BOOLEAN DEFAULT FALSE,
                          in_granularity  IN VARCHAR2 DEFAULT 'DEFAULT') AS

BEGIN
    DBMS_STATS.GATHER_SCHEMA_STATS

         (OWNNAME => in_schema_owner,
          ESTIMATE_PERCENT => in_estimate,
          BLOCK_SAMPLE => in_block,
          METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',
          DEGREE => in_degree,
          GRANULARITY => in_granularity,
          OPTIONS => 'GATHER',
          CASCADE => TRUE);

END gather_schema;

Jeff T.
thomasje_at_tce.com

-----Original Message-----
From: Troiano, Paul (CAP, GEFA) [mailto:Paul.Troiano_at_gecapital.com] Sent: Wednesday, September 06, 2000 3:37 PM To: Multiple recipients of list ORACLE-L Subject: RE: analyze estimate sample size

Ruth,

Here is the partial output of a describe on dbms_utlity ... Is this how you are analyzing the schema? If so, I think estimate rows is what you are looking for. Can't try it out now. My dev, test, etc server is currently having some sort of breakdown. Sun is looking into it though...

• Paul

PROCEDURE ANALYZE_SCHEMA

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SCHEMA                         VARCHAR2                IN
 METHOD                         VARCHAR2                IN
 ESTIMATE_ROWS                  NUMBER                  IN     DEFAULT
 ESTIMATE_PERCENT               NUMBER                  IN     DEFAULT
 METHOD_OPT                     VARCHAR2                IN     DEFAULT



		-----Original Message-----
		From:	Ruth Gramolini [mailto:rgramolini_at_tax.state.vt.us]
		Sent:	Wednesday, September 06, 2000 10:31 AM
		To:	Multiple recipients of list ORACLE-L
		Subject:	analyze estimate sample size

		Hello all,

		Can anyone tell me if you can change the sample size used
when you analyze a
		schema with the 'estimate' option.  I would like to make it

bigger.

		Thanks in advance,
		Ruth B. Gramolini
		ORACLE & DB2  DBA
		VT Dept. of Taxes
		ph# 802.828.5708
		fax# 802.828..3754
		rgramolini_at_tax.state.vt.us

		-- 
		Author: Ruth Gramolini
		  INET: rgramolini_at_tax.state.vt.us

		Fat City Network Services    -- (858) 538-5051  FAX: (858)
538-5051
		San Diego, California        -- Public Internet access /

Mailing Lists         

---

                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).

-- 
Author: Troiano, Paul (CAP, GEFA)
  INET: Paul.Troiano_at_gecapital.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

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

Received on Wed Sep 06 2000 - 18:50:21 CDT