LF_ROWS and DEL_LF_ROWS

This example was tested on Oracle 9.2.0.7. It shows how you can sometimes improve performance by rebuilding an index. I found a table:

 

SQL> desc braid.b_old_sp_profile_image

Name                       Null?    Type

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

SESSIONID                  NOT NULL NUMBER

SPLYPTID                   NOT NULL VARCHAR2(10)

START_DATE                 NOT NULL DATE

END_DATE                   NOT NULL DATE

BASELOAD_VOLUME            NOT NULL NUMBER(16,4)

PROFILE_VOLUME                      NUMBER(16,4)

 

SQL>

 

I counted its rows:

 

SQL> select count(*)

  2  from braid.b_old_sp_profile_image

  3  /

 

  COUNT(*)

----------

    610666

 

SQL>

 

I looked at the indexes on the table:

 

SQL> l

  1  select owner, index_name

  2  from dba_indexes

  3  where table_owner = 'BRAID'

  4* and table_name = 'B_OLD_SP_PROFILE_IMAGE'

SQL> /

 

OWNER                          INDEX_NAME

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

BRAID                          OSPP_PK

 

SQL>

 

I checked which column(s) it indexed:

 

SQL> l

  1  select column_position, column_name

  2  from dba_ind_columns

  3  where table_owner = 'BRAID'

  4  and table_name = 'B_OLD_SP_PROFILE_IMAGE'

  5  and index_owner = 'BRAID'

  6  and index_name = 'OSPP_PK'

  7* order by 1

SQL> /

 

COLUMN_POSITION COLUMN_NAME

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

              1 SESSIONID

              2 SPLYPTID

              3 START_DATE

 

SQL>

 

I ran a query and hoped it would use the index (it did):

 

SQL> alter session set sql_trace = true;

 

Session altered.

 

SQL> set timing on

SQL> select count(*) query1

  2  from braid.b_old_sp_profile_image

  3  where sessionid between 3000000 and 15000000

  4  /

 

    QUERY1

----------

    347244

 

Elapsed: 00:03:03.25

SQL> set timing off

SQL> alter session set sql_trace = false;

 

Session altered.

 

SQL>

 

It seemed slow so I checked the table had been analyzed recently:

 

SQL> l

  1  select last_analyzed

  2  from dba_tables

  3  where owner = 'BRAID'

  4* and table_name = 'B_OLD_SP_PROFILE_IMAGE'

SQL> /

 

LAST_ANALYZED

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

15-APR-13

 

SQL>

 

Then I ran the trace file through tkprof and looked at the output:

 

********************************************************************************

 

select count(*) query1

from braid.b_old_sp_profile_image

where sessionid between 3000000 and 15000000

 

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.02       0.02          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2     29.50     191.94     305413     305526          0           1

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

total        4     29.52     191.97     305413     305526          0           1

 

Misses in library cache during parse: 1

Optimizer goal: ALL_ROWS

Parsing user id: 236  (ORACLE)

 

Rows     Row Source Operation

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

      1  SORT AGGREGATE

347244   INDEX FAST FULL SCAN OSPP_PK (object id 32074)

 

 

Rows     Execution Plan

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

      0  SELECT STATEMENT   GOAL: ALL_ROWS

      1   SORT (AGGREGATE)

347244    INDEX   GOAL: ANALYZED (FAST FULL SCAN) OF 'OSPP_PK' (UNIQUE)

 

********************************************************************************

 

I analyzed the index and saw that it had a high percentage of deleted leaf rows (as shown in the del_lf_rows column):

 

SQL> analyze index braid.ospp_pk validate structure;

 

Index analyzed.

 

SQL> l

  1  select name, lf_rows, del_lf_rows, height

  2* from index_stats

SQL> /

 

NAME                    LF_ROWS DEL_LF_ROWS     HEIGHT

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

OSPP_PK                24121377    23510711          4

 

SQL>

 

… so I rebuilt it, checked that the deleted leaf rows had disappeared then analyzed the table again:

 

SQL> alter index braid.ospp_pk rebuild;

 

Index altered.

 

SQL> analyze index braid.ospp_pk validate structure;

 

Index analyzed.

 

SQL> select name, lf_rows, del_lf_rows, height

  2  from index_stats

  3  /

 

NAME                    LF_ROWS DEL_LF_ROWS     HEIGHT

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

OSPP_PK                  610666           0          3

 

SQL> exec dbms_stats.gather_table_stats( -

> ownname => 'braid', -

> tabname => 'b_old_sp_profile_image');

 

PL/SQL procedure successfully completed.

 

SQL>

 

I reran the query and it finished very quickly:

 

SQL> alter session set sql_trace = true;

 

Session altered.

 

SQL> set timing on

SQL> select count(*) query2

  2  from braid.b_old_sp_profile_image

  3  where sessionid between 3000000 and 15000000

  4  /

 

    QUERY2

----------

    347244

 

Elapsed: 00:00:00.71

SQL> set timing off

SQL> alter session set sql_trace = false;

 

Session altered.

 

SQL>

 

I ran the trace file through tkprof and looked at the output, which was also very different:

 

********************************************************************************

 

select count(*) query2

from braid.b_old_sp_profile_image

where sessionid between 3000000 and 15000000

 

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.02       0.03          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.57       0.60          0       3010          0           1

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

total        4      0.60       0.64          0       3010          0           1

 

Misses in library cache during parse: 1

Optimizer goal: ALL_ROWS

Parsing user id: 236  (ORACLE)

 

Rows     Row Source Operation

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

      1  SORT AGGREGATE

347244   INDEX FAST FULL SCAN OSPP_PK (object id 32074)

 

 

Rows     Execution Plan

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

      0  SELECT STATEMENT   GOAL: ALL_ROWS

      1   SORT (AGGREGATE)

347244    INDEX   GOAL: ANALYZED (FAST FULL SCAN) OF 'OSPP_PK' (UNIQUE)

 

********************************************************************************

 

Some people say that you should rebuild indexes when the percentage of deleted leaf rows is 20% or more. Personally I believe that there is a risk, albeit small, in any maintenance so you should only do it, as in this case, where you can demonstrate that you will achieve some measurable performance improvement.