How to Check Oracle Database Table and Index Fragmentation

The following posts demonstrated how to check fragmentations in PostgreSQL:
How to Identify Fragmentation in PostgreSQL RDS
Code to Report Table Index Schema or Database Fragmentation in PostgreSQL

This post will show how to check table and index fragmentations in Oracle database.

Subscribe to get access

Read more of this content when you subscribe today.

Find Oracle SQL Execution History Details

DBAs are always asked about the database performance issues, which are mostly caused by some SQL queries’ execution plan changes.

This post demonstrates how to get a SQL query performance details like executions, disk/physical read/write, cpu time, elapsed time and waiting time, etc.

With the help of those statistics of a specific SQL query, DBA can easily identify the issue, and remediate it by applying SQL plan baselines.

Subscribe to get access

Read more of this content when you subscribe today.

How to Find Bind Variable Values of Oracle SQL Query

A couple of ways to retrieve bind variables in real time or from history

Oracle database captures a SQL query bind variables and their  values periodically, and the default capture bind variables interval is 900 seconds(15 minutes), which is modifiable but facing performance concerns.

Parameter Description                 Session Value  Instance Value
------------------------------------- -------------- --------------- 
_cursor_bind_capture_interval              900           900
interval(in seconds) between two bind 
capture for a cursor
SQL>alter system set "_cursor_bind_capture_interval"=30 scope=both;

Oracle intends to minimize the impact on database performance, and makes the capturing interval a pretty big value( like 15 minutes). So it is not a very reliable method to retrieve bind variables from Oracle database views.

Please note you need have licenses of “Oracle Diagnostics & Tuning Packs” if you query dba_hist_****** and v$sql_monitor in this post.

There are a couple of ways to retrieve bind variables in real time or from history.

Subscribe to get access

Read more of this content when you subscribe today.

dbms_spm.drop_sql_plan_baseline Fails With ‘ORA-14552: Cannot Perform A DDL, Commit Or Rollback Inside A Query Or DML


Drop a sql execution plan baseline with following errors:

SQL> select dbms_spm.drop_sql_plan_baseline(sql_handle=>    -
                       'SQL_89d3699974845bdb') from dual;
select dbms_spm.drop_sql_plan_baseline(sql_handle=>        -
                       'SQL_89d3699974845bdb') from dual
ERROR at line 1:
ORA-14552: cannot perform a DDL,commit or rollback inside 
           a query or DML
ORA-06512: at "SYS.DBMS_SPM", line 1210
ORA-06512: at "SYS.DBMS_SPM", line 1200
ORA-06512: at line 1

dbms_spm.drop_sql_plan_baseline updates dictionary tables. So you should not use it in a query.


SQL> exec :xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>   -

PL/SQL procedure successfully completed.

SQL> select :xx from dual;


SQL> print :xx

Drop all sql execution plan baselines.
SQL> set serveroutput on
SQL> declare
     v_count pls_integer;
     v_sql_handle varchar2(50);
     cursor c1 is
     select sql_handle from dba_sql_plan_baselines
         where sql_handle like 'SQL_%';
        open c1 ;
            fetch c1 into v_sql_handle;
            exit when c1%notfound;
            v_count := dbms_spm.drop_sql_plan_baseline( 
                             sql_handle=>v_sql_handle );
      end loop;

PL/SQL procedure successfully completed.

SQL> select count(*) from dba_sql_plan_baselines;


SYS.SYS$SERVICE_METRICS_TAB Size Grows Big and Affects AQ Performance


1)Table SYS.SYS$SERVICE_METRICS_TAB keeps growing everyday.

SQL> select count(*) from SYS$SERVICE_METRICS_TAB;


  FROM sys$service_metrics_tab
 GROUP BY TRUNC(enq_time)

--------- ----------
07-JAN-19      41052
08-JAN-19      63338
09-JAN-19      63096
10-JAN-19      63338
11-JAN-19      62128
12-JAN-19      63338
13-JAN-19      63316
14-JAN-19      63316

2) Oracle background process ora_cjq — Job scheduler coordinator slows and hangs when running below SQL with “enq: TQ – DDL contention” contention.

begin dbms_aqadm_sys.remove_subscriber (
                            queue_name => 'SYS.SYS$SERVICE_METRICS', 
                 subscriber =>$_agent(:1, NULL, NULL)); end;

3) Oracle background process Qnnn: Queue cleanup processes hang with “library cache pin” contention.

4) oraagent.bin@host program also has “library cache pin” contention”.

5) Manageability Monitor Process MMON background slave process ora_m000 has “library cache lock” contention.

Subscribe to get access

Read more of this content when you subscribe today.