Slow query
From: astalavista <nobody_at_nowhere.com>
Date: Tue, 1 Jul 2008 21:34:31 +0200
Message-ID: <486a86c5$0$7233$426a74cc@news.free.fr>
| Id | Operation | Name
| Rows | Bytes |TempSpc| Cost |
| 0 | SELECT STATEMENT |
| 196K| 85M| | 51394 |
| 1 | SORT ORDER BY |
| 196K| 85M| 180M| 51394 |
30 - access("DC0"."NRID"="DC6"."DC0_NRID" AND "DC6"."AP01_NAME"='Qté Totale Cdée')
31 - filter(UPPER("AM0_1"."FONCTION") LIKE '%S%CLIENT%') 32 - access("AM0_1"."TITULAIRE"=:Z)
Date: Tue, 1 Jul 2008 21:34:31 +0200
Message-ID: <486a86c5$0$7233$426a74cc@news.free.fr>
Hi,
Is it possible to speed up this query ?
Thanks in advance
9.2.0.6
2 SELECT dc0.nrid, dc0.so0_nrid, dc0.etat,
3 TO_CHAR (dc0.date_creat, 'DD/MM/YYYY') "dc0.date_creat",
4 w.remark "Modif Groupement", y.remark "Ecart Coop",
5 u.remark "Nom Précommande", z.remark "Amex", x.remark
"Commentaires",
6 TO_CHAR (dc0.dat5, 'DD/MM/YYYY') "dc0.dat5", dc0.REF,
dc0.netat,
7 so0.var53 "CIP", so0.societe "Pharmacie", so0.code_post "CP",
8 so0.loc "Ville", so0.concur "VIP", so0.var18 "Segment",
9 dc0.titulaire "Preneur ordre", depos.societe "Dépositaire
Cde",
10 TRUNC (dc0.num4, 2) "CA Cde", t.remark "Qté Cde",
11 TRUNC (dc0.num12, 2) "P.C.", dc0.etat, dc0.code_dep, dc0.niv,
12 dc0.TYPE, dc0.netat
13 FROM bcrm.dc0 dc0,
14 bcrm.so0 so0,
15 bcrm.so0 depos,
16 (SELECT /*+ first_rows index(dc6) */
17 dc0_nrid, ap01_name, remark
18 FROM bcrm.dc6) z,
19 (SELECT /*+ first_rows index(dc6) */
20 dc0_nrid, ap01_name, remark
21 FROM bcrm.dc6) y,
22 (SELECT /*+ first_rows index(dc6) */
23 dc0_nrid, ap01_name, remark
24 FROM bcrm.dc6) x,
25 (SELECT /*+ first_rows index(dc6) */
26 dc0_nrid, ap01_name, remark
27 FROM bcrm.dc6) w,
28 (SELECT /*+ first_rows index(dc6) */
29 dc0_nrid, ap01_name, remark
30 FROM bcrm.dc6) u,
31 (SELECT /*+ first_rows index(dc6) */
32 dc0_nrid, ap01_name, remark
33 FROM bcrm.dc6) t
34 WHERE dc0.so0_nrid = so0.nrid
35 AND dc0.TEMPLATE IS NULL
36 AND TO_NUMBER (dc0.var22) = depos.nrid(+)
37 AND dc0.nrid = t.dc0_nrid(+)
38 AND t.ap01_name = 'Qté Totale Cdée'
39 AND dc0.nrid = z.dc0_nrid(+)
40 AND z.ap01_name(+) = 'Commentaire : AMEX'
41 AND dc0.nrid = y.dc0_nrid(+)
42 AND y.ap01_name(+) = 'Commentaire : Modif. PC'
43 AND dc0.nrid = x.dc0_nrid(+)
44 AND x.ap01_name = 'Commentaire'
45 AND dc0.nrid = w.dc0_nrid(+)
46 AND w.ap01_name(+) = 'Commentaire : Modif. Gpt'
47 AND dc0.nrid = u.dc0_nrid(+)
48 AND u.ap01_name(+) = 'Commentaire : Précommande'
49 AND dc0.etat IN
50 ('A valider par SCLI', 'Validée par SCLI', 'A compléter par
SCOM')
51 AND EXISTS (
52 SELECT NULL
53 FROM am0 am0_1
54 WHERE am0_1.titulaire = :1
55 AND UPPER (am0_1.fonction) LIKE '%S%CLIENT%')
56 ORDER BY 3, 12, 8, 9, 10, 11, 13 DESC
57 /
| Id | Operation | Name
| Rows | Bytes |TempSpc| Cost |
| 0 | SELECT STATEMENT |
| 196K| 85M| | 51394 |
| 1 | SORT ORDER BY |
| 196K| 85M| 180M| 51394 |
|* 2 | FILTER |
| | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | DC6
| 2 | 62 | | 1 |
| 4 | NESTED LOOPS |
| 196K| 85M| | 38123 |
| 5 | NESTED LOOPS |
| 123K| 49M| | 31970 |
| 6 | NESTED LOOPS OUTER |
| 108K| 40M| | 26558 |
| 7 | NESTED LOOPS OUTER |
| 99237 | 34M| | 21597 |
| 8 | NESTED LOOPS OUTER |
| 91033 | 28M| | 17045 |
| 9 | NESTED LOOPS OUTER |
| 83582 | 23M| | 12866 |
| 10 | NESTED LOOPS OUTER |
| 76786 | 19M| | 9027 |
| 11 | NESTED LOOPS |
| 76786 | 15M| | 5187 |
| 12 | INLIST ITERATOR |
| | | | |
|* 13 | TABLE ACCESS BY INDEX ROWID | DC0
| 76786 | 8323K| | 1347 |
| 14 | BITMAP CONVERSION TO ROWIDS|
| | | | |
|* 15 | BITMAP INDEX SINGLE VALUE | INDB_DC0_ETAT
| | | | |
| 16 | TABLE ACCESS BY INDEX ROWID | SO0
| 1 | 107 | | 1 |
|* 17 | INDEX UNIQUE SCAN | PK_SO0NRID
| 1 | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | SO0
| 1 | 51 | | 1 |
|* 19 | INDEX UNIQUE SCAN | PK_SO0NRID
| 1 | | | |
| 20 | TABLE ACCESS BY INDEX ROWID | DC6
| 1 | 31 | | 1 |
|* 21 | INDEX RANGE SCAN | IND_DC6_DC0_NRID_AP01_NAME | 1 | | | |
| 22 | TABLE ACCESS BY INDEX ROWID | DC6
| 1 | 31 | | 1 |
|* 23 | INDEX RANGE SCAN | IND_DC6_DC0_NRID_AP01_NAME | 1 | | | |
| 24 | TABLE ACCESS BY INDEX ROWID | DC6
| 1 | 31 | | 1 |
|* 25 | INDEX RANGE SCAN | IND_DC6_DC0_NRID_AP01_NAME | 1 | | | |
| 26 | TABLE ACCESS BY INDEX ROWID | DC6
| 1 | 31 | | 1 |
|* 27 | INDEX RANGE SCAN | IND_DC6_DC0_NRID_AP01_NAME | 1 | | | |
| 28 | TABLE ACCESS BY INDEX ROWID | DC6
| 1 | 31 | | 1 |
|* 29 | INDEX RANGE SCAN | IND_DC6_DC0_NRID_AP01_NAME | 1 | | | | |* 30 | INDEX RANGE SCAN | IND_DC6_DC0_NRID_AP01_NAME | 1 | | | | |* 31 | TABLE ACCESS BY INDEX ROWID | AM0
| 1 | 29 | | 1 |
|* 32 | INDEX UNIQUE SCAN | IND_AM0_TITULAIRE
| 1 | | | 1 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - filter( EXISTS (SELECT /*+ */ 0 FROM "BXAT"."AM0" "AM0_1" WHERE "AM0_1"."TITULAIRE"=:Z AND
UPPER("AM0_1"."FONCTION") LIKE '%S%CLIENT%'))
13 - filter("DC0"."TEMPLATE" IS NULL)
15 - access("DC0"."ETAT"='A compléter par SCOM' OR "DC0"."ETAT"='A valider
par SCLI' OR "DC0"."ETA
par SCLI')
17 - access("DC0"."SO0_NRID"="SO0"."NRID")
19 - access("DEPOS"."NRID"(+)=TO_NUMBER("DC0"."VAR22"))
21 - access("DC0"."NRID"="DC6"."DC0_NRID"(+) AND
"DC6"."AP01_NAME"(+)='Commentaire : Modif. PC')
23 - access("DC0"."NRID"="DC6"."DC0_NRID"(+) AND
"DC6"."AP01_NAME"(+)='Commentaire : AMEX')
25 - access("DC0"."NRID"="DC6"."DC0_NRID"(+) AND
"DC6"."AP01_NAME"(+)='Commentaire : Modif. Gpt')
27 - access("DC0"."NRID"="DC6"."DC0_NRID"(+) AND
"DC6"."AP01_NAME"(+)='Commentaire : Précommande')
29 - access("DC0"."NRID"="DC6"."DC0_NRID" AND
"DC6"."AP01_NAME"='Commentaire')
30 - access("DC0"."NRID"="DC6"."DC0_NRID" AND "DC6"."AP01_NAME"='Qté Totale Cdée')
31 - filter(UPPER("AM0_1"."FONCTION") LIKE '%S%CLIENT%') 32 - access("AM0_1"."TITULAIRE"=:Z)
Note: cpu costing is off
59 rows selected.
Elapsed: 00:00:00.08
>
No virus found in this incoming message.
Checked by AVG.
Version: 7.5.524 / Virus Database: 270.4.3/1526 - Release Date: 30/06/2008
08:43
Received on Tue Jul 01 2008 - 14:34:31 CDT