Re: sql disappears from v$sql as soon as it finishes running?

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Fri, 15 May 2020 23:00:52 +0200
Message-ID: <CAJu8R6ha9vBzSwRisrsLx91nvC58ZnZLN2onMFX_YqnPqF4U+g_at_mail.gmail.com>



While your SQL statement is runing you can get the corresponding SQL monitoring report as it contains both Estimatimated and Actual rows. You can also get the execution plan from memory as long as your SQL statement is still runing.

Is you SQL statement a create table or an index rebuild or such kind of DDL? Indeed those kind of statements are not kept in memory by Oracle as soon as they finished because, understandbly, they are not going to be re-executed.

Best regards
Mohamed Houri

Le ven. 15 mai 2020 à 19:52, Redacted sender Jay.Miller for DMARC < dmarc-noreply_at_freelists.org> a écrit :

> I'm trying to run xbi on a sql id coming from a batch job. They've added
> the gather statistics hint so I should be able to see estimated vs. actual
> rows.
>
> However the query runs for about 5 hours (showing in v$sql the whole time)
> but as soon as it completes it vanishes from v$sql and therefore xbi
> returns no data.
>
> Does anyone have any suggestions on how to prevent this from happening?
> Google has been no help for the last 20 minutes of searching.
>
> Oracle 12.1.0.2
> There is no minimum shared_pool parameter set, sga_target is 64G
>
> Thank you!
>
> Jay Miller
> Sr. Oracle DBA
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri May 15 2020 - 23:00:52 CEST

Original text of this message