| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: about analyse table..
Hi Saurabh,
The Analyse command helps to collect information on the specified table and updates the system table called user_tables with this info
This helps to optimize any query which is run on the table after the table is analyzed. You can take help from the SQLPLUS prompt if it is avaliable. The help is also listed below in case you do not have HELP option
help analyze
ANALYZE command
PURPOSE:
To perform one of these functions on an index, table, or cluster:
* to collect statistics about the object used by the optimizer and
store them in the data dictionary* to validate the structure of the object
* to delete statistics about the object from the data dictionary
SYNTAX:
ANALYZE
{ INDEX [schema.]index
{ { COMPUTE STATISTICS
| ESTIMATE STATISTICS [SAMPLE integer {ROWS | PERCENT}]
| DELETE STATISTICS }
| VALIDATE STRUCTURE }
{ { COMPUTE
| ESTIMATE [SAMPLE integer {ROWS | PERCENT}]
| DELETE } STATISTICS
| VALIDATE STRUCTURE [CASCADE]
| LIST CHAINED ROWS [INTO [schema.]table] } }
where:
INDEX
identifies an index to be analyzed. If you omit schema, Oracle
assumes the index is in your own schema.
TABLE
identifies a table to be analyzed. If you omit schema, Oracle
assumes the table is in your own schema. When you collect
statistics for a table, Oracle also automatically collects the
statistics for each of the table's indexes.
CLUSTER
identifies a cluster to be analyzed. If you omit schema, Oracle
assumes the cluster is in your own schema. When you collect
statistics for a cluster, Oracle also automatically collects the
statistics for all the cluster's tables and all their indexes,
including the cluster index.
COMPUTE STATISTICS
computes exact statistics about the analyzed object and stores them
in the data dictionary.
ESTIMATE STATISTICS
estimates statistics about the analyzed object and stores them in
the data dictionary.
SAMPLE specifies the amount of data from the analyzed object Oracle samples to estimate statistics. If you omit this parameter, Oracle samples 1064 rows. If you specify more than half of the data, Oracle reads all the data and computes the statistics. ROWS causes Oracle to sample integer rows of the table or cluster or integer entries from the index. The integer must be at least 1. PERCENT causes Oracle to sample integer percent of the rows from the table or cluster or integer percent of the index entries. The integer can range from 1 to 99.
DELETE STATISTICS
deletes any statistics about the analyzed object that are currently
stored in the data dictionary.
VALIDATE STRUCTURE
validates the structure of the analyzed object. If you use this
option when analyzing a cluster, Oracle automatically validates the
structure of the cluster's tables.
CASCADE
validates the structure of the indexes associated with the table or
cluster. If you use this option when validating a table, Oracle
also validates the table's indexes. If you use this option when
validating a cluster, Oracle also validates all the clustered
tables' indexes, including the cluster index.
LIST CHAINED ROWS
identifies migrated and chained rows of the analyzed table or
cluster. You cannot use this option when analyzing an index.
INTO specifies a table into which Oracle lists the migrated and chained rows. If you omit schema, Oracle assumes the list table is in your own schema. If you omit this clause altogether, Oracle assumes that the table is named CHAINED_ROWS. The list table must be on your local database.
PREREQUISITES:
The object to be analyzed must be in your own schema or you must
have the ANALYZE ANY system privilege.
If you are using Trusted Oracle in DBMS MAC mode, your DBMS label
must match the creation label of the object to be analyzed or you
must satisfy one of these criteria:
* If the object's creation label is higher than your DBMS label, you
must have READUP and WRITEUP system privileges.
must have WRITEDOWN system privilege.
If you want to list chained rows of a table or cluster into a list
table, the list table must be in your own schema or you must have
INSERT privilege on the list table or you must have INSERT ANY TABLE
system privilege. If you are using Trusted Oracle in DBMS MAC mode,
the list table must also meet the criteria for the analyzed object
described above.
Regards,
Karthik M
-----Original Message-----
From: Paul Drake [mailto:paled_at_home.com]
Sent: Friday, March 23, 2001 2:51 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: about analyse table..
on NT -
C:\> notepad D:\Oracle\Ora81\RDBMS\ADMIN\dbmsutil.sql
in notepad - find "procedure analyze_schema"
sqlplus> exec dbms_utility.analyze_schema('pr0n_kewlecshun','COMPUTE');
hth.
> Saurabh Sharma wrote: > > hello, > > i'm not very familiar with the analyse table command. and not fully > aware of it's advantage or limits to analyse the tables. > could anyone pls help me with this cmd, how to see the analysed > statistics, etc. > any help is highly appreciated. > > thanks in advance. > > saurabh sharma
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Drake INET: paled_at_home.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 also send the HELP command for other information (like subscribing).Received on Fri Mar 23 2001 - 04:17:09 CST
 |
 |