Hi all,
I am trying to query this table that has 41M rows:
SQL> select count(*) from MYTAB ;
COUNT(*)
----------
41833878
Elapsed: 00:00:05.46
Structure of table:
SQL> DESC MYTAB
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
COL1 NOT NULL NUMBER(14)
COL2 NOT NULL NUMBER(2)
COL3 NOT NULL VARCHAR2(50 CHAR)
COL4 NOT NULL VARCHAR2(4000 CHAR)
COL5 NOT NULL NUMBER(18,4)
Structure of indexes:
SQL> COL COLUMN_NAME FOR A30
SQL> SELECT INDEX_NAME , COLUMN_NAME , COLUMN_POSITION
2 FROM USER_IND_COLUMNS
3 WHERE TABLE_NAME = 'MYTAB'
4 ORDER BY INDEX_NAME , COLUMN_NAME;
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ ---------------
IND_I01 COL3 1
IND_I01 COL5 2
IND_I01 COL2 4
IND_I01 COL1 3
IND_PRIMARY COL3 2
IND_PRIMARY COL1 1
COL2 COL2 2
COL2 COL1 1
TTT COL3 2
TTT COL5 1
10 rows selected.
Statistics were collected:
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname => 'MYOWNER',tabname => 'MYTAB',estimate_percent=>100,method_opt => 'FOR ALL INDEXED COLUMNS SIZE auto' , FORCE=>TRUE, CASCADE=>TRUE);
PL/SQL procedure successfully completed.
Elapsed: 00:38:21.51
SQL>
This is the bind initialization:
SQL> var B1 varchar2(35);
SQL> var B2 number;
SQL>
SQL>
SQL>
SQL>
SQL> begin
2 :B1 := 'VALUE1';
3 :B2 := 30000101010010.00;
4 end;
5 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL>
This is the query and execution results( clean buffer cache ):
SQL>
SQL> set autot on
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.62
SQL> --
SQL> select A.COL1,
2 A.COL3,
3 A.COL4,
4 A.COL5,
5 A.COL2
6 from MYTAB A
7 WHERE A.COL3 = :B1
8 AND A.COL5 = :B2;
..
...
....
.....
......
.......
8677 rows selected.
Elapsed: 00:00:44.45
Execution Plan
----------------------------------------------------------
Plan hash value: 4225748633
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MYTAB | 1 | 43 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TTT | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."COL5"=TO_NUMBER(:B2) AND "A"."COL3"=:B1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6686 consistent gets
5919 physical reads
0 redo size
528634 bytes sent via SQL*Net to client
6722 bytes received via SQL*Net from client
580 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8677 rows processed
It retrieves the 8k rows in some 45 seconds when on disk in SQL*Plus.
2nd execution ( from cache , right after 1st one ) took 18 seconds:
8677 rows selected.
Elapsed: 00:00:18.39
Execution Plan
----------------------------------------------------------
Plan hash value: 4225748633
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MYTAB | 1 | 43 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TTT | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."COL5"=TO_NUMBER(:B2) AND "A"."COL3"=:B1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6686 consistent gets
0 physical reads
0 redo size
528634 bytes sent via SQL*Net to client
6722 bytes received via SQL*Net from client
580 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8677 rows processed
SQL>
I thought maybe the 6k consistent gets are to blaim, meaning, that for 8k rows - we use 6k blocks , which may indicate row chaining ?
So I tried to use the following query to see if it is so:
SQL>
SQL> SELECT a.name, b.value
2 FROM v$statname a, v$mystat b
3 WHERE a.statistic# = b.statistic#
4 AND lower(a.name) = 'table fetch continued row';
NAME VALUE
---------------------------------------------------------------- ----------
table fetch continued row 14
Elapsed: 00:00:00.01
So 14 out of 6k is not significant.. or I am getting stuff wrong maybe.
So, what can be the reason for 8k rows to be retrieved so long, when I have just the right index for it ?
Any suggestions/ideas ?
TIA.
Andrey
[Updated on: Sun, 30 December 2018 09:34]
Report message to a moderator