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 next message
Andrey_R
Messages: 309
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

Re: Retrieving 8k rows from one table takes too long [message #674045 is a reply to message #674044] Sun, 30 December 2018 10:50 Go to previous messageGo to next message
John Watson
Messages: 8075
Registered: January 2010
Location: Global Village
Senior Member
You are using the default SQL*Plus array fetch size of 15, you could eliminate most of the 580 fetches if you give it
set arraysize 5000

Also, what difference is there if you suppress the screen output with
set autot traceonly

Lastly, your ind_i01 index is covering 4 of the five columns. You could add the remaining column to it and declare it the primary key for an IOT rather than using a heap table perhaps?
Re: Retrieving 8k rows from one table takes too long [message #674046 is a reply to message #674044] Sun, 30 December 2018 10:56 Go to previous messageGo to next message
John Watson
Messages: 8075
Registered: January 2010
Location: Global Village
Senior Member
And I forgot to mention, the reason for so many gets is that the clustering factor of that index must be terrible. Unless you upgrade to 12.2 where attribute clustered tables are possible, an IOT is he only way to fix this.
Re: Retrieving 8k rows from one table takes too long [message #674047 is a reply to message #674045] Sun, 30 December 2018 11:11 Go to previous messageGo to next message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

John Watson wrote on Sun, 30 December 2018 18:50
You are using the default SQL*Plus array fetch size of 15, you could eliminate most of the 580 fetches if you give it
set arraysize 5000

Also, what difference is there if you suppress the screen output with
set autot traceonly

Lastly, your ind_i01 index is covering 4 of the five columns. You could add the remaining column to it and declare it the primary key for an IOT rather than using a heap table perhaps?

Thanks.
I've set up the arraysize as recommended.

SQL>    set arraysize 5000
SQL> set autot traceonly
SQL> alter system flush buffer_cache;

System altered.
SQL>
Elapsed: 00:00:00.39
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:36.06

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
       5921  consistent gets
       5919  physical reads
          0  redo size
     477858  bytes sent via SQL*Net to client
        375  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       8677  rows processed

SQL>

So 36 seconds.
2nd time, when in cache it is super fast:

SQL> /

8677 rows selected.

Elapsed: 00:00:00.11

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
       5921  consistent gets
          0  physical reads
          0  redo size
     477858  bytes sent via SQL*Net to client
        375  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       8677  rows processed

SQL>
Does this prove that disk is slow ?
And what do you think about the number of consistent gets, is it too high or am I reading it wrong ?


I also generated a treedump file, but I will only receive it tomorrow..

The columns that are in the where clause of the query are not forming a unique combination that does not re-occur, to set it to be primary key...
Also, I am limited to performing structural changes unless inevitable..



And
Re: Retrieving 8k rows from one table takes too long [message #674048 is a reply to message #674046] Sun, 30 December 2018 11:13 Go to previous messageGo to next message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

John Watson wrote on Sun, 30 December 2018 18:56
And I forgot to mention, the reason for so many gets is that the clustering factor of that index must be terrible. Unless you upgrade to 12.2 where attribute clustered tables are possible, an IOT is he only way to fix this.
Isn't there a way to build an index to fit the clustering factor, meaning, be ordered to access the table efficiently for performing a specific query ?
Re: Retrieving 8k rows from one table takes too long [message #674049 is a reply to message #674048] Sun, 30 December 2018 11:37 Go to previous messageGo to next message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

And yes, clustering factor of this index is terrible indeed...


    BLEVEL LEAF_BLOCKS TABLE_NAME      INDEX_NAME      CLUSTERING_FACTOR
---------- ----------- --------------- --------------- -----------------
         3      182108 MYTAB            TTT                      14107450

Re: Retrieving 8k rows from one table takes too long [message #674050 is a reply to message #674047] Sun, 30 December 2018 13:00 Go to previous messageGo to next message
John Watson
Messages: 8075
Registered: January 2010
Location: Global Village
Senior Member
Quote:
The columns that are in the where clause of the query are not forming a unique combination that does not re-occur, to set it to be primary key...
They don't have to. All five columns would form your primary key.


Quote:
Isn't there a way to build an index to fit the clustering factor, meaning, be ordered to access the table efficiently for performing a specific query ?
No. Unless you make it an IOT.
Re: Retrieving 8k rows from one table takes too long [message #674074 is a reply to message #674050] Wed, 02 January 2019 07:49 Go to previous messageGo to next message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

John Watson wrote on Sun, 30 December 2018 21:00
Quote:
The columns that are in the where clause of the query are not forming a unique combination that does not re-occur, to set it to be primary key...
They don't have to. All five columns would form your primary key.


Quote:
Isn't there a way to build an index to fit the clustering factor, meaning, be ordered to access the table efficiently for performing a specific query ?
No. Unless you make it an IOT.
I have made a test with CTAS MYTAB_NEW ordered by the 2 columns from the query.

Then created the same indexes.

The query on MYTAB_NEW took a couple of seconds - mass improvement.

However, when I checked clustering factors for the indexes of MYTAB and compared to MYTAB_NEW

SQL> select TABLE_NAME,INDEX_NAME, UNIQUENESS,LEAF_BLOCKS,COMPRESSION,clustering_factor from user_indexes where table_name='MYTAB_NEW'
  2  UNION ALL
  3  select TABLE_NAME,INDEX_NAME, UNIQUENESS,LEAF_BLOCKS,COMPRESSION,clustering_factor from user_indexes where table_name='MYTAB'
  4  ORDER BY INDEX_NAME;

TABLE_NAME                     INDEX_NAME                     UNIQUENES LEAF_BLOCKS COMPRESS CLUSTERING_FACTOR
------------------------------ ------------------------------ --------- ----------- -------- -----------------
MYTAB                 IND_I01                       NONUNIQUE      251540 DISABLED          22531239
MYTAB_NEW             IND_I011                      NONUNIQUE      251544 DISABLED          13853350
MYTAB                 IND_PRIMARY                   UNIQUE         571624 DISABLED           6528962
MYTAB_NEW             IND_PRIMARY1                  UNIQUE         200004 DISABLED          41476871
MYTAB                 COL2                  		UNIQUE         261903 DISABLED           6131554
MYTAB_NEW             COL21               			UNIQUE         127444 DISABLED          41476871
MYTAB_NEW             TEST1               			NONUNIQUE      182106 DISABLED         280400
MYTAB                 TTT                           NONUNIQUE      182108 DISABLED          14107450

8 rows selected.

Elapsed: 00:00:00.03
SQL>

I've noticed that some got better, but some got much worse in terms of clustering factor.

I can imagine that it will have the same effect on other queries accessing the table with those indexes that got clustering factor to be very bad - some will get much worse because of this.

So, this means that there is basically no solution to this on this version ?
Or will it be any different with IOT ?




P.S Oh, and all, HAPPY NEW YEAR !
May the new year bring good health , happiness and prosperity to you and your families!

[Updated on: Wed, 02 January 2019 07:52]

Report message to a moderator

Re: Retrieving 8k rows from one table takes too long [message #675128 is a reply to message #674044] Sun, 10 March 2019 01:26 Go to previous message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
You mentioned about number of consistent gets is high,what is threshold or limit for consistent gets numbers i think it depends on each database or system,but any reference to indicate that it should be in this limit

also any way to find from consistent gets how much data (in bytes or mb) it is using from buffer cache

Like 5921 consistent gets ,approximately how much bytes it fetch or bytes in buffer cache its using

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


Current Time: Fri Dec 06 11:00:54 CST 2019