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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Mohamed Houri
Sent: Friday, May 15, 2020 5:01 PM
To: dmarc-noreply@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx<mailto:dmarc-noreply@xxxxxxxxxxxxx>> 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
--
//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=>