Re: peeking into global temporary table from other session and some tunning - plans attached
Date: Fri, 24 Nov 2017 20:57:25 +0100
Message-ID: <205b6bb8-5cd9-815e-4bf3-e6c505c2a381_at_interia.pl>
W dniu 2017-11-24 o 20:35, Andy Sayer pisze:
> If the query is the same one being run each time then it’s only going
> to do a hard parse (and dynamically sample) once rather than once per
> execution (well it will reparse when the cursor cache is flushed or if
> the sql is invalidated somehow). I find it much more likely that the
> cardinalities you are seeing in the execution plan were very correct
> for the first execution, but further executions of the process were
> just not the same.
>
> Are you able to know in advance what the size of the GTT will be
> before you call this process? It may be worth having two separate
> SQLs, one for the nested loop, the other for the hash join. The SQLs
> only need to be different enough to not share a sql_id, dynamic
> sampling will then kick in for the first parse of either query and you
> should get an accurate picture of the size of these tables and how the
> join is expected to behave, and therefore a decent plan for each version.
>
> If you upgrade to 12c you could take advantage of private GTT
> statistics, meaning your sessions that are using the GTT can gather
> stats without effecting any other session. This will cause your SQL to
> hard parse (it must in order to take advantage of the new statistics).
>
> How is the GTT being populated? You might actually find that using
> that query instead as a subquery is a better idea as the CBO may be
> able to apply the predicates you are using to influence it’s decision
> (should the predicates be trigger ACS). Additionally, better plans
> might be available.
>
Hi,
thanks for the comment, well the gtt seems to be populated by insert /+* append */ logic and there is comment added (by app logic) to force reparse . The queries itself: EXPORT111111 = gtt
--good
SQL Text
select d.nodetype, d.nodeid, d.sortsec, d.sortkey, d.sortlevel,
m.attrid, m.qualid linkqualid, m.valnocopy, m.rev maprev, v.unitid, v.VALUE1, v.domainid, v.valno, v.qualid valqualid, v.rev valrev, x.externalid from valuemodel x, qualifiers c, VALUE1 v, VALUEMMM m,EXPORT111111 d, qualifiers b where m.auxtype = d.nodetype and m.auxid = d.nodeid and d.edgetype not in (200, 201, 310, 311) and d.edgetype not between 591 and 609 and m.qualid = b.qualid and b.selected = :1 and v.valno = m.valno and
v.domainid = m.domainid and v.qualid = c.qualid and c.selected = :2 and v.present != 201 and exists (select null from WWWWspaces w where m.present = w.WWWW and w.selWWWW = :3 ) and exists (select null fromWWWWspaces w where v.present = w.WWWW and w.selWWWW = :4 ) and 1=decode(d.edgetype,8,1,-311,1,0) and m.valno = x.valno and m.domainid = x.domainid order by d.sortsec, d.sortkey, d.sortkey2, d.sortlevel, d.edgeid, m.attrid, b.quallevel, c.quallevel, m.valnocopy /* 22 */
Global Information
Status : EXECUTING
Instance ID : 1
Session : USERSYS (924:33341)
SQL ID : 45565rk8qwd30
SQL Execution ID : 16777219
Execution Started : 11/22/2017 17:51:40
First Refresh Time : 11/22/2017 17:52:00
Last Refresh Time : 11/22/2017 17:54:32
Duration : 174s
Module/Action : JDBC Thin Client/-
Service : SYS$USERS
Program : JDBC Thin Client
Binds
| Name | Position | Type |
Value |
| :1 | 1 | NUMBER | 1622488 | | :2 | 2 | NUMBER | 1622488 | | :3 | 3 | NUMBER | 203 | | :4 | 4 | NUMBER | 203 |
Global Stats
| Elapsed | Cpu | IO | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes |
| 172 | 5.48 | 167 | 2M | 17036 | 133MB |
SQL Plan Monitoring Details (Plan Hash Value=1099905145)
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail | Progress | | | | |(Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | (%) | (# samples) | |
| 0 | SELECT STATEMENT | | | | | | 1 | | | | | | | | | -> 1 | SORT ORDER BY || 114K | 517K | 153 | +20 | 1 | 0 | | | 60M | | | |
| -> 2 | NESTED LOOPS | | | | 153 | +20 | 1 | 350K | | | | | | | | -> 3 | NESTED LOOPS || 114K | 509K | 153 | +20 | 1 | 350K | | | | | | |
| 4 | HASH JOIN RIGHT SEMI | | 117K | 474K | 153 | +20 | 1 | 350K | | | 758K | 0.57 | Cpu (1) | | | 5 | INDEX RANGE SCAN | WWWWSPACES_PK| 5 | 1 | 1 | +20 | 1 | 5 | | | | | | |
| -> 6 | HASH JOIN | | 187K | 474K | 153 | +20 | 1 | 350K | | | 1M | | | | | 7 | INDEX RANGE SCAN | QUALIFIERS_SORTIX |13 | 1 | 1 | +20 | 1 | 13 | | | | | | |
| -> 8 | NESTED LOOPS | | | | 153 | +20 | 1 | 350K | | | | | | |
| -> 9 | NESTED LOOPS | | 348K | 474K | 153 | +20 | 1 | 387K | | | | | | |
| -> 10 | HASH JOIN | | 348K | 266K | 153 | +20 | 1 | 350K | | | 1M | | | | | 11 | INDEX RANGE SCAN | QUALIFIERS_SORTIX| 13 | 1 | 1 | +20 | 1 | 13 | | | | | | |
| -> 12 | HASH JOIN RIGHT SEMI | | 445K | 266K | 153 | +20 | 1 | 350K | | | 819K | | | | | 13 | INDEX RANGE SCAN | WWWWSPACES_PK| 5 | 1 | 1 | +20 | 1 | 5 | | | | | | |
| -> 14 | NESTED LOOPS | | | | 153 | +20 | 1 | 351K | | | | | | |
| -> 15 | NESTED LOOPS | | 712K | 266K | 153 | +20 | 1 | 351K | | | | | | |
| -> 16 | TABLE ACCESS FULL | EXPORT111111 | 288K | 6356 | 153 | +20 | 1 | 13340 | | | | | | 0% |
| 17 | INDEX RANGE SCAN | VALUEMMM_UIX | 2 | 1 | 153 | +20 | 15758 | 351K | 160 | 1MB | | 1.72 | Cpu (1) | |
| | | | | | | | | | | | | | db file parallel read (1) | |
| | | | | | | | | | | | | | db file sequential read (1) | |
| -> 18 | TABLE ACCESS BY INDEX ROWID | VALUEMMM | 2 | 1 | 172 | +3 | 362K | 351K | 5309 | 41MB | | 21.26 | db file parallel read (32) | |
| | | | | | | | | | | | | | db file sequential read (5) | |
| 19 | INDEX RANGE SCAN | VALUE_PK | 1 | 1 | 166 | +7 | 360K | 387K | 3455 | 27MB | | 27.01 | Cpu (2) | |
| | | | | | | | | | | | | | db file parallel read (25) | |
| | | | | | | | | | | | | | db file sequential read (20) | | | 20 | TABLE ACCESS BY INDEX ROWID | VALUE1| 1 | 1 | 172 | +2 | 395K | 350K | 2693 | 21MB | | 11.49 | db file parallel read (15) | |
| | | | | | | | | | | | | | db file sequential read (5) | |
| 21 | INDEX UNIQUE SCAN | VALUEMODEL_PK | 1 | 1 | 169 | +4 | 364K | 350K | 4888 | 38MB | | 36.78 | db file parallel read (19) | |
| | | | | | | | | | | | | | db file sequential read (45) | |
| 22 | TABLE ACCESS BY INDEX ROWID | VALUEMODEL | 1 | 1 | 153 | +20 | 350K | 350K | | | | 0.57 | Cpu (1) | | =========================================================================================================================================================================================================== ---------------------------------------------------------bad
SQL Text
select d.nodetype, d.nodeid, d.sortsec, d.sortkey, d.sortlevel,
m.attrid, m.qualid linkqualid, m.valnocopy, m.rev maprev, v.unitid, v.VALUE1, v.domainid, v.valno, v.qualid valqualid, v.rev valrev, x.externalid from valuemodel x, qualifiers c, VALUE1 v, VALUEMMM m,EXPORT111111 d, qualifiers b where m.auxtype = d.nodetype and m.auxid = d.nodeid and d.edgetype not in (200, 201, 310, 311) and d.edgetype not between 591 and 609 and m.qualid = b.qualid and b.selected = :1 and v.valno = m.valno and
v.domainid = m.domainid and v.qualid = c.qualid and c.selected = :2 and v.present != 201 and exists (select null from WWWWspaces w where m.present = w.WWWW and w.selWWWW = :3 ) and exists (select null fromWWWWspaces w where v.present = w.WWWW and w.selWWWW = :4 ) and 1=decode(d.edgetype,8,1,-311,1,0) and m.valno = x.valno and m.domainid = x.domainid order by d.sortsec, d.sortkey, d.sortkey2, d.sortlevel, d.edgeid, m.attrid, b.quallevel, c.quallevel, m.valnocopy /* 21 */
Global Information
Status : EXECUTING
Instance ID : 1
Session : USERSYS (685:47393)
SQL ID : btay965futjwg
SQL Execution ID : 16777559
Execution Started : 11/23/2017 05:12:06
First Refresh Time : 11/23/2017 05:12:18
Last Refresh Time : 11/23/2017 08:51:18
Duration : 13154s
Module/Action : JDBC Thin Client/-
Service : SYS$USERS
Program : JDBC Thin Client
Binds
| Name | Position | Type |
Value |
| :1 | 1 | NUMBER | 1622492 | | :2 | 2 | NUMBER | 1622492 |
| :3 | 3 | NUMBER | 203 | | :4 | 4 | NUMBER | 203 |
Global Stats
| Elapsed | Cpu | IO | Concurrency | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes |
| 13228 | 617 | 12609 | 2.80 | 11M | 510K | 4GB | 85172 | 20GB |
SQL Plan Monitoring Details (Plan Hash Value=3893052121)
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail | Progress | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs |Bytes | | | (%) | (# samples) | |
| 0 | SELECT STATEMENT | | | | 1 | +49 | 1 | 0 | | | | | | | 0.01 | Cpu (1) | | | 1 | SORT ORDER BY | | 1M | 3M | | | 1 | || | | | | | | | |
| 2 | HASH JOIN | | 1M | 3M | | | 1 | | | | | | 17M | | | | | | 3 | HASH JOIN || 1M | 3M | 1 | +12 | 1 | 0 | | | | | 39M | | | | |
| 4 | TABLE ACCESS FULL | EXPORT111111 | 4M | 4279 | 12 | +1 | 1 | 72426 | | | | | | | 0.01 | Cpu(1) | |
| 5 | HASH JOIN | | 23M | 2M | 1 | +12 | 1 | 0 | | | | | 1M | | | | | | 6 | INDEX RANGE SCAN | QUALIFIERS_SORTIX | 12 | 1 | 1 | +12 | 1 | 11 || | | | | | | | |
| 7 | HASH JOIN RIGHT SEMI | | 31M | 2M | 1 | +12 | 1 | 0 | | | | | 791K | | | | | | 8 | INDEX RANGE SCAN | WWWWSPACES_PK | 5 | 1 | 1 | +12 | 1 | 5 || | | | | | | | |
| -> 9 | HASH JOIN | | 44M | 2M | 13152 | +3 | 1 | 0 | | | 84622 | 20GB | 133M | 22G | 4.07 | Cpu(280) | |
| | | | | | | | | | | | | | | | | direct path write temp (255) | |
| 10 | TABLE ACCESS FULL | VALUEMMM | 145M | 744K | 1133 | +2 | 1 | 201M | 30456 | 326MB | | | | | 5.54 | Cpu (34) | 100% |
| | | | | | | | | | | | | | | | | db file scattered read (108) | | | | | | || | | | | | | | | | | | db file sequential read (303) | |
| | | | | | | | | | | | | | | | | read by other session (282) | | | -> 11 | HASH JOIN RIGHT SEMI | | 75M | 935K | 12021 | +1134 | 1 | 127M || | | | 759K | | 0.20 | Cpu (26) | |
| 12 | INDEX RANGE SCAN | WWWWSPACES_PK | 5 | 1 | 1 | +1134 | 1 | 5 | | | | | | | | | | | -> 13 | NESTED LOOPS | | | | 12021 | +1134 | 1 | 128M | | || | | | 0.18 | Cpu (23) | |
| -> 14 | NESTED LOOPS | | 120M | 934K | 12021 | +1134 | 1 | 159M | | | | | | | | | |
| 15 | INDEX RANGE SCAN | QUALIFIERS_SORTIX | 12 | 1 | 867 | +1134 | 1 | 11 | | | | | | | | | |
| -> 16 | INDEX RANGE SCAN | VALUE_NODE2_FKIX | 2M | 707 | 12021 | +1134 | 5033 | 159M | 232K | 2GB | | | | | 44.44 | latch: cache buffers chains (2) | |
| | | | | | | | | | | | | | | | | Cpu (85) | | | | | | || | | | | | | | | | | | db file parallel read (132) | |
| | | | | | | | | | | | | | | | | db file scattered read (5) | |
| | | | | | | | | | | | | | | | | db file sequential read (1586) | |
| | | | | | | | | | | | | | | | | read by other session (4024) | |
| -> 17 | TABLE ACCESS BY INDEX ROWID | VALUE1 | 10M | 77870 | 12021 | +1134 | 161M | 128M | 248K | 2GB | | | | | 45.56 | latch: cache buffers chains(1) | |
| | | | | | | | | | | | | | | | | Cpu (181) | | | | | | | || | | | | | | | | | | db file parallel read (188) | |
| | | | | | | | | | | | | | | | | db file scattered read (38) | |
| | | | | | | | | | | | | | | | | db file sequential read (1695) | |
| | | | | | | | | | | | | | | | | read by other session (3879) | |
| 18 | TABLE ACCESS FULL | VALUEMODEL | 162M | 131K | | | | | | | | | | | | | | =====================================================================================================================================================================================================================================
G
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Nov 24 2017 - 20:57:25 CET