Re: Spd 19c adaptive statistics

From: Moustafa Ahmed <moustafa_dba_at_hotmail.com>
Date: Wed, 6 Jan 2021 21:03:17 +0000
Message-ID: <BN6PR06MB2785A800D1DF102D97712E48E1D00_at_BN6PR06MB2785.namprd06.prod.outlook.com>

That is great!

1-I have noticed filtering by <spd> works for 12c Yet on 19c latest release it is only spd with <> Anyone saw the same?

2-will a sql with v$sql is_reoptimized = Y Show on dba_hist (other_xml) with spd string? As a proof adaptive stats kicking in ?
I have seen sql’s with v$ on that reoptimized = Y Yet when spilled to disk other_xml never had any spd values..?

On Jan 6, 2021, at 5:55 AM, Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:


Hi Moustafa,

You can try this query:

select
  sql_id

 ,plan_hash_value
 ,id
 ,spd.*

from dba_hist_sql_plan p
 ,xmltable(

     '//spd'
     passing xmltype(other_xml)
     columns spd xmltype path '.'
     )(+) spd

where lower(other_xml) like '%<spd>%';

On Wed, Jan 6, 2021 at 1:20 PM Moustafa Ahmed <moustafa_dba_at_hotmail.com<mailto:moustafa_dba_at_hotmail.com>> wrote: Stefan

Is there a way to find when this happened in the best and for which sql’s(dba_hist_xxx)?

> On Jan 6, 2021, at 1:09 AM, Stefan Koehler <contact_at_soocs.de<mailto:contact_at_soocs.de>> wrote:
>
> Stefan

--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

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

Received on Wed Jan 06 2021 - 22:03:17 CET