Oracle table fragmentation causing performance issue

Issue Description:

Application team reported, one of the jobs is running very slow in production. Due to this delay another job also accessing the same objects and it’s causing blocking locks on database.

1.    Almost one year, we deployed the new enhancement in prod database. Suddenly we faced this performance issue.

2.    Job A is running every  hour and it’s copying the data  from few tables and fetching into another DB via DB link and deleting the records in source database (whatever data copied from source database to target database)

3.     We locked the better statistics for these tables based on performance testing.

Impact:

1.    Job A deleting records on tables for every hour, Due to the large number of deletion on tables causing the fragmentation on tables. Obviously if tables are fragmented, corresponding indexes also fragmented.

2.    If tables statistics were locked, so I couldn’t find the exact details about these tables also I couldn’t gather the current stats for these tables.

How to check if tables are fragmented?

select t.owner,

t.table_name,

t.avg_row_len,

t.last_analyzed,

s.bytes/1024/1024 as SEGMENT_SIZE_MB

from

dba_tables t,

dba_segments s

where t.table_name=s.segment_name

and

t.owner=s.owner

and s.segment_type='TABLE'

and owner='&owner';

 

These below table’s stats were locked. So I manually count the records from below tables and put on Original columns.

OWNER     TABLE_NAME    AVG_ROW_LEN    SEGMENT SIZE  (MB)    Original Rows    Original Space Size (MB)
TEST           Table1             302                          8.000                         0                               0.000
TEST           Table2            120                          259.000                     4369                          0.650
TEST           Table3            104                          145.000                     442                            0.057
TEST           Table4           148                          0.125                         0                                0.000
TEST           Table5           147                          0.125                         0                                0.000
TEST           Table6           0                              0.125                         0                                0.000
TEST           Table7           0                              0.125                         0                                0.000
TEST           Table8           154                          20.000                       4509                          0.861
TEST           Table9           143                          130.000                      25058                       4.442
TEST           Table10          152                          59.000                        0                               0.000
TEST           Table11          158                            0.125                        0                               0.000
TEST           Table12           0                               0.125                        0                               0.000
                                                                   ~622MB                                                      ~6MB

These tables having only need ~ 6 MB, but these occupied ~622 MB.

How to calculate the actual space requirement?

Actual Space = (Num of rows in a table) * (Avg_row_len) + ((Num of rows in a table) * (Avg_row_len)* 0.3)

Explanation:                                     

(Num of rows in a table) * (Avg_row_len) --- gives a actual space required for a table

Oracle thumb rule says (actual space required for a table + 30 % space) will calculate the original space requirement for a table.

Note: whenever we creating the segment oracle initially allocated, 0.125 MB space allocated to each segment.

Temporary Solution:

We have a several method to fix the fragmentation.( reset the HWM)

1.    Export/import method

2.    Online redefinition method

3.    CTA’s method

4.    Move the table segment

I suggested the below method.

1)    Hold the jobs

2)    Take the listed tables backup using exp/expdp utility

3)    Truncate the tables

4)    Imported the tables using backup

5)    Release the Job

Permanent Solution:

1.    Tables should be change as daily partition tables.

2.    Instead of deleting the records from tables for every hour, every day that job will drop the daily partition. It will help to avoid the fragmentation.

OPTIMIZER_MODE-ALL_ROWS/FIRST_ROWS/FIRST_ROWS_N

Possible values for optimizer_mode = choose(or)all_rows/ first_rows/ first_rows[n]
By default, the value of optimizer_mode is CHOOSE which basically means ALL_ROWS.
FIRST_ROWS and ALL_ROWS are both cost based optimizer features. You may use them 
according to their requirement.
FIRST_ROWS/ FIRST_ROWS[n]

In simple terms it ensures best response time of first few rows (n rows).

This mode is good for interactive client-server environment where server serves first few rows and by the time user scroll down for more rows, it fetches other. So user feels that he has been served the data he requested, but in reality the request is still pending and query is still fetching the data in background.

Best example for this is toad, if you click on data tab, it instantaneously start showing you data and you feel toad is faster than sqlplus, but the fact is if you scroll down, you will see the query is still running.

Ok, let us simulate this on SQLPLUS

Create a table and index over it:

SQL> create table test as select * from all_objects;

Table created.
SQL> create index test_in on test(object_type);

Index created.

SQL> exec dbms_stats.gather_table_stats(‘SAC’,'TEST')

PL/SQL procedure successfully completed.

SQL> select count(*) from test;COUNT(*)----------37944

SQL> select count(*) from test where object_type='JAVA CLASS';

COUNT(*)----------14927


You see out of almost 38k records, 15k are of JAVA class.

And now if you select the rows having object_type=’JAVA_CLASS’, it should not use index as almost half of the rows are JAVA_CLASS.

It will be foolish of optimizer to read the index first and then go to table.


Check out the Explain plans

SQL> set autotrace traceonly exp

SQL> select * from test where object_type='JAVA CLASS';

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------

