Saturday, April 20, 2013

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.

If you have an Oracle book on Amazon, which you would like to advertise here for free, please write to me at international_dba@yahoo.co.uk. 

4 comments:

Mark Kuntz said...

How can "measurable performance improvement" be demonstrated without rebuilding the index? And what are the possible risks with an index rebuild?

Andrew Reid said...

Dear Mark,

Excellent question and thank you for taking the time to comment on my blog.

The answer to the first part is that we are able to take copies of our production databases to test index rebuilds and see whether they improve performance. In most cases they don't seem to make much difference at all.

I agree that there is not a great deal of risk with index rebuilds. The main problem for me is that they just take so long to do if the tables concerned are large and I find it hard to justify them if I cannot demonstrate a tangible improvement as a result.

Kind Regards,
Andrew

Mark Kuntz said...

I concur that rebuilds should be the exception not the norm. In this case, I've just started with this company. The 'SYSTEM' DBAs are located in another city, 6 hours away. One of the tasks of my predecessor was to monitor the indexes. At a previous job, I had the luxury of rebuilding every user index every weekend. At another job, there was no scheduled index rebuilds at all. No task to even look to see if any required it. I am in the last day of my first 3 weeks with this company. At this point I have no idea how they refresh DEV and TEST from PRODUCTION. I'll pick that information up in little more than a week.
That being said, I have used two different methods of doing the refresh.
One used two different networks, where everything was identical. The refresh was just a physical restore of all the production files to the test servers. That should give the test environment where the indexes would be the same. That’s the job where I had the luxury to rebuild the world in 7 hours. Oracle 9 in a WINDOWS environment.
The other method used was to refresh only specific schemas. All the objects in the DEV instance would be dropped, then an IMP would be perform to import only that specific schema. All the indexes in that schema would be rebuilt. That was the REBUILD-LESS environment. Oracle 11 in a UNIX environment, still using DICTIONARY MANAGED tablespaces, something I am looking to address.
What method do you recommend for refreshing the DEV environment so that the desired testing can be performed? The choice is not limited to the two I’ve used. I’m always looking for better ways to perform tasks.

Andrew Reid said...

I have seen a disaster recovery set up where the production and disaster recovery environments were identical but on 2 different networks. However I have not seen this idea used for test environments. I can imagine it would work well if done with proper controls.
I don't like the thought of using exp and imp to set up test environments.
Here is one method I have seen for copying production databases into test databases:
(1) Close the production database and make a snapshot of it at the hardware level. Open the production database again. This can be done in 15 minutes or so.
(2) Mount the production snapshot to the server hosting the test database.
(3) Copy the database's datafiles from the snapshot into the file system holding the test database. This can take a few hours.
(4) Connect to the production database and do an ALTER DATABASE BACKUP CONTROLFILE TO TRACE to get SQL to recreate the controlfile.
(5) Using a global replace, change the directory names in the trace file to get SQL to recreate the test database's controlfile. Use this SQL to create a test database with the desired name.
This method can be scripted with a bit of work so that it can be run regularly without intervention.

However, I have to say that your idea of copying production directly into an identical test area on a separate network is superior!