Re: High consistent Gets and response time in prod

Various scenarios could produce this.
As others have said you need more information - Sayan suggested Tanel's "snapper" which is a tool that everyone should know about, but if you haven't used it before

Start a new session
run the query

select

        name, value
from

        V$mystat
join v$statname
using

        (statistic#)
where

        value != 0
/

That will tell us what sort of work the session has done. There are two obvious possibilities:
* it's doing a tablescan of a very large table which has had almost all the data deleted butbut the blocks haven't been cleaned out properly. * it's doing a huge amount of work to get read-consistent versions of blocks.

In the first case we will see lots of multiblock reads, in the second we'll see lots of "xxxx - undo records applied".

You mentioned the 30 hour parallel query - are these actually related to DML, have they got uncommitted transactions sitting behind them Check v$lock for locks TM locks on the table Check v$transaction for transactions that have done a lot of work (used_urec) and started a long time in the past.

You could also query v$session_event
select event, total_waits, time_waited
from v$session_event
where sid = sys_context('userenv','sid') order by

        time_waited
/

And, of course, you could enable extend tracing (10046 level 8) before running the query to get a detailed sequence of activity for the session

Regards
Jonathan Lewis

P.S. The fact that the table is 11.2M blocks (when it looks like it should be less) is interesting - and the 11.5M physical reads suggests a very long (empty) tablescan to find the first row with a few hundred thousand reads of undo to check commit times ("transaction table consistent reads - undo records applied"). There's a hint of big processes doing "delete everything, insert it all again" and not cleaning up the space management bitmaps properly. (Oracle has a history of this, but I don't know if it can still happen in 19c.)

On Fri, 30 Apr 2021 at 23:39, Ram Raman <veeeraman_at_gmail.com> wrote:

> Hi,
>
> Fri evening and I have a problem. grrr..
>
> We have a simple SELECT statement like this:
>
> select * from c.pd where rownum <=1
>
>
>
> ---------------------------------------------------------------------------------
> | 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.
>
>
> TABLE_NAME NUM_ROWS LAST_ANAL BLOCKS
> AVG_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.
>
>
>

--
http://www.freelists.org/webpage/oracle-l