Geek Answers Handbook

Oracle 11.2 has a 2 second delay for plain SQL at random times

A simple table join is usually performed in 0.0XX seconds, and sometimes in 2.0XX seconds (in accordance with SQL PL / SQL Developer execution). This happens when starting from SQL Plus.

If I run SQL 10 times, 8 times it works fine and 2 times in 2 seconds.

This is a clean install of Oracle 11.2.0.4 for Linux x86_64 on Centos 7. I installed the recommended Oracle fixes:

  • Patch 19769489 - Updating the database patch 11.2.0.4.5 (Includes CPUJan2015)
  • Patch 19877440 - Oracle JavaVM component 11.2.0.4.2 DB (January 2010)

Without correction, no changes.

In two tables: LNK_PACK_REP: 13 rows PACKAGES: 6 rows

In SQL Plus, I have included all the statistics and run SQL several times. From time to time, it changes from 0.1 to 2.1. No other statistics change if I compare a run in 0.1 seconds with a run in 2.1 seconds. The server has 16 GB of RAM and 8 processor cores. Server load is less than 0.1 (currently the user does not use the server).

Conclusion:

SQL> select PACKAGE_ID, id, package_name from LNK_PACK_REP LNKPR INNER JOIN PACKAGES P ON LNKPR.PACKAGE_ID = P.ID;

PACKAGE_ID PACKAGE_NAME

     3          3 RAPOARTE
     3          3 RAPOARTE
   121        121 VANZARI
   121        121 VANZARI
   121        121 VANZARI
     2          2 PACHETE
     2          2 PACHETE
     1          1 DEPARTAMENTE
     1          1 DEPARTAMENTE
    81         81 ROLURI
    81         81 ROLURI

PACKAGE_ID PACKAGE_NAME

   101        101 UTILIZATORI
   101        101 UTILIZATORI

13 rows selected.

Expired: 00: 00: 02.01

Execution plan

Plan hash value: 2671988802

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name              | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                   |    13 |   351 |     3   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR         |                   |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10002          |    13 |   351 |     3   (0)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN            |                   |    13 |   351 |     3   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE          |                   |     6 |    84 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   5 |      PX SEND HASH       | :TQ10001          |     6 |    84 |     2   (0)| 00:00:01 |  Q1,01 | P->P | HASH       |
|   6 |       PX BLOCK ITERATOR |                   |     6 |    84 |     2   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|   7 |        TABLE ACCESS FULL| PACKAGES          |     6 |    84 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   8 |     BUFFER SORT         |                   |       |       |            |          |  Q1,02 | PCWC |            |
|   9 |      PX RECEIVE         |                   |    13 |   169 |     1   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  10 |       PX SEND HASH      | :TQ10000          |    13 |   169 |     1   (0)| 00:00:01 |        | S->P | HASH       |
|  11 |        INDEX FULL SCAN  | UNQ_PACK_REP      |    13 |   169 |     1   (0)| 00:00:01 |        |      |            |
--------------------------------------------------------------------------------------------------------------------------

Predicate information (identified by operation identifier):

3 - access ("LNKPR". "PACKAGE_ID" = "P". "ID")

Note

  • dynamic fetch used for this statement (level = 2)

Statistics

     24  recursive calls
      0  db block gets
     10  consistent gets
      0  physical reads
      0  redo size
    923  bytes sent via SQL*Net to client
    524  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      4  sorts (memory)
      0  sorts (disk)
     13  rows processed

1:

-- Create table
create table PACKAGES
(
  id           NUMBER(3) not null,
  package_name VARCHAR2(150),
  position     NUMBER(3),
  activ        NUMBER(1)
)
tablespace UM
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table PACKAGES
  add constraint PACKAGES_ID primary key (ID)
  using index 
  tablespace UM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index PACKAGES_ACTIV on PACKAGES (ID, ACTIV)
  tablespace UM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

2:

-- Create table
create table LNK_PACK_REP
(
  package_id NUMBER(3) not null,
  report_id  NUMBER(3) not null
)
tablespace UM
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table LNK_PACK_REP
  add constraint UNQ_PACK_REP primary key (PACKAGE_ID, REPORT_ID)
  using index 
  tablespace UM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index LNK_PACK_REP_REPORT_ID on LNK_PACK_REP (REPORT_ID)
  tablespace UM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

Oracle Enterprise Manager SQL Monitor SQL, . runns " " 0.0 ( 10 , ) "" 0.0 2.0 . Monitored SQL Executions 2.0, :

  • : 2.0s
  • : 0.0s
  • PL/SQL Java: 0.0
  • :% ( )
  • : 10
  • IO: 0
  • IO Bytes: 0
  • : 2
  • : 4

, Duration, , (10,163 3748 , ), 0.0, .

, .

+4
2

. , .

- :

alter system set parallel_max_servers=0;

( . , , , .)

, parallelism .

, . SQL, . , "CPU". , . , , 2 :

  • - parallelism , . dynamic sampling used for this statement (level=2) .
  • OS Thread startup. SQL, , 8 . PARALLEL_MIN_SERVERS , .
  • . , SELECT INSERT.
  • . . , , .
  • . parallelism . , 39 , DOP. , , , .

, , , . parallelism . .

+2

parallelism, , , , parallelism ( ). "alter session", , . , , 8 10 , 10/10.

+1

More articles:


All Articles