SQL> select count(*) from ftp where id_session = 2878;
COUNT(*)
----------
4
Затрач.время: 00:00:00.00
SQL> begin
2 dbms_stats.gather_table_stats
3 (ownname=>'ASMODEUS',
4 tabname=>'FTP',
5 cascade=>true,
6 estimate_percent=>null,
7 method_opt=>'for all indexed columns size auto',
8 no_invalidate=>false);
9 end;
10 /
Процедура PL/SQL успешно завершена.
Затрач.время: 00:00:00.06
SQL> select distinct A.ID_DEMAND,
2 A.NUM_LOT,
3 A.ID_SESSION,
4 count (*) over () as ALL_ROW_COUNT,
5 F.SOUND_TIME_BEGIN
6 from D A, FTP F
7 where A.ID_SESSION = 2878
8 and A.ID_SECTION = 1
9 and A.ID_NAPRAV = 1
10 and a.is_active = 1
11 and A.ID_SESSION = F.ID_SESSION
12 --and f.id_session = 2878
13 ;
27 строк выбрано.
Затрач.время: 00:00:00.03
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4rkt8bvgp39s6, child number 1
-------------------------------------
select distinct A.ID_DEMAND, A.NUM_LOT,
A.ID_SESSION, count (*) over () as ALL_ROW_COUNT,
F.SOUND_TIME_BEGIN from D A, FTP F where A.ID_SESSION
= 2878 and A.ID_SECTION = 1 and A.ID_NAPRAV = 1
and a.is_active = 1 and A.ID_SESSION = F.ID_SESSION --and
f.id_session = 2878
Plan hash value: 1224676671
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 27 |00:00:00.01 | 21 | | | |
| 1 | HASH UNIQUE | | 1 | 3 | 27 |00:00:00.01 | 21 | 795K| 795K| 1274K (0)|
| 2 | WINDOW BUFFER | | 1 | 3 | 108 |00:00:00.01 | 21 | 11264 | 11264 |10240 (0)|
| 3 | MERGE JOIN CARTESIAN | | 1 | 10 | 108 |00:00:00.01 | 21 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | D | 1 | 3 | 27 |00:00:00.01 | 18 | | | |
|* 5 | INDEX RANGE SCAN | I_DEMANDS_NAMEGOOD | 1 | 3 | 27 |00:00:00.01 | 3 | | | |
| 6 | BUFFER SORT | | 27 | 4 | 108 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 7 | TABLE ACCESS BY INDEX ROWID| FTP | 1 | 4 | 4 |00:00:00.01 | 3 | | | |
|* 8 | INDEX RANGE SCAN | I_FOREST_TIME_PERIOD_IDSESSION | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."ID_SECTION"=1 AND "A"."ID_SESSION"=2878 AND "A"."ID_NAPRAV"=1 AND "A"."IS_ACTIVE"=1)
8 - access("F"."ID_SESSION"=2878)
31 строк выбрано.
Затрач.время: 00:00:00.06
SQL> select distinct A.ID_DEMAND,
2 A.NUM_LOT,
3 A.ID_SESSION,
4 count (*) over () as ALL_ROW_COUNT,
5 F.SOUND_TIME_BEGIN
6 from D A, FTP F
7 where A.ID_SESSION = 2878
8 and A.ID_SECTION = 1
9 and A.ID_NAPRAV = 1
10 and a.is_active = 1
11 and A.ID_SESSION = F.ID_SESSION
12 and f.id_session = 2878
13 ;
27 строк выбрано.
Затрач.время: 00:00:00.03
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5ghv6yydqdfqg, child number 0
-------------------------------------
select distinct A.ID_DEMAND, A.NUM_LOT,
A.ID_SESSION, count (*) over () as ALL_ROW_COUNT,
F.SOUND_TIME_BEGIN from D A, FTP F where A.ID_SESSION
= 2878 and A.ID_SECTION = 1 and A.ID_NAPRAV = 1
and a.is_active = 1 and A.ID_SESSION = F.ID_SESSION and
f.id_session = 2878
Plan hash value: 1054189618
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 27 |00:00:00.01 | 75 | | | |
| 1 | HASH UNIQUE | | 1 | 1 | 27 |00:00:00.01 | 75 | 795K| 795K| 1266K (0)|
| 2 | WINDOW BUFFER | | 1 | 1 | 108 |00:00:00.01 | 75 | 11264 | 11264 |10240 (0)|
| 3 | NESTED LOOPS | | 1 | | 108 |00:00:00.01 | 75 | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 108 |00:00:00.01 | 21 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| D | 1 | 3 | 27 |00:00:00.01 | 18 | | | |
|* 6 | INDEX RANGE SCAN | I_DEMANDS_NAMEGOOD | 1 | 3 | 27 |00:00:00.01 | 3 | | | |
|* 7 | INDEX RANGE SCAN | I_FOREST_TIME_PERIOD_IDSESSION | 27 | 1 | 108 |00:00:00.01 | 3 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | FTP | 108 | 1 | 108 |00:00:00.01 | 54 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("A"."ID_SECTION"=1 AND "A"."ID_SESSION"=2878 AND "A"."ID_NAPRAV"=1 AND "A"."IS_ACTIVE"=1)
7 - access("F"."ID_SESSION"=2878)
filter("A"."ID_SESSION"="F"."ID_SESSION")
32 строк выбрано.
Затрач.время: 00:00:00.04
|