Category: Performance Tunning

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

SYMPTOMS

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

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

COUNT(*)
----------
46946104

SQL> SELECT TRUNC(enq_time), COUNT(*)
  FROM sys$service_metrics_tab
 GROUP BY TRUNC(enq_time)
 ORDER BY 1 ;

TRUNC(ENQ   COUNT(*)
--------- ----------
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 => sys.aq$_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.

Subscribe

Indexing NULL Value in a Table Column

As we know, a normal index does not include NULL value,  so SQL query won’t use this normal index for a SQL query with condition ‘IS NULL’.

Here is a tip for how to create an index on column with NULL value.

SQL> create index IDX_BATCH_ID_NULL on test ( BATCH_ID, 1);

Index created.

Actually the index is created by including two columns of BATCH_ID and constant number 1.

SQL> select count(*) from test where BATCH_ID is null;

COUNT(*)
----------
292

SQL> select count(*) from test where BATCH_ID is not null;

COUNT(*)
----------
172

SQL> create index idx_BATCH_ID on test(BATCH_ID);

Index created.

SQL> SET AUTOTRACE ON EXPLAIN

SQL> select MACHINE_ID from test 
     where BATCH_ID is not null and rownum<=2;

MACHINE_ID
----------------------
MACHINE_1
MACHINE_2

2 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3931117773

--------------------------------------------------------------------
| Id | Operation          | Name|Rows| Bytes | Cost(%CPU)| Time     |
---------------------------------------------------------------------
|  0 | SELECT STATEMENT   |     |   2|   130 |    2   (0)| 00:00:01 |
|* 1 |  COUNT STOPKEY     |     |    |       |           |          |
|* 2 |   TABLE ACCESS FULL|TEST | 172| 11180 |    2   (0)| 00:00:01 |
---------------------------------------------------------------------


SQL> select MACHINE_ID from test 
     where BATCH_ID is null and rownum<=2;

MACHINE_ID
---------------------------------------------
MACHINE_3
MACHINE_4

Execution Plan
----------------------------------------------------------
Plan hash value: 3931117773

-----------------------------------------------------------------
|Id| Operation        |Name|Rows|Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------
|0 | SELECT STATEMENT |    | 2  |  650 | 2 (0)      | 00:00:01 |
|*1| COUNT STOPKEY    |    |    |      |            |          |
|*2| TABLE ACCESS FULL|TEST|292 |18980 | 2 (0)      | 00:00:01 |
-------------------------------------------------------------------

SQL> create index idx_BATCH_ID_null on test ( BATCH_ID, 1);

Index created.

SQL> select MACHINE_ID from test 
    where BATCH_ID is null and rownum<=2;

MACHINE_ID
------------------------------------------------------
MACHINE_3
MACHINE_4


Execution Plan
----------------------------------------------------------
Plan hash value: 1426346817

--------------------------------------------------------------------
|Id| Operation                   |Name|Rows|Bytes|Cost(%CPU)| Time |
---------------------------------------------------------------------
|0 |SELECT STATEMENT             |    |  2 | 130 |  3   (0)|00:00:01|
|*1| COUNT STOPKEY               |    |    |     |         |        |
| 2|  TABLE ACCESS BY INDEX ROWID|TEST|292 |18980|  3   (0)|00:00:01|
|*3|   INDEX RANGE SCAN          | IDX_BATCH_ID_NULL 
                                      | 23 |     |  2   (0)|00:00:01|
----------------------------------------------------------------------

Create Materialised View Log on the Remote Database

DB1 is a local database, on which the materialized views are to be created.
DB2 is a remote database, on which materialized view logs are created .

The high level steps are:

  • on local database DB1, create a database link between DB1 and DB2 database.
  • On the remote database DB2, create materialized logs on the tables of DB2.
  • On local database DB1, create materialized views with Fast Refresh option , etc.
  • On Local database DB1, run a complete refresh on your materialized views:          Exec dbms_snapshot.refresh(‘schema_name.mview_name’,’c’);
  • Then on local database DB1 again ,  refresh the materialized  view using the Fast Refresh option:
    Exec dbms_snapshot.refresh(‘schema_name.mview_name’,’f’);

FAST_START_PARALLEL_ROLLBACK for SMON to Rollback Transactions Slowly

There are situations when parallel transaction recovery is not as fast as serial transaction recovery because the PQ slaves may interfere with each others by contending for the same resource.

SMON and parallel query slaves may be seen to take all the available CPUs.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

SQL*Net message from client

A client asked about why it takes 14.96 seconds for below sql(1):

SQL>select * from dba_tables;
Elapsed: 00:00:14.96

While it takes only 0.59 second for this sql(2):

SQL>select count(*) from dba_tables;
Elapsed: 00:00:00.59

Let’s compare the tkprof summary from sql tracing for those two sqls:

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe