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

My concern with getting data while it was still running was that I assumed not all the actual rows would be populated before it completes.

I suppose I could still get most of it though.

Jay Miller
Sr. Oracle DBA
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mohamed Houri Sent: Friday, May 15, 2020 5:01 PM
To: dmarc-noreply_at_freelists.org
Cc: ORACLE-L
Subject: Re: sql disappears from v$sql as soon as it finishes running?

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<mailto: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<https://urldefense.proofpoint.com/v2/url?u=http-3A__www.freelists.org_webpage_oracle-2Dl&d=DwMFaQ&c=nulvIAQnC0yOOjC0e0NVa8TOcyq9jNhjZ156R-JJU10&r=aiKV3Uv2Wo7GqYQcis9TSvB1MZslPOnintrOY1rjG58&m=0n3mzNCfTDhx8729KVn5e5sDLP-7QSGoBkgDkuE2gz8&s=GvC9F9zjen7OryUeYQAdzTHfGdI7eKFmZYwcGLB9LWU&e=>

--

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