optimisation de requête sql

Version imprimable

Bonjour,
J'ai une base de données 10.2.0.4.0, j'ai actuellement une requête qui consomme beaucoup de cpu time, je voudrais l'optimiser:
voici ci-dessous la requête:

Code:

1
2
3
4
5
SELECT DISTINCT A.QUEUENAME
  FROM SYSADM.PSAPMSGPUBHDR A, SYSADM.PSQUEUEDEFN B
 WHERE PUBSTATUS in (1,2)
   AND A.QUEUENAME = B.QUEUENAME
   AND B.QUEUESTATUS <> 2;

ci-dessous le plan d'execution:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
-------------------------------------------------------------------------------------------
| Id  | Operation              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                  |       |       |   940 (100)|          |
|   1 |  HASH UNIQUE           |                  |     8 |   328 |   940   (7)| 00:00:12 |
|*  2 |   HASH JOIN            |                  |   672K|    26M|   899   (2)| 00:00:11 |
|*  3 |    TABLE ACCESS FULL   | PSQUEUEDEFN      |    73 |  1387 |     5   (0)| 00:00:01 |
|*  4 |    INDEX FAST FULL SCAN| PSDPSAPMSGPUBHDR |   672K|    14M|   889   (2)| 00:00:11 |
-------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   3 - SEL$1 / B@SEL$1
   4 - SEL$1 / A@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      OPT_PARAM('_optimizer_cost_based_transformation' 'off')
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "B"@"SEL$1")
      INDEX_FFS(@"SEL$1" "A"@"SEL$1" ("PSAPMSGPUBHDR"."PUBSTATUS"
              "PSAPMSGPUBHDR"."QUEUENAME"))
      LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")
      USE_HASH(@"SEL$1" "A"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("A"."QUEUENAME"="B"."QUEUENAME")
   3 - filter("B"."QUEUESTATUS"<>2)
   4 - filter(("PUBSTATUS"=1 OR "PUBSTATUS"=2))
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "A"."QUEUENAME"[VARCHAR2,120]
   2 - (#keys=1) "A"."QUEUENAME"[VARCHAR2,120]
   3 - "B"."QUEUENAME"[VARCHAR2,120]
   4 - "A"."QUEUENAME"[VARCHAR2,120]

ci-dessous le nombre de ligne de chaque table , les index etc ...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS LAST_ANA
------------------------------ ---------- ---------- ------------ --------
PSAPMSGPUBHDR                      672909      20307            0 09/11/10
 
 
 
 
 
INDEX_NAME      TABLE_NAME      COLUMN_NAME     COLUMN_POSITION
--------------- --------------- --------------- ---------------
PS_PSQUEUEDEFN  PSQUEUEDEFN     QUEUENAME                     1
 
INDEX_NAME           TABLE_NAME      COLUMN_NAME          COLUMN_POSITION
-------------------- --------------- -------------------- ---------------
PS_PSAPMSGPUBHDR     PSAPMSGPUBHDR   IBTRANSACTIONID                    1
PSAPSAPMSGPUBHDR     PSAPMSGPUBHDR   QUEUENAME                          1
PSBPSAPMSGPUBHDR     PSAPMSGPUBHDR   LASTUPDDTTM                        1
PSDPSAPMSGPUBHDR     PSAPMSGPUBHDR   PUBSTATUS                          1
PSCPSAPMSGPUBHDR     PSAPMSGPUBHDR   PUBSTATUS                          1
PSDPSAPMSGPUBHDR     PSAPMSGPUBHDR   QUEUENAME                          2
PSCPSAPMSGPUBHDR     PSAPMSGPUBHDR   PUBLISHTIMESTAMP                   2

Quelqu'un peut me donner des idées sur ce qui ne va pas ou comment puis-je le réécrire ?

Merci.

Il manque les informations de volumétrie sur PSQUEUEDEFN, ainsi que des colonnes : dans la requête vous utilisez QUEUESTATUS, mais elle n'apparaît pas dans votre description.

Il manque aussi les index en place.
Actuellement vous utilisez PSDPSAPMSGPUBHDR, mais il a l'air gros et lent.

Enfin, deux petites requêtes pour voir la répartition des données :
Code:

1 2 3 4 select PUBSTATUS, count(*) as nb_lignes from SYSADM.PSAPMSGPUBHDR group by PUBSTATUS order by PUBSTATUS ASC;
Code:

1 2 3 4 5 select case QUEUESTATUS when 2 then 0 else 1 end as status_2, count(*) as nb_lignes from SYSADM.PSQUEUEDEFN group by case QUEUESTATUS when 2 then 0 else 1 end order by status_2 asc;

Merci Waldar de votre réponse.
Voici ci-dessous les informations sur les colonnes des deux tables :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
SQL> desc SYSADM.PSAPMSGPUBHDR
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 IBTRANSACTIONID                           NOT NULL VARCHAR2(36 CHAR)
 EXTOPERATIONNAME                          NOT NULL VARCHAR2(254 CHAR)
 IB_OPERATIONNAME                          NOT NULL VARCHAR2(30 CHAR)
 EXTERNALMESSAGEID                         NOT NULL VARCHAR2(70 CHAR)
 PUBNODE                                   NOT NULL VARCHAR2(30 CHAR)
 QUEUENAME                                 NOT NULL VARCHAR2(30 CHAR)
 QUEUESEQID                                NOT NULL NUMBER(38)
 SUBQUEUE                                  NOT NULL VARCHAR2(60 CHAR)
 ORIGPUBNODE                               NOT NULL VARCHAR2(30 CHAR)
 PUBCLASS                                  NOT NULL VARCHAR2(30 CHAR)
 PUBLISHER                                 NOT NULL VARCHAR2(30 CHAR)
 PUBPROC                                   NOT NULL VARCHAR2(30 CHAR)
 TRXTYPE                                   NOT NULL VARCHAR2(4 CHAR)
 CREATEDTTM                                         DATE
 PUBLISHTIMESTAMP                                   DATE
 NRID                                      NOT NULL NUMBER(38)
 DESTPUBNODE                               NOT NULL VARCHAR2(248 CHAR)
 CONVERSATIONID                            NOT NULL VARCHAR2(36 CHAR)
 INREPLYTOID                               NOT NULL VARCHAR2(36 CHAR)
 ISREQUEST                                 NOT NULL NUMBER(38)
 CANONICALTRSFRMID                         NOT NULL VARCHAR2(36 CHAR)
 PUBSTATUS                                 NOT NULL NUMBER(38)
 STATUSSTRING                              NOT NULL VARCHAR2(5 CHAR)
 LASTUPDDTTM                                        DATE
 RETRYCOUNT                                NOT NULL NUMBER(38)
 MACHINENAME                               NOT NULL VARCHAR2(30 CHAR)
 SLAVEPROCESSNAME                          NOT NULL VARCHAR2(15 CHAR)
 IB_SLAVEQUEUED                            NOT NULL NUMBER(38)
 TUXDOMAIN                                 NOT NULL VARCHAR2(30 CHAR)
 PROCESSID                                 NOT NULL NUMBER(38)
 PUBROUTINGTRAIL                           NOT NULL VARCHAR2(248 CHAR)
 
SQL> desc SYSADM.PSQUEUEDEFN
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 QUEUENAME                                 NOT NULL VARCHAR2(30 CHAR)
 VERSION                                   NOT NULL NUMBER(38)
 QUEUESTATUS                               NOT NULL NUMBER(38)
 THRUPUTTYPE                               NOT NULL NUMBER(38)
 ARCHIVE                                   NOT NULL NUMBER(38)
 OBJECTOWNERID                             NOT NULL VARCHAR2(4 CHAR)
 LASTUPDDTTM                                        DATE
 LASTUPDOPRID                              NOT NULL VARCHAR2(30 CHAR)
 DESCR                                     NOT NULL VARCHAR2(30 CHAR)
 DESCRLONG                                          CLOB

ci-dessous la volumetrie des deux tables:

Citation:

Pour la table PSAPMSGPUBHDR : 672909 lignes

Pour la table PSQUEUEDEFN :146 lignes

requêtes pour voir la répartition des données :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL>  SELECT PUBSTATUS, count(*) AS nb_lignes
    FROM SYSADM.PSAPMSGPUBHDR
GROUP BY PUBSTATUS
ORDER BY PUBSTATUS ASC;  2    3    4
 
 PUBSTATUS  NB_LIGNES
---------- ----------
         1     586079
         4      86990
 
SQL> SELECT case QUEUESTATUS when 2 then 0 else 1 end AS status_2,
         count(*) AS nb_lignes
    FROM SYSADM.PSQUEUEDEFN
GROUP BY case QUEUESTATUS when 2 then 0 else 1 end
ORDER BY status_2 ASC;  2    3    4    5
 
  STATUS_2  NB_LIGNES
---------- ----------
         0        111
         1         35

Ok merci pour les informations.

Il manque encore les index en place - et notamment PSDPSAPMSGPUBHDR qui je suppose contient au moins les colonnes (QUEUENAME, PUBSTATUS), et qui est utilisé actuellement dans votre plan d'exécution.

-- Edit : Pardon, j'avais mal vu c'est dans votre premier message.

Est-ce que PSQUEUEDEFN.QUEUENAME est la clef primaire de cette table ?
Si oui, essayez cette requête :
Code:

1 2 3 4 5 6 7 SELECT B.QUEUENAME FROM SYSADM.PSQUEUEDEFN B WHERE B.QUEUESTATUS <> 2 AND EXISTS (SELECT NULL FROM SYSADM.PSAPMSGPUBHDR A WHERE A.PUBSTATUS IN (1,2) AND A.QUEUENAME = B.QUEUENAME);
Et refaites un explain plan.

09/11/2010, 15h28
dngaya

Merci waldar,
PSQUEUEDEFN.QUEUENAME n'est pas la clef primaire de cette table .il n'ya pas de clé primaire dans cette table.

Alors comme ceci :

Code:

1
2
3
4
5
6
7
SELECT DISTINCT B.QUEUENAME
  FROM SYSADM.PSQUEUEDEFN B
 WHERE B.QUEUESTATUS <> 2
   AND EXISTS (SELECT NULL
                 FROM SYSADM.PSAPMSGPUBHDR A
                WHERE A.PUBSTATUS IN (1,2)
                  AND A.QUEUENAME = B.QUEUENAME);

Voici le plan d'exécution:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
SQL> SELECT DISTINCT B.QUEUENAME
  FROM SYSADM.PSQUEUEDEFN B
 WHERE B.QUEUESTATUS <> 2
   AND EXISTS (SELECT NULL
                 FROM SYSADM.PSAPMSGPUBHDR A
                WHERE A.PUBSTATUS IN (1,2)
                  AND A.QUEUENAME = B.QUEUENAME);
 
no rows selected
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2064485491
 
-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |     8 |   328 |    77  (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI           |                  |     8 |   328 |    77  (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | PSQUEUEDEFN      |    35 |   665 |     5  (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| PSAPMSGPUBHDR    |   139K|  3005K|     3  (0)| 00:00:01 |
|   4 |    INDEX RANGE SCAN          | PSAPSAPMSGPUBHDR |     1 |       |     2  (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("B"."QUEUESTATUS"<>2)
   3 - filter("A"."PUBSTATUS"=1 OR "A"."PUBSTATUS"=2)
   4 - access("A"."QUEUENAME"="B"."QUEUENAME")
 
 
Statistics
----------------------------------------------------------
        797  recursive calls
          0  db block gets
       8811  consistent gets
          0  physical reads
          0  redo size
        326  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
          0  rows processed

Je vois qu'il a beaucoup de consistent gets par rapport au premier ci-dessous :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
SQL> SELECT DISTINCT A.QUEUENAME FROM SYSADM.PSAPMSGPUBHDR A, SYSADM.PSQUEUEDEFN B WHERE PUBSTATUS in  (1,2)
 AND A.QUEUENAME=B.QUEUENAME AND B.QUEUESTATUS<>2;  2
 
no rows selected
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1637650812
 
-------------------------------------------------------------------------------------------
| Id  | Operation              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                  |     8 |   328 |   907   (3)| 00:00:11 |
|   1 |  HASH UNIQUE           |                  |     8 |   328 |   907   (3)| 00:00:11 |
|   2 |   HASH JOIN            |                  |   153K|  6127K|   898   (2)| 00:00:11 |
|   3 |    TABLE ACCESS FULL   | PSQUEUEDEFN      |    35 |   665 |     5   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| PSDPSAPMSGPUBHDR |   629K|    13M|   888   (2)| 00:00:11 |
-------------------------------------------------------------------------------------------
 
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("A"."QUEUENAME"="B"."QUEUENAME")
   3 - filter("B"."QUEUESTATUS"<>2)
   4 - filter("PUBSTATUS"=1 OR "PUBSTATUS"=2)
 
 
Statistics
----------------------------------------------------------
        334  recursive calls
         10  db block gets
       3432  consistent gets
          0  physical reads
          0  redo size
        326  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          0  rows processed

09/11/2010, 16h45
remi4444

Au final, la requête de Waldar est beaucoup plus efficace non ?

C'est vrai que c'est bizarre le coup des consistent-get, mais je pense que la grosse amélioration vient du fait que ne remonte pas en mémoire la totalité de l'index (range-scan au lieu de Fast-Full scan)
09/11/2010, 17h04
Waldar

À priori une seconde versus onze.
Le nombre de consistent gets est probablement lié à la première lecture de l'index qui entraine la mise en cache.