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=>'advanced
> -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