In order to create the SCOTT demonstration tables, you will simply:

demobld.sql will create and populate five tables. When it is complete, it exits SQL*Plus automatically, so don't be surprised when SQL*Plus disappears after running the script—it's supposed to do that.

The standard demo tables do not have any referential integrity defined on them. Some of my examples rely on them having referential integrity. After you run demobld.sql, it is recommended you also execute the following:

alter table emp add constraint emp_pk primary key(empno);
alter table dept add constraint dept_pk primary key(deptno);
alter table emp add constraint emp_fk_dept
                                 foreign key(deptno) references dept;
alter table emp add constraint emp_fk_emp foreign key(mgr) references emp;

This finishes off the installation of the demonstration schema. If you would like to drop this schema at any time to clean up, you can simply execute [ORACLE_HOME]/sqlplus/demo/demodrop.sql. This will drop the five tables and exit SQL*Plus.

In the event you do not have access to demobld.sql, the following is sufficient to run the examples in this book:

CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
 ENAME VARCHAR2(10),
 JOB VARCHAR2(9),
 MGR NUMBER(4),
 HIREDATE DATE,
 SAL NUMBER(7, 2),
 COMM NUMBER(7, 2),
 DEPTNO NUMBER(2)
);

INSERT INTO EMP VALUES (7369, 'SMITH',  'CLERK',     7902,
TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN',  'SALESMAN',  7698,
TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMP VALUES (7521, 'WARD',   'SALESMAN',  7698,
TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMP VALUES (7566, 'JONES',  'MANAGER',   7839,
TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN',  7698,
TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE',  'MANAGER',   7839,
TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK',  'MANAGER',   7839,
TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT',  'ANALYST',   7566,
TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING',   'PRESIDENT', NULL,
TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN',  7698,

TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500,    0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS',  'CLERK',     7788,
TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES',  'CLERK',     7698,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD',   'ANALYST',   7566,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK',     7782,
TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE DEPT
(DEPTNO NUMBER(2),
 DNAME VARCHAR2(14),
 LOC VARCHAR2(13)
);

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK'),
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS'),
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO'),
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON'),

If you create the schema by executing the commands above, do remember to go back to the previous subsection and execute the commands to create the constraints.

This is what I like to do to get AUTOTRACE working:

You can replace the GRANT TO PUBLIC with some user if you want. By making it public, you let anyone trace using SQL*Plus (not a bad thing, in my opinion). This prevents every user from having to install their own plan table. The alternative is for you to run @utlxplan in every schema from which you want to use AUTOTRACE.

The next step is creating and granting the PLUSTRACE role:

Again, you can replace PUBLIC in the GRANT command with some user if you want.

You can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after successful SQL DML (that is, SELECT, DELETE, UPDATE, MERGE, and INSERT) statements. It is useful for monitoring and tuning the performance of these statements.

You can control the report by setting the AUTOTRACE system variable.

Runstats is a tool I developed to compare two different methods of doing the same thing and show which one is superior. You supply the two different methods and Runstats does the rest. Runstats simply measures three key things:

As we'll see in this book, latches are a type of lightweight lock. Locks are serialization devices. Serialization devices inhibit concurrency. Applications that inhibit concurrency are less scalable, can support fewer users, and require more resources. Our goal is always to build applications that have the potential to scale—ones that can service one user as well as 1,000 or 10,000. The less latching we incur in our approaches, the better off we will be. I might choose an approach that takes longer to run on the wall clock but that uses 10 percent of the latches. I know that the approach that uses fewer latches will scale substantially better than the approach that uses more latches.

Runstats is best used in isolation; that is, on a single-user database. We will be measuring statistics and latching (locking) activity that result from our approaches. We do not want other sessions to contribute to the system's load or latching while this is going on. A small test database is perfect for these sorts of tests. I frequently use my desktop PC or laptop, for example.

In order to use Runstats, you need to set up access to several V$ views, create a table to hold the statistics, and create the Runstats package. You will need access to four V$ tables (those magic, dynamic performance tables): V$STATNAME, V$MYSTAT, V$TIMER and V$LATCH. Here is a view I use:

create or replace view stats
as select 'STAT...' || a.name name, b.value
      from v$statname a, v$mystat b
     where a.statistic# = b.statistic#
    union all
    select 'LATCH.' || name,  gets
      from v$latch
    union all
    select 'STAT...Elapsed Time', hsecs from v$timer;

You can either have SELECT on V$STATNAME, V$MYSTAT, V$TIMER, and V$LATCH granted directly to you (so you can create the view yourself) or you can have someone that does have SELECT on those objects create the view for you and grant SELECT privileges on the view to you.

Once you have that set up, all you need is a small table to collect the statistics:

create global temporary table run_stats
( runid varchar2(15),
  name varchar2(80),
  value int )
on commit preserve rows;

Last, you need to create the package that is Runstats. It contains three simple API calls:

The specification is as follows:

ops$tkyte%ORA11GR2> create or replace package runstats_pkg
  2  as
  3      procedure rs_start;
  4      procedure rs_middle;
  5      procedure rs_stop( p_difference_threshold in number default 0 );
  6  end;
  7  /
Package created.

The parameter, p_difference_threshold, is used to control the amount of data printed at the end. Runstats collects statistics and latching information for each run, and then prints a report of how much of a resource each test (each approach) used and the difference between them. You can use this input parameter to see only the statistics and latches that had a difference greater than this number. By default, this is zero, and you see all of the outputs.

Next, we'll look at the package body procedure by procedure. The package begins with some global variables. These will be used to record the elapsed times for our runs:

ops$tkyte%ORA11GR2> create or replace package body runstats_pkg
  2  as
  3
  4  g_start number;
  5  g_run1  number;
  6  g_run2  number;
  7

Next is the RS_START routine. This will simply clear out our statistics holding table and then populate it with the "before" statistics and latches. It will then capture the current timer value, a clock of sorts that we can use to compute elapsed times in hundredths of seconds:

8  procedure rs_start
  9  is
 10  begin
 11      delete from run_stats;
 12
 13      insert into run_stats
 14      select 'before', stats.* from stats;
 15

16      g_start := dbms_utility.get_cpu_time;
 17  end;
 18

Next is the RS_MIDDLE routine. This procedure simply records the elapsed time for the first run of our test in G_RUN1. Then it inserts the current set of statistics and latches. If we were to subtract these values from the ones we saved previously in RS_START, we could discover how many latches the first method used, how many cursors (a statistic) it used, and so on.

Last, it records the start time for our next run:

19  procedure rs_middle
 20  is
 21  begin
 22      g_run1 := (dbms_utility.get_cpu_time-g_start);
 23
 24      insert into run_stats
 25      select 'after 1', stats.* from stats;
 26      g_start := dbms_utility.get_cpu_time;
 27
 28  end;
 29

The next and final routine in this package is the RS_STOP routine. Its job is to print out the aggregate CPU times for each run and then print out the difference between the statistic/latching values for each of the two runs (only printing out those that exceed the threshold):

30  procedure rs_stop(p_difference_threshold in number default 0)
 31  is
 32  begin
 33      g_run2 := (dbms_utility.get_cpu_time-g_start);
 34
 35      dbms_output.put_line
 36      ( 'Run1 ran in ' || g_run1 || ' cpu hsecs' );
 37      dbms_output.put_line
 38      ( 'Run2 ran in ' || g_run2 || ' cpu hsecs' );
 39          if ( g_run2 <> 0 )
 40          then
 41      dbms_output.put_line
 42      ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
 43        '% of the time' );
 44          end if;
 45      dbms_output.put_line( chr(9) );
 46
 47      insert into run_stats
 48      select 'after 2', stats.* from stats;
 49
 50      dbms_output.put_line
 51      ( rpad( 'Name', 30 ) || lpad( 'Run1', 12 ) ||
 52        lpad( 'Run2', 12 ) || lpad( 'Diff', 12 ) );
 53
 54      for x in
 55      ( select rpad( a.name, 30 ) ||
 56               to_char( b.value-a.value, '999,999,999' ) ||
 57               to_char( c.value-b.value, '999,999,999' ) ||

58               to_char( ( (c.value-b.value)-(b.value-a.value)),
                                    '999,999,999' ) data
 59          from run_stats a, run_stats b, run_stats c
 60         where a.name = b.name
 61           and b.name = c.name
 62           and a.runid = 'before'
 63           and b.runid = 'after 1'
 64           and c.runid = 'after 2'
 65
 66           and abs( (c.value-b.value) - (b.value-a.value) )
 67                 > p_difference_threshold
 68         order by abs( (c.value-b.value)-(b.value-a.value))
 69      ) loop
 70          dbms_output.put_line( x.data );
 71      end loop;
 72
 73      dbms_output.put_line( chr(9) );
 74      dbms_output.put_line
 75      ( 'Run1 latches total versus runs -- difference and pct' );
 76      dbms_output.put_line
 77      ( lpad( 'Run1', 12 ) || lpad( 'Run2', 12 ) ||
 78        lpad( 'Diff', 12 ) || lpad( 'Pct', 10 ) );
 79
 80      for x in
 81      ( select to_char( run1, '999,999,999' ) ||
 82               to_char( run2, '999,999,999' ) ||
 83               to_char( diff, '999,999,999' ) ||
 84               to_char( round( run1/decode( run2, 0,
                             to_number(0), run2) *100,2 ), '99,999.99' ) || '%' data
 85          from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
 86                        sum( (c.value-b.value)-(b.value-a.value)) diff
 87                   from run_stats a, run_stats b, run_stats c
 88                  where a.name = b.name
 89                    and b.name = c.name
 90                    and a.runid = 'before'
 91                    and b.runid = 'after 1'
 92                    and c.runid = 'after 2'
 93                    and a.name like 'LATCH%'
 94                  )
 95      ) loop
 96          dbms_output.put_line( x.data );
 97      end loop;
 98  end;
 99
100  end;
101  /
Package body created.

Now you are ready to use Runstats. By way of example, we'll demonstrate how to use Runstats to see which is more efficient, a single bulk INSERT versus row-by-row processing. We'll start by setting up two tables into which we'll insert 1,000,000 rows (the BIG_TABLE creation script is provided later in this section):

ops$tkyte%ORA11GR2> create table t1
  2  as
  3  select * from big_table.big_table
  4  where 1=0;
Table created.

ops$tkyte%ORA11GR2> create table t2
  2  as
  3  select * from big_table.big_table
  4  where 1=0;
Table created.

And now we are ready to perform the first method of inserting the records, using a single SQL statement. We start by calling RUNSTATS_PKG.RS_START:

ops$tkyte%ORA11GR2> exec runstats_pkg.rs_start;
PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> insert into t1
  2  select *
  3    from big_table.big_table
  4   where rownum <= 1000000;
1000000 rows created.

ops$tkyte%ORA11GR2> commit;
Commit complete.

Now we are ready to perform the second method, row by row insertion of data:

ops$tkyte%ORA11GR2> exec runstats_pkg.rs_middle;
PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> begin
  2          for x in ( select *
  3                       from big_table.big_table
  4                      where rownum <= 1000000 )
  5          loop
  6                  insert into t2 values X;
  7          end loop;
  8          commit;
  9  end;
 10  /
PL/SQL procedure successfully completed.

And finally, we'll generate the report:

ops$tkyte%ORA11GR2> exec runstats_pkg.rs_stop(1000000)
Run1 ran in 411 cpu hsecs
Run2 ran in 6192 cpu hsecs
run 1 ran in 6.64% of the time

Name                                  Run1        Run2        Diff
STAT...opened cursors cumulati         213   1,000,365   1,000,152
STAT...execute count                   213   1,000,372   1,000,159

LATCH.shared pool                    2,820   1,006,421   1,003,601
STAT...recursive calls               3,256   1,014,103   1,010,847
STAT...physical read total byt 122,503,168 124,395,520   1,892,352
STAT...cell physical IO interc 122,503,168 124,395,520   1,892,352
STAT...physical read bytes     122,503,168 124,395,520   1,892,352
STAT...db block changes            110,810   2,087,125   1,976,315
STAT...file io wait time         5,094,828     438,102  −4,656,726
LATCH.cache buffers chains         571,469   5,510,416   4,938,947
STAT...undo change vector size   3,885,808  67,958,316  64,072,508
STAT...redo size               120,944,520 379,497,588 258,553,068

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
804,522   6,840,599   6,036,077     11.76%

PL/SQL procedure successfully completed.

This confirms you have the RUNSTATS_PKG package installed and shows you why you should use a single SQL statement instead of a bunch of procedural code when developing applications whenever possible!

Mystat.sql and its companion, mystat2.sql, are used to show the increase in some Oracle "statistic" before and after some operation. Mystat.sql captures the begin value of some statistic

set echo off
set verify off
column value new_val V
define S="&1"

set autotrace off
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on

and mystat2.sql reports the difference (&V is populated by running the first script, mystat.sql—it uses the SQL*Plus NEW_VAL feature for that. It contains the last VALUE selected from the query above):

set echo off
set verify off
select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on

For example, to see how much redo is generated by some UPDATE statement we can:

big_table@ORA11GR2> @mystat "redo size"
big_table@ORA11GR2> set echo off

NAME                                VALUE
------------------------------ ----------
redo size                             496

big_table@ORA11GR2> update big_table set owner = lower(owner)
  2  where rownum <= 1000;

1000 rows updated.

big_table@ORA11GR2> @mystat2
big_table@ORA11GR2> set echo off

NAME                                    V DIFF
------------------------------ ---------- ----------------
redo size                           89592           89,096

This shows our UPDATE of 1,000 rows generated 89,096 bytes of redo.

The SHOW_SPACE routine prints detailed space utilization information for database segments. Here is the interface to it:

ops$tkyte@ORA11GR2> desc show_space
PROCEDURE show_space
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_SEGNAME                      VARCHAR2                IN
 P_OWNER                        VARCHAR2                IN     DEFAULT
 P_TYPE                         VARCHAR2                IN     DEFAULT
 P_PARTITION                    VARCHAR2                IN     DEFAULT

The arguments are as follows:

The output of this routine looks as follows, when the segment resides in an Automatic Segment Space Management (ASSM) tablespace:

big_table@ORA11GR2> exec show_space('BIG_TABLE'),
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................          14,469
Total Blocks............................          15,360
Total Bytes.............................     125,829,120
Total MBytes............................             120
Unused Blocks...........................             728
Unused Bytes............................       5,963,776
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          43,145
Last Used Block.........................             296

PL/SQL procedure successfully completed.

The items reported are as follows:

When you use SHOW_SPACE to look at objects in user space managed tablespaces, the output resembles this:

big_table@ORA11GR2> exec show_space( 'BIG_TABLE' )
Free Blocks.............................               1
Total Blocks............................         147,456
Total Bytes.............................   1,207,959,552
Total MBytes............................           1,152
Unused Blocks...........................           1,616
Unused Bytes............................      13,238,272
Last Used Ext FileId....................               7

Last Used Ext BlockId...................         139,273
Last Used Block.........................           6,576

PL/SQL procedure successfully completed.

The only difference is the Free Blocks item at the beginning of the report. This is a count of the blocks in the first freelist group of the segment. My script reports only on this freelist group. You would need to modify the script to accommodate multiple freelist groups.

The commented code follows. This utility is a simple layer on top of the DBMS_SPACE API in the database.

create or replace procedure show_space
( p_segname in varchar2,
  p_owner   in varchar2 default user,
  p_type    in varchar2 default 'TABLE',
  p_partition in varchar2 default NULL )
-- this procedure uses authid current user so it can query DBA_*
-- views using privileges from a ROLE and so it can be installed
-- once per database, instead of once per user that wants to use it
authid current_user
as
    l_free_blks                 number;
    l_total_blocks              number;
    l_total_bytes               number;
    l_unused_blocks             number;
    l_unused_bytes              number;
    l_LastUsedExtFileId         number;
    l_LastUsedExtBlockId        number;
    l_LAST_USED_BLOCK           number;
    l_segment_space_mgmt        varchar2(255);
    l_unformatted_blocks number;
    l_unformatted_bytes number;
    l_fs1_blocks number; l_fs1_bytes number;
    l_fs2_blocks number; l_fs2_bytes number;
    l_fs3_blocks number; l_fs3_bytes number;
    l_fs4_blocks number; l_fs4_bytes number;
    l_full_blocks number; l_full_bytes number;

    -- inline procedure to print out numbers nicely formatted
    -- with a simple label
    procedure p( p_label in varchar2, p_num in number )
    is
    begin
        dbms_output.put_line( rpad(p_label,40,'.') ||
                              to_char(p_num,'999,999,999,999') );
    end;
begin
   -- this query is executed dynamically in order to allow this procedure
   -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
   -- via a role as is customary.
   -- NOTE: at runtime, the invoker MUST have access to these two
   -- views!
   -- this query determines if the object is an ASSM object or not
   begin

execute immediate
          'select ts.segment_space_management
             from dba_segments seg, dba_tablespaces ts
            where seg.segment_name      = :p_segname
              and (:p_partition is null or
                  seg.partition_name = :p_partition)
              and seg.owner = :p_owner
              and seg.tablespace_name = ts.tablespace_name'
             into l_segment_space_mgmt
            using p_segname, p_partition, p_partition, p_owner;
   exception
       when too_many_rows then
          dbms_output.put_line
          ( 'This must be a partitioned table, use p_partition => '),
          return;
   end;


   -- if the object is in an ASSM tablespace, we must use this API
   -- call to get space information, else we use the FREE_BLOCKS
   -- API for the user managed segments
   if l_segment_space_mgmt = 'AUTO'
   then
     dbms_space.space_usage
     ( p_owner, p_segname, p_type, l_unformatted_blocks,
       l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
       l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
       l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);

     p( 'Unformatted Blocks ', l_unformatted_blocks );
     p( 'FS1 Blocks (0-25)  ', l_fs1_blocks );
     p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
     p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
     p( 'FS4 Blocks (75-100)', l_fs4_blocks );
     p( 'Full Blocks        ', l_full_blocks );
  else
     dbms_space.free_blocks(
       segment_owner     => p_owner,
       segment_name      => p_segname,
       segment_type      => p_type,
       freelist_group_id => 0,
       free_blks         => l_free_blks);

     p( 'Free Blocks', l_free_blks );
  end if;

  -- and then the unused space API call to get the rest of the
  -- information
  dbms_space.unused_space
  ( segment_owner     => p_owner,
    segment_name      => p_segname,
    segment_type      => p_type,
    partition_name    => p_partition,
    total_blocks      => l_total_blocks,

total_bytes       => l_total_bytes,
    unused_blocks     => l_unused_blocks,
    unused_bytes      => l_unused_bytes,
    LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
    LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
    LAST_USED_BLOCK => l_LAST_USED_BLOCK );

    p( 'Total Blocks', l_total_blocks );
    p( 'Total Bytes', l_total_bytes );
    p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
    p( 'Unused Blocks', l_unused_blocks );
    p( 'Unused Bytes', l_unused_bytes );
    p( 'Last Used Ext FileId', l_LastUsedExtFileId );
    p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
    p( 'Last Used Block', l_LAST_USED_BLOCK );
end;
/

For examples throughout this book, I use a table called BIG_TABLE. Depending on which system I use, this table has between one record and four million records and varies in size from 200MB to 800MB. In all cases, the table structure is the same.

To create BIG_TABLE, I wrote a script that does the following:

To build the BIG_TABLE table, you can run the following script at the SQL*Plus prompt and pass in the number of rows you want in the table. The script will stop when it hits that number of rows.

create table big_table
as
select rownum id, a.*
  from all_objects a
 where 1=0
/
alter table big_table nologging;

declare
    l_cnt number;
    l_rows number := &1;
begin
    insert /*+ append */

into big_table
    select rownum, a.*
      from all_objects a
     where rownum <= &1;

    l_cnt := sql%rowcount;

    commit;

    while (l_cnt < l_rows)
    loop
        insert /*+ APPEND */ into big_table
        select rownum+l_cnt,
               OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
               OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
               TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME
          from big_table
         where rownum <= l_rows-l_cnt;
        l_cnt := l_cnt + sql%rowcount;
        commit;
    end loop;
end;
/
alter table big_table add constraint
big_table_pk primary key(id);

exec dbms_stats.gather_table_stats( user, 'BIG_TABLE', estimate_percent=> 1);

I estimated baseline statistics on the table. The index associated with the primary key will have statistics computed automatically when it is created.