0 SELECT STATEMENT 1001 94094 10 (0) 00:00:01 * 1 TABLE ACCESS FULL TEST 1001 94094 10 (0) 00:00:01

--------------------------------------------------------------------------
As you see above, optimizer has not used Index we created on this table.

Now use FIRST_ROWS hint:

SQL> select /*+ FIRST_ROWS*/ * from test where object_type='JAVA CLASS';

Execution Plan
----------------------------------------------------------
Plan hash value: 3548301374
---------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------------------
0 SELECT STATEMENT 14662 1345K 536 (1) 00:00:07 1 TABLE ACCESS BY INDEX ROWID TEST 14662 1345K 536 (1) 00:00:07 * 2 INDEX RANGE SCAN TEST_IN 14662 43 (3) 00:00:01
---------------------------------------------------------------------------------------
In this case, optimizer has used the index.

Q> Why?

Ans> Because you wanted to see first few rows quickly. So, following your instructions oracle delivered you first few rows quickly using index and later delivering the rest.
See the difference in cost, although the response time (partial) of second query was faster but resource consumption was high.

But that does not mean that this optimizer mode is bad. As I said this mode may be good for interactive client-server model. In most of OLTP systems, where users want to see data fast on their screen, this mode of optimizer is very handy.

Important facts about FIRST_ROWS

It gives preference to Index scan Vs Full scan (even when index scan is not good).
It prefers nested loop over hash joins because nested loop returns data as selected (& compared), but hash join hashes one first input in hash table which takes time.

Cost of the query is not the only criteria for choosing the execution plan. It chooses plan which helps in fetching first rows fast.

It may be a good option to use this in an OLTP environment where user wants to see data as early as possible.

ALL_ROWS

In simple terms, it means better throughput
While FIRST_ROWS may be good in returning first few rows,

ALL_ROWS ensures the optimum resource consumption and throughput of the query.

In other words, ALL_ROWS is better to retrieve the last row first.

In above example while explaining FIRST_ROWS, you have already seen how efficient ALL_ROWS is.

Important facts about ALL_ROWS

ALL_ROWS considers both index scan and full scan and based on their contribution to the overall query, it uses them. If Selectivity of a column is low, optimizer may use index to fetch the data (for example ‘where employee_code=7712’), but if selectivity of column is quite high ('where deptno=10'), optimizer may consider doing Full table scan. With ALL_ROWS, optimizer has more freedom to its job at its best.

Good for OLAP system, where work happens in batches/procedures. (While some of the report may still use FIRST_ROWS depending upon the anxiety level of report reviewers)
Likes hash joins over nested loop for larger data sets. ConclusionCost based optimizer gives you flexibility to choose response time or throughput. So use them based on your business requirement.

statistics_level parameter options

Unsetting the default statistics_level=basic will disable AWR and the advisory utilities.
statistics_level=typical activates the advisory statistics collections, but it does not activate “Timed OS statistics” and “Plan Execution Statistics”.  To activate these statistics collection, you must set statistics_level=all.

SQL> ALTER SYSTEM SET statistics_level=basic;

 

System altered.

 

SQL> SELECT statistics_name,

  2         session_status,

  3         system_status,

  4         activation_level,

  5         session_settable

  6  FROM   v$statistics_level

  7  ORDER BY statistics_name;

 

                               Session    System     Activation Session

Statistics Name                Status     Status     Level      Settable

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

Buffer Cache Advice            DISABLED   DISABLED   TYPICAL    NO

MTTR Advice                    DISABLED   DISABLED   TYPICAL    NO

PGA Advice                     DISABLED   DISABLED   TYPICAL    NO

Plan Execution Statistics      DISABLED   DISABLED   ALL        YES

Segment Level Statistics       DISABLED   DISABLED   TYPICAL    NO

Shared Pool Advice             DISABLED   DISABLED   TYPICAL    NO

Timed OS Statistics            DISABLED   DISABLED   ALL        YES

Timed Statistics               DISABLED   DISABLED   TYPICAL    YES

 

8 rows selected.

 

SQL> ALTER SYSTEM SET statistics_level=typical;

 

System altered.

 

SQL> SELECT statistics_name,

  2         session_status,

  3         system_status,

  4         activation_level,

  5         session_settable

  6  FROM   v$statistics_level

  7  ORDER BY statistics_name;

 

                               Session    System     Activation Session

Statistics Name                Status     Status     Level      Settable

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

Buffer Cache Advice            ENABLED    ENABLED    TYPICAL    NO

MTTR Advice                    ENABLED    ENABLED    TYPICAL    NO

PGA Advice                     ENABLED    ENABLED    TYPICAL    NO

Plan Execution Statistics      DISABLED   DISABLED   ALL        YES

Segment Level Statistics       ENABLED    ENABLED    TYPICAL    NO

Shared Pool Advice             ENABLED    ENABLED    TYPICAL    NO

Timed OS Statistics            DISABLED   DISABLED   ALL        YES

Timed Statistics               ENABLED    ENABLED    TYPICAL    YES

 

