Re: Query with same plan running longer

Thank you Jonathan.

Never heard of those 'passthru' statistics and kind of ignored it initially but now I can understand those. Also never encountered issues due to sql quarantine, but just read through a few blogs on sql quarantining. We have moved the tablespace to TDE in the recent past but this issue popped up after some days. And now I am seeing the dba_hist_cell_open_alerts and the problematic plans are quarantined under severity critical.

To see if the offloading is only impacting that exact sql_id, I tried with different sql text(by adding dummy hints) to have a different sql_id, but even with that the offloading is not happening to 90%+. So it points to the fact that the specific plan/plan_hash_value has been quarantined and any query with the same plan_hash_value is going to be pushed to the DB layer for processing and will see the performance hit. Also the alert is pointing to exact sql_plan_line_id i.e. full scan of TAB1.

And also it points to the fact that "blocks decrypted" statistics on the higher side for the slow query is just the result of the quarantine plan issue. And considering this quarantine can be because of any reason which we are not aware of (something like Ora-600 error) , we may have to reach out to support to get fix for this. But yes, here it seems to be somehow related to encryption as these quarantine plan alerts pop up after tde only. Correct me if I'm wrong.

*Message in dba_hist_cell_open_alerts looks like below:-*

"A SQL PLAN quarantine has been added. As a result, Smart Scan is disabled for SQL statements with the quarantined SQL plan. <exadata:br/> Quarantine id : 51 <exadata:br/> Quarantine type : SQL PLAN <exadata:br/> Quarantine reason : Crash <exadata:br/> Quarantine Plan : SYSTEM <exadata:br/> Quarantine Mode : FULL_Quarantine <exadata:br/> DB Unique Name : DB6 <exadata:br/> Incident id : 1 <exadata:br/> SQLID  : XXXXXXXX <exadata:br/> SQL Plan details : {SQL_PLAN_HASH_VALUE=8979798797, PLAN_LINE_ID=6} <exadata:br/> In addition, the following disk region has been quarantined, and Smart Scan will be disabled for this region: <exadata:br/> Disk Region : {Grid Disk Name=DATA_DB6_cel06, offset=1604518412288, size=1M} <exadata:br/>"

"A SQL PLAN quarantine has been added. As a result, Smart Scan is disabled for SQL statements with the quarantined SQL plan. <exadata:br/> Quarantine id : 43 <exadata:br/> Quarantine type : SQL PLAN <exadata:br/> Quarantine reason : Crash <exadata:br/> Quarantine Plan : SYSTEM <exadata:br/> Quarantine Mode : FULL_Quarantine <exadata:br/> DB Unique Name : DB6 <exadata:br/> Incident id : 1 <exadata:br/> SQLID  : XXXXXXXX <exadata:br/> SQL Plan details : {SQL_PLAN_HASH_VALUE=1439998345, PLAN_LINE_ID=6} <exadata:br/> In addition, the following disk region has been quarantined, and Smart Scan will be disabled for this region: <exadata:br/> Disk Region : {Grid Disk Name=DATA_DB6_cel01, offset=1555867631616, size=1M} <exadata:br/>"

On Tue, Mar 1, 2022 at 7:27 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> This looks like it might be helpful.
>
> https://exadatadba.blog/2021/09/21/exadata-quarantines-not-your-typical-oracle-quarantine/
>
>
> On Tue, 1 Mar 2022 at 13:07, Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>> Obvious guess number 2 would have been about storage indexes
>> Obvious guess number 3 would have been about flash cache
>> I don't think I would have got to passthru without the statistics in
>> front of me.
>>
>> If you don't look at your own results and think about them, and don't
>> show us any relevant information, we just have to keep guessing until you
>> confirm a guess; and that's a huge waste of effort. You spotted the
>> passthru - what did you discover about that. Didn't you notice the "blocks
>> decrypted"?
>>
>> NAME Diff
>> blocks decrypted 11,058,075
>> cell physical IO interconnect bytes 90,600,000,000
>> cell num bytes in passthru due to quarantine 90,600,000,000
>> cell num smart IO sessions using passthru mode due to cellsrv 1
>> cell physical IO interconnect bytes returned by smart scan 90,600,000,000
>> cell num bytes in passthru during predicate offload 90,600,000,000
>>
>> All your extra I/O corresponds to passthru - and the number of blocks
>> decrypted (which could consume a lot of CPU) by the database is a good
>> match the interconnect bytes. And there's a message there about quarantine
>> - which should be investigated. And all that passthru happens during smart
>> scan and predicate offload.
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> On Tue, 1 Mar 2022 at 11:02, Lok P <loknath.73_at_gmail.com> wrote:
>>
>>> Thank you Jonathan.
>>> I have checked v$stataname but have not seen any statistics with the
>>> name '%load balanc%', is that statistics you referring to having a
>>> different name? And here table TAB1 is not compressed and is not
>>> partitioned too.
>>>
>>>

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