Solved

Sample size in ANALYZE command

Posted on 2001-06-29
7
1,974 Views
Last Modified: 2012-05-04
Is there a recommended rule of thumb for choosing what
percentage of a table to analyse ?

I have some large tables (20 million rows) which I want
to analyse on a regular basis.

I don't want to analyse too small a number of rows, as
the stat's probably won't be accurate, and could make
things worse if the wrong execution path is chosen as
a result of inaccurate stat's.

But I don't want to analyse the whole table as this will
take too long.

What percentage will give me a good balance ? 10 ? 20 ?

Does Oracle recommend a suitable figure ?

Any accompanying explanations or URL's would be most
appreciated.

Thanks.
0
Comment
Question by:myerpz
7 Comments
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 6239166
Rather than a percentage, I've seen the number 1024 recommended as the number of rows to count when you want analyze to do a quicker job.  I used that figure in the past when I was working on a system with some multi-million row tables, with good results as far as I could tell.
0
 
LVL 3

Author Comment

by:myerpz
ID: 6239177
I think the default on the ANALYZE command is 1604.
I just wonder if there's any official word from Oracle
on it, or whether they leave it up to the analyst.
0
 
LVL 2

Expert Comment

by:jammalk
ID: 6239195
******************************************************
ANALYZE .... ESTIMATE STATISTICS SAMPLE integer ;

SAMPLE INTEGER:
Specify the amount of data from the analyzed object Oracle should sample to estimate statistics. If you omit this parameter, Oracle samples 1064 rows.
 
The default sample value is adequate for tables up to a few thousand rows. If your tables are larger, specify a higher value for SAMPLE. 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.
******************************************************
I think 20% of the rows is a good figure estimate the statistics.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 100 total points
ID: 6239269
The others are correct, the number is 1064, not 1024 as I said in my earlier comment.  I don't know what is magical about this number, but unless you know or suspect that the first 1,000 or so records in the table are not representative of the majority of records, it is likely much faster for analyze to do this number of rows than a fixed (even small) percentage of a large table.
0
 
LVL 1

Expert Comment

by:dbrower
ID: 6239297
I have used this script for a number of years an it seems to do the trick. It is base on the size of the table not the number of rows.

REM *******************************************************
REM
REM File: C:\Dba\analytab.SQL
REM
REM ******************************************************
REM  This script will produce and run the script
REM  ANALYTB2.SQL to analyze
REM  all tables and create the listing analytab.rpt
REM
REM ******************************************************

set echo off
set heading off
set pagesize 50000
set feedback off
spool c:\dba\analytb2.sql
SELECT 'ANALYZE TABLE ' || RTRIM( owner ) || '.' ||
          segment_name || ' COMPUTE STATISTICS ;'
     FROM sys.dba_segments
     WHERE segment_type = 'TABLE'
          AND owner NOT IN ('SYS','SYSTEM','VOSER')
          AND bytes < 5000000
UNION
SELECT 'ANALYZE TABLE ' || RTRIM( owner ) || '.' ||
          segment_name || ' ESTIMATE STATISTICS SAMPLE 30 PERCENT;'
     FROM sys.dba_segments
     WHERE segment_type = 'TABLE'
          AND owner NOT IN ('SYS','SYSTEM','VOSER')
          AND bytes between 5000001 AND 30000000
UNION
SELECT 'ANALYZE TABLE ' || RTRIM( owner ) || '.' ||
          segment_name || ' ESTIMATE STATISTICS SAMPLE 10 PERCENT;'
     FROM sys.dba_segments
     WHERE segment_type = 'TABLE'
          AND owner NOT IN ('SYS','SYSTEM','VOSER')
          AND bytes between 30000001 AND 50000000
UNION
SELECT 'ANALYZE TABLE ' || RTRIM( owner ) || '.' ||
          segment_name || ' ESTIMATE STATISTICS SAMPLE 5 PERCENT;'
     FROM sys.dba_segments
     WHERE segment_type = 'TABLE'
          AND owner IN ('SYS','SYSTEM')
          AND bytes > 50000000
ORDER BY 1
;

spool off

set echo on
set feedback on

@C:\dba\analytb2

0
 
LVL 1

Expert Comment

by:chdba
ID: 6242125
I'm not experienced with analyze schema.

But dbrower, are you sure, that you always used THIS script for a number of years?

What if a table is > 50000000 Bytes?

Ooops, is there perhaps a "NOT" missing ;-)?

Good luck.
0
 

Expert Comment

by:orbaj
ID: 6243956
Hi

Why not test different samples percentage for both time and statistics accuracy ?

 
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now