I think you suggested earlier that you could inject hints.
IF that is correct and IF you know the number of rows inserted into the gtt in
the current session, then perhaps using that number as a cardinality hint will
be useful.
IF multiple queries additionally use different values for predicates on the gtt
and IF you know the counts by predicate (again on the gtt), you can possibly
inject that more accurate cardinality for each query.
IF these apply to you, then your known cardinality from the gtt is cheaper than
dynamic statistics (and more accurate unless you're on a version that evaluates
gtt statistics per session instead of on all sessions and can generate
histograms on gtts). Since the number in the cardinality hint may vary, you
should get a fresh parse for each, so if parse rate is a challenge, this may
not be for you unless the plan improvement exceeds the cost of the additional
parses (including any concurrency delays).
Your mileage may vary.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of GG
Sent: Sunday, November 26, 2017 8:06 AM
To: andysayer@xxxxxxxxx
Cc: ORACLE-L
Subject: Re: peeking into global temporary table from other session and some
tunning - plans attached
W dniu 2017-11-26 o 12:43, Andy Sayer pisze:
Thanks Andy, I totally agree that makes no sense, app is populating gtt and
GG, to confirm what bind variables were used when the statement was
first parsed, you could use:
select * from
table(dbms_xplan.display_cursor(sql_id=>'btay965futjwg',format=>'advan
ced
-projection'));
However, this bind variables in this statement seem to have nothing to
do with what went into populating the GTT so it might not really help,
other than to prove that the statement was parsed with differing bind
variables being peeked. You say that you should be getting a new SQL
each time the application executes it due to the comment, if that were
the case then I would expect the dynamic sampling to figure out the
number of rows in a table much better. In my experiments, it does seem
easy for the CBO to get it slightly wrong but I don't think this would
ever be the sort of scale you're looking at. It is probably easier to
get it wrong when you are applying further predicates against the GTT
(which seems weird to me, why not just not populate those rows to
begin with?)
