Re: 19c DISPLAY_AWR deprecated in favor of DISPLAY_WORKLOAD_REPOSITORY = Confused

The queries appear to be captured in the workload repository tables (DBA_HIST*) . I cannot get any of the saved sqlids to display historical plans via DBMS_XPLAN.DISPLAY_AWR or DBMS_XPLAN.DISPLAY_WORKLOAD_REPOSITORY.

I feel like it has to be something I'm doing wrong in 19c where the AWR data is stored at the CDB level and not in the PDB itself but having difficulty figuring out exactly *what *it is I'm missing at the moment.

Thanks,
Chris

On Thu, Jan 12, 2023 at 1:15 PM Powell, Mark <mark.powell2_at_dxc.com> wrote:

> Are you sure the plan meets the requirements to be saved in the AWR (run
> time, CPU, etc...)?
> Can you find the current plan using dbms_xplan.display_cursor?
>
> Mark Powell
> Database Administration
> (313) 592-5148
>
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Chris Taylor <christopherdtaylor1994_at_gmail.com>
> *Sent:* Thursday, January 12, 2023 12:08 PM
> *To:* oracle-l_at_freelists.org <oracle-l_at_freelists.org>
> *Subject:* 19c DISPLAY_AWR deprecated in favor of
> DISPLAY_WORKLOAD_REPOSITORY = Confused
>
> Ok, so my DBMS_XPLAN.DISPLAY_AWR scripts in 19c (19.14) do not seem to be
> working.
> No problem, I check the docs for 19c and there's a note to start using
> DISPLAY_WORKLOAD_REPOSITORY. Ok, I can do that.
>
> However, neither is returning a plan for a stored SQL_ID and
> plan_hash_value and I'm very confused.
>
> Setup:
> 19.14 with AWR in the CDB (not in the PDB) - Snapshots only occur at the
> CDB level on this db.
> One PDB per CDB.
>
> I check DBID and CON_DBID and have played around with the arguments but it
> says it cannot find a result.
>
> Anyone run into this and if so, how did you get it to work in 19c? (I'm
> still investigating but wanted to post here in case someone ran into this
> already and got it to work)
>
> Thanks,
> Chris
>
>
>
>

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