High consistent Gets and response time in prod
Date: Fri, 30 Apr 2021 17:39:43 -0500
Message-ID: <CAHSa0M2S2ZQXdTUCmYSRLMs_JFiQmgx4BCk0AMWER7yN-5iSPw_at_mail.gmail.com>
Hi,
Fri evening and I have a problem. grrr..
We have a simple SELECT statement like this:
select * from c.pd where rownum <=1
This executes instantly in test, but takes over 2 mins in prod. I did some
autotrace and here are some stats:
TEST:
Execution Plan
Predicate Information (identified by operation id):
1 - filter(ROWNUM<=1)
Statistics
...
Elapsed: 00:00:00.03
Plan hash value: 1087134000
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 983 | 2 (0)|
00:00:01 |
|* 1 | COUNT STOPKEY | | | | |
|
| 2 | TABLE ACCESS FULL| PD | 1 | 983 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
8556 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
PROD:
...
Elapsed: 00:02:13.61
Execution Plan
Plan hash value: 1087134000
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 993 | 4 (0)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | TABLE ACCESS FULL| PD | 1 | 993 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter(ROWNUM<=1)
Statistics
0 recursive calls
0 db block gets
11290619 consistent gets
11289575 physical reads
8024 redo size
8510 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
First thing that jumps out is the number of consistent gets, which is in the same range in prod again and again. In test also, the consistent gets are in the same range for repeated executions - just couple of dozens.
I checked the stats and here they are:
Test:
TABLE_NAME NUM_ROWS LAST_ANAL BLOCKSAVG_ROW_LEN PCT_FREE PCT_USED EMPTY_BLOCKS AVG_SPACE
------------------------ ------------------------ --------- ----------------------- ---------- ---------- ------------ ---------- CHAIN_CNT PAR MON COMPRESS
---------- --- --- --------
PD 989,633 25-FEB-21 143,844
983 10 0 0
0 NO YES DISABLED
PROD:
TABLE_NAME NUM_ROWS LAST_ANAL BLOCKSAVG_ROW_LEN PCT_FREE PCT_USED EMPTY_BLOCKS AVG_SPACE
------------------------ ------------------------ --------- ----------------------- ---------- ---------- ------------ ---------- CHAIN_CNT PAR MON COMPRESS
---------- --- --- --------
PD 1,420,080 29-APR-21 11,537,288
993 10 0 0
0 NO YES DISABLED
What is confusing is that the number of blocks the table takes in the prod. I did some calc and I feel that it should take around 172,000+ blocks, given our block size is 8K, but I am unable to explain 11.5M blocks.
19c, if that helps. Table definitions are identical in prod and test. Recollecting the table stats in prod at 50% has not helped with the query which I did few minutes ago. Stats almost remain the same - just the number of rows has increased by a few hundred. I see some parallel queries running against the prod table for 30+ hours.
Thanks a lot,
Ram.
-- http://www.freelists.org/webpage/oracle-lReceived on Sat May 01 2021 - 00:39:43 CEST