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  |
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 |
|
|
|
| Re: Retrieving 8k rows from one table takes too long [message #674045 is a reply to message #674044] |
Sun, 30 December 2018 10:50  |
John Watson
Messages: 8964
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 #674047 is a reply to message #674045] |
Sun, 30 December 2018 11:11 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
John Watson wrote on Sun, 30 December 2018 18:50You 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>
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 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
John Watson wrote on Sun, 30 December 2018 18:56And 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 #674050 is a reply to message #674047] |
Sun, 30 December 2018 13:00 |
John Watson
Messages: 8964
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 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
John Watson wrote on Sun, 30 December 2018 21:00Quote: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 |
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
|
|
|
|
Goto Forum:
Current Time: Sun Feb 09 00:48:08 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
