RE: peeking into global temporary table from other session and some tunning - plans attached

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of GG Sent: Sunday, November 26, 2017 8:06 AM
To: andysayer_at_gmail.com
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:
>
> 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?)
>
Thanks Andy, I totally agree that makes no sense, app is populating gtt and then adding some more predicates to it (why just not populate already clean resultset:) ) . Moreover the bind values are not related to gtt itself (aliased as d) maybe only due to transitive closure . I think it could be wise to take a look on table aliased as 'm' and its stats but the impact would be global (in case of stats refresh, meaning all queries using 'm' ) .

So my ideas to check:

- Directing Plans with Baselines/Profiles Using coe_load_sql_baseline.sql / coe_load_sql_profile.sql (shipped with SQLT) (Doc ID 1400903.1)
- How to Move SQL Profiles from One Database to Another (Including to Higher Versions) (Doc ID 457531.1)
- injecting dynamic_sampling(4) or 8 to such queries (via sql_patch or profile or baseline)

I hope that during weekend runs on different test/dev environments I can see some good performing plans and create baseline on them, then move those baselines to prod (even if sqlid differs) .

Regards .
G

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


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