8 rows selected.

 

SQL> ALTER SYSTEM SET statistics_level=all;

 

System altered.

 

SQL> SELECT statistics_name,

  2         session_status,

  3         system_status,

  4         activation_level,

  5         session_settable

  6  FROM   v$statistics_level

  7  ORDER BY statistics_name;

 

                               Session    System     Activation Session

Statistics Name                Status     Status     Level      Settable

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

Buffer Cache Advice            ENABLED    ENABLED    TYPICAL    NO

MTTR Advice                    ENABLED    ENABLED    TYPICAL    NO

PGA Advice                     ENABLED    ENABLED    TYPICAL    NO

Plan Execution Statistics      ENABLED    ENABLED    ALL        YES

Segment Level Statistics       ENABLED    ENABLED    TYPICAL    NO

Shared Pool Advice             ENABLED    ENABLED    TYPICAL    NO

Timed OS Statistics            ENABLED    ENABLED    ALL        YES

Timed Statistics               ENABLED    ENABLED    TYPICAL    YES

Setting The Oracle Database Listener Password

This short paper shows you how you can set a password for the Oracle listener. These passwords can be set either encrypted or unencrypted. Obviously I would not recommend setting clear text passwords. Whilst in some cases these can be made reasonably secure the problem is that a password is stored in clear text in a file. For completeness I will show both methods:

Setting an Oracle listener password in clear text

The listener password can be set in clear text as follows: (It should be noted that it is possible to set more than one listener password in this way.)

Open the $ORACLE_HOME/network/admin/listener.ora file and locate the name of your listener. For instance my listener has the default name LISTENER. I can see this in the following lines:

       

        LISTENER =

        (DESCRIPTION_LIST =

                                             

It is the line starting LISTENER= . Simply add a line to this file with the following format:

        PASSWORDS_{LISTENER_NAME}=somesecretpassword

                                             

i.e in my case if the listener is called LISTENER then:

        PASSWORDS_LISTENER=somesecretpassword                                              

                                             

You can also set multiple passwords as follows:

        PASSWORDS_LISTENER=(somesecretpassword,anothersecret)

Anyway above method is not the best way to secure your listener.

Setting an encrypted Oracle listener password

 It should be done with an encrypted password as follows:

        C:\oracle\ora90\network\admin>lsnrctl

       

        LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 24-FEB-2004 11:27:

        55

       

        Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

       

        Welcome to LSNRCTL, type "help" for information.

       

        LSNRCTL> set current_listener listener

        Current Listener is listener

 LSNRCTL> set password

        Password:

        The command completed successfully

        LSNRCTL> save_config

        Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))

        Saved LISTENER configuration parameters.

        Listener Parameter File   C:\oracle\ora90\network\admin\listener.ora

        Old Parameter File   C:\oracle\ora90\network\admin\listener.bak

        The command completed successfully

        LSNRCTL>                               

You must save the configuration after setting the password otherwise it will be lost. Also you can check what was generated by looking in the listener.ora file. This is what was generated from the above commands:

        #----ADDED BY TNSLSNR 24-FEB-2004 11:29:18---

        PASSWORDS_LISTENER = F0354118688257FB

 

Should you want to remove the listener password do the following:

        C:\oracle\ora90\network\admin>lsnrctl

       

        LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 24-FEB-2004 11:32:

        05

       

        Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

       

        Welcome to LSNRCTL, type "help" for information.

 

LSNRCTL> stop

        Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))

        The command completed successfully

 

Edit the listener.ora file to remove the lines added above and restart the listener as follows:

        C:\oracle\ora90\network\admin>lsnrctl

       

        LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 24-FEB-2004 11:33:

        34

       

        Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

       

        Welcome to LSNRCTL, type "help" for information.

       

        LSNRCTL> start

        Starting tnslsnr: please wait...

       

        TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production

        System parameter file is C:\oracle\ora90\network\admin\listener.ora

        Log messages written to C:\oracle\ora90\network\log\listener.log

        Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc

        )))

        Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zulia)(PORT=1521)))

       

        Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))

        STATUS of the LISTENER

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

        Alias                     LISTENER

        Version                   TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Produc

        tion

        Start Date                24-FEB-2004 11:33:38

        Uptime                    0 days 0 hr. 0 min. 2 sec

        Trace Level               off

        Security                  OFF

        SNMP                      OFF

        Listener Parameter File   C:\oracle\ora90\network\admin\listener.ora

        Listener Log File         C:\oracle\ora90\network\log\listener.log

        Listening Endpoints Summary...

          (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))

          (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zulia)(PORT=1521)))

        Services Summary...

        Service "PLSExtProc" has 1 instance(s).

          Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

        Service "sans" has 1 instance(s).

          Instance "sans", status UNKNOWN, has 1 handler(s) for this service...

        The command completed successfully

 

That's it!, you should always protect your listener with a password. This is a basic security requirement. If the listener is not password protected then it can be shutdown remotely or have its configuration change or could be used to hack your server