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 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151
| 3031 ligne(s) sÚlectionnÚe(s).
SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 4j4vd8bdnrvtg, child number 0
-------------------------------------
WITH vj AS ( SELECT code_produit,
to_char(NVL(round(sum(QTE_VENDUE)),0),'999G999G999G999',
'nls_numeric_characters='', ''') "Qte Vendue",
to_char(NVL(round(sum(VALEUR_VENTE)),0) ,'999G999G999G999',
'nls_numeric_characters='', ''') "C.A.H.T", to_char(NVL(
round(sum(MARGE_HT)),0) ,'999G999G999G999',
'nls_numeric_characters='', ''') "Marge HT",
to_char(round((sum(VALEUR_VENTE)-sum(VALEUR))*100/nullif(sum(VALEUR_VENT
E),0),2),'999G999G999G999D99', 'nls_numeric_characters='', ''') "TAUX"
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
FROM VENTE_JOUR WHERE DATE_GENERATION BETWEEN '01/06/2013' AND
'09/06/2013' GROUP BY code_produit ), s AS ( SELECT
code_produit, to_char(NVL(round(sum(QUANTITE)
),0),'999G999G999G999', 'nls_numeric_characters='', ''') "Qte Stock",
to_char(NVL(round(sum(VALEUR_PRIX_REV) ),0),'999G999G999G999',
'nls_numeric_characters='', ''') "Valeur du stock" FROM STOCK WHERE
DATE_GENERATION = '09/06/2013' GROUP BY code_produit ) SELECT /*+
GATHER_PLAN_STATISTI
Plan hash value: 2896693882
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Ti
me | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------
----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3031 |00:00:0
2.46 | 26722 | 26711 | | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 1 | SORT ORDER BY | | 1 | 3017 | 3031 |00:00:0
2.46 | 26722 | 26711 | 690K| 486K| 613K (0)|
|* 2 | HASH JOIN RIGHT OUTER | | 1 | 3017 | 3031 |00:00:0
2.46 | 26722 | 26711 | 826K| 826K| 1265K (0)|
| 3 | VIEW | | 1 | 3951 | 3529 |00:00:0
1.35 | 15247 | 15243 | | | |
| 4 | HASH GROUP BY | | 1 | 3951 | 3529 |00:00:0
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1.34 | 15247 | 15243 | 869K| 869K| 2395K (0)|
|* 5 | TABLE ACCESS FULL | STOCK | 1 | 82997 | 86156 |00:00:0
0.82 | 15247 | 15243 | | | |
|* 6 | HASH JOIN RIGHT OUTER| | 1 | 3017 | 3031 |00:00:0
1.11 | 11475 | 11468 | 845K| 845K| 1320K (0)|
| 7 | VIEW | | 1 | 2652 | 2631 |00:00:0
0.30 | 3079 | 3076 | | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 8 | HASH GROUP BY | | 1 | 2652 | 2631 |00:00:0
0.28 | 3079 | 3076 | 895K| 895K| 2403K (0)|
|* 9 | TABLE ACCESS FULL | VENTE_JOUR | 1 | 223K| 234K|00:00:0
0.73 | 3079 | 3076 | | | |
| 10 | VIEW | PRODUIT | 1 | 3017 | 3031 |00:00:0
0.80 | 8396 | 8392 | | | |
| 11 | HASH GROUP BY | | 1 | 3017 | 3031 |00:00:0
0.79 | 8396 | 8392 | 1057K| 940K| 2753K (0)|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 12 | TABLE ACCESS FULL | VENTES | 1 | 687K| 820K|00:00:0
0.26 | 8396 | 8392 | | | |
--------------------------------------------------------------------------------
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - access("S"."CODE_PRODUIT"="P"."CODE_PRODUIT")
5 - filter("DATE_GENERATION"=TO_DATE(' 2013-06-09 00:00:00', 'syyyy-mm-dd hh2
4:mi:ss'))
6 - access("VJ"."CODE_PRODUIT"="P"."CODE_PRODUIT")
9 - filter(("DATE_GENERATION"<=TO_DATE(' 2013-06-09 00:00:00', 'syyyy-mm-dd h
h24:mi:ss') AND
"DATE_GENERATION">=TO_DATE(' 2013-06-01 00:00:00', 'syyyy-mm-dd hh
24:mi:ss')))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
49 ligne(s) sÚlectionnÚe(s) |
Partager