Home » RDBMS Server » Performance Tuning » Retrieving 8k rows from one table takes too long (Oracle EE 11.2.0.3.0 - 64bit, Linux )
Retrieving 8k rows from one table takes too long [message #674044] Sun, 30 December 2018 09:32 Go to previous message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
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

 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: high buffers
Next Topic: Oracle high "cost" SELECT query
Goto Forum:
  


Current Time: Thu Mar 28 08:35:35 CDT 2024