General Information

Task Information:
Workload Information:
Task Name : 10g_11g_spa
Task Owner : SYS
Description : experiment for 10gR2 to 11gR2 upgrade
SQL Tuning Set Name : MAC_SPA
SQL Tuning Set Owner : SYS
Total SQL Statement Count : 330

Execution Information:
Execution Name : compare_10g_112_buffergets
Execution Type : COMPARE PERFORMANCE
Description : Compare 10g SQL T...
Scope : COMPREHENSIVE
Status : COMPLETED
Number of Unsupported SQL : 39
Started : 03/10/2013 07:28:40
Last Updated : 03/10/2013 07:28:47
Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED
Number of Errors : 64

Analysis Information:
Before Change Execution:
After Change Execution:
Execution Name : 10g_trail
Execution Type : CONVERT SQLSET
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 03/10/2013 07:24:41
Last Updated : 03/10/2013 07:24:42
Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED
Execution Name : 11g_trail
Execution Type : TEST EXECUTE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 03/10/2013 07:25:15
Last Updated : 03/10/2013 07:25:26
Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED
Number of Errors : 64
Comparison Metric: BUFFER_GETS
Workload Impact Threshold: 1%
SQL Impact Threshold: 1%

Report Summary

Projected Workload Change Impact:
Overall Impact : -222.67%
Improvement Impact : 7.72%
Regression Impact : -230.39%

SQL Statement Count
SQL Category SQL Count Plan Change Count
Overall 330 70
Improved 4 1
Regressed 2 2
Unchanged 221 67
with Errors 64 0
Unsupported 39 0

Top 100 SQL Sorted by Absolute Value of Change Impact on the Workload

object_id
sql_id 		Impact on
Workload 		Execution
Frequency 		Metric
Before 		Metric
After 		Impact
on SQL 		Plan
Change
819 7cq8d0jqxzum1 -227.73% 3517214 77.4372830313993 336 -333.9% y
676 0h6b2sajwb74n -2.66% 9192692 3.84393940316939 5 -30.07% y
677 0k8522rmdzg4k 2.3% 323186 32.4440848304072 4 87.67% n
797 693zq1f3xndjq 1.82% 22960 316.000130662021 0 100% n
980 g5wrrtj2t3v56 1.82% 22960 316.000043554007 0 100% n
977 g337099aatnuj 1.79% 2097 3738.48831664282 336 91.01% y
882 a1mbfp580hw3k .56% 321444 8.01248740060477 1 87.52% n
912 bf5u4t4dn33fp -.52% 134 459.5 16088 -3401.2% y
762 47a50dvdgnxc2 .41% 321445 6.12210487019552 1 83.67% n
850 8mrg623amm8rx .26% 1007 1300.46077457795 289 77.78% y
895 aq8yqxyyb40nn .16% 321445 3.03420491841528 1 67.04% n
739 31a13pnjps7j3 .14% 16 35637 128 99.64% y
735 2uunxrv2wj57b .08% 68880 5.43012485481998 1 81.58% y
846 8cuu0uynbrhbn .07% 275530 3.00274380285268 2 33.39% y
938 cjaa80k1hvpc1 .05% 92152 3.00036895563851 1 66.67% n
988 ggyn905py8mt5 .05% 22960 9.00365853658537 1 88.89% y
703 1drcud8h9f38b .04% 22960 9.07857142857143 2 77.97% n
922 bznx2bj4cyq6c .03% 22960 9.03310104529617 3 66.79% y
751 3h1g1sszzr4xr .03% 22960 9.03244773519164 3 66.79% y
712 1ua7x8h4kptvt .03% 23194 7.96136931965163 3 62.32% y
976 g00cj285jmgsw .02% 52125 2.89181774580336 1 65.42% n
666 06xju6khjr73a .02% 45920 3.00415940766551 1 66.71% y
972 fqnjcr2jbqpsc .02% 22960 6.00013066202091 2 66.67% y
795 6769wyy3yf66f .02% 15632 7.42259467758444 2 73.06% n
937 chsyr0gssbuqf .02% 4405 31.0020431328036 14 54.84% n
979 g50sv3qtu91ac .02% 22960 4.06702961672474 1 75.41% y
945 czbjavaavnum5 .02% 22960 4.04677700348432 1 75.29% n
919 bypx1rgz00158 .02% 22960 5.01655052264808 2 60.13% n
843 87qkab0x1s3xr .02% 22960 5.01585365853659 2 60.13% n
710 1p6n2jxrca2a8 .02% 22960 3.00121951219512 0 100% n
892 amwaz5brtm76u .02% 22960 3 0 100% n
866 96g93hntrzjtr .01% 62327 2.86387921767452 2 30.16% n
947 d3rfnc4ubc57j .01% 22960 3.05587979094077 1 67.28% n
832 7vbdnn98ynscx .01% 22960 4.0455574912892 2 50.56% n
691 120sawa70z3ht .01% 22960 2.0293118466899 0 100% n
844 88cwftrsh6rjb .01% 22960 2.02787456445993 0 100% n
820 7f9sk6wcpy76f .01% 22960 3.0140243902439 1 66.82% y
761 459f3z9u4fb3u .01% 23002 4.00026084688288 2 50% n
773 4yvsj6a508pgf .01% 45920 2 1 50% n
887 a9qtc5suhfqs8 .01% 45920 1 0 100% n
935 cgc66x001ru5q .01% 22960 3 1 66.67% n
764 49d638dnzfsub -.01% 22960 1.00174216027875 3 -199.48% y
856 8vhktgq0r66ny -.01% 22960 1.00409407665505 3 -198.78% y
883 a1xgxtssv5rrp .01% 2283 31.0039421813403 14 54.84% y
982 g7z4n2kpnzw6f .01% 22960 2.1065331010453 1 52.53% n
915 bsa0wjtftg3uw .01% 11712 4.07317281420765 2 50.9% n
699 19x1189chq3xd .01% 22978 4.03860214117852 3 25.72% y
929 ca3m153v3y1qu .01% 22960 4.03493031358885 3 25.65% n
890 ak5crjygnpk60 .01% 22975 1.01562568008705 0 100% n
821 7ggm58t3935kt .01% 22960 3.01515679442509 2 33.67% n
701 1bpwuhy1jzjdg .01% 22960 2.00365853658537 1 50.09% n
810 6w58g883hbqxd .01% 22960 2.00060975609756 1 50.02% n
725 2gdu55njxq8cs .01% 22960 3 2 33.33% y
749 3c1kubcdjnppq 0% 15400 3.23792207792208 2 38.23% n
985 gb4qjzp9su4h4 0% 1 17740 62 99.65% y
722 2d1tg62apru59 0% 1 17731 60 99.66% n
878 9qgtwh66xg6nz 0% 3574 6.34667039731393 2 68.49% n
870 9f30br0xvyxxb 0% 4790 4.08830897703549 1 75.54% y
777 53saa2zkr6wc3 0% 6542 4.83598287985326 3 37.97% n
904 b3ys9bs1v9cch 0% 1 10372 3 99.97% y
885 a73wbv1yu8x5c 0% 1836 7.51416122004357 2 73.38% n
772 4w33a18a3ma73 0% 4594 3.08576404005224 1 67.59% y
824 7hngp2sru12zc 0% 1 9087 62 99.32% y
875 9p1um1wd886xb 0% 1304 7.31288343558282 2 72.65% y
855 8swypbbr0m372 0% 3136 5.15082908163265 3 41.76% n
842 86bbtavv6hwmr 0% 1 6351 0 100% n
966 fd9hn33xa7bph 0% 1174 6.30408858603066 1 84.14% n
768 4ng4xn7jw3dbb 0% 1 6061 3 99.95% y
724 2g9qjuvr2hju9 0% 803 8.42714819427148 1 88.13% n
896 aqhru5uwcvukg 0% 297 18.6767676767677 0 100% n
962 f6y537gy0p982 0% 861 7.28339140534262 1 86.27% n
876 9pudktzqq9ywb 0% 5035 3.07050645481629 2 34.86% y
704 1gfaj4z5hn1kf 0% 3112 3.69762210796915 2 45.91% n
854 8savyrs6b4cp2 0% 4779 2.10106716886378 1 52.41% n
813 71p20wq7rr9w4 0% 4777 2.09503872723467 1 52.27% n
765 4g4425d7a24k1 0% 4772 2.03206202849958 1 50.79% n
729 2mp99nzd9u1qp 0% 18 260.555555555556 2 99.23% n
905 b6b0ujx89mmnc 0% 1 4332 138 96.81% y
663 0103qb611xu3g 0% 400 11.195 1 91.07% n
898 attkdbbcm69h3 0% 1 4065 2 99.95% y
837 83taa7kaw59c1 0% 11643 3.34741905007301 3 10.38% n
826 7ng34ruy5awxq 0% 5121 8.75668814684632 8 8.64% y
927 c7sn076yz7030 0% 1 3933 336 91.46% y
894 aq4js2gkfjru8 0% 860 5.17906976744186 1 80.69% n
907 b7jn4mf49n569 0% 1 4951 1647 66.73% n
916 btzq46kta67dz 0% 2950 3.11322033898305 2 35.76% n
705 1gu8t96d0bdmu 0% 10270 4.3143135345667 4 7.29% n
991 guw87u8x36z8r 0% 2616 2.0447247706422 1 51.09% n
944 cvn54b7yz0s8u 0% 464 7.64655172413793 2 73.84% y
838 84ygtc6ucuafk 0% 400 7.2225 1 86.15% n
760 451q2032pshqm 0% 3136 8.38871173469388 9 -7.29% y
738 2ym6hhaq30r73 0% 2175 2.80781609195402 2 28.77% n
852 8q9hmgy8uvwtc 0% 29 59.551724137931 0 100% n
989 grwydz59pu6mc 0% 857 2.00816802800467 0 100% n
914 bhvyz9bgyrhb2 0% 16 107 3 97.2% n
695 16q5mbuwtzv6r 0% 767 3.08735332464146 1 67.61% n
830 7s2y99c3nsdz5 0% 297 7.21212121212121 2 72.27% n
767 4m7m0t6fjcs5x 0% 253 6.26482213438735 1 84.04% n
733 2syvqzbxp4k9z 0% 608 3.08881578947368 1 67.63% y
889 acg2mzntn54kc 0% 22960 2.05209059233449 2 2.54% n
Note: time statistics are displayed in microseconds


Report Details

SQL Details:
Object ID : 819
Schema Name : SYS
SQL ID : 7cq8d0jqxzum1
Execution Frequency : 3517214
SQL Text : delete from smon_scn_time where thread=0 and scn = (select min(scn) from smon_scn_time where thread=0)

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 80.24% .006408 .00048 92.51%
parse_time .000454
cpu_time 84.88% .006437 .000555 91.38%
user_io_time 0
buffer_gets -227.73% 77 336 -333.9%
cost 10747128.56% 799 103 87.11%
reads 2.07% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. The performance of this SQL has regressed.
  2. The structure of the SQL execution plan has changed.


Execution Plan Before Change:
Plan Hash Value : 2779095807

Id Operation Name Rows Bytes Cost Time
0 DELETE STATEMENT 799
1 . DELETE SMON_SCN_TIME
2 .. TABLE ACCESS BY INDEX ROWID SMON_SCN_TIME 1 15 2 00:00:01
3 ... INDEX UNIQUE SCAN SMON_SCN_TIME_SCN_IDX 1 1 00:00:01
4 .... SORT AGGREGATE 1 8
5 ..... TABLE ACCESS CLUSTER SMON_SCN_TIME 21880 175040 797 00:00:10
6 ...... INDEX UNIQUE SCAN SMON_SCN_TO_TIME_IDX 1 0

Execution Plan After Change:
Plan Id : 1061
Plan Hash Value : 1280645852

Id Operation Name Rows Bytes Cost Time
0 DELETE STATEMENT 1 15 103 00:00:02
1 . DELETE SMON_SCN_TIME
* 2 .. TABLE ACCESS BY INDEX ROWID SMON_SCN_TIME 1 15 2 00:00:01
* 3 ... INDEX UNIQUE SCAN SMON_SCN_TIME_SCN_IDX 1 1 00:00:01
4 .... SORT AGGREGATE 1 8
5 ..... TABLE ACCESS CLUSTER SMON_SCN_TIME 1503 12024 101 00:00:02
* 6 ...... INDEX UNIQUE SCAN SMON_SCN_TO_TIME_AUX_IDX 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 676
Schema Name : SYS
SQL ID : 0h6b2sajwb74n
Execution Frequency : 9192692
SQL Text : select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time -4.1% .000117 .000233 -98.76%
parse_time .000622
cpu_time -5.79% .000068 .000222 -224.58%
user_io_time 0
buffer_gets -2.66% 3 5 -30.07%
cost -40357.77% 2 3 -50%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 35
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (3):
  1. The performance of this SQL has regressed.
  2. The structure of the SQL execution plan has changed.
  3. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 4123551767

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 2
1 . CONNECT BY WITH FILTERING
2 .. TABLE ACCESS BY INDEX ROWID SYSAUTH$
3 ... INDEX RANGE SCAN I_SYSAUTH1 2 16 2 00:00:01
4 .. NESTED LOOPS
5 ... CONNECT BY PUMP
6 ... INDEX RANGE SCAN I_SYSAUTH1 2 16 2 00:00:01

Execution Plan After Change:
Plan Id : 918
Plan Hash Value : 1762314239

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 621 16146 3 00:00:01
* 1 . CONNECT BY NO FILTERING WITH START-WITH
2 .. INDEX FAST FULL SCAN I_SYSAUTH1 621 4968 2 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 677
Schema Name : SYS
SQL ID : 0k8522rmdzg4k
Execution Frequency : 323186
SQL Text : select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .62% .000603 .000103 82.92%
parse_time .000183
cpu_time .27% .000315 .000111 64.8%
user_io_time 0
buffer_gets 2.3% 32 4 87.67%
cost 0% 2 2 0%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 28 23
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. The performance of this SQL has improved.
  2. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 2057665657

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 2
1 . INLIST ITERATOR
2 .. INDEX RANGE SCAN I_SYSAUTH1 2 16 2 00:00:01

Execution Plan After Change:
Plan Id : 919
Plan Hash Value : 2057665657

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 17 136 2 00:00:01
1 . INLIST ITERATOR
* 2 .. INDEX RANGE SCAN I_SYSAUTH1 17 136 2 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 797
Schema Name : SYS
SQL ID : 693zq1f3xndjq
Execution Frequency : 22960
SQL Text : select 1 from "MACLEAN"."USLOG$_MVIEWA" where rownum = 1

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .09% .001082 .000063 94.18%
parse_time .000143
cpu_time .08% .000985 .000111 88.73%
user_io_time 0
buffer_gets 1.82% 316 0 100%
cost -1915.18% 83 102 -22.89%
reads .17% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. The performance of this SQL has improved.
  2. This SQL statement returned zero rows.


Execution Plan Before Change:
Plan Hash Value : 4119227072

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 83
1 . COUNT STOPKEY
2 .. TABLE ACCESS FULL USLOG$_MVIEWA 1 83 00:00:01

Execution Plan After Change:
Plan Id : 1039
Plan Hash Value : 4119227072

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 102 00:00:02
* 1 . COUNT STOPKEY
2 .. TABLE ACCESS FULL USLOG$_MVIEWA 1 102 00:00:02

Predicate Information (identified by operation id):

SQL Details:
Object ID : 980
Schema Name : MACLEAN
SQL ID : g5wrrtj2t3v56
Execution Frequency : 22960
SQL Text : delete from "MACLEAN"."USLOG$_MVIEWA"

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .03% .000405 .000045 88.88%
parse_time .000069
cpu_time .02% .000234 0 100%
user_io_time 0
buffer_gets 1.82% 316 0 100%
cost -1915.18% 83 102 -22.89%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. The performance of this SQL has improved.


Execution Plan Before Change:
Plan Hash Value : 3972615639

Id Operation Name Rows Bytes Cost Time
0 DELETE STATEMENT 83
1 . DELETE USLOG$_MVIEWA
2 .. TABLE ACCESS FULL USLOG$_MVIEWA 1 83 00:00:01

Execution Plan After Change:
Plan Id : 1222
Plan Hash Value : 3972615639

Id Operation Name Rows Bytes Cost Time
0 DELETE STATEMENT 1 102 00:00:02
1 . DELETE USLOG$_MVIEWA
2 .. TABLE ACCESS FULL USLOG$_MVIEWA 1 102 00:00:02

SQL Details:
Object ID : 977
Schema Name : SYS
SQL ID : g337099aatnuj
Execution Frequency : 2097
SQL Text : update smon_scn_time set orig_thread=0, time_mp=:1, time_dp=:2, scn=:3, scn_wrp=:4, scn_bas=:5, num_mappings=:6, tim_scn_map=:7 where thread=0 and scn = (select min(scn) from smon_scn_time where thread=0)

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .09% .011702 .000425 96.37%
parse_time .000384
cpu_time .1% .011828 .000555 95.31%
user_io_time 0
buffer_gets 1.79% 3738 336 91.01%
cost 6407.55% 799 103 87.11%
reads 1.03% 1 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. The performance of this SQL has improved.
  2. The structure of the SQL execution plan has changed.


Execution Plan Before Change:
Plan Hash Value : 1475282527

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 799
1 . UPDATE SMON_SCN_TIME
2 .. TABLE ACCESS BY INDEX ROWID SMON_SCN_TIME 1 1124 2 00:00:01
3 ... INDEX UNIQUE SCAN SMON_SCN_TIME_SCN_IDX 1 1 00:00:01
4 .... SORT AGGREGATE 1 8
5 ..... TABLE ACCESS CLUSTER SMON_SCN_TIME 21880 175040 797 00:00:10
6 ...... INDEX UNIQUE SCAN SMON_SCN_TO_TIME_IDX 1 0

Execution Plan After Change:
Plan Id : 1219
Plan Hash Value : 3378707914

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 1175 103 00:00:02
1 . UPDATE SMON_SCN_TIME
* 2 .. TABLE ACCESS BY INDEX ROWID SMON_SCN_TIME 1 1175 2 00:00:01
* 3 ... INDEX UNIQUE SCAN SMON_SCN_TIME_SCN_IDX 1 1 00:00:01
4 .... SORT AGGREGATE 1 8
5 ..... TABLE ACCESS CLUSTER SMON_SCN_TIME 1503 12024 101 00:00:02
* 6 ...... INDEX UNIQUE SCAN SMON_SCN_TO_TIME_AUX_IDX 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 882
Schema Name : SYS
SQL ID : a1mbfp580hw3k
Execution Frequency : 321444
SQL Text : select u1.user#, u2.user#, u3.user#, failures, flag, interval#, what, nlsenv, env, field1 from sys.job$ j, sys.user$ u1, sys.user$ u2, sys.user$ u3 where job=:1 and (next_date <= sysdate or :2 != 0) and lowner = u1.name and powner = u2.name and cowner = u3.name

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .23% .000246 .000062 74.82%
parse_time .000355
cpu_time -.19% .000078 .000222 -184.69%
user_io_time 0
buffer_gets .56% 8 1 87.52%
cost 5644.82% 4 0 100%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. This SQL statement returned zero rows.
  2. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 761852094

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 4
1 . NESTED LOOPS 1 357 4 00:00:01
2 .. NESTED LOOPS 1 343 3 00:00:01
3 ... NESTED LOOPS 1 329 2 00:00:01
4 .... TABLE ACCESS BY INDEX ROWID JOB$ 1 315 1 00:00:01
5 ..... INDEX UNIQUE SCAN I_JOB_JOB 1 0
6 .... TABLE ACCESS BY INDEX ROWID USER$ 84 1176 1 00:00:01
7 ..... INDEX UNIQUE SCAN I_USER1 1 0
8 ... TABLE ACCESS BY INDEX ROWID USER$ 84 1176 1 00:00:01
9 .... INDEX UNIQUE SCAN I_USER1 1 0
10 .. TABLE ACCESS BY INDEX ROWID USER$ 84 1176 1 00:00:01
11 ... INDEX UNIQUE SCAN I_USER1 1 0

Execution Plan After Change:
Plan Id : 1124
Plan Hash Value : 761852094

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 4290 0 00:00:01
1 . NESTED LOOPS 1 4290 0 00:00:01
2 .. NESTED LOOPS 1 4272 0 00:00:01
3 ... NESTED LOOPS 1 4254 0 00:00:01
* 4 .... TABLE ACCESS BY INDEX ROWID JOB$ 1 4236 0 00:00:01
* 5 ..... INDEX UNIQUE SCAN I_JOB_JOB 1 0 00:00:01
6 .... TABLE ACCESS BY INDEX ROWID USER$ 36 648 0 00:00:01
* 7 ..... INDEX UNIQUE SCAN I_USER1 1 0 00:00:01
8 ... TABLE ACCESS BY INDEX ROWID USER$ 36 648 0 00:00:01
* 9 .... INDEX UNIQUE SCAN I_USER1 1 0 00:00:01
10 .. TABLE ACCESS BY INDEX ROWID USER$ 36 648 0 00:00:01
* 11 ... INDEX UNIQUE SCAN I_USER1 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 912
Schema Name : SYS
SQL ID : bf5u4t4dn33fp
Execution Frequency : 134
SQL Text : SELECT /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.1') OPT_PARAM('_optimizer_cost_model' 'fixed') ALL_ROWS OUTLINE_LEAF(@"SEL$F5B21678") MERGE(@"SEL$ABDE6DFF") OUTLINE_LEAF(@"SEL$B01C6807") PUSH_PRED(@"SEL$B146ABC2" "S"@"SEL$2") OUTLINE_LEAF(@"SEL$B146ABC2") MERGE(@"SEL$427601E3") OUTLINE_LEAF(@"SEL$CB610AF7") MERGE(@"SEL$3B673E5C") OUTLINE_LEAF(@"SEL$6A03F9A0") PUSH_PRED(@"SEL$1" "OLD"@"SEL$1") OUTLINE_LEAF(@"SEL$1") OUTLINE(@"SEL$4") OUTLINE(@"SEL$ABDE6DFF") MERGE(@"SEL$6") OUTLINE(@"SEL$3") OUTLINE(@"SEL$B146ABC2") MERGE(@"SEL$427601E3") OUTLINE(@"SEL$2") OUTLINE(@"SEL$427601E3") MERGE(@"SEL$A1DD22CC") OUTLINE(@"SEL$18") OUTLINE(@"SEL$3B673E5C") MERGE(@"SEL$20") OUTLINE(@"SEL$17") OUTLINE(@"SEL$1") OUTLINE(@"SEL$5") OUTLINE(@"SEL$6") OUTLINE(@"SEL$7") OUTLINE(@"SEL$A1DD22CC") MERGE(@"SEL$D77A6ED7") OUTLINE(@"SEL$19") OUTLINE(@"SEL$20") OUTLINE(@"SEL$8") OUTLINE(@"SEL$D77A6ED7") MERGE(@"SEL$D71D5EE9") MERGE(@"SEL$E3DF9B48") OUTLINE(@"SEL$9") OUTLINE(@"SEL$D71D5EE9") MERGE(@"SEL$B584FDD1") OUTLINE(@"SEL$E3DF9B48") MERGE(@"SEL$42DFC41A") OUTLINE(@"SEL$13") OUTLINE(@"SEL$B584FDD1") MERGE(@"SEL$29F99543") OUTLINE(@"SEL$10") OUTLINE(@"SEL$42DFC41A") MERGE(@"SEL$12") OUTLINE(@"SEL$14") OUTLINE(@"SEL$29F99543") MERGE(@"SEL$16") OUTLINE(@"SEL$11") OUTLINE(@"SEL$12") OUTLINE(@"SEL$15") OUTLINE(@"SEL$16") NO_ACCESS(@"SEL$1" "NEW"@"SEL$1") NO_ACCESS(@"SEL$1" "OLD"@"SEL$1") LEADING(@"SEL$1" "NEW"@"SEL$1" "OLD"@"SEL$1") USE_NL(@"SEL$1" "OLD"@"SEL$1") FULL(@"SEL$B146ABC2" "X$KKSSQLSTAT"@"SEL$12") FULL(@"SEL$B146ABC2" "X$KGLCURSOR_CHILD_SQLIDPH"@"SEL$16") NO_ACCESS(@"SEL$B146ABC2" "S"@"SEL$2") LEADING(@"SEL$B146ABC2" "X$KKSSQLSTAT"@"SEL$12" "X$KGLCURSOR_CHILD_SQLIDPH"@"SEL$16" "S"@"SEL$2") USE_NL(@"SEL$B146ABC2" "X$KGLCURSOR_CHILD_SQLIDPH"@"SEL$16") USE_NL(@"SEL$B146ABC2" "S"@"SEL$2") INDEX(@"SEL$6A03F9A0" "DEFNS"@"SEL$17" ("WRI$_SQLSET_DEFINITIONS"."ID")) INDEX(@"SEL$6A03F9A0" "PLNS"@"SEL$17" ("WRI$_SQLSET_PLANS"."STMT_ID" "WRI$_SQLSET_PLANS"."PLAN_HASH_VALUE")) INDEX(@"SEL$6A03F9A0" "STMTS"@"SEL$17" ("WRI$_SQLSET_STATEMENTS"."ID")) INDEX(@"SEL$6A03F9A0" "STAT"@"SEL$17" ("WRI$_SQLSET_STATISTICS"."STMT_ID" "WRI$_SQLSET_STATISTICS"."PLAN_HASH_VALUE")) LEADING(@"SEL$6A03F9A0" "DEFNS"@"SEL$17" "PLNS"@"SEL$17" "STMTS"@"SEL$17" "STAT"@"SEL$17") USE_NL(@"SEL$6A03F9A0" "PLNS"@"SEL$17") USE_NL(@"SEL$6A03F9A0" "STMTS"@"SEL$17") USE_NL(@"SEL$6A03F9A0" "STAT"@"SEL$17") FULL(@"SEL$CB610AF7" "X$KZSPR"@"SEL$20") INDEX(@"SEL$B01C6807" "S"@"SEL$3" ("WRI$_SQLSET_STATEMENTS"."SQLSET_ID" "WRI$_SQLSET_STATEMENTS"."SQL_ID")) INDEX(@"SEL$B01C6807" "D"@"SEL$3" ("WRI$_SQLSET_DEFINITIONS"."ID")) LEADING(@"SEL$B01C6807" "S"@"SEL$3" "D"@"SEL$3") USE_NL(@"SEL$B01C6807" "D"@"SEL$3") FULL(@"SEL$F5B21678" "X$KZSPR"@"SEL$6") PUSH_SUBQ(@"SEL$CB610AF7") PUSH_SUBQ(@"SEL$F5B21678") END_OUTLINE_DATA */ /* CAPTURE SQLSET QUERY */ new.sql_seq, old.plan_hash_value, sqlset_row(new.sql_id,new.force_matching_signature, new.sql_text, new.object_list, new.bind_data, new.parsing_schema_name, new.module, new.action, new.elapsed_time, new.cpu_time, new.buffer_gets, new.disk_reads, new.direct_writes, new.rows_processed, new.fetches, new.executions, new.end_of_fetch_count, new.optimizer_cost, new.optimizer_env, new.priority, new.command_type, new.first_load_time, new.stat_period, new.active_stat_period, new.other, new.plan_hash_value, new.sql_plan, new.bind_list) as new_row, old.plan_timestamp, old.binds_captured , new.last_load_time FROM (SELECT s.sql_seq, c.* FROM "_ALL_SQLSET_STATEMENTS_ONLY" s, ( SELECT /*+ first_rows(1) */ sql_id, force_matching_signature, sql_text, parsing_schema_name,bind_data, module, action, elapsed_time, cpu_time, buffer_gets, disk_reads, direct_writes,rows_processed, fetches, executions, end_of_fetch_count, optimizer_cost, optimizer_env,NULL priority, command_type, first_load_time, last_load_time, null stat_period, null active_stat_period, null other, plan_hash_value, null bind_list, null object_list, cast(NULL as SQL_PLAN_TABLE_TYPE) sql_plan FROM (SELECT sql_id, force_matching_signature, sql_text, parsing_schema_name, bind_data, module, action, elapsed_time, cpu_time, buffer_gets, disk_reads, direct_writes, rows_processed, fetches, executions, end_of_fetch_count, optimizer_cost, optimizer_env, command_type, first_load_time, last_load_time, plan_hash_value, last_active_child_address, loaded_versions FROM (SELECT vs.* FROM sys.v_$sqlstats vst, ( SELECT sql_id, force_matching_signature, NVL(plan_hash_value, 0) plan_hash_value, sql_fulltext as sql_text, parsing_schema_name, module, action, elapsed_time, cpu_time, buffer_gets, last_active_child_address, last_active_time, TO_CHAR(first_load_time, 'YYYY-MM-DD/HH24:MI:SS') first_load_time, last_load_time, disk_reads, direct_writes, rows_processed, fetches, executions, end_of_fetch_count, optimizer_cost, optimizer_env, command_type, loaded_versions, bind_data FROM sys.v_$sqlarea_plan_hash s ) vs WHERE vst.sql_id = vs.sql_id and vst.last_active_time >= :lat and vs.last_active_time >= :lat) WHERE executions > 0 AND command_type in (1, 2, 3, 6, 7, 189) ) S ) c WHERE s.sqlset_id(+) = :stsid AND s.sql_id(+) = c.sql_id) new ,"_ALL_SQLSET_STATISTICS_ONLY" old WHERE :stsid = old.sqlset_id(+) AND new.sql_seq = old.sql_seq(+) AND new.plan_hash_value = old.plan_hash_value(+) AND ((new.executions >= old.executions) OR old.sql_seq is NULL)

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time -.01% .023413 .036088 -54.14%
parse_time .019482
cpu_time -.01% .018624 .035439 -90.29%
user_io_time 0
buffer_gets -.52% 459 16088 -3401.2%
cost 0% 1 1 0%
reads .03% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 37 365
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. The structure of the SQL execution plan has changed.
  2. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 2824937026

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1
1 . FILTER
2 .. NESTED LOOPS OUTER 1 3853 0
3 ... VIEW 1 3798 0
4 .... NESTED LOOPS OUTER 1 3823 0
5 ..... NESTED LOOPS 1 3806 0
6 ...... FIXED TABLE FULL X$KKSSQLSTAT 1 30 0
7 ...... FIXED TABLE FIXED INDEX X$KGLCURSOR_CHILD_SQLIDPH (ind 1 3776 0
8 ..... VIEW PUSHED PREDICATE _ALL_SQLSET_STATEMENTS_ONLY 1 17 0
9 ...... NESTED LOOPS 1 72 0
10 ....... TABLE ACCESS BY INDEX ROWID WRI$_SQLSET_STATEMENTS 1 42 0
11 ........ INDEX UNIQUE SCAN WRI$_SQLSET_STATEMENTS_IDX_01 1 0
12 ....... TABLE ACCESS BY INDEX ROWID WRI$_SQLSET_DEFINITIONS 1 30 0
13 ........ INDEX UNIQUE SCAN WRI$_SQLSET_DEFINITIONS_PK 1 0
14 ........ FIXED TABLE FULL X$KZSPR 1 26 0
15 ... VIEW PUSHED PREDICATE _ALL_SQLSET_STATISTICS_ONLY 1 55 0
16 .... NESTED LOOPS 1 159 0
17 ..... NESTED LOOPS 1 94 0
18 ...... NESTED LOOPS 1 68 0
19 ....... TABLE ACCESS BY INDEX ROWID WRI$_SQLSET_DEFINITIONS 1 30 0
20 ........ INDEX UNIQUE SCAN WRI$_SQLSET_DEFINITIONS_PK 1 0
21 ........ FIXED TABLE FULL X$KZSPR 1 26 0
22 ....... TABLE ACCESS BY INDEX ROWID WRI$_SQLSET_PLANS 1 38 0
23 ........ INDEX UNIQUE SCAN WRI$_SQLSET_PLANS_PK 1 0
24 ...... TABLE ACCESS BY INDEX ROWID WRI$_SQLSET_STATEMENTS 1 26 0
25 ....... INDEX UNIQUE SCAN WRI$_SQLSET_STATEMENTS_PK 1 0
26 ..... TABLE ACCESS BY INDEX ROWID WRI$_SQLSET_STATISTICS 1 65 0
27 ...... INDEX UNIQUE SCAN WRI$_SQLSET_STATISTICS_PK 1 0

Execution Plan After Change:
Plan Id : 1154
Plan Hash Value : 3647448970

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 4406 1 00:00:01
* 1 . FILTER
2 .. NESTED LOOPS OUTER 1 4406 1 00:00:01
3 ... VIEW 1 4351 1 00:00:01
* 4 .... HASH JOIN OUTER 1 4372 1 00:00:01
5 ..... NESTED LOOPS 1 4351 0 00:00:01
* 6 ...... FIXED TABLE FULL X$KGLCURSOR_CHILD_SQLIDPH 1 4321 0 00:00:01
* 7 ...... FIXED TABLE FIXED INDEX X$KKSSQLSTAT (ind:1) 1 30 0 00:00:01
8 ..... VIEW _ALL_SQLSET_STATEMENTS_ONLY 1 21 0 00:00:01
* 9 ...... FILTER
10 ....... NESTED LOOPS 1 64 0 00:00:01
11 ........ TABLE ACCESS BY INDEX ROWID WRI$_SQLSET_DEFINITIONS 1 30 0 00:00:01
* 12 ......... INDEX UNIQUE SCAN WRI$_SQLSET_DEFINITIONS_PK 1 0 00:00:01
13 ........ TABLE ACCESS BY INDEX ROWID WRI$_SQLSET_STATEMENTS 1 34 0 00:00:01
* 14 ......... INDEX RANGE SCAN WRI$_SQLSET_STATEMENTS_IDX_01 1 0 00:00:01
* 15 ....... FIXED TABLE FULL X$KZSPR 1 26 0 00:00:01
16 ... VIEW PUSHED PREDICATE _ALL_SQLSET_STATISTICS_ONLY 1 55 0 00:00:01
* 17 .... FILTER
18 ..... NESTED LOOPS 1 159 0 00:00:01
19 ...... NESTED LOOPS 1 94 0 00:00:01
20 ....... NESTED LOOPS 1 68 0 00:00:01
21 ........ TABLE ACCESS BY INDEX ROWID WRI$_SQLSET_DEFINITIONS 1 30 0 00:00:01
* 22 ......... INDEX UNIQUE SCAN WRI$_SQLSET_DEFINITIONS_PK 1 0 00:00:01
23 ........ TABLE ACCESS BY INDEX ROWID WRI$_SQLSET_PLANS 1 38 0 00:00:01
* 24 ......... INDEX UNIQUE SCAN WRI$_SQLSET_PLANS_PK 1 0 00:00:01
* 25 ....... TABLE ACCESS BY INDEX ROWID WRI$_SQLSET_STATEMENTS 1 26 0 00:00:01
* 26 ........ INDEX RANGE SCAN WRI$_SQLSET_STATEMENTS_IDX_01 1 0 00:00:01
27 ...... TABLE ACCESS BY INDEX ROWID WRI$_SQLSET_STATISTICS 1 65 0 00:00:01
* 28 ....... INDEX UNIQUE SCAN WRI$_SQLSET_STATISTICS_PK 1 0 00:00:01
* 29 ..... FIXED TABLE FULL X$KZSPR 1 26 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 762
Schema Name : SYS
SQL ID : 47a50dvdgnxc2
Execution Frequency : 321445
SQL Text : update sys.job$ set failures=0, this_date=null, flag=:1, last_date=:2, next_date = greatest(:3, sysdate), total=total+(sysdate-nvl(this_date,sysdate)) where job=:4

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .24% .000248 .00005 79.8%
parse_time .000878
cpu_time .12% .000088 0 100%
user_io_time 0
buffer_gets .41% 6 1 83.67%
cost 1411.21% 1 0 100%
reads .45% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 2981428395

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE JOB$
2 .. INDEX UNIQUE SCAN I_JOB_JOB 1 46 0

Execution Plan After Change:
Plan Id : 1004
Plan Hash Value : 2981428395

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 79 0 00:00:01
1 . UPDATE JOB$
* 2 .. INDEX UNIQUE SCAN I_JOB_JOB 1 79 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 850
Schema Name : SYS
SQL ID : 8mrg623amm8rx
Execution Frequency : 1007
SQL Text : select OBJOID, CLSOID, RUNTIME, PRI, JOBTYPE, SCHLIM, WT, INST, RUNNOW, ENQ_SCHLIM from ( select a.obj# OBJOID, a.class_oid CLSOID, decode(bitand(a.flags, 16384), 0, a.next_run_date, a.last_enabled_time) RUNTIME, (2*a.priority + decode(bitand(a.job_status, 4), 0, 0, decode(a.running_instance, :1, -1, 1))) PRI, 1 JOBTYPE, a.schedule_limit SCHLIM, a.job_weight WT, decode(a.running_instance, NULL, 0, a.running_instance) INST, decode(bitand(a.flags, 16384), 0, 0, 1) RUNNOW, decode(bitand(a.job_status, 8388608), 0, 0, 1) ENQ_SCHLIM from sys.scheduler$_job a, v$instance i where bitand(a.job_status, 515) = 1 and ( i.logins = 'ALLOWED' or bitand(a.flags, 2147483648) <> 0 ) and ((bitand(a.flags, 134217728 + 268435456) = 0) or (bitand(a.job_status, 1024) <> 0)) and bitand(a.flags, 4096) = 0 and (a.next_run_date <= :2 or bitand(a.flags, 16384) <> 0) and (a.class_oid is null or (a.class_o id is not null and a.class_oid in (select b.obj# from sys.scheduler$_class b where b.affinity is null))) union all select c.obj#, 0, c.next_start_date, 0, 2, c.duration, 1, 0, 0, 0 from sys.scheduler$_window c, v$instance i where bitand(c.flags, 1) <> 0 and bitand(c.flags, 2) = 0 and bitand(c.flags, 64) = 0 and c.next_start_date <= :4 and c.next_start_date + c.duration > :5 and i.logins = 'ALLOWED' union all select d.obj#, 0, d.next_start_date + d.duration, 0, 3, numtodsinterval(0,'minute'), 1, 0, 0, 0 from sys.scheduler$_window d, v$instance i where bitand(d.flags, 1) <> 0 and bitand(d.flags, 2) = 0 and bitand(d.flags, 64) = 0 and d.next_start_date <= :6 and d.next_start_date + d.duration > :7 and i.logins = 'ALLOWED' union all select f.obj#, 0, e.attr_tstamp, 0, decode(bitand(e.flags, 131072), 0, 2, 4), e.attr_intv, 1, 0, 0, 0 from sys.sch eduler$_global_attribute e, sys.obj$ f, sys.obj$ g, v$instance i where e.obj# = g.obj# and g.name = 'CURRENT_OPEN_WINDOW' and e.value = f.name and f.type# = 69 and e.attr_tstamp is not null and e.attr_intv is not null and i.logins = 'ALLOWED' union all select i.obj#, 0, h.attr_tstamp + h.attr_intv, 0, decode(bitand(h.flags, 131072), 0, 3, 5), numtodsinterval(0, 'minute'), 1, 0, 0, 0 from sys.scheduler$_global_attribute h, sys.obj$ i, sys.obj$ j, v$instance ik where h.obj# = j.obj# and ik.logins = 'ALLOWED' and j.name = 'CURRENT_OPEN_WINDOW' and h.value = i.name and i.type# = 69 and h.attr_tstamp is not null and h.attr_intv is not null) order by RUNTIME, CLSOID, PRI, WT DESC, OBJOID

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .03% .010352 .002481 76.03%
parse_time .036209
cpu_time .03% .01069 .002444 77.14%
user_io_time 0
buffer_gets .26% 1300 289 77.78%
cost 941.66% 301 88 70.76%
reads .34% 1 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 2 2
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. The structure of the SQL execution plan has changed.
  2. At least one important bind value was missing for this sql statement. The accuracy of the advisor's analysis may depend on all important bind values being supplied.


Execution Plan Before Change:
Plan Hash Value : 3060841175

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 301
1 . SORT ORDER BY 18 1836 301 00:00:04
2 .. VIEW 18 1836 300 00:00:04
3 ... UNION-ALL
4 .... FILTER
5 ..... MERGE JOIN CARTESIAN 279 32922 1 00:00:01
6 ...... NESTED LOOPS 3 354 1 00:00:01
7 ....... MERGE JOIN CARTESIAN 1 60 0
8 ........ FIXED TABLE FULL X$KSUXSINST 1 26 0
9 ........ BUFFER SORT 1 34 0
10 ......... FIXED TABLE FULL X$KVIT 1 34 0
11 ....... TABLE ACCESS BY INDEX ROWID SCHEDULER$_JOB 3 174 1 00:00:01
12 ........ INDEX RANGE SCAN I_SCHEDULER_JOB4 5 0
13 ...... BUFFER SORT 100 0
14 ....... FIXED TABLE FULL X$QUIESCE 100 0
15 ..... TABLE ACCESS BY INDEX ROWID SCHEDULER$_CLASS 1 38 1 00:00:01
16 ...... INDEX UNIQUE SCAN SCHEDULER$_CLASS_PK 1 0
17 .... MERGE JOIN CARTESIAN 1 94 2 00:00:01
18 ..... MERGE JOIN CARTESIAN 1 68 2 00:00:01
19 ...... MERGE JOIN CARTESIAN 1 34 2 00:00:01
20 ....... TABLE ACCESS FULL SCHEDULER$_WINDOW 1 34 2 00:00:01
21 ....... BUFFER SORT 100 0
22 ........ FIXED TABLE FULL X$QUIESCE 100 0
23 ...... BUFFER SORT 1 34 2 00:00:01
24 ....... FIXED TABLE FULL X$KVIT 1 34 0
25 ..... BUFFER SORT 1 26 2 00:00:01
26 ...... FIXED TABLE FULL X$KSUXSINST 1 26 0
27 .... MERGE JOIN CARTESIAN 1 94 2 00:00:01
28 ..... MERGE JOIN CARTESIAN 1 68 2 00:00:01
29 ...... MERGE JOIN CARTESIAN 1 34 2 00:00:01
30 ....... TABLE ACCESS FULL SCHEDULER$_WINDOW 1 34 2 00:00:01
31 ....... BUFFER SORT 100 0
32 ........ FIXED TABLE FULL X$QUIESCE 100 0
33 ...... BUFFER SORT 1 34 2 00:00:01
34 ....... FIXED TABLE FULL X$KVIT 1 34 0
35 ..... BUFFER SORT 1 26 2 00:00:01
36 ...... FIXED TABLE FULL X$KSUXSINST 1 26 0
37 .... MERGE JOIN CARTESIAN 1 145 147 00:00:02
38 ..... NESTED LOOPS 1 145 147 00:00:02
39 ...... HASH JOIN 1 116 146 00:00:02
40 ....... MERGE JOIN CARTESIAN 1 92 144 00:00:02
41 ........ MERGE JOIN CARTESIAN 1 60 0
42 ......... FIXED TABLE FULL X$KSUXSINST 1 26 0
43 ......... BUFFER SORT 1 34 0
44 .......... FIXED TABLE FULL X$KVIT 1 34 0
45 ........ BUFFER SORT 4 128 144 00:00:02
46 ......... TABLE ACCESS FULL OBJ$ 4 128 143 00:00:02
47 ....... TABLE ACCESS FULL SCHEDULER$_GLOBAL_ATTRIBUTE 1 24 2 00:00:01
48 ...... TABLE ACCESS BY INDEX ROWID OBJ$ 1 29 1 00:00:01
49 ....... INDEX UNIQUE SCAN I_OBJ1 1 0
50 ..... BUFFER SORT 100 146 00:00:02
51 ...... FIXED TABLE FULL X$QUIESCE 100 0
52 .... MERGE JOIN CARTESIAN 1 145 147 00:00:02
53 ..... NESTED LOOPS 1 145 147 00:00:02
54 ...... HASH JOIN 1 116 146 00:00:02
55 ....... MERGE JOIN CARTESIAN 1 92 144 00:00:02
56 ........ MERGE JOIN CARTESIAN 1 60 0
57 ......... FIXED TABLE FULL X$KSUXSINST 1 26 0
58 ......... BUFFER SORT 1 34 0
59 .......... FIXED TABLE FULL X$KVIT 1 34 0
60 ........ BUFFER SORT 4 128 144 00:00:02
61 ......... TABLE ACCESS FULL OBJ$ 4 128 143 00:00:02
62 ....... TABLE ACCESS FULL SCHEDULER$_GLOBAL_ATTRIBUTE 1 24 2 00:00:01
63 ...... TABLE ACCESS BY INDEX ROWID OBJ$ 1 29 1 00:00:01
64 ....... INDEX UNIQUE SCAN I_OBJ1 1 0
65 ..... BUFFER SORT 100 146 00:00:02
66 ...... FIXED TABLE FULL X$QUIESCE 100 0

Execution Plan After Change:
Plan Id : 1092
Plan Hash Value : 1324343010

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 29 2639 88 00:00:02
1 . SORT ORDER BY 29 2639 88 00:00:02
2 .. VIEW 29 2639 87 00:00:02
3 ... UNION-ALL
* 4 .... FILTER
5 ..... MERGE JOIN CARTESIAN 493 56202 2 00:00:01
6 ...... NESTED LOOPS 5 570 2 00:00:01
7 ....... MERGE JOIN CARTESIAN 1 60 0 00:00:01
* 8 ........ FIXED TABLE FULL X$KSUXSINST 1 26 0 00:00:01
9 ........ BUFFER SORT 1 34 0 00:00:01
* 10 ......... FIXED TABLE FULL X$KVIT 1 34 0 00:00:01
* 11 ....... TABLE ACCESS FULL SCHEDULER$_JOB 5 270 2 00:00:01
12 ...... BUFFER SORT 100 0 00:00:01
13 ....... FIXED TABLE FULL X$QUIESCE 100 0 00:00:01
* 14 ..... TABLE ACCESS BY INDEX ROWID SCHEDULER$_CLASS 1 39 1 00:00:01
* 15 ...... INDEX UNIQUE SCAN SCHEDULER$_CLASS_PK 1 0 00:00:01
16 .... MERGE JOIN CARTESIAN 1 104 2 00:00:01
17 ..... MERGE JOIN CARTESIAN 1 104 2 00:00:01
18 ...... MERGE JOIN CARTESIAN 1 78 2 00:00:01
* 19 ....... TABLE ACCESS BY INDEX ROWID SCHEDULER$_WINDOW 1 44 2 00:00:01
* 20 ........ INDEX RANGE SCAN I_SCHEDULER_WINDOW1 2 1 00:00:01
21 ....... BUFFER SORT 1 34 0 00:00:01
* 22 ........ FIXED TABLE FULL X$KVIT 1 34 0 00:00:01
23 ...... BUFFER SORT 1 26 2 00:00:01
* 24 ....... FIXED TABLE FULL X$KSUXSINST 1 26 0 00:00:01
25 ..... BUFFER SORT 100 2 00:00:01
26 ...... FIXED TABLE FULL X$QUIESCE 100 0 00:00:01
27 .... MERGE JOIN CARTESIAN 1 104 2 00:00:01
28 ..... MERGE JOIN CARTESIAN 1 104 2 00:00:01
29 ...... MERGE JOIN CARTESIAN 1 78 2 00:00:01
* 30 ....... TABLE ACCESS BY INDEX ROWID SCHEDULER$_WINDOW 1 44 2 00:00:01
* 31 ........ INDEX RANGE SCAN I_SCHEDULER_WINDOW1 2 1 00:00:01
32 ....... BUFFER SORT 1 34 0 00:00:01
* 33 ........ FIXED TABLE FULL X$KVIT 1 34 0 00:00:01
34 ...... BUFFER SORT 1 26 2 00:00:01
* 35 ....... FIXED TABLE FULL X$KSUXSINST 1 26 0 00:00:01
36 ..... BUFFER SORT 100 2 00:00:01
37 ...... FIXED TABLE FULL X$QUIESCE 100 0 00:00:01
38 .... MERGE JOIN CARTESIAN 1 128 41 00:00:01
39 ..... NESTED LOOPS
40 ...... NESTED LOOPS 1 128 41 00:00:01
* 41 ....... HASH JOIN 1 104 39 00:00:01
42 ........ MERGE JOIN CARTESIAN 1 88 36 00:00:01
43 ......... MERGE JOIN CARTESIAN 1 60 0 00:00:01
* 44 .......... FIXED TABLE FULL X$KSUXSINST 1 26 0 00:00:01
45 .......... BUFFER SORT 1 34 0 00:00:01
* 46 ........... FIXED TABLE FULL X$KVIT 1 34 0 00:00:01
47 ......... BUFFER SORT 9 252 36 00:00:01
* 48 .......... INDEX FAST FULL SCAN I_OBJ2 9 252 36 00:00:01
* 49 ........ TABLE ACCESS FULL SCHEDULER$_GLOBAL_ATTRIBUTE 1 16 2 00:00:01
* 50 ....... INDEX RANGE SCAN I_OBJ1 1 1 00:00:01
* 51 ...... TABLE ACCESS BY INDEX ROWID OBJ$ 1 24 2 00:00:01
52 ..... BUFFER SORT 100 39 00:00:01
53 ...... FIXED TABLE FULL X$QUIESCE 100 0 00:00:01
54 .... MERGE JOIN CARTESIAN 1 128 41 00:00:01
55 ..... NESTED LOOPS
56 ...... NESTED LOOPS 1 128 41 00:00:01
* 57 ....... HASH JOIN 1 104 39 00:00:01
58 ........ MERGE JOIN CARTESIAN 1 88 36 00:00:01
59 ......... MERGE JOIN CARTESIAN 1 60 0 00:00:01
* 60 .......... FIXED TABLE FULL X$KSUXSINST 1 26 0 00:00:01
61 .......... BUFFER SORT 1 34 0 00:00:01
* 62 ........... FIXED TABLE FULL X$KVIT 1 34 0 00:00:01
63 ......... BUFFER SORT 9 252 36 00:00:01
* 64 .......... INDEX FAST FULL SCAN I_OBJ2 9 252 36 00:00:01
* 65 ........ TABLE ACCESS FULL SCHEDULER$_GLOBAL_ATTRIBUTE 1 16 2 00:00:01
* 66 ....... INDEX RANGE SCAN I_OBJ1 1 1 00:00:01
* 67 ...... TABLE ACCESS BY INDEX ROWID OBJ$ 1 24 2 00:00:01
68 ..... BUFFER SORT 100 39 00:00:01
69 ...... FIXED TABLE FULL X$QUIESCE 100 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 895
Schema Name : SYS
SQL ID : aq8yqxyyb40nn
Execution Frequency : 321445
SQL Text : update sys.job$ set this_date=:1 where job=:2

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .54% .000485 .000051 89.48%
parse_time .000173
cpu_time -.02% .000094 .000111 -18.68%
user_io_time 0
buffer_gets .16% 3 1 67.04%
cost 1411.21% 1 0 100%
reads .5% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 2981428395

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE JOB$
2 .. INDEX UNIQUE SCAN I_JOB_JOB 1 4 0

Execution Plan After Change:
Plan Id : 1137
Plan Hash Value : 2981428395

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 22 0 00:00:01
1 . UPDATE JOB$
* 2 .. INDEX UNIQUE SCAN I_JOB_JOB 1 22 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 739
Schema Name : SYS
SQL ID : 31a13pnjps7j3
Execution Frequency : 16
SQL Text : SELECT source, (case when time_secs < 1 then 1 else time_secs end) as time_secs, operation FROM ( SELECT 1 as source, trunc((sysdate - cast(ll.log_date as date)) * 86400) as time_secs, decode(ll.operation, 'OPEN', 0 , 1 ) as operation, ll.log_id as log_id FROM DBA_SCHEDULER_WINDOW_LOG ll , ( SELECT max(l.log_id) as max_log_id FROM DBA_SCHEDULER_WINDOW_LOG l , DBA_SCHEDULER_WINGROUP_MEMBERS m WHERE l.window_name = m.window_name AND m.window_group_name = 'MAINTENANCE_WINDOW_GROUP' AND l.operation in ('OPEN', 'CLOSE') AND CAST(l.log_date AS DATE) < (SELECT cast(s1.end_interval_time as date) FROM WRM$_SNAPSHOT s1 WHERE s1.dbid = :dbid AND s1.instance_number = :inst_num AND s1.snap_id = :bid ) ) max_log WHERE ll.log_id = max_log.max_log_id UNION ALL SELECT 2 as source, trunc((sysdate - cast(l.log_date as date)) * 86400) as time_secs, decode(l.operation, 'OPEN', 0 , 1 ) as operation, l.log_id as log_id FROM DBA_SCHEDULER_WINDOW_LOG l , DBA_SCHEDULER_WINGROUP_MEMBERS m WHERE l.window_name = m.window_name AND m.window_group_name = 'MAINTENANCE_WINDOW_GROUP' AND l.operation in ('OPEN', 'CLOSE') AND CAST(l.log_date AS DATE) <= (SELECT cast(s2.end_interval_time as date) FROM WRM$_SNAPS HOT s2 WHERE s2.dbid = :dbid AND s2.instance_number = :inst_num AND s2.snap_id = :eid ) AND CAST(l.log_date AS DATE) >= (SELECT cast(s3.end_interval_time as date) FROM WRM$_SNAPSHOT s3 WHERE s3.dbid = :dbid AND s3.instance_number = :inst_num AND s3.snap_id = :bid ) UNION ALL SELECT 0 as source, trunc((sysdate - cast(s4.end_interval_time as date)) * 86400) as time_secs, decode(s4.snap_id, :bid, 2 , 3 ) as operation, 0 as log_id FROM WRM$_SNAPSHOT s4 WHERE s4.dbid = :dbid AND s4.instance_number = :inst_num AND s4.snap_id in (:bid, :eid) ) WHERE time_secs >= 0 ORDER BY source ASC, time_secs DESC, log_id ASC

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .01% .099284 .000348 99.65%
parse_time .018905
cpu_time .01% .10161 .000444 99.56%
user_io_time 0
buffer_gets .14% 35637 128 99.64%
cost 552.32% 7922 59 99.26%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 3 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (3):
  1. The structure of the SQL execution plan has changed.
  2. This SQL statement returned zero rows.
  3. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 948353432

Id Operation Name Rows Bytes Cost Time

Execution Plan After Change:
Plan Id : 981
Plan Hash Value : 1713197656

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 3 96 59 00:00:01
1 . SORT ORDER BY 3 96 59 00:00:01
2 .. VIEW 3 96 58 00:00:01
3 ... UNION-ALL
4 .... NESTED LOOPS 1 40 28 00:00:01
5 ..... VIEW 1 13 26 00:00:01
6 ...... SORT AGGREGATE 1 118
7 ....... NESTED LOOPS
8 ........ NESTED LOOPS 1 118 24 00:00:01
9 ......... NESTED LOOPS 1 90 22 00:00:01
10 .......... NESTED LOOPS 1 66 20 00:00:01
11 ........... MERGE JOIN CARTESIAN 1 56 19 00:00:01
* 12 ............ TABLE ACCESS FULL SCHEDULER$_EVENT_LOG 1 48 17 00:00:01
13 ............. TABLE ACCESS BY INDEX ROWID WRM$_SNAPSHOT 1 25 2 00:00:01
* 14 .............. INDEX UNIQUE SCAN WRM$_SNAPSHOT_PK 1 1 00:00:01
15 ............ BUFFER SORT 1 8 2 00:00:01
* 16 ............. TABLE ACCESS FULL SCHEDULER$_WINDOW_GROUP 1 8 2 00:00:01
* 17 ........... INDEX RANGE SCAN SCHEDULER$_WINGRP_MEMBER_UQ 7 70 1 00:00:01
* 18 .......... TABLE ACCESS BY INDEX ROWID OBJ$ 1 24 2 00:00:01
* 19 ........... INDEX RANGE SCAN I_OBJ1 1 1 00:00:01
* 20 ......... INDEX RANGE SCAN I_OBJ1 1 1 00:00:01
* 21 ........ TABLE ACCESS BY INDEX ROWID OBJ$ 1 28 2 00:00:01
* 22 ..... TABLE ACCESS BY INDEX ROWID SCHEDULER$_EVENT_LOG 1 27 2 00:00:01
* 23 ...... INDEX RANGE SCAN SCHEDULER$_INSTANCE_PK 1 1 00:00:01
24 .... NESTED LOOPS
25 ..... NESTED LOOPS 1 118 23 00:00:01
26 ...... NESTED LOOPS 1 90 21 00:00:01
27 ....... NESTED LOOPS 1 66 19 00:00:01
28 ........ MERGE JOIN CARTESIAN 1 58 18 00:00:01
* 29 ......... TABLE ACCESS FULL SCHEDULER$_EVENT_LOG 1 48 17 00:00:01
30 .......... TABLE ACCESS BY INDEX ROWID WRM$_SNAPSHOT 1 25 2 00:00:01
* 31 ........... INDEX UNIQUE SCAN WRM$_SNAPSHOT_PK 1 1 00:00:01
32 .......... TABLE ACCESS BY INDEX ROWID WRM$_SNAPSHOT 1 25 2 00:00:01
* 33 ........... INDEX UNIQUE SCAN WRM$_SNAPSHOT_PK 1 1 00:00:01
34 ......... BUFFER SORT 28 280 1 00:00:01
35 .......... INDEX FULL SCAN SCHEDULER$_WINGRP_MEMBER_UQ 28 280 1 00:00:01
* 36 ........ TABLE ACCESS BY INDEX ROWID SCHEDULER$_WINDOW_GROUP 1 8 1 00:00:01
* 37 ......... INDEX UNIQUE SCAN SCHEDULER$_WINDOW_GROUP_PK 1 0 00:00:01
* 38 ....... TABLE ACCESS BY INDEX ROWID OBJ$ 1 24 2 00:00:01
* 39 ........ INDEX RANGE SCAN I_OBJ1 1 1 00:00:01
* 40 ...... INDEX RANGE SCAN I_OBJ1 1 1 00:00:01
* 41 ..... TABLE ACCESS BY INDEX ROWID OBJ$ 1 28 2 00:00:01
42 .... INLIST ITERATOR
* 43 ..... TABLE ACCESS BY INDEX ROWID WRM$_SNAPSHOT 1 25 3 00:00:01
* 44 ...... INDEX UNIQUE SCAN WRM$_SNAPSHOT_PK 1 2 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 735
Schema Name : SYS
SQL ID : 2uunxrv2wj57b
Execution Frequency : 68880
SQL Text : update sum$ set containerobj#=:2,containertype=:3,containernam=:4,fullrefreshtim=:5,increfreshtim=:6,lastrefreshscn=:7,lastrefreshdate=:8,refreshmode=:9,pflags=:10,mflags=:11,numdetailtab=:12,numaggregates=:13,numkeycolumns=:14,numjoins=:15,numinlines=:16,sumtextlen=:17,sumtext=:18,fromoffset=:19,fromlen=:20,objcount=:21,metaversion=:22, xpflags=:23,numwhrnodes=:24, numhavnodes=:25, numqbnodes=:26, qbcmarker=:27, markerdty=:28, rw_mode=:29, rw_name=:30, dest_stmt=NULL, src_stmt=NULL, spare1=:31 where obj#=:1

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .04% .000201 .000051 74.58%
parse_time .026787
cpu_time 0% .00011 .000111 -.97%
user_io_time 0
buffer_gets .08% 5 1 81.58%
cost 302.4% 1 0 100%
reads 1.26% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. The structure of the SQL execution plan has changed.


Execution Plan Before Change:
Plan Hash Value : 428298058

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE SUM$
2 .. TABLE ACCESS BY INDEX ROWID SUM$ 1 106 1 00:00:01
3 ... INDEX UNIQUE SCAN I_SUM$_1 1 0

Execution Plan After Change:
Plan Id : 977
Plan Hash Value : 3952316262

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 4401 0 00:00:01
1 . UPDATE SUM$
* 2 .. INDEX UNIQUE SCAN I_SUM$_1 1 4401 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 846
Schema Name : SYS
SQL ID : 8cuu0uynbrhbn
Execution Frequency : 275530
SQL Text : select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (next_date <= :2)) or ((last_date is null) and (next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)) and (this_date is null) and ((dbms_logstdby.db_is_logstdby = 0 and job < 1000000000) or (dbms_logstdby.db_is_logstdby = 1 and job >

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .19% .000257 .000079 69.28%
parse_time .002435
cpu_time .1% .000085 0 100%
user_io_time 0
buffer_gets .07% 3 2 33.39%
cost 2419.26% 3 1 66.67%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (3):
  1. The structure of the SQL execution plan has changed.
  2. This SQL statement returned zero rows.
  3. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 1846751226

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 3
1 . SORT ORDER BY 1 22 3 00:00:01
2 .. TABLE ACCESS FULL JOB$ 1 22 2 00:00:01

Execution Plan After Change:
Plan Id : 1088
Plan Hash Value : 2269144151

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 2 106 1 00:00:01
1 . SORT ORDER BY 2 106 1 00:00:01
2 .. CONCATENATION
* 3 ... TABLE ACCESS BY INDEX ROWID JOB$ 1 53 0 00:00:01
* 4 .... INDEX RANGE SCAN I_JOB_NEXT 1 0 00:00:01
* 5 ... TABLE ACCESS BY INDEX ROWID JOB$ 1 53 0 00:00:01
* 6 .... INDEX RANGE SCAN I_JOB_NEXT 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 938
Schema Name : SYS
SQL ID : cjaa80k1hvpc1
Execution Frequency : 92152
SQL Text : select 1 from sys.cdc_change_tables$ where source_schema_name = :1 and source_table_name = :2 and bitand(mvl_flag, 128)=128

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .01% .000088 .000073 17.12%
parse_time .013063
cpu_time -.02% .000056 .000111 -97.61%
user_io_time 0
buffer_gets .05% 3 1 66.67%
cost 0% 2 2 0%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. This SQL statement returned zero rows.


Execution Plan Before Change:
Plan Hash Value : 626680409

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 2
1 . TABLE ACCESS FULL CDC_CHANGE_TABLES$ 1 47 2 00:00:01

Execution Plan After Change:
Plan Id : 1180
Plan Hash Value : 626680409

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 47 2 00:00:01
* 1 . TABLE ACCESS FULL CDC_CHANGE_TABLES$ 1 47 2 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 988
Schema Name : SYS
SQL ID : ggyn905py8mt5
Execution Frequency : 22960
SQL Text : SELECT operation#, cols, sql_txt, tabnum, fcmaskvec, ejmaskvec, setnum FROM sys.snap_refop$ WHERE ((tabnum = 0 AND operation# IN (1, 3, 4, 5, 6, 8, 9, 10, 12, 13)) OR (tabnum > 0 AND operation# IN (0, 1, 2))) AND sowner = :1 AND vname = :2 AND instsite = :3 ORDER BY tabnum, setnum, operation#

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .01% .000178 .00008 55.15%
parse_time .006585
cpu_time 0% .000079 .000111 -40.83%
user_io_time 0
buffer_gets .05% 9 1 88.89%
cost 201.6% 3 1 66.67%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 6 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (3):
  1. The structure of the SQL execution plan has changed.
  2. This SQL statement returned zero rows.
  3. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 1181706333

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 3
1 . SORT ORDER BY 1 27 3 00:00:01
2 .. TABLE ACCESS FULL SNAP_REFOP$ 1 27 2 00:00:01

Execution Plan After Change:
Plan Id : 1230
Plan Hash Value : 4184286231

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 360 1 00:00:01
1 . SORT ORDER BY 1 360 1 00:00:01
2 .. TABLE ACCESS BY INDEX ROWID SNAP_REFOP$ 1 360 0 00:00:01
* 3 ... INDEX RANGE SCAN I_SNAP_REFOP1 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 703
Schema Name : SYS
SQL ID : 1drcud8h9f38b
Execution Frequency : 22960
SQL Text : update sys.slog$ set snaptime = :1 where snapid = :2 and mowner = :3 and master = :4

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .01% .000229 .00006 73.81%
parse_time .003322
cpu_time .02% .000173 0 100%
user_io_time 0
buffer_gets .04% 9 2 77.97%
cost 100.8% 1 0 100%
reads .02% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 2279215096

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE SLOG$
2 .. TABLE ACCESS CLUSTER SLOG$ 1 26 1 00:00:01
3 ... INDEX UNIQUE SCAN I_MLOG# 1 0

Execution Plan After Change:
Plan Id : 945
Plan Hash Value : 2279215096

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 56 0 00:00:01
1 . UPDATE SLOG$
* 2 .. TABLE ACCESS CLUSTER SLOG$ 1 56 0 00:00:01
* 3 ... INDEX UNIQUE SCAN I_MLOG# 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 922
Schema Name : SYS
SQL ID : bznx2bj4cyq6c
Execution Frequency : 22960
SQL Text : select log, oldest, oldest_pk, oldest_oid, oldest_new, youngest+1/86400, flag, yscn, oldest_seq from sys.mlog$ where mowner = :1 and master = :2 for update

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .01% .000195 .000091 53.32%
parse_time .000425
cpu_time .01% .00011 0 100%
user_io_time 0
buffer_gets .03% 9 3 66.79%
cost 100.8% 1 0 100%
reads .03% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. The structure of the SQL execution plan has changed.


Execution Plan Before Change:
Plan Hash Value : 2842875245

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1
1 . FOR UPDATE
2 .. TABLE ACCESS CLUSTER MLOG$ 1 81 1 00:00:01
3 ... INDEX UNIQUE SCAN I_MLOG# 1 0

Execution Plan After Change:
Plan Id : 1164
Plan Hash Value : 1551139906

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 131 0 00:00:01
1 . FOR UPDATE
2 .. BUFFER SORT
3 ... TABLE ACCESS CLUSTER MLOG$ 1 131 0 00:00:01
* 4 .... INDEX UNIQUE SCAN I_MLOG# 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 751
Schema Name : SYS
SQL ID : 3h1g1sszzr4xr
Execution Frequency : 22960
SQL Text : select OLDEST, flag from sys.mlog$ where mowner = :1 and master = :2 for update

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time -.12% .000194 .001594 -720.41%
parse_time .000859
cpu_time .01% .000145 0 100%
user_io_time 0
buffer_gets .03% 9 3 66.79%
cost 100.8% 1 0 100%
reads .03% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. The structure of the SQL execution plan has changed.


Execution Plan Before Change:
Plan Hash Value : 2842875245

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1
1 . FOR UPDATE
2 .. TABLE ACCESS CLUSTER MLOG$ 1 26 1 00:00:01
3 ... INDEX UNIQUE SCAN I_MLOG# 1 0

Execution Plan After Change:
Plan Id : 993
Plan Hash Value : 1551139906

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 56 0 00:00:01
1 . FOR UPDATE
2 .. BUFFER SORT
3 ... TABLE ACCESS CLUSTER MLOG$ 1 56 0 00:00:01
* 4 .... INDEX UNIQUE SCAN I_MLOG# 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 712
Schema Name : SYS
SQL ID : 1ua7x8h4kptvt
Execution Frequency : 23194
SQL Text : select log, sysdate, youngest, youngest+1/86400, oldest, oldest_pk, oldest_oid, oldest_new, oldest_seq from sys.mlog$ where master = :2 and mowner = :1 for update

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .01% .000203 .000098 51.61%
parse_time .005257
cpu_time .02% .00017 0 100%
user_io_time 0
buffer_gets .03% 7 3 62.32%
cost 101.83% 1 0 100%
reads .04% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (3):
  1. The structure of the SQL execution plan has changed.
  2. This SQL statement returned zero rows.
  3. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 2842875245

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1
1 . FOR UPDATE
2 .. TABLE ACCESS CLUSTER MLOG$ 1 76 1 00:00:01
3 ... INDEX UNIQUE SCAN I_MLOG# 1 0

Execution Plan After Change:
Plan Id : 954
Plan Hash Value : 1551139906

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 105 0 00:00:01
1 . FOR UPDATE
2 .. BUFFER SORT
3 ... TABLE ACCESS CLUSTER MLOG$ 1 105 0 00:00:01
* 4 .... INDEX UNIQUE SCAN I_MLOG# 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 976
Schema Name : SYS
SQL ID : g00cj285jmgsw
Execution Frequency : 52125
SQL Text : update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, deletes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags + :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where obj# = :objn

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .02% .000164 .000075 54.32%
parse_time .000198
cpu_time .01% .000163 .000111 32.06%
user_io_time 0
buffer_gets .02% 2 1 65.42%
cost 228.84% 1 0 100%
reads .07% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 315182377

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE MON_MODS$
2 .. INDEX UNIQUE SCAN I_MON_MODS$_OBJ 1 87 0

Execution Plan After Change:
Plan Id : 1218
Plan Hash Value : 315182377

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 87 0 00:00:01
1 . UPDATE MON_MODS$
* 2 .. INDEX UNIQUE SCAN I_MON_MODS$_OBJ 1 87 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 666
Schema Name : SYS
SQL ID : 06xju6khjr73a
Execution Frequency : 45920
SQL Text : select last_delivered, last_enq_tid, rowid from system.def$_destination dd where (dd.dblink != :1) and ( exists ( select 1 from system.def$_calldest cd where cd.dblink = dd.dblink and cd.catchup = dd.catchup) or exists ( select 1 from system.repcat$_repprop rp where rp.dblink = dd.dblink and rp.how = 1 and rp.extension_id = dd.catchup))

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000102 .000074 27.64%
parse_time .10046
cpu_time -.01% .000049 .000111 -125.87%
user_io_time 0
buffer_gets .02% 3 1 66.71%
cost 0% 2 2 0%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. The structure of the SQL execution plan has changed.
  2. This SQL statement returned zero rows.


Execution Plan Before Change:
Plan Hash Value : 67989312

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 2
1 . FILTER
2 .. TABLE ACCESS FULL DEF$_DESTINATION 1 25 2 00:00:01
3 .. INDEX RANGE SCAN DEF$_CALLDEST_N2 1 76 1 00:00:01
4 ... INDEX RANGE SCAN REPCAT$_REPPROP_DBLINK_HOW 1 89 0

Execution Plan After Change:
Plan Id : 908
Plan Hash Value : 811096117

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 190 2 00:00:01
1 . MERGE JOIN SEMI 1 190 2 00:00:01
2 .. TABLE ACCESS BY INDEX ROWID DEF$_DESTINATION 1 114 0 00:00:01
* 3 ... INDEX FULL SCAN DEF$_DESTINATION_PRIMARY 1 0 00:00:01
* 4 .. SORT UNIQUE 2 152 2 00:00:01
5 ... VIEW VW_SQ_1 2 152 1 00:00:01
6 .... UNION-ALL
* 7 ..... INDEX SKIP SCAN REPCAT$_REPPROP_DBLINK_HOW 1 89 1 00:00:01
* 8 ..... INDEX FULL SCAN DEF$_CALLDEST_N2 1 76 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 972
Schema Name : SYS
SQL ID : fqnjcr2jbqpsc
Execution Frequency : 22960
SQL Text : select count(*), max(scn) from (select sp.BO#, sp.PMOPTYPE, sp.scn, sp.flags from sumpartlog$ sp, sumdep$ sd where sd.sumobj# = :1 and sd.p_obj# = sp.bo# group by sp.BO#, sp.PMOPTYPE, sp.scn, sp.flags minus select sp.BO#, sp.PMOPTYPE, sp.scn, sp.flags from sumpartlog$ sp where sp.bo# not in (select sk.DETAILOBJ# from sumkey$ sk where sk.sumobj# = :1 and sk.DETAILCOLFUNCTION in (2,3) ) and bitand(sp.flags, 2) != 2 and sp.PMOPTYPE in (2,3,5,7) group by sp.BO#, sp.PMOPTYPE, sp.scn, sp.flags) where scn > : 2

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000196 .00015 23.59%
parse_time .006871
cpu_time -.01% .000116 .000222 -90.65%
user_io_time 0
buffer_gets .02% 6 2 66.67%
cost 604.79% 12 6 50%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. The structure of the SQL execution plan has changed.


Execution Plan Before Change:
Plan Hash Value : 3086337994

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 12
1 . SORT AGGREGATE 1 13
2 .. VIEW 1 13 12 00:00:01
3 ... MINUS
4 .... SORT UNIQUE NOSORT 1 62 5 00:00:01
5 ..... SORT GROUP BY 1 62 5 00:00:01
6 ...... TABLE ACCESS BY INDEX ROWID SUMDEP$ 1 10 1 00:00:01
7 ....... NESTED LOOPS 1 62 3 00:00:01
8 ........ TABLE ACCESS FULL SUMPARTLOG$ 1 52 2 00:00:01
9 ........ INDEX RANGE SCAN I_SUMDEP$_2 2 1 00:00:01
10 .... SORT UNIQUE NOSORT 1 65 7 00:00:01
11 ..... SORT GROUP BY 1 65 7 00:00:01
12 ...... HASH JOIN ANTI 1 65 5 00:00:01
13 ....... TABLE ACCESS FULL SUMPARTLOG$ 1 52 2 00:00:01
14 ....... TABLE ACCESS FULL SUMKEY$ 1 13 2 00:00:01

Execution Plan After Change:
Plan Id : 1214
Plan Hash Value : 536103818

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 13 6 00:00:01
1 . SORT AGGREGATE 1 13
2 .. VIEW 1 13 6 00:00:01
3 ... MINUS
4 .... SORT UNIQUE 1 78 2 00:00:01
5 ..... HASH GROUP BY 1 78 2 00:00:01
6 ...... NESTED LOOPS
7 ....... NESTED LOOPS 1 78 0 00:00:01
8 ........ TABLE ACCESS BY INDEX ROWID SUMDEP$ 1 26 0 00:00:01
* 9 ......... INDEX RANGE SCAN I_SUMDEP$_1 1 0 00:00:01
* 10 ........ INDEX RANGE SCAN I_SUMPARTLOG$ 1 0 00:00:01
* 11 ....... TABLE ACCESS BY INDEX ROWID SUMPARTLOG$ 1 52 0 00:00:01
12 .... SORT UNIQUE 1 91 4 00:00:01
13 ..... HASH GROUP BY 1 91 4 00:00:01
14 ...... NESTED LOOPS ANTI 1 91 2 00:00:01
* 15 ....... TABLE ACCESS FULL SUMPARTLOG$ 1 52 2 00:00:01
* 16 ....... TABLE ACCESS BY INDEX ROWID SUMKEY$ 1 39 0 00:00:01
* 17 ........ INDEX RANGE SCAN I_SUMKEY$_1 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 795
Schema Name : SYS
SQL ID : 6769wyy3yf66f
Execution Frequency : 15632
SQL Text : select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#=:1

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000077 .000067 13.34%
parse_time .021842
cpu_time 0% .000052 0 100%
user_io_time 0
buffer_gets .02% 7 2 73.06%
cost -68.63% 1 2 -100%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 2 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. This SQL statement returned zero rows.
  2. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 299250003

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT
1 . TABLE ACCESS BY INDEX ROWID ICOL$
2 .. INDEX RANGE SCAN I_ICOL1

Execution Plan After Change:
Plan Id : 1037
Plan Hash Value : 299250003

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 2 50 2 00:00:01
1 . TABLE ACCESS BY INDEX ROWID ICOL$ 2 50 2 00:00:01
* 2 .. INDEX RANGE SCAN I_ICOL1 2 1 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 937
Schema Name : SYS
SQL ID : chsyr0gssbuqf
Execution Frequency : 4405
SQL Text : select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000189 .000072 61.84%
parse_time .000185
cpu_time 0% .000152 0 100%
user_io_time 0
buffer_gets .02% 31 14 54.84%
cost 58.02% 8 5 37.5%
reads .01% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. This SQL statement returned zero rows.


Execution Plan Before Change:
Plan Hash Value : 2285252071

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 8
1 . NESTED LOOPS 1 61 8 00:00:01
2 .. TABLE ACCESS FULL FET$ 1 52 7 00:00:01
3 .. TABLE ACCESS CLUSTER TS$ 1 9 1 00:00:01

Execution Plan After Change:
Plan Id : 1179
Plan Hash Value : 2285252071

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 61 5 00:00:01
1 . NESTED LOOPS 1 61 5 00:00:01
2 .. TABLE ACCESS FULL FET$ 1 52 4 00:00:01
* 3 .. TABLE ACCESS CLUSTER TS$ 1 9 1 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 979
Schema Name : SYS
SQL ID : g50sv3qtu91ac
Execution Frequency : 22960
SQL Text : update sumdetail$ set detailobjtype=:3, refreshscn=:5, detaileut=:6, spare4=:7, inline# =:8, dataless =:10 where sumobj#=:1 and detailobj#=:2 and detailalias=:4 and instance# =:9 and qbcid = :11

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .01% .000144 .000074 48.62%
parse_time .005283
cpu_time 0% .000064 .000111 -74.47%
user_io_time 0
buffer_gets .02% 4 1 75.41%
cost 100.8% 2 1 50%
reads .02% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. The structure of the SQL execution plan has changed.


Execution Plan Before Change:
Plan Hash Value : 2255859666

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 2
1 . UPDATE SUMDETAIL$
2 .. TABLE ACCESS FULL SUMDETAIL$ 1 37 2 00:00:01

Execution Plan After Change:
Plan Id : 1221
Plan Hash Value : 2543856601

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 143 1 00:00:01
1 . UPDATE SUMDETAIL$
* 2 .. TABLE ACCESS BY INDEX ROWID SUMDETAIL$ 1 143 1 00:00:01
* 3 ... INDEX RANGE SCAN I_SUMDETAIL$_1 1 1 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 945
Schema Name : SYS
SQL ID : czbjavaavnum5
Execution Frequency : 22960
SQL Text : update sys.snap$ set snapshot = :1, snaptime = :2, auto_date = :3, snapid = :4, error# = 0 where vname = :6 and sowner = :5 and instsite = :7

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .01% .000163 .000054 66.92%
parse_time .001951
cpu_time .01% .000094 0 100%
user_io_time 0
buffer_gets .02% 4 1 75.29%
cost 100.8% 1 0 100%
reads .03% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 2381934836

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE SNAP$
2 .. INDEX UNIQUE SCAN I_SNAP1 1 39 0

Execution Plan After Change:
Plan Id : 1187
Plan Hash Value : 2381934836

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 100 0 00:00:01
1 . UPDATE SNAP$
* 2 .. INDEX UNIQUE SCAN I_SNAP1 1 100 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 919
Schema Name : SYS
SQL ID : bypx1rgz00158
Execution Frequency : 22960
SQL Text : update sys.mlog$ set oldest = :1, oldest_pk = :2, oldest_oid = :3, oldest_new = :4, youngest = :5, oldest_seq = :6 where master = :7 and mowner = :8

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000104 .000053 49.13%
parse_time .000329
cpu_time .01% .000058 0 100%
user_io_time 0
buffer_gets .02% 5 2 60.13%
cost 100.8% 1 0 100%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 4090313777

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE MLOG$
2 .. TABLE ACCESS CLUSTER MLOG$ 1 63 1 00:00:01
3 ... INDEX UNIQUE SCAN I_MLOG# 1 0

Execution Plan After Change:
Plan Id : 1161
Plan Hash Value : 4090313777

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 88 0 00:00:01
1 . UPDATE MLOG$
2 .. TABLE ACCESS CLUSTER MLOG$ 1 88 0 00:00:01
* 3 ... INDEX UNIQUE SCAN I_MLOG# 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 843
Schema Name : SYS
SQL ID : 87qkab0x1s3xr
Execution Frequency : 22960
SQL Text : update sys.mlog$ set youngest = :1, yscn = :2 where mowner = :3 and master = :4

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .01% .000124 .000062 49.94%
parse_time .000874
cpu_time .01% .000068 0 100%
user_io_time 0
buffer_gets .02% 5 2 60.13%
cost 100.8% 1 0 100%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 4090313777

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE MLOG$
2 .. TABLE ACCESS CLUSTER MLOG$ 1 25 1 00:00:01
3 ... INDEX UNIQUE SCAN I_MLOG# 1 0

Execution Plan After Change:
Plan Id : 1085
Plan Hash Value : 4090313777

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 56 0 00:00:01
1 . UPDATE MLOG$
2 .. TABLE ACCESS CLUSTER MLOG$ 1 56 0 00:00:01
* 3 ... INDEX UNIQUE SCAN I_MLOG# 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 710
Schema Name : SYS
SQL ID : 1p6n2jxrca2a8
Execution Frequency : 22960
SQL Text : update "MACLEAN"."MLOG$_NEWTAB" set snaptime$$ = :1 where snaptime$$ > to_date('2100-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS')

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000082 .000042 48.75%
parse_time .001054
cpu_time 0% .000037 0 100%
user_io_time 0
buffer_gets .02% 3 0 100%
cost -100.8% 1 2 -100%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 287223022

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT
1 . UPDATE MLOG$_NEWTAB
2 .. TABLE ACCESS FULL MLOG$_NEWTAB

Execution Plan After Change:
Plan Id : 952
Plan Hash Value : 287223022

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 9 2 00:00:01
1 . UPDATE MLOG$_NEWTAB
* 2 .. TABLE ACCESS FULL MLOG$_NEWTAB 1 9 2 00:00:01

Predicate Information (identified by operation id):

Note
- dynamic sampling used for this statement

SQL Details:
Object ID : 892
Schema Name : SYS
SQL ID : amwaz5brtm76u
Execution Frequency : 22960
SQL Text : delete from "MACLEAN"."MLOG$_NEWTAB" where snaptime$$ <= :1

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000063 .000047 25.92%
parse_time .000132
cpu_time -.01% .000034 .000111 -224.71%
user_io_time 0
buffer_gets .02% 3 0 100%
cost -100.8% 1 2 -100%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 1938842286

Id Operation Name Rows Bytes Cost Time
0 DELETE STATEMENT
1 . DELETE MLOG$_NEWTAB
2 .. TABLE ACCESS FULL MLOG$_NEWTAB

Execution Plan After Change:
Plan Id : 1134
Plan Hash Value : 1938842286

Id Operation Name Rows Bytes Cost Time
0 DELETE STATEMENT 1 9 2 00:00:01
1 . DELETE MLOG$_NEWTAB
* 2 .. TABLE ACCESS FULL MLOG$_NEWTAB 1 9 2 00:00:01

Predicate Information (identified by operation id):

Note
- dynamic sampling used for this statement

SQL Details:
Object ID : 866
Schema Name : SYS
SQL ID : 96g93hntrzjtr
Execution Frequency : 62327
SQL Text : select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000056 .000067 -19.05%
parse_time .000026
cpu_time .02% .000069 0 100%
user_io_time 0
buffer_gets .01% 2 2 30.16%
cost 273.63% 1 0 100%
reads .06% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. This SQL statement returned zero rows.


Execution Plan Before Change:
Plan Hash Value : 2239883476

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT
1 . TABLE ACCESS BY INDEX ROWID HIST_HEAD$
2 .. INDEX RANGE SCAN I_HH_OBJ#_INTCOL#

Execution Plan After Change:
Plan Id : 1108
Plan Hash Value : 2239883476

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT
1 . TABLE ACCESS BY INDEX ROWID HIST_HEAD$
* 2 .. INDEX RANGE SCAN I_HH_OBJ#_INTCOL#

Predicate Information (identified by operation id):

SQL Details:
Object ID : 947
Schema Name : SYS
SQL ID : d3rfnc4ubc57j
Execution Frequency : 22960
SQL Text : UPDATE sys.snap$ SET status = :1, rscn = :2, refhnt = :3, flag = :4 WHERE vname = :5 AND sowner = :6 AND instsite = :7

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .01% .000128 .000067 47.48%
parse_time .000239
cpu_time .01% .00006 0 100%
user_io_time 0
buffer_gets .01% 3 1 67.28%
cost 100.8% 1 0 100%
reads .02% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 2381934836

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE SNAP$
2 .. INDEX UNIQUE SCAN I_SNAP1 1 37 0

Execution Plan After Change:
Plan Id : 1189
Plan Hash Value : 2381934836

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 99 0 00:00:01
1 . UPDATE SNAP$
* 2 .. INDEX UNIQUE SCAN I_SNAP1 1 99 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 832
Schema Name : SYS
SQL ID : 7vbdnn98ynscx
Execution Frequency : 22960
SQL Text : UPDATE SYS.JOB$ J SET FLAG = :B4 , NEXT_DATE = NVL(:B3 , J.NEXT_DATE) WHERE JOB = :B2 AND (:B1 = 'SYS' OR J.POWNER != 'SYS')

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .01% .00011 .000051 53.67%
parse_time .00032
cpu_time -.01% .000054 .000111 -105.73%
user_io_time 0
buffer_gets .01% 4 2 50.56%
cost 100.8% 1 0 100%
reads .04% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 3532225794

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE JOB$
2 .. TABLE ACCESS BY INDEX ROWID JOB$ 1 19 1 00:00:01
3 ... INDEX UNIQUE SCAN I_JOB_JOB 1 0

Execution Plan After Change:
Plan Id : 1074
Plan Hash Value : 3532225794

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 52 0 00:00:01
1 . UPDATE JOB$
* 2 .. TABLE ACCESS BY INDEX ROWID JOB$ 1 52 0 00:00:01
* 3 ... INDEX UNIQUE SCAN I_JOB_JOB 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 691
Schema Name : SYS
SQL ID : 120sawa70z3ht
Execution Frequency : 22960
SQL Text : update system.def$_destination set last_delivered = :1, last_enq_tid = :2, last_seq = :3, last_txn_count = :4, total_txn_count = total_txn_count + :5, total_prop_time_throughput = total_prop_time_throughput + :6, total_prop_time_latency = total_prop_time_latency + :7, to_communication_size = to_communication_size + :8, from_communication_size = from_communication_size + :9, spare1 = spare1 + :10, spare2 = spare2 + :11, spare3 = spare3 + :12, spare4 = spare4 + :13, last_error_number = :14, last_error_message = :15 where rowid = :16

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .01% .000211 .000044 79.13%
parse_time .007102
cpu_time .01% .000122 0 100%
user_io_time 0
buffer_gets .01% 2 0 100%
cost 0% 1 1 0%
reads .03% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. At least one important bind value was missing for this sql statement. The accuracy of the advisor's analysis may depend on all important bind values being supplied.


Execution Plan Before Change:
Plan Hash Value : 1898999806

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE DEF$_DESTINATION
2 .. TABLE ACCESS BY USER ROWID DEF$_DESTINATION 1 48 1 00:00:01

Execution Plan After Change:
Plan Id : 933
Plan Hash Value : 1898999806

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 1196 1 00:00:01
1 . UPDATE DEF$_DESTINATION
* 2 .. TABLE ACCESS BY USER ROWID DEF$_DESTINATION 1 1196 1 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 844
Schema Name : SYS
SQL ID : 88cwftrsh6rjb
Execution Frequency : 22960
SQL Text : update system.def$_destination set last_delivered = :1, last_enq_tid = :2, last_seq = :3, last_txn_count = :4, total_txn_count = total_txn_count + :5, total_prop_time_throughput = total_prop_time_throughput + :6, total_prop_time_latency = total_prop_time_latency + :7, to_communication_size = to_communication_size + :8, from_communication_size = from_communication_size + :9, spare1 = spare1 + :10, spare2 = spare2 + :11, spare3 = spare3 + :12, spare4 = spare4 + :13 where rowid = :14

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .01% .00018 .000042 76.62%
parse_time .00015
cpu_time .01% .000106 0 100%
user_io_time 0
buffer_gets .01% 2 0 100%
cost 0% 1 1 0%
reads .03% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. At least one important bind value was missing for this sql statement. The accuracy of the advisor's analysis may depend on all important bind values being supplied.


Execution Plan Before Change:
Plan Hash Value : 1898999806

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE DEF$_DESTINATION
2 .. TABLE ACCESS BY USER ROWID DEF$_DESTINATION 1 40 1 00:00:01

Execution Plan After Change:
Plan Id : 1086
Plan Hash Value : 1898999806

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 181 1 00:00:01
1 . UPDATE DEF$_DESTINATION
* 2 .. TABLE ACCESS BY USER ROWID DEF$_DESTINATION 1 181 1 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 820
Schema Name : SYS
SQL ID : 7f9sk6wcpy76f
Execution Frequency : 22960
SQL Text : select min(s1.lastrefreshscn) from sum$ s1, sumdetail$ s2 where s1.obj#= s2.sumobj# and s2.detailobj# = :1 and s1.obj#!= :2

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000085 .000069 18.8%
parse_time .00137
cpu_time -.01% .000049 .000111 -128.4%
user_io_time 0
buffer_gets .01% 3 1 66.82%
cost 302.4% 4 1 75%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (3):
  1. The structure of the SQL execution plan has changed.
  2. This SQL statement returned zero rows.
  3. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 4074612683

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 4
1 . SORT AGGREGATE 1 21
2 .. NESTED LOOPS 2 42 4 00:00:01
3 ... TABLE ACCESS FULL SUMDETAIL$ 2 20 2 00:00:01
4 ... TABLE ACCESS BY INDEX ROWID SUM$ 1 11 1 00:00:01
5 .... INDEX UNIQUE SCAN I_SUM$_1 1 0

Execution Plan After Change:
Plan Id : 1062
Plan Hash Value : 3029652425

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 52 1 00:00:01
1 . SORT AGGREGATE 1 52
2 .. NESTED LOOPS
3 ... NESTED LOOPS 1 52 1 00:00:01
* 4 .... TABLE ACCESS BY INDEX ROWID SUMDETAIL$ 1 26 1 00:00:01
* 5 ..... INDEX RANGE SCAN I_SUMDETAIL$_2 1 1 00:00:01
* 6 .... INDEX UNIQUE SCAN I_SUM$_1 1 0 00:00:01
7 ... TABLE ACCESS BY INDEX ROWID SUM$ 1 26 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 761
Schema Name : SYS
SQL ID : 459f3z9u4fb3u
Execution Frequency : 23002
SQL Text : select value$ from props$ where name = 'GLOBAL_DB_NAME'

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .01% .000175 .00007 59.89%
parse_time .000117
cpu_time .01% .000082 0 100%
user_io_time 0
buffer_gets .01% 4 2 50%
cost 0% 2 2 0%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 415205717

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 2
1 . TABLE ACCESS FULL PROPS$ 1 28 2 00:00:01

Execution Plan After Change:
Plan Id : 1003
Plan Hash Value : 415205717

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 28 2 00:00:01
* 1 . TABLE ACCESS FULL PROPS$ 1 28 2 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 773
Schema Name : SYS
SQL ID : 4yvsj6a508pgf
Execution Frequency : 45920
SQL Text : SELECT NAME FROM SYS.USER$ WHERE USER# = :B1

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .01% .000095 .000057 40.05%
parse_time .00015
cpu_time .01% .000051 0 100%
user_io_time 0
buffer_gets .01% 2 1 50%
cost 0% 1 1 0%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. This SQL statement returned zero rows.
  2. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 2709293936

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1
1 . TABLE ACCESS CLUSTER USER$ 1 14 1 00:00:01
2 .. INDEX UNIQUE SCAN I_USER# 1 0

Execution Plan After Change:
Plan Id : 1015
Plan Hash Value : 2709293936

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 18 1 00:00:01
1 . TABLE ACCESS CLUSTER USER$ 1 18 1 00:00:01
* 2 .. INDEX UNIQUE SCAN I_USER# 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 887
Schema Name : SYS
SQL ID : a9qtc5suhfqs8
Execution Frequency : 45920
SQL Text : select disabled, NVL(flag, '00000000') from system.def$_destination where rowid = :1

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .01% .000129 .000062 52.06%
parse_time .000098
cpu_time -.01% .000066 .000111 -67.2%
user_io_time 0
buffer_gets .01% 1 0 100%
cost 0% 1 1 0%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (3):
  1. This SQL statement returned zero rows.
  2. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.
  3. At least one important bind value was missing for this sql statement. The accuracy of the advisor's analysis may depend on all important bind values being supplied.


Execution Plan Before Change:
Plan Hash Value : 1917641327

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1
1 . TABLE ACCESS BY USER ROWID DEF$_DESTINATION 1 19 1 00:00:01

Execution Plan After Change:
Plan Id : 1129
Plan Hash Value : 1917641327

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 19 1 00:00:01
* 1 . TABLE ACCESS BY USER ROWID DEF$_DESTINATION 1 19 1 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 935
Schema Name : SYS
SQL ID : cgc66x001ru5q
Execution Frequency : 22960
SQL Text : select last_delivered, last_enq_tid from system.def$_destination

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000107 .000076 29.04%
parse_time .000074
cpu_time .01% .000056 0 100%
user_io_time 0
buffer_gets .01% 3 1 66.67%
cost 0% 2 2 0%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. This SQL statement returned zero rows.
  2. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 1726086208

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 2
1 . TABLE ACCESS FULL DEF$_DESTINATION 1 4 2 00:00:01

Execution Plan After Change:
Plan Id : 1177
Plan Hash Value : 1726086208

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 26 2 00:00:01
1 . TABLE ACCESS FULL DEF$_DESTINATION 1 26 2 00:00:01

SQL Details:
Object ID : 764
Schema Name : SYS
SQL ID : 49d638dnzfsub
Execution Frequency : 22960
SQL Text : select 1 from sumdelta$ a where a.tableobj# = :1 and a.timestamp > :2 and rownum = 1

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000063 .000088 -40.16%
parse_time .003386
cpu_time -.02% .000029 .000222 -672.41%
user_io_time 0
buffer_gets -.01% 1 3 -199.48%
cost -100.8% 1 2 -100%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. The structure of the SQL execution plan has changed.
  2. This SQL statement returned zero rows.


Execution Plan Before Change:
Plan Hash Value : 1839496746

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT
1 . COUNT STOPKEY
2 .. INDEX RANGE SCAN I_SUMDELTA$

Execution Plan After Change:
Plan Id : 1006
Plan Hash Value : 2650317422

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 22 2 00:00:01
* 1 . COUNT STOPKEY
* 2 .. TABLE ACCESS FULL SUMDELTA$ 1 22 2 00:00:01

Predicate Information (identified by operation id):

Note
- dynamic sampling used for this statement

SQL Details:
Object ID : 856
Schema Name : SYS
SQL ID : 8vhktgq0r66ny
Execution Frequency : 22960
SQL Text : select enq_tid from system.def$_aqcall where (cscn < :1)

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000093 .00006 35.32%
parse_time .015911
cpu_time 0% .000051 0 100%
user_io_time 0
buffer_gets -.01% 1 3 -198.78%
cost -100.8% 1 2 -100%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. The structure of the SQL execution plan has changed.
  2. This SQL statement returned zero rows.


Execution Plan Before Change:
Plan Hash Value : 2533530608

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT
1 . INDEX RANGE SCAN DEF$_TRANORDER

Execution Plan After Change:
Plan Id : 1098
Plan Hash Value : 2863750123

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 30 2 00:00:01
* 1 . TABLE ACCESS FULL DEF$_AQCALL 1 30 2 00:00:01

Predicate Information (identified by operation id):

Note
- dynamic sampling used for this statement

SQL Details:
Object ID : 883
Schema Name : SYS
SQL ID : a1xgxtssv5rrp
Execution Frequency : 2283
SQL Text : select sum(used_blocks), ts.ts# from GV$SORT_SEGMENT gv, ts$ ts where gv.tablespace_name = ts.name and ts.bitmapped <> 0 group by ts.ts#

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .01% .001406 .000279 80.15%
parse_time .001222
cpu_time .01% .00156 .000222 85.77%
user_io_time 0
buffer_gets .01% 31 14 54.84%
cost 20.05% 9 7 22.22%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. The structure of the SQL execution plan has changed.


Execution Plan Before Change:
Plan Hash Value : 3392096401

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 9
1 . SORT GROUP BY 23 1035 9 00:00:01
2 .. HASH JOIN 98 4410 8 00:00:01
3 ... TABLE ACCESS FULL TS$ 23 345 7 00:00:01
4 ... FIXED TABLE FULL X$KTSTSSD 100 3000 0

Execution Plan After Change:
Plan Id : 1125
Plan Hash Value : 3427437174

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 8 352 7 00:00:01
1 . HASH GROUP BY 8 352 7 00:00:01
* 2 .. HASH JOIN 94 4136 6 00:00:01
* 3 ... TABLE ACCESS FULL TS$ 8 112 5 00:00:01
4 ... PX COORDINATOR 100 3000 0 00:00:01
5 .... PX SEND QC (RANDOM) :TQ10000 100 3000 0 00:00:01
6 ..... VIEW GV$SORT_SEGMENT
7 ...... FIXED TABLE FULL X$KTSTSSD 100 3000 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 982
Schema Name : SYS
SQL ID : g7z4n2kpnzw6f
Execution Frequency : 22960
SQL Text : UPDATE sys.snap_reftime$ SET snaptime = :1, loadertime = :2, refscn = :3, fcmaskvec = :4, ejmaskvec = :5, masobj# = :6, sub_handle = :7, change_view = :8, mowner = :9, master = :10 WHERE sowner = :11 AND vname = :12 AND tablenum = :13 AND instsite = :14

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000107 .000052 51.4%
parse_time .000204
cpu_time .01% .00007 0 100%
user_io_time 0
buffer_gets .01% 2 1 52.53%
cost 100.8% 1 0 100%
reads .04% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 3866428682

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE SNAP_REFTIME$
2 .. INDEX UNIQUE SCAN I_SNAP_REFTIME1 1 69 0

Execution Plan After Change:
Plan Id : 1224
Plan Hash Value : 3866428682

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 426 0 00:00:01
1 . UPDATE SNAP_REFTIME$
* 2 .. INDEX UNIQUE SCAN I_SNAP_REFTIME1 1 426 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 915
Schema Name : SYS
SQL ID : bsa0wjtftg3uw
Execution Frequency : 11712
SQL Text : select file# from file$ where ts#=:1

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000132 .000071 46.15%
parse_time .000161
cpu_time 0% .000085 0 100%
user_io_time 0
buffer_gets .01% 4 2 50.9%
cost 0% 2 2 0%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 1512486435

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 2
1 . TABLE ACCESS FULL FILE$ 1 6 2 00:00:01

Execution Plan After Change:
Plan Id : 1157
Plan Hash Value : 1512486435

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 6 2 00:00:01
* 1 . TABLE ACCESS FULL FILE$ 1 6 2 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 699
Schema Name : SYS
SQL ID : 19x1189chq3xd
Execution Frequency : 22978
SQL Text : SELECT LOCKID FROM DBMS_LOCK_ALLOCATED WHERE NAME = :B1 FOR UPDATE

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .01% .00024 .000074 69.16%
parse_time .000429
cpu_time .01% .000133 0 100%
user_io_time 0
buffer_gets .01% 4 3 25.72%
cost 0% 1 1 0%
reads .03% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (3):
  1. The structure of the SQL execution plan has changed.
  2. This SQL statement returned zero rows.
  3. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 1874406218

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1
1 . FOR UPDATE
2 .. TABLE ACCESS BY INDEX ROWID DBMS_LOCK_ALLOCATED 1 42 1 00:00:01
3 ... INDEX UNIQUE SCAN SYS_C001305 1 0

Execution Plan After Change:
Plan Id : 941
Plan Hash Value : 2035885240

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 42 1 00:00:01
1 . FOR UPDATE
2 .. TABLE ACCESS BY INDEX ROWID DBMS_LOCK_ALLOCATED 1 42 1 00:00:01
* 3 ... INDEX UNIQUE SCAN SYS_C003408 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 929
Schema Name : SYS
SQL ID : ca3m153v3y1qu
Execution Frequency : 22960
SQL Text : SELECT R.REFGROUP FROM SYS.RGROUP$ R WHERE R.OWNER = :B3 AND R.NAME = :B2 AND R.INSTSITE = :B1 FOR UPDATE

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .02% .000325 .000079 75.7%
parse_time .000219
cpu_time -.01% .000164 .000222 -35.58%
user_io_time 0
buffer_gets .01% 4 3 25.65%
cost 100.8% 1 0 100%
reads .04% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 3425184707

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1
1 . FOR UPDATE
2 .. TABLE ACCESS BY INDEX ROWID RGROUP$ 1 20 1 00:00:01
3 ... INDEX UNIQUE SCAN I_RGROUP 1 0

Execution Plan After Change:
Plan Id : 1171
Plan Hash Value : 3425184707

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 60 0 00:00:01
1 . FOR UPDATE
2 .. TABLE ACCESS BY INDEX ROWID RGROUP$ 1 60 0 00:00:01
* 3 ... INDEX UNIQUE SCAN I_RGROUP 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 890
Schema Name : SYS
SQL ID : ak5crjygnpk60
Execution Frequency : 22975
SQL Text : UPDATE DBMS_LOCK_ALLOCATED SET EXPIRATION = SYSDATE + (:B1 /86400) WHERE ROWID = :B2

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .01% .000188 .000053 71.84%
parse_time .00068
cpu_time .01% .000113 0 100%
user_io_time 0
buffer_gets .01% 1 0 100%
cost 0% 1 1 0%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. At least one important bind value was missing for this sql statement. The accuracy of the advisor's analysis may depend on all important bind values being supplied.


Execution Plan Before Change:
Plan Hash Value : 3800490500

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE DBMS_LOCK_ALLOCATED
2 .. TABLE ACCESS BY USER ROWID DBMS_LOCK_ALLOCATED 1 20 1 00:00:01

Execution Plan After Change:
Plan Id : 1132
Plan Hash Value : 3800490500

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 20 1 00:00:01
1 . UPDATE DBMS_LOCK_ALLOCATED
* 2 .. TABLE ACCESS BY USER ROWID DBMS_LOCK_ALLOCATED 1 20 1 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 821
Schema Name : SYS
SQL ID : 7ggm58t3935kt
Execution Frequency : 22960
SQL Text : SELECT query_txt FROM sys.snap$ WHERE sowner = :1 and vname = :2 AND instsite = :3

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000072 .000082 -13.17%
parse_time .001306
cpu_time -.01% .00005 .000111 -121.45%
user_io_time 0
buffer_gets .01% 3 2 33.67%
cost 100.8% 1 0 100%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 4758086

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1
1 . TABLE ACCESS BY INDEX ROWID SNAP$ 1 23 1 00:00:01
2 .. INDEX UNIQUE SCAN I_SNAP1 1 0

Execution Plan After Change:
Plan Id : 1063
Plan Hash Value : 4758086

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 50 0 00:00:01
1 . TABLE ACCESS BY INDEX ROWID SNAP$ 1 50 0 00:00:01
* 2 .. INDEX UNIQUE SCAN I_SNAP1 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 701
Schema Name : SYS
SQL ID : 1bpwuhy1jzjdg
Execution Frequency : 22960
SQL Text : SELECT NVL(DISABLED, 'F'), FLAG FROM SYSTEM.DEF$_DESTINATION WHERE DBLINK = NLS_UPPER(:B2 ) AND CATCHUP = :B1

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .01% .000177 .000063 64.41%
parse_time .001655
cpu_time 0% .000093 .000111 -18.83%
user_io_time 0
buffer_gets .01% 2 1 50.09%
cost 100.8% 1 0 100%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. This SQL statement returned zero rows.
  2. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 2673646612

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1
1 . TABLE ACCESS BY INDEX ROWID DEF$_DESTINATION 1 16 1 00:00:01
2 .. INDEX UNIQUE SCAN DEF$_DESTINATION_PRIMARY 1 0

Execution Plan After Change:
Plan Id : 943
Plan Hash Value : 2673646612

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 83 0 00:00:01
1 . TABLE ACCESS BY INDEX ROWID DEF$_DESTINATION 1 83 0 00:00:01
* 2 .. INDEX UNIQUE SCAN DEF$_DESTINATION_PRIMARY 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 810
Schema Name : SYS
SQL ID : 6w58g883hbqxd
Execution Frequency : 22960
SQL Text : select last_delivered, last_enq_tid, last_seq, disabled, rowid, decode(apply_init, NULL, 1, 0), NVL(flag, '00000000') from system.def$_destination where dblink = :1 and catchup = :2

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .01% .000123 .000057 53.8%
parse_time .000852
cpu_time 0% .000067 .000111 -66.38%
user_io_time 0
buffer_gets .01% 2 1 50.02%
cost 100.8% 1 0 100%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. This SQL statement returned zero rows.
  2. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 2673646612

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1
1 . TABLE ACCESS BY INDEX ROWID DEF$_DESTINATION 1 48 1 00:00:01
2 .. INDEX UNIQUE SCAN DEF$_DESTINATION_PRIMARY 1 0

Execution Plan After Change:
Plan Id : 1052
Plan Hash Value : 2673646612

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 2136 0 00:00:01
1 . TABLE ACCESS BY INDEX ROWID DEF$_DESTINATION 1 2136 0 00:00:01
* 2 .. INDEX UNIQUE SCAN DEF$_DESTINATION_PRIMARY 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 725
Schema Name : SYS
SQL ID : 2gdu55njxq8cs
Execution Frequency : 22960
SQL Text : SELECT o.obj# FROM sys.obj$ o WHERE o.owner# = :1 AND o.name = :2 AND o.type# = 2 AND o.namespace = 1 AND o.linkname IS NULL AND o.remoteowner IS NULL

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000082 .00007 14.74%
parse_time .00029
cpu_time -.01% .000037 .000111 -200.23%
user_io_time 0
buffer_gets .01% 3 2 33.33%
cost 100.8% 3 2 33.33%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (3):
  1. The structure of the SQL execution plan has changed.
  2. This SQL statement returned zero rows.
  3. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 2853959010

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 3
1 . TABLE ACCESS BY INDEX ROWID OBJ$ 1 79 3 00:00:01
2 .. INDEX RANGE SCAN I_OBJ2 1 2 00:00:01

Execution Plan After Change:
Plan Id : 967
Plan Hash Value : 1191833520

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 78 2 00:00:01
* 1 . INDEX RANGE SCAN I_OBJ2 1 78 2 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 749
Schema Name : SYS
SQL ID : 3c1kubcdjnppq
Execution Frequency : 15400
SQL Text : update sys.col_usage$ set equality_preds = equality_preds + decode(bitand(:flag,1),0,0,1), equijoin_preds = equijoin_preds + decode(bitand(:flag,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0,1), range_preds = range_preds + decode(bitand(:flag,8),0,0,1), like_preds = like_preds + decode(bitand(:flag,16),0,0,1), null_preds = null_preds + decode(bitand(:flag,32),0,0,1), timestamp = :time where obj# = :objn and intcol# = :coln

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .00011 .000052 52.86%
parse_time .000027
cpu_time .01% .000103 0 100%
user_io_time 0
buffer_gets 0% 3 2 38.23%
cost 0% 2 2 0%
reads .05% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 477378112

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 2
1 . UPDATE COL_USAGE$
2 .. INDEX UNIQUE SCAN I_COL_USAGE$ 1 29 1 00:00:01

Execution Plan After Change:
Plan Id : 991
Plan Hash Value : 477378112

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 28 2 00:00:01
1 . UPDATE COL_USAGE$
* 2 .. INDEX UNIQUE SCAN I_COL_USAGE$ 1 28 1 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 985
Schema Name : SYS
SQL ID : gb4qjzp9su4h4
Execution Frequency : 1
SQL Text : DELETE FROM SYS.SCHEDULER$_JOB_RUN_DETAILS WHERE LOG_ID IN (SELECT E.LOG_ID FROM SYS.SCHEDULER$_EVENT_LOG E, SYS.SCHEDULER$_CLASS C WHERE E.TYPE# = 66 AND E.CLASS_ID = C.OBJ#(+) AND INSTR(NAME,'"')=0 AND OPERATION NOT LIKE 'CHAIN%' AND E.LOG_DATE < :B2 - NUMTODSINTERVAL(NVL(C.LOG_HISTORY, :B1 ), 'DAY'))

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .072123 .001064 98.52%
parse_time .00261
cpu_time 0% .073989 .000999 98.65%
user_io_time 0
buffer_gets 0% 17740 62 99.65%
cost 17.31% 3963 21 99.47%
reads 0% 1 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. The structure of the SQL execution plan has changed.
  2. At least one important bind value was missing for this sql statement. The accuracy of the advisor's analysis may depend on all important bind values being supplied.


Execution Plan Before Change:
Plan Hash Value : 4087975820

Id Operation Name Rows Bytes Cost Time

Execution Plan After Change:
Plan Id : 1227
Plan Hash Value : 3540382979

Id Operation Name Rows Bytes Cost Time
0 DELETE STATEMENT 12 204 21 00:00:01
1 . DELETE SCHEDULER$_JOB_RUN_DETAILS
2 .. NESTED LOOPS 12 204 21 00:00:01
3 ... VIEW VW_NSO_1 12 156 20 00:00:01
4 .... SORT UNIQUE 12 732
* 5 ..... FILTER
* 6 ...... HASH JOIN OUTER 12 732 20 00:00:01
* 7 ....... TABLE ACCESS FULL SCHEDULER$_EVENT_LOG 12 636 17 00:00:01
8 ....... TABLE ACCESS FULL SCHEDULER$_CLASS 13 104 2 00:00:01
* 9 ... INDEX RANGE SCAN I_SCHEDULER_JOB_RUN_DETAILS 1 4 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 722
Schema Name : SYS
SQL ID : 2d1tg62apru59
Execution Frequency : 1
SQL Text : SELECT REGEXP_SUBSTR(ADDITIONAL_INFO,'CHAIN_LOG_ID="[0-9]+"') FROM SYS.SCHEDULER$_EVENT_LOG E, SYS.SCHEDULER$_CLASS C WHERE E.TYPE# = 66 AND E.CLASS_ID = C.OBJ#(+) AND OPERATION IN ('CHAIN_RUN','CHAIN_RETRY_RUN') AND E.LOG_DATE < :B2 - NUMTODSINTERVAL(NVL(C.LOG_HISTORY, :B1 ), 'DAY') AND INSTR(E.NAME,'"')=0

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .037969 .000643 98.31%
parse_time .000967
cpu_time 0% .038994 .000777 98.01%
user_io_time 0
buffer_gets 0% 17731 60 99.66%
cost 17.28% 3955 18 99.54%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. This SQL statement returned zero rows.
  2. At least one important bind value was missing for this sql statement. The accuracy of the advisor's analysis may depend on all important bind values being supplied.


Execution Plan Before Change:
Plan Hash Value : 689816502

Id Operation Name Rows Bytes Cost Time

Execution Plan After Change:
Plan Id : 964
Plan Hash Value : 689816502

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 319 18 00:00:01
* 1 . FILTER
2 .. NESTED LOOPS OUTER 1 319 18 00:00:01
* 3 ... TABLE ACCESS FULL SCHEDULER$_EVENT_LOG 1 311 17 00:00:01
4 ... TABLE ACCESS BY INDEX ROWID SCHEDULER$_CLASS 1 8 1 00:00:01
* 5 .... INDEX UNIQUE SCAN SCHEDULER$_CLASS_PK 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 878
Schema Name : SYS
SQL ID : 9qgtwh66xg6nz
Execution Frequency : 3574
SQL Text : update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000166 .000055 66.86%
parse_time .000235
cpu_time 0% .000102 0 100%
user_io_time 0
buffer_gets 0% 6 2 68.49%
cost 0% 2 2 0%
reads .01% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 2170058777

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 2
1 . UPDATE SEG$
2 .. TABLE ACCESS CLUSTER SEG$ 1 60 2 00:00:01
3 ... INDEX UNIQUE SCAN I_FILE#_BLOCK# 1 1 00:00:01

Execution Plan After Change:
Plan Id : 1120
Plan Hash Value : 2170058777

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 64 2 00:00:01
1 . UPDATE SEG$
2 .. TABLE ACCESS CLUSTER SEG$ 1 64 2 00:00:01
* 3 ... INDEX UNIQUE SCAN I_FILE#_BLOCK# 1 1 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 870
Schema Name : SYS
SQL ID : 9f30br0xvyxxb
Execution Frequency : 4790
SQL Text : UPDATE WRI$_SQLSET_MASK SET PRIORITY = NVL(:B4 , PRIORITY), OTHER = NVL(:B3 , OTHER) WHERE STMT_ID = :B2 AND PLAN_HASH_VALUE = :B1

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .00007 .000046 33.95%
parse_time .000188
cpu_time 0% .000046 .000111 -141.72%
user_io_time 0
buffer_gets 0% 4 1 75.54%
cost 21.03% 1 0 100%
reads .04% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. The structure of the SQL execution plan has changed.


Execution Plan Before Change:
Plan Hash Value : 258552715

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE WRI$_SQLSET_MASK
2 .. TABLE ACCESS BY INDEX ROWID WRI$_SQLSET_MASK 1 2041 0
3 ... INDEX UNIQUE SCAN WRI$_SQLSET_MASK_PK 1 0

Execution Plan After Change:
Plan Id : 1112
Plan Hash Value : 4107729842

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 2041 0 00:00:01
1 . UPDATE WRI$_SQLSET_MASK
* 2 .. INDEX UNIQUE SCAN WRI$_SQLSET_MASK_PK 1 2041 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 777
Schema Name : SYS
SQL ID : 53saa2zkr6wc3
Execution Frequency : 6542
SQL Text : select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .00006 .000093 -53.97%
parse_time .000383
cpu_time 0% .000065 .000111 -70.49%
user_io_time 0
buffer_gets 0% 4 3 37.97%
cost 0% 3 3 0%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 3954488388

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 3
1 . TABLE ACCESS BY INDEX ROWID CCOL$ 1 15 3 00:00:01
2 .. INDEX RANGE SCAN I_CCOL1 1 2 00:00:01

Execution Plan After Change:
Plan Id : 1019
Plan Hash Value : 3954488388

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 15 3 00:00:01
1 . TABLE ACCESS BY INDEX ROWID CCOL$ 1 15 3 00:00:01
* 2 .. INDEX RANGE SCAN I_CCOL1 1 2 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 904
Schema Name : SYS
SQL ID : b3ys9bs1v9cch
Execution Frequency : 1
SQL Text : delete from WRH$_SYSMETRIC_SUMMARY tab where (:beg_snap <= tab.snap_id and tab.snap_id <= :end_snap and dbid = :dbid) and not exists (select 1 from WRM$_BASELINE b where (tab.dbid = b.dbid) and (tab.snap_id >= b.start_snap_id) and (tab.snap_id <= b.end_snap_id))

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .087061 .000069 99.92%
parse_time .018555
cpu_time 0% .06799 0 100%
user_io_time 0
buffer_gets 0% 10372 3 99.97%
cost .04% 14 5 64.29%
reads .06% 236 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 3240 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. The structure of the SQL execution plan has changed.


Execution Plan Before Change:
Plan Hash Value : 524949286

Id Operation Name Rows Bytes Cost Time

Execution Plan After Change:
Plan Id : 1146
Plan Hash Value : 2483910460

Id Operation Name Rows Bytes Cost Time
0 DELETE STATEMENT 1 54 5 00:00:01
1 . DELETE WRH$_SYSMETRIC_SUMMARY
* 2 .. FILTER
3 ... NESTED LOOPS ANTI 1 54 5 00:00:01
* 4 .... INDEX RANGE SCAN WRH$_SYSMETRIC_SUMMARY_INDEX 1 21 3 00:00:01
* 5 .... TABLE ACCESS BY INDEX ROWID WRM$_BASELINE 1 33 2 00:00:01
* 6 ..... INDEX RANGE SCAN WRM$_BASELINE_PK 1 1 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 885
Schema Name : SYS
SQL ID : a73wbv1yu8x5c
Execution Frequency : 1836
SQL Text : select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0) from cdef$ where obj#=:1

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000122 .000055 55%
parse_time .000156
cpu_time 0% .00014 .000111 20.99%
user_io_time 0
buffer_gets 0% 7 2 73.38%
cost 0% 2 2 0%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 4 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. This SQL statement returned zero rows.
  2. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 2570921597

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 2
1 . TABLE ACCESS CLUSTER CDEF$ 3 123 2 00:00:01
2 .. INDEX UNIQUE SCAN I_COBJ# 1 1 00:00:01

Execution Plan After Change:
Plan Id : 1127
Plan Hash Value : 2570921597

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 3 144 2 00:00:01
1 . TABLE ACCESS CLUSTER CDEF$ 3 144 2 00:00:01
* 2 .. INDEX UNIQUE SCAN I_COBJ# 1 1 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 772
Schema Name : SYS
SQL ID : 4w33a18a3ma73
Execution Frequency : 4594
SQL Text : select count(*) from sys.job$ where next_date < :1 and (field1 = :2 or (field1 = 0 and 'Y' = :3)) and ((dbms_logstdby.db_is_logstdby = 0 and job < 1000000000) or (dbms_logstdby.db_is_logstdby = 1 and job >

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000165 .000076 54%
parse_time .002112
cpu_time 0% .000065 .000111 -71.14%
user_io_time 0
buffer_gets 0% 3 1 67.59%
cost 40.34% 2 0 100%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. The structure of the SQL execution plan has changed.


Execution Plan Before Change:
Plan Hash Value : 1201854128

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 2
1 . SORT AGGREGATE 1 13
2 .. TABLE ACCESS FULL JOB$ 1 13 2 00:00:01

Execution Plan After Change:
Plan Id : 1014
Plan Hash Value : 3859747703

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 35 0 00:00:01
1 . SORT AGGREGATE 1 35
* 2 .. TABLE ACCESS BY INDEX ROWID JOB$ 1 35 0 00:00:01
* 3 ... INDEX RANGE SCAN I_JOB_NEXT 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 824
Schema Name : SYS
SQL ID : 7hngp2sru12zc
Execution Frequency : 1
SQL Text : DELETE FROM SYS.SCHEDULER$_EVENT_LOG WHERE LOG_ID IN (SELECT E.LOG_ID FROM SYS.SCHEDULER$_EVENT_LOG E, SYS.SCHEDULER$_CLASS C WHERE E.TYPE# = 66 AND E.CLASS_ID = C.OBJ#(+) AND INSTR(NAME,'"')=0 AND OPERATION NOT LIKE 'CHAIN%' AND E.LOG_DATE < :B2 - NUMTODSINTERVAL(NVL(C.LOG_HISTORY, :B1 ), 'DAY'))

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .75216 .000865 99.88%
parse_time .002821
cpu_time 0% .19897 .000999 99.5%
user_io_time 0
buffer_gets 0% 9087 62 99.32%
cost 7.07% 1636 25 98.47%
reads 1.01% 3823 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. The structure of the SQL execution plan has changed.
  2. At least one important bind value was missing for this sql statement. The accuracy of the advisor's analysis may depend on all important bind values being supplied.


Execution Plan Before Change:
Plan Hash Value : 2257969734

Id Operation Name Rows Bytes Cost Time

Execution Plan After Change:
Plan Id : 1066
Plan Hash Value : 592921269

Id Operation Name Rows Bytes Cost Time
0 DELETE STATEMENT 12 360 25 00:00:01
1 . DELETE SCHEDULER$_EVENT_LOG
* 2 .. HASH JOIN RIGHT SEMI 12 360 25 00:00:01
3 ... VIEW VW_NSO_1 12 156 20 00:00:01
* 4 .... FILTER
* 5 ..... HASH JOIN OUTER 12 732 20 00:00:01
* 6 ...... TABLE ACCESS FULL SCHEDULER$_EVENT_LOG 12 636 17 00:00:01
7 ...... TABLE ACCESS FULL SCHEDULER$_CLASS 13 104 2 00:00:01
8 ... INDEX FULL SCAN SCHEDULER$_INSTANCE_PK 1314 22338 5 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 875
Schema Name : SYS
SQL ID : 9p1um1wd886xb
Execution Frequency : 1304
SQL Text : select o.owner#, u.name, o.name, o.namespace, o.obj#, d.d_timestamp, nvl(d.property,0), o.type#, o.subname, d.d_attrs from dependency$ d, obj$ o, user$ u where d.p_obj#=:1 and (d.p_timestamp=:2 or d.property=2) and d.d_obj#=o.obj# and o.owner#=u.user# order by o.obj#

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000182 .000086 52.75%
parse_time .000828
cpu_time 0% .000135 .000111 17.75%
user_io_time 0
buffer_gets 0% 7 2 72.65%
cost 0% 8 8 0%
reads .01% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. The structure of the SQL execution plan has changed.
  2. This SQL statement returned zero rows.


Execution Plan Before Change:
Plan Hash Value : 762350482

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 8
1 . SORT ORDER BY 1 85 8 00:00:01
2 .. NESTED LOOPS 1 85 7 00:00:01
3 ... NESTED LOOPS 1 71 6 00:00:01
4 .... TABLE ACCESS BY INDEX ROWID DEPENDENCY$ 1 31 5 00:00:01
5 ..... INDEX RANGE SCAN I_DEPENDENCY2 4 3 00:00:01
6 .... TABLE ACCESS BY INDEX ROWID OBJ$ 1 40 1 00:00:01
7 ..... INDEX UNIQUE SCAN I_OBJ1 1 0
8 ... TABLE ACCESS CLUSTER USER$ 1 14 1 00:00:01
9 .... INDEX UNIQUE SCAN I_USER# 1 0

Execution Plan After Change:
Plan Id : 1117
Plan Hash Value : 77848507

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 89 8 00:00:01
1 . SORT ORDER BY 1 89 8 00:00:01
2 .. NESTED LOOPS 1 89 7 00:00:01
3 ... NESTED LOOPS 1 71 6 00:00:01
* 4 .... TABLE ACCESS BY INDEX ROWID DEPENDENCY$ 1 35 4 00:00:01
* 5 ..... INDEX RANGE SCAN I_DEPENDENCY2 3 2 00:00:01
6 .... TABLE ACCESS BY INDEX ROWID OBJ$ 1 36 2 00:00:01
* 7 ..... INDEX RANGE SCAN I_OBJ1 1 1 00:00:01
8 ... TABLE ACCESS CLUSTER USER$ 1 18 1 00:00:01
* 9 .... INDEX UNIQUE SCAN I_USER# 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 855
Schema Name : SYS
SQL ID : 8swypbbr0m372
Execution Frequency : 3136
SQL Text : select order#,columns,types from access$ where d_obj#=:1

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000148 .000071 52.13%
parse_time .000167
cpu_time 0% .000056 0 100%
user_io_time 0
buffer_gets 0% 5 3 41.76%
cost -27.54% 1 3 -200%
reads .06% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 2
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 893970548

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT
1 . TABLE ACCESS BY INDEX ROWID ACCESS$
2 .. INDEX RANGE SCAN I_ACCESS1

Execution Plan After Change:
Plan Id : 1097
Plan Hash Value : 893970548

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 4 172 3 00:00:01
1 . TABLE ACCESS BY INDEX ROWID ACCESS$ 4 172 3 00:00:01
* 2 .. INDEX RANGE SCAN I_ACCESS1 4 2 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 842
Schema Name : SYS
SQL ID : 86bbtavv6hwmr
Execution Frequency : 1
SQL Text : SELECT sql_id, sql_text FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('buffer_gets > 500')) ORDER BY sql_id

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .563993 .031735 94.37%
parse_time .000848
cpu_time 0% .422935 .030662 92.75%
user_io_time 0
buffer_gets 0% 6351 0 100%
cost -.02% 26 30 -15.38%
reads .01% 31 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 920 51
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 3 executions.


Findings (1):
  1. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 4036651514

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 26
1 . SORT ORDER BY 8168 16336 26 00:00:01
2 .. COLLECTION ITERATOR PICKLER FETCH SELECT_CURSOR_CACHE

Execution Plan After Change:
Plan Id : 1084
Plan Hash Value : 4036651514

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 8168 16336 30 00:00:01
1 . SORT ORDER BY 8168 16336 30 00:00:01
2 .. COLLECTION ITERATOR PICKLER FETCH SELECT_CURSOR_CACHE 8168 16336 29 00:00:01

SQL Details:
Object ID : 966
Schema Name : SYS
SQL ID : fd9hn33xa7bph
Execution Frequency : 1174
SQL Text : delete from WRI$_ALERT_OUTSTANDING where reason_id = :1 and object_id = :2 and subobject_id = :3 and internal_instance_number = :4 returning owner, object_name, subobject_name, sequence_id, error_instance_id, creation_time into :5, :6, :7, :8, :9, :10

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .01% .001164 .000047 95.96%
parse_time .000477
cpu_time 0% .000122 0 100%
user_io_time 0
buffer_gets 0% 6 1 84.14%
cost 5.15% 1 0 100%
reads .01% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 997253226

Id Operation Name Rows Bytes Cost Time
0 DELETE STATEMENT 1
1 . DELETE WRI$_ALERT_OUTSTANDING
2 .. INDEX UNIQUE SCAN WRI$_ALERTS_OUTSTANDING_PK 1 445 0

Execution Plan After Change:
Plan Id : 1208
Plan Hash Value : 997253226

Id Operation Name Rows Bytes Cost Time
0 DELETE STATEMENT 1 445 0 00:00:01
1 . DELETE WRI$_ALERT_OUTSTANDING
* 2 .. INDEX UNIQUE SCAN WRI$_ALERTS_OUTSTANDING_PK 1 445 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 768
Schema Name : SYS
SQL ID : 4ng4xn7jw3dbb
Execution Frequency : 1
SQL Text : delete from WRH$_ENQUEUE_STAT tab where (:beg_snap <= tab.snap_id and tab.snap_id <= :end_snap and dbid = :dbid) and not exists (select 1 from WRM$_BASELINE b where (tab.dbid = b.dbid) and (tab.snap_id >= b.start_snap_id) and (tab.snap_id <= b.end_snap_id))

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .042624 .000162 99.62%
parse_time .025382
cpu_time 0% .037994 .000222 99.42%
user_io_time 0
buffer_gets 0% 6061 3 99.95%
cost .03% 11 5 54.55%
reads .02% 58 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1920 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. The structure of the SQL execution plan has changed.


Execution Plan Before Change:
Plan Hash Value : 2779820094

Id Operation Name Rows Bytes Cost Time

Execution Plan After Change:
Plan Id : 1010
Plan Hash Value : 1392069464

Id Operation Name Rows Bytes Cost Time
0 DELETE STATEMENT 1 65 5 00:00:01
1 . DELETE WRH$_ENQUEUE_STAT
* 2 .. FILTER
3 ... NESTED LOOPS ANTI 1 65 5 00:00:01
* 4 .... INDEX RANGE SCAN WRH$_ENQUEUE_STAT_PK 1 32 3 00:00:01
* 5 .... TABLE ACCESS BY INDEX ROWID WRM$_BASELINE 1 33 2 00:00:01
* 6 ..... INDEX RANGE SCAN WRM$_BASELINE_PK 1 1 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 724
Schema Name : SYS
SQL ID : 2g9qjuvr2hju9
Execution Frequency : 803
SQL Text : update sys.scheduler$_job set next_run_date = :1, run_count = :2, retry_count = :3, running_instance = :4, running_slave = :5, job_status = :6, last_end_date = :7 where obj# = :8

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000334 .000046 86.23%
parse_time .003539
cpu_time 0% .000161 .000111 30.9%
user_io_time 0
buffer_gets 0% 8 1 88.13%
cost 0% 1 1 0%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 105323984

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE SCHEDULER$_JOB
2 .. INDEX UNIQUE SCAN SCHEDULER$_JOB_PK 1 37 0

Execution Plan After Change:
Plan Id : 966
Plan Hash Value : 105323984

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 33 1 00:00:01
1 . UPDATE SCHEDULER$_JOB
* 2 .. INDEX UNIQUE SCAN SCHEDULER$_JOB_PK 1 33 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 896
Schema Name : SYS
SQL ID : aqhru5uwcvukg
Execution Frequency : 297
SQL Text : INSERT ALL WHEN (:B29 = 1) THEN INTO WRI$_SQLSET_STATEMENTS (ID, SQLSET_ID, SQL_ID, FORCE_MATCHING_SIGNATURE, PARSING_SCHEMA_NAME, MODULE, ACTION, COMMAND_TYPE) VALUES (:B15 , :B28 , :B27 , :B26 , :B22 , :B25 , :B24 , :B23 ) WHEN (1 = 1) THEN INTO WRI$_SQLSET_PLANS (STMT_ID, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME, BIND_DATA, OPTIMIZER_ENV, PLAN_TIMESTAMP, BINDS_CAPTURED) VALUES (:B15 , :B14 , :B22 , :B21 , :B20 , :B19 , :B18 ) INTO WRI$_SQLSET_MASK (STMT_ID, PLAN_HASH_VALUE, PRIORITY, OTHER) VALUES (:B15 , :B14 , :B17 , :B16 ) INTO WRI$_SQLSET_STATISTICS (STMT_ID, PLAN_HASH_VALUE, ELAPSED_TIME, ELAPSED_TIME_DELTA, CPU_TIME, CPU_TIME_DELTA, BUFFER_GETS, BUFFER_GETS_DELTA, DISK_READS, DISK_READS_DELTA, DIRECT_WRITES, DIRECT_WRITES_DELTA, ROWS_PROCESSED, ROWS_PROCESSED_DELTA, FETCHES, FETCHES_DELTA, EXECUTIONS, EXECUTIONS_DELTA, END_OF_FETCH_COUNT, OPTIMIZER_COST, FIRST_LOAD_TIME, FIRST_LOAD_TIME_DELTA, STAT_PERIOD, ACTIVE_STAT_PERIOD) VALUES (:B15 , :B14 , :B13 , 0, :B12 , 0, :B11 , 0, :B10 , 0, :B9 , 0, :B8 , 0, :B7 , 0, :B6 , 0, :B5 , :B4 , :B3 , NULL, :B2 , :B1 ) SELECT 1 FROM DUAL

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000381 .000087 77.15%
parse_time .007231
cpu_time 0% .000185 .000111 40.05%
user_io_time 0
buffer_gets 0% 18 0 100%
cost 0% 2 2 0%
reads .03% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 4 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 2939908344

Id Operation Name Rows Bytes Cost Time
0 INSERT STATEMENT 2
1 . MULTI-TABLE INSERT
2 .. FAST DUAL 1 2 00:00:01

Execution Plan After Change:
Plan Id : 1138
Plan Hash Value : 2939908344

Id Operation Name Rows Bytes Cost Time
0 INSERT STATEMENT 1 2 00:00:01
1 . MULTI-TABLE INSERT
2 .. FAST DUAL 1 2 00:00:01
3 .. INTO WRI$_SQLSET_STATEMENTS
4 .. INTO WRI$_SQLSET_PLANS
5 .. INTO WRI$_SQLSET_MASK
6 .. INTO WRI$_SQLSET_STATISTICS

SQL Details:
Object ID : 962
Schema Name : SYS
SQL ID : f6y537gy0p982
Execution Frequency : 861
SQL Text : update sys.scheduler$_job set running_instance = :1, running_slave = :2, job_status = :3, last_start_date = :4 where obj# = :5

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000668 .000045 93.27%
parse_time .000172
cpu_time 0% .000435 0 100%
user_io_time 0
buffer_gets 0% 7 1 86.27%
cost 0% 1 1 0%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 105323984

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE SCHEDULER$_JOB
2 .. INDEX UNIQUE SCAN SCHEDULER$_JOB_PK 1 24 0

Execution Plan After Change:
Plan Id : 1204
Plan Hash Value : 105323984

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 22 1 00:00:01
1 . UPDATE SCHEDULER$_JOB
* 2 .. INDEX UNIQUE SCAN SCHEDULER$_JOB_PK 1 22 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 876
Schema Name : SYS
SQL ID : 9pudktzqq9ywb
Execution Frequency : 5035
SQL Text : select a.obj# OBJOID, a.class_oid CLSOID, decode(bitand(a.flags, 16384), 0, a.next_run_date, a.last_enabled_time) RUNTIME, (2*a.priority + decode(bitand(a.job_status, 4), 0, 0, decode(a.running_instance, :1, -1, 1))) PRI, 1 JOBTYPE, a.schedule_limit SCHLIM, a.job_weight WT, decode(a.running_instance, NULL, 0, a.running_instance) INST, decode(bitand(a.flags, 16384), 0, 0, 1) RUNNOW, decode(bitand(a.job_status, 8388608), 0, 0, 1) ENQ_SCHLIM from sys.scheduler$_job a, v$instance i where bitand(a.job_status, 515) = 1 and ( i.logins = 'ALLOWED' or bitand(a.flags, 2147483648) <> 0 ) and ((bitand(a.flags, 134217728 + 268435456) = 0) or (bitand(a.job_status, 1024) <> 0)) and bitand(a.flags, 4096) = 0 and (a.next_run_date <= :2 or bitand(a.flags, 16384) <> 0) and a.class_oid is not null and a.class_oid in (select b.obj# from scheduler$_class b where bitand(b.flags, :4) <> 0 and lower(b.affinity) = lower(:5)) order by RUNTIME, CLSOID, PRI, WT DESC, OBJOID

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000277 .000129 53.5%
parse_time .006527
cpu_time .01% .000339 0 100%
user_io_time 0
buffer_gets 0% 3 2 34.86%
cost -22.1% 4 5 -25%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (3):
  1. The structure of the SQL execution plan has changed.
  2. This SQL statement returned zero rows.
  3. At least one important bind value was missing for this sql statement. The accuracy of the advisor's analysis may depend on all important bind values being supplied.


Execution Plan Before Change:
Plan Hash Value : 995320577

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 4
1 . SORT ORDER BY 2 318 4 00:00:01
2 .. MERGE JOIN CARTESIAN 2 318 3 00:00:01
3 ... TABLE ACCESS BY INDEX ROWID SCHEDULER$_JOB 1 58 1 00:00:01
4 .... NESTED LOOPS 1 159 3 00:00:01
5 ..... MERGE JOIN CARTESIAN 1 101 2 00:00:01
6 ...... MERGE JOIN CARTESIAN 1 60 0
7 ....... FIXED TABLE FULL X$KSUXSINST 1 26 0
8 ....... BUFFER SORT 1 34 0
9 ........ FIXED TABLE FULL X$KVIT 1 34 0
10 ...... BUFFER SORT 1 41 2 00:00:01
11 ....... TABLE ACCESS FULL SCHEDULER$_CLASS 1 41 2 00:00:01
12 ..... INDEX RANGE SCAN I_SCHEDULER_JOB2 4 0
13 ... BUFFER SORT 100 3 00:00:01
14 .... FIXED TABLE FULL X$QUIESCE 100 0

Execution Plan After Change:
Plan Id : 1118
Plan Hash Value : 909600012

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 18 2826 5 00:00:01
1 . SORT ORDER BY 18 2826 5 00:00:01
2 .. MERGE JOIN CARTESIAN 18 2826 4 00:00:01
3 ... NESTED LOOPS
4 .... NESTED LOOPS 1 157 4 00:00:01
5 ..... MERGE JOIN CARTESIAN 1 103 2 00:00:01
6 ...... MERGE JOIN CARTESIAN 1 60 0 00:00:01
* 7 ....... FIXED TABLE FULL X$KSUXSINST 1 26 0 00:00:01
8 ....... BUFFER SORT 1 34 0 00:00:01
* 9 ........ FIXED TABLE FULL X$KVIT 1 34 0 00:00:01
10 ...... BUFFER SORT 1 43 2 00:00:01
* 11 ....... TABLE ACCESS FULL SCHEDULER$_CLASS 1 43 2 00:00:01
* 12 ..... INDEX RANGE SCAN I_SCHEDULER_JOB2 4 0 00:00:01
* 13 .... TABLE ACCESS BY INDEX ROWID SCHEDULER$_JOB 2 108 2 00:00:01
14 ... BUFFER SORT 100 3 00:00:01
15 .... FIXED TABLE FULL X$QUIESCE 100 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 704
Schema Name : SYS
SQL ID : 1gfaj4z5hn1kf
Execution Frequency : 3112
SQL Text : delete from dependency$ where d_obj#=:1

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000127 .000052 58.92%
parse_time .000925
cpu_time 0% .000065 0 100%
user_io_time 0
buffer_gets 0% 3 2 45.91%
cost 13.66% 3 2 33.33%
reads .01% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 1110520934

Id Operation Name Rows Bytes Cost Time
0 DELETE STATEMENT 3
1 . DELETE DEPENDENCY$
2 .. INDEX RANGE SCAN I_DEPENDENCY1 3 87 3 00:00:01

Execution Plan After Change:
Plan Id : 946
Plan Hash Value : 1110520934

Id Operation Name Rows Bytes Cost Time
0 DELETE STATEMENT 1 29 2 00:00:01
1 . DELETE DEPENDENCY$
* 2 .. INDEX RANGE SCAN I_DEPENDENCY1 1 29 2 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 854
Schema Name : SYS
SQL ID : 8savyrs6b4cp2
Execution Frequency : 4779
SQL Text : UPDATE WRI$_SQLSET_PLANS SET PARSING_SCHEMA_NAME = NVL(:B4 , PARSING_SCHEMA_NAME), OPTIMIZER_ENV = NVL(:B3 , OPTIMIZER_ENV) WHERE STMT_ID = :B2 AND PLAN_HASH_VALUE = :B1

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000043 .000043 .16%
parse_time .000168
cpu_time 0% .000027 0 100%
user_io_time 0
buffer_gets 0% 2 1 52.41%
cost 20.98% 1 0 100%
reads .04% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 245262389

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE WRI$_SQLSET_PLANS
2 .. INDEX UNIQUE SCAN WRI$_SQLSET_PLANS_PK 1 545 0

Execution Plan After Change:
Plan Id : 1096
Plan Hash Value : 245262389

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 1045 0 00:00:01
1 . UPDATE WRI$_SQLSET_PLANS
* 2 .. INDEX UNIQUE SCAN WRI$_SQLSET_PLANS_PK 1 1045 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 813
Schema Name : SYS
SQL ID : 71p20wq7rr9w4
Execution Frequency : 4777
SQL Text : UPDATE WRI$_SQLSET_STATISTICS SET ELAPSED_TIME = NVL(:B15 , ELAPSED_TIME), CPU_TIME = NVL(:B14 , CPU_TIME), BUFFER_GETS = NVL(:B13 , BUFFER_GETS), DISK_READS = NVL(:B12 , DISK_READS), DIRECT_WRITES = NVL(:B11 , DIRECT_WRITES), ROWS_PROCESSED = NVL(:B10 , ROWS_PROCESSED), FETCHES = NVL(:B9 , FETCHES), EXECUTIONS = NVL(:B8 , EXECUTIONS), END_OF_FETCH_COUNT = NVL(:B7 , END_OF_FETCH_COUNT), OPTIMIZER_COST = NVL(:B6 , OPTIMIZER_COST), FIRST_LOAD_TIME = NVL(:B5 , FIRST_LOAD_TIME), STAT_PERIOD = NVL(:B4 , STAT_PERIOD), ACTIVE_STAT_PERIOD = NVL(:B3 , ACTIVE_STAT_PERIOD) WHERE STMT_ID = :B2 AND PLAN_HASH_VALUE = :B1

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time .14% .007725 .000052 99.33%
parse_time .001281
cpu_time 0% .000035 0 100%
user_io_time 0
buffer_gets 0% 2 1 52.27%
cost 20.97% 1 0 100%
reads .03% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 759112154

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE WRI$_SQLSET_STATISTICS
2 .. INDEX UNIQUE SCAN WRI$_SQLSET_STATISTICS_PK 1 193 0

Execution Plan After Change:
Plan Id : 1055
Plan Hash Value : 759112154

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 193 0 00:00:01
1 . UPDATE WRI$_SQLSET_STATISTICS
* 2 .. INDEX UNIQUE SCAN WRI$_SQLSET_STATISTICS_PK 1 193 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 765
Schema Name : SYS
SQL ID : 4g4425d7a24k1
Execution Frequency : 4772
SQL Text : UPDATE WRI$_SQLSET_STATEMENTS SET MODULE = NVL(:B4 , MODULE), ACTION = NVL(:B3 , ACTION), COMMAND_TYPE = NVL(:B2 , COMMAND_TYPE) WHERE ID = :B1

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000049 .000044 10.69%
parse_time .000123
cpu_time 0% .00002 .000111 -446.12%
user_io_time 0
buffer_gets 0% 2 1 50.79%
cost 20.95% 1 0 100%
reads .01% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 2428692559

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE WRI$_SQLSET_STATEMENTS
2 .. INDEX UNIQUE SCAN WRI$_SQLSET_STATEMENTS_PK 1 70 0

Execution Plan After Change:
Plan Id : 1007
Plan Hash Value : 2428692559

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 94 0 00:00:01
1 . UPDATE WRI$_SQLSET_STATEMENTS
* 2 .. INDEX UNIQUE SCAN WRI$_SQLSET_STATEMENTS_PK 1 94 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 729
Schema Name : SYS
SQL ID : 2mp99nzd9u1qp
Execution Frequency : 18
SQL Text : delete from histgrm$ where obj# = :1

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .005085 .000049 99.04%
parse_time .009076
cpu_time 0% .004666 .000111 97.62%
user_io_time 0
buffer_gets 0% 260 2 99.23%
cost 0% 2 2 0%
reads .02% 4 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 81 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 2015116224

Id Operation Name Rows Bytes Cost Time

Execution Plan After Change:
Plan Id : 971
Plan Hash Value : 2015116224

Id Operation Name Rows Bytes Cost Time
0 DELETE STATEMENT 1 8 2 00:00:01
1 . DELETE HISTGRM$
* 2 .. INDEX RANGE SCAN I_H_OBJ#_COL# 1 8 2 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 905
Schema Name : SYS
SQL ID : b6b0ujx89mmnc
Execution Frequency : 1
SQL Text : select o.owner#,o.obj#,decode(o.linkname,null, decode(u.name,null,'SYS',u.name),o.remoteowner), o.name,o.linkname,o.namespace,o.subname from user$ u, obj$ o where u.user#(+)=o.owner# and o.type#=:1 and not exists (select p_obj# from dependency$ where p_obj# = o.obj#) order by o.obj# for update of o.owner#

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .083308 .000847 98.98%
parse_time .000978
cpu_time 0% .039994 .000888 97.78%
user_io_time 0
buffer_gets 0% 4332 138 96.81%
cost -.17% 1 40 -3900%
reads .04% 156 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. The structure of the SQL execution plan has changed.
  2. This SQL statement returned zero rows.


Execution Plan Before Change:
Plan Hash Value : 361378610

Id Operation Name Rows Bytes Cost Time

Execution Plan After Change:
Plan Id : 1147
Plan Hash Value : 2423986472

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 96 40 00:00:01
1 . FOR UPDATE
2 .. SORT ORDER BY 1 96 40 00:00:01
* 3 ... FILTER
4 .... NESTED LOOPS OUTER 1 96 37 00:00:01
* 5 ..... INDEX FAST FULL SCAN I_OBJ2 1 78 36 00:00:01
6 ..... TABLE ACCESS CLUSTER USER$ 1 18 1 00:00:01
* 7 ...... INDEX UNIQUE SCAN I_USER# 1 0 00:00:01
* 8 .... INDEX RANGE SCAN I_DEPENDENCY2 2 10 2 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 663
Schema Name : SYS
SQL ID : 0103qb611xu3g
Execution Frequency : 400
SQL Text : update sys.scheduler$_job set next_run_date = :1, last_enabled_time = :2, job_status = :3 where obj# = :4

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000536 .000058 89.18%
parse_time .000165
cpu_time 0% .000432 0 100%
user_io_time 0
buffer_gets 0% 11 1 91.07%
cost 0% 1 1 0%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 105323984

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE SCHEDULER$_JOB
2 .. INDEX UNIQUE SCAN SCHEDULER$_JOB_PK 1 27 0

Execution Plan After Change:
Plan Id : 905
Plan Hash Value : 105323984

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 25 1 00:00:01
1 . UPDATE SCHEDULER$_JOB
* 2 .. INDEX UNIQUE SCAN SCHEDULER$_JOB_PK 1 25 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 898
Schema Name : SYS
SQL ID : attkdbbcm69h3
Execution Frequency : 1
SQL Text : SELECT /*+ first_rows(1) */ sql_id, force_matching_signature, sql_text, parsing_schema_name,bind_data, module, action, elapsed_time, cpu_time, buffer_gets, disk_reads, direct_writes,rows_processed, fetches, executions, end_of_fetch_count, optimizer_cost, optimizer_env,NULL priority, command_type, NULL first_load_time, null stat_period, null active_stat_period, null other, plan_hash_value, cast(NULL as SQL_PLAN_TABLE_TYPE) sql_plan FROM ((SELECT t1.sql_id, t1.force_matching_signature, sql_text, module, action, selap as ELAPSED_TIME, scpu as CPU_TIME, sbgets as BUFFER_GETS, swrites as DIRECT_WRITES, sdreads as DISK_READS, srow as ROWS_PROCESSED, sfetches as fetches, sexec as EXECUTIONS, seofc as end_of_fetch_count, optimizer_cost, optimizer_env, command_type, parsing_schema_name, T1.snap_id, T1.plan_hash_value, T1.dbid, loaded_versions, bind_data FROM (SELECT sql_id, force_matching_signature, action, module, snap_id, dbid, loaded_versions, instance_number, sql_text, command_type, optimizer_env, bind_data, parsing_schema_name, plan_hash_value, optimizer_cost FROM (SELECT s.snap_id, s.dbid, s.instance_number, s.sql_id, s.force_matching_signature, sql_text, t.command_type, module, action, fetches_delta as FETCHES, executions_delta as EXECUTIONS, end_of_fetch_count_delta as END_OF_FETCH_COUNT, disk_reads_delta AS DISK_READS, direct_writes_delta DIRECT_WRITES, buffer_gets_delta as BUFFER_GETS, optimizer_env, rows_processed_delta as ROWS_PROCESSED, cpu_time_delta as CPU_TIME, elapsed_time_delta as ELAPSED_TIME, optimizer_cost, s.parsing_schema_name, plan_hash_value, loaded_versions, bind_data FROM DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t, DBA_HIST_OPTIMIZER_ENV e, V$DATABASE d WHERE s.sql_id = t.sql_id and s.dbid = t.dbid and s.dbid = e.dbid(+) and s.optimizer_env_hash_value = e.optimizer_env_hash_value(+) and s.dbid = d.dbid)) T1, (SELECT sql_id, plan_hash_value, sum(CPU_TIME) AS scpu, sum(BUFFER_GETS) AS sbgets, sum(DISK_READS) AS sdreads, sum(DIRECT_WRITES) AS swrites, sum(ROWS_PROCESSED) AS srow, sum(FETCHES) AS sfetches, sum(EXECUTIONS) AS sexec, sum(END_OF_FETCH_COUNT) AS seofc, sum(ELAPSED_TIME) AS selap, MAX(SNAP_ID) KEEP (DENSE_RANK LAST ORDER BY ELAPSED_TIME) AS snap_id, MAX(INSTANCE_NUMBER) KEEP (DENSE_RANK LAST ORDER BY ELAPSED_TIME) AS instance_number FROM (SELECT s.snap_id, s.dbid, s.instance_number, s.sql_id, s.force_matching_signature, sql_text, t.command_type, module, action, fetches_delta as FETCHES, executions_delta as EXECUTIONS, end_of_fetch_count_delta as END_OF_FETCH_COUNT, disk_reads_delta AS DISK_READS, direct_writes_delta DIRECT_WRITES, buffer_gets_delta as BUFFER_GETS, optimizer_env, rows_processed_delta as ROWS_PROCESSED, cpu_time_delta as CPU_TIME, elapsed_time_delta as ELAPSED_TIME, optimizer_cost, s.parsing_schema_name, plan_hash_value, loaded_versions, bind_data FROM DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t, DBA_HIST_OPTIMIZER_ENV e, V$DATABASE d WHERE s.sql_id = t.sql_id and s.dbid = t.dbid and s.dbid = e.dbid(+) and s.optimizer_env_hash_value = e.optimizer_env_hash_value(+) and s.dbid = d.dbid)WHERE snap_id >= :begin_snap and snap_id <= :end_snap AND command_type in (1, 2, 3, 6, 7, 189) GROUP BY sql_id, plan_hash_value) T2 WHERE T1.sql_id = T2.sql_id AND T1.plan_hash_value = T2.plan_hash_value AND T1.snap_id = T2.snap_id AND T1.instance_number = T2.instance_number)) S

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .392777 .000786 99.8%
parse_time .135219
cpu_time 0% .104984 .000555 99.47%
user_io_time 0
buffer_gets 0% 4065 2 99.95%
cost -.13% 127 157 -23.62%
reads .15% 567 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 49152
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (3):
  1. The structure of the SQL execution plan has changed.
  2. This SQL statement returned zero rows.
  3. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 3823109865

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 127
1 . MERGE JOIN CARTESIAN 1 1646 127 00:00:02
2 .. NESTED LOOPS 1 1646 127 00:00:02
3 ... NESTED LOOPS OUTER 1 1534 126 00:00:02
4 .... HASH JOIN 1 1376 125 00:00:02
5 ..... HASH JOIN 1 1350 125 00:00:02
6 ...... VIEW 1 164 8 00:00:01
7 ....... SORT GROUP BY 1 255 8 00:00:01
8 ........ FILTER
9 ......... MERGE JOIN CARTESIAN 1 255 7 00:00:01
10 .......... NESTED LOOPS 1 255 7 00:00:01
11 ........... NESTED LOOPS OUTER 1 230 6 00:00:01
12 ............ HASH JOIN 1 216 6 00:00:01
13 ............. FIXED TABLE FULL X$KCCDI 1 26 0
14 ............. VIEW DBA_HIST_SQLSTAT 2 380 5 00:00:01
15 .............. UNION-ALL
16 ............... FILTER
17 ................ TABLE ACCESS BY LOCAL INDEX ROWID WRH$_SQLSTAT 1 76 2 00:00:01
18 ................. NESTED LOOPS 1 94 4 00:00:01
19 .................. TABLE ACCESS BY INDEX ROWID WRM$_SNAPSHOT 1 18 2 00:00:01
20 ................... INDEX SKIP SCAN WRM$_SNAPSHOT_PK 1 1 00:00:01
21 .................. PARTITION RANGE ITERATOR 1 1 00:00:01
22 ................... INDEX RANGE SCAN WRH$_SQLSTAT_PK 1 1 00:00:01
23 ............... FILTER
24 ................ NESTED LOOPS 1 208 1 00:00:01
25 ................. TABLE ACCESS BY INDEX ROWID WRH$_SQLSTAT_BL 1 190 1 00:00:01
26 .................. INDEX SKIP SCAN WRH$_SQLSTAT_BL_PK 1 1 00:00:01
27 ................. TABLE ACCESS BY INDEX ROWID WRM$_SNAPSHOT 1 18 0
28 .................. INDEX UNIQUE SCAN WRM$_SNAPSHOT_PK 1 0
29 ............ INDEX UNIQUE SCAN WRH$_OPTIMIZER_ENV_PK 1 14 0
30 ........... TABLE ACCESS BY INDEX ROWID WRH$_SQLTEXT 1 25 1 00:00:01
31 ............ INDEX UNIQUE SCAN WRH$_SQLTEXT_PK 1 0
32 .......... BUFFER SORT 100 7 00:00:01
33 ........... FIXED TABLE FULL X$KCCDI2 100 0
34 ...... VIEW DBA_HIST_SQLSTAT 15583 18481438 116 00:00:02
35 ....... UNION-ALL
36 ........ HASH JOIN 15582 1620528 114 00:00:02
37 ......... TABLE ACCESS FULL WRM$_SNAPSHOT 170 3060 3 00:00:01
38 ......... PARTITION RANGE ALL 15674 1347964 111 00:00:02
39 .......... TABLE ACCESS FULL WRH$_SQLSTAT 15674 1347964 111 00:00:02
40 ........ NESTED LOOPS 1 1204 2 00:00:01
41 ......... TABLE ACCESS FULL WRH$_SQLSTAT_BL 1 1186 2 00:00:01
42 ......... TABLE ACCESS BY INDEX ROWID WRM$_SNAPSHOT 1 18 0
43 .......... INDEX UNIQUE SCAN WRM$_SNAPSHOT_PK 1 0
44 ..... FIXED TABLE FULL X$KCCDI 1 26 0
45 .... TABLE ACCESS BY INDEX ROWID WRH$_OPTIMIZER_ENV 1 158 1 00:00:01
46 ..... INDEX UNIQUE SCAN WRH$_OPTIMIZER_ENV_PK 1 0
47 ... TABLE ACCESS BY INDEX ROWID WRH$_SQLTEXT 1 112 1 00:00:01
48 .... INDEX UNIQUE SCAN WRH$_SQLTEXT_PK 1 0
49 .. BUFFER SORT 100 126 00:00:02
50 ... FIXED TABLE FULL X$KCCDI2 100 0

Execution Plan After Change:
Plan Id : 1140
Plan Hash Value : 1909202217

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 1413 157 00:00:02
1 . FIXED TABLE FULL X$MODACT_LENGTH 1 13 0 00:00:01
2 . FIXED TABLE FULL X$MODACT_LENGTH 1 13 0 00:00:01
3 . NESTED LOOPS 1 1413 157 00:00:02
4 .. NESTED LOOPS 1 1413 157 00:00:02
5 ... NESTED LOOPS 1 637 156 00:00:02
6 .... NESTED LOOPS OUTER 1 621 155 00:00:02
* 7 ..... HASH JOIN 1 392 154 00:00:02
8 ...... VIEW 48 7872 8 00:00:01
9 ....... SORT GROUP BY 48 7440 8 00:00:01
* 10 ........ FILTER
11 ......... MERGE JOIN CARTESIAN 103 15965 7 00:00:01
12 .......... NESTED LOOPS OUTER 1 155 7 00:00:01
13 ........... NESTED LOOPS 1 141 7 00:00:01
* 14 ............ HASH JOIN 1 116 6 00:00:01
15 ............. NESTED LOOPS
16 .............. NESTED LOOPS 34 3400 2 00:00:01
* 17 ............... FIXED TABLE FULL X$KCCDI 1 26 0 00:00:01
18 ............... PARTITION RANGE ITERATOR 1 1 00:00:01
* 19 ................ INDEX RANGE SCAN WRH$_SQLSTAT_PK 1 1 00:00:01
20 .............. TABLE ACCESS BY LOCAL INDEX ROWID WRH$_SQLSTAT 34 2516 2 00:00:01
* 21 ............. TABLE ACCESS BY INDEX ROWID WRM$_SNAPSHOT 1 16 3 00:00:01
* 22 .............. INDEX SKIP SCAN WRM$_SNAPSHOT_PK 1 2 00:00:01
* 23 ............ TABLE ACCESS BY INDEX ROWID WRH$_SQLTEXT 1 25 1 00:00:01
* 24 ............. INDEX UNIQUE SCAN WRH$_SQLTEXT_PK 1 0 00:00:01
* 25 ........... INDEX UNIQUE SCAN WRH$_OPTIMIZER_ENV_PK 1 14 0 00:00:01
26 .......... BUFFER SORT 100 8 00:00:01
27 ........... FIXED TABLE FULL X$KCCDI2 100 0 00:00:01
28 ...... NESTED LOOPS 14578 1661892 146 00:00:02
* 29 ....... FIXED TABLE FULL X$KCCDI 1 26 0 00:00:01
30 ....... PARTITION RANGE ITERATOR 14578 1282864 146 00:00:02
* 31 ........ TABLE ACCESS FULL WRH$_SQLSTAT 14578 1282864 146 00:00:02
32 ..... TABLE ACCESS BY INDEX ROWID WRH$_OPTIMIZER_ENV 1 229 1 00:00:01
* 33 ...... INDEX UNIQUE SCAN WRH$_OPTIMIZER_ENV_PK 1 0 00:00:01
* 34 .... TABLE ACCESS BY INDEX ROWID WRM$_SNAPSHOT 1 16 1 00:00:01
* 35 ..... INDEX UNIQUE SCAN WRM$_SNAPSHOT_PK 1 0 00:00:01
36 ... TABLE ACCESS BY INDEX ROWID WRH$_SQLTEXT 1 776 1 00:00:01
* 37 .... INDEX UNIQUE SCAN WRH$_SQLTEXT_PK 1 0 00:00:01
38 .. FIXED TABLE FULL X$KCCDI2 2 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 837
Schema Name : SYS
SQL ID : 83taa7kaw59c1
Execution Frequency : 11643
SQL Text : select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ where obj#=:1 order by intcol#

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000181 .000097 46.48%
parse_time .000229
cpu_time 0% .000131 .000111 15.02%
user_io_time 0
buffer_gets 0% 3 3 10.38%
cost -102.23% 1 3 -200%
reads .01% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 15 11
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 3765558045

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT
1 . SORT ORDER BY 0
2 .. TABLE ACCESS CLUSTER COL$
3 ... INDEX UNIQUE SCAN I_OBJ#

Execution Plan After Change:
Plan Id : 1079
Plan Hash Value : 3765558045

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 12 684 3 00:00:01
1 . SORT ORDER BY 12 684 3 00:00:01
2 .. TABLE ACCESS CLUSTER COL$ 12 684 2 00:00:01
* 3 ... INDEX UNIQUE SCAN I_OBJ# 1 1 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 826
Schema Name : SYS
SQL ID : 7ng34ruy5awxq
Execution Frequency : 5121
SQL Text : select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000222 .000093 58.16%
parse_time .007444
cpu_time 0% .000193 .000111 42.63%
user_io_time 0
buffer_gets 0% 8 8 8.64%
cost 67.45% 7 4 42.86%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. The structure of the SQL execution plan has changed.


Execution Plan Before Change:
Plan Hash Value : 2542797530

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 7
1 . SORT ORDER BY 2 372 7 00:00:01
2 .. HASH JOIN OUTER 2 372 6 00:00:01
3 ... NESTED LOOPS OUTER 2 286 2 00:00:01
4 .... TABLE ACCESS CLUSTER IND$ 2 182 2 00:00:01
5 ..... INDEX UNIQUE SCAN I_OBJ# 1 1 00:00:01
6 .... TABLE ACCESS BY INDEX ROWID IND_STATS$ 1 52 0
7 ..... INDEX UNIQUE SCAN I_IND_STATS$_OBJ# 1 0
8 ... VIEW 1 43 3 00:00:01
9 .... SORT GROUP BY 1 15 3 00:00:01
10 ..... TABLE ACCESS CLUSTER CDEF$ 1 15 2 00:00:01
11 ...... INDEX UNIQUE SCAN I_COBJ# 1 1 00:00:01

Execution Plan After Change:
Plan Id : 1068
Plan Hash Value : 2606284882

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 163 4 00:00:01
1 . SORT GROUP BY 1 163 4 00:00:01
2 .. NESTED LOOPS OUTER 1 163 3 00:00:01
3 ... NESTED LOOPS OUTER 1 148 2 00:00:01
4 .... TABLE ACCESS CLUSTER IND$ 1 84 2 00:00:01
* 5 ..... INDEX UNIQUE SCAN I_OBJ# 1 1 00:00:01
6 .... TABLE ACCESS BY INDEX ROWID IND_STATS$ 1 64 0 00:00:01
* 7 ..... INDEX UNIQUE SCAN I_IND_STATS$_OBJ# 1 0 00:00:01
* 8 ... TABLE ACCESS CLUSTER CDEF$ 1 15 1 00:00:01
* 9 .... INDEX UNIQUE SCAN I_COBJ# 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 927
Schema Name : SYS
SQL ID : c7sn076yz7030
Execution Frequency : 1
SQL Text : select smontabv.cnt, smontab.time_mp, smontab.scn, smontab.num_mappings, smontab.tim_scn_map, smontab.orig_thread from smon_scn_time smontab, (select max(scn) scnmax, count(*)+sum(NVL2(TIM_SCN_MAP,NUM_MAPPINGS,0)) cnt from smon_scn_time where thread=0) smontabv where smontab.scn = smontabv.scnmax and thread=0

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .021048 .000564 97.32%
parse_time .000287
cpu_time 0% .021997 .000666 96.97%
user_io_time 0
buffer_gets 0% 3933 336 91.46%
cost 3.06% 798 102 87.22%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. The structure of the SQL execution plan has changed.


Execution Plan Before Change:
Plan Hash Value : 3210068263

Id Operation Name Rows Bytes Cost Time

Execution Plan After Change:
Plan Id : 1169
Plan Hash Value : 1157191230

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 1185 102 00:00:02
1 . NESTED LOOPS 1 1185 102 00:00:02
2 .. VIEW 1 26 101 00:00:02
3 ... SORT AGGREGATE 1 1150
4 .... TABLE ACCESS CLUSTER SMON_SCN_TIME 1503 1728450 101 00:00:02
* 5 ..... INDEX UNIQUE SCAN SMON_SCN_TO_TIME_AUX_IDX 1 0 00:00:01
* 6 .. TABLE ACCESS BY INDEX ROWID SMON_SCN_TIME 1 1159 1 00:00:01
* 7 ... INDEX UNIQUE SCAN SMON_SCN_TIME_SCN_IDX 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 894
Schema Name : SYS
SQL ID : aq4js2gkfjru8
Execution Frequency : 860
SQL Text : update tsq$ set blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,priv2=:7,priv3=:8 where ts#=:1 and user#=:2

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000191 .000045 76.5%
parse_time .004507
cpu_time 0% .000153 .000111 27.67%
user_io_time 0
buffer_gets 0% 5 1 80.69%
cost 0% 1 1 0%
reads .01% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 1119449133

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE TSQ$
2 .. TABLE ACCESS CLUSTER TSQ$ 1 19 1 00:00:01
3 ... INDEX UNIQUE SCAN I_USER# 1 0

Execution Plan After Change:
Plan Id : 1136
Plan Hash Value : 1119449133

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 20 1 00:00:01
1 . UPDATE TSQ$
* 2 .. TABLE ACCESS CLUSTER TSQ$ 1 20 1 00:00:01
* 3 ... INDEX UNIQUE SCAN I_USER# 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 907
Schema Name : SYS
SQL ID : b7jn4mf49n569
Execution Frequency : 1
SQL Text : select o.name, u.name from obj$ o, type$ t, user$ u where o.oid$ = t.tvoid and u.user#=o.owner# and bitand(t.properties,8388608) = 8388608 and (sysdate-o.ctime) > 0.0007

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .266849 .005537 97.93%
parse_time .00025
cpu_time 0% .093985 .005221 94.44%
user_io_time 0
buffer_gets 0% 4951 1647 66.73%
cost .57% 245 115 53.06%
reads .53% 2008 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. This SQL statement returned zero rows.


Execution Plan Before Change:
Plan Hash Value : 4266358741

Id Operation Name Rows Bytes Cost Time

Execution Plan After Change:
Plan Id : 1149
Plan Hash Value : 4266358741

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 72 115 00:00:02
1 . NESTED LOOPS 1 72 115 00:00:02
2 .. NESTED LOOPS 1 54 114 00:00:02
* 3 ... TABLE ACCESS FULL OBJ$ 85 2805 48 00:00:01
* 4 ... TABLE ACCESS BY INDEX ROWID TYPE$ 1 21 1 00:00:01
* 5 .... INDEX UNIQUE SCAN I_TYPE2 1 0 00:00:01
6 .. TABLE ACCESS CLUSTER USER$ 1 18 1 00:00:01
* 7 ... INDEX UNIQUE SCAN I_USER# 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 916
Schema Name : SYS
SQL ID : btzq46kta67dz
Execution Frequency : 2950
SQL Text : update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .00018 .000058 67.86%
parse_time .00069
cpu_time 0% .000094 .000111 -17.81%
user_io_time 0
buffer_gets 0% 3 2 35.76%
cost 0% 3 3 0%
reads .01% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 2683643009

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 3
1 . UPDATE OBJ$
2 .. INDEX RANGE SCAN I_OBJ2 1 79 2 00:00:01

Execution Plan After Change:
Plan Id : 1158
Plan Hash Value : 2683643009

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 78 3 00:00:01
1 . UPDATE OBJ$
* 2 .. INDEX RANGE SCAN I_OBJ2 1 78 2 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 705
Schema Name : SYS
SQL ID : 1gu8t96d0bdmu
Execution Frequency : 10270
SQL Text : select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .00009 .000079 12.51%
parse_time .000352
cpu_time 0% .000076 .000111 -46.36%
user_io_time 0
buffer_gets 0% 4 4 7.29%
cost 0% 2 2 0%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 2035254952

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 2
1 . MERGE JOIN OUTER 1 187 2 00:00:01
2 .. TABLE ACCESS CLUSTER TAB$ 1 135 2 00:00:01
3 ... INDEX UNIQUE SCAN I_OBJ# 1 1 00:00:01
4 .. BUFFER SORT 1 52 0
5 ... TABLE ACCESS BY INDEX ROWID TAB_STATS$ 1 52 0
6 .... INDEX UNIQUE SCAN I_TAB_STATS$_OBJ# 1 0

Execution Plan After Change:
Plan Id : 947
Plan Hash Value : 2035254952

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 182 2 00:00:01
1 . MERGE JOIN OUTER 1 182 2 00:00:01
2 .. TABLE ACCESS CLUSTER TAB$ 1 130 2 00:00:01
* 3 ... INDEX UNIQUE SCAN I_OBJ# 1 1 00:00:01
4 .. BUFFER SORT 1 52 0 00:00:01
5 ... TABLE ACCESS BY INDEX ROWID TAB_STATS$ 1 52 0 00:00:01
* 6 .... INDEX UNIQUE SCAN I_TAB_STATS$_OBJ# 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 991
Schema Name : SYS
SQL ID : guw87u8x36z8r
Execution Frequency : 2616
SQL Text : UPDATE WRI$_SQLSET_PLANS SET BIND_DATA = :B4 , BINDS_CAPTURED = :B3 WHERE STMT_ID = :B2 AND PLAN_HASH_VALUE = :B1

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .00004 .000044 -9.97%
parse_time .000146
cpu_time 0% .000028 .000111 -297.87%
user_io_time 0
buffer_gets 0% 2 1 51.09%
cost 11.48% 1 0 100%
reads .01% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 245262389

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE WRI$_SQLSET_PLANS
2 .. INDEX UNIQUE SCAN WRI$_SQLSET_PLANS_PK 1 1031 0

Execution Plan After Change:
Plan Id : 1233
Plan Hash Value : 245262389

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 1031 0 00:00:01
1 . UPDATE WRI$_SQLSET_PLANS
* 2 .. INDEX UNIQUE SCAN WRI$_SQLSET_PLANS_PK 1 1031 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 944
Schema Name : SYS
SQL ID : cvn54b7yz0s8u
Execution Frequency : 464
SQL Text : select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece#

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .002137 .00006 97.19%
parse_time .000189
cpu_time 0% .000384 0 100%
user_io_time 0
buffer_gets 0% 7 2 73.84%
cost -4.07% 1 3 -200%
reads .34% 2 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 2 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (3):
  1. The structure of the SQL execution plan has changed.
  2. This SQL statement returned zero rows.
  3. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 2334475966

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT
1 . SORT ORDER BY 0
2 .. TABLE ACCESS BY INDEX ROWID IDL_UB1$
3 ... INDEX RANGE SCAN I_IDL_UB11

Execution Plan After Change:
Plan Id : 1186
Plan Hash Value : 3246118364

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 22 3 00:00:01
1 . TABLE ACCESS BY INDEX ROWID IDL_UB1$ 1 22 3 00:00:01
* 2 .. INDEX RANGE SCAN I_IDL_UB11 1 2 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 838
Schema Name : SYS
SQL ID : 84ygtc6ucuafk
Execution Frequency : 400
SQL Text : update sys.scheduler$_job set job_status = :1 where obj# = :2

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000504 .000058 88.49%
parse_time .000171
cpu_time 0% .00028 0 100%
user_io_time 0
buffer_gets 0% 7 1 86.15%
cost 0% 1 1 0%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 105323984

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE SCHEDULER$_JOB
2 .. INDEX UNIQUE SCAN SCHEDULER$_JOB_PK 1 8 0

Execution Plan After Change:
Plan Id : 1080
Plan Hash Value : 105323984

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 8 1 00:00:01
1 . UPDATE SCHEDULER$_JOB
* 2 .. INDEX UNIQUE SCAN SCHEDULER$_JOB_PK 1 8 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 760
Schema Name : SYS
SQL ID : 451q2032pshqm
Execution Frequency : 3136
SQL Text : select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname, type#,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000215 .000089 58.57%
parse_time .000353
cpu_time 0% .000099 .000111 -11.95%
user_io_time 0
buffer_gets 0% 8 9 -7.29%
cost -123.91% 1 10 -900%
reads .08% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 2 2
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. The structure of the SQL execution plan has changed.


Execution Plan Before Change:
Plan Hash Value : 1310495014

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT
1 . SORT ORDER BY 0
2 .. NESTED LOOPS OUTER
3 ... TABLE ACCESS BY INDEX ROWID DEPENDENCY$
4 .... INDEX RANGE SCAN I_DEPENDENCY1
5 ... TABLE ACCESS BY INDEX ROWID OBJ$
6 .... INDEX UNIQUE SCAN I_OBJ1

Execution Plan After Change:
Plan Id : 1002
Plan Hash Value : 4184428695

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 3 324 10 00:00:01
1 . SORT ORDER BY 3 324 10 00:00:01
2 .. NESTED LOOPS OUTER 3 324 9 00:00:01
3 ... TABLE ACCESS BY INDEX ROWID DEPENDENCY$ 3 90 3 00:00:01
* 4 .... INDEX RANGE SCAN I_DEPENDENCY1 3 2 00:00:01
5 ... TABLE ACCESS BY INDEX ROWID OBJ$ 1 78 2 00:00:01
* 6 .... INDEX RANGE SCAN I_OBJ1 1 1 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 738
Schema Name : SYS
SQL ID : 2ym6hhaq30r73
Execution Frequency : 2175
SQL Text : select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0) from seg$ where ts#=:1 and file#=:2 and block#=:3

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000066 .000084 -27.35%
parse_time .000199
cpu_time 0% .000082 0 100%
user_io_time 0
buffer_gets 0% 2 2 28.77%
cost 0% 2 2 0%
reads .02% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. This SQL statement returned zero rows.


Execution Plan Before Change:
Plan Hash Value : 3755742892

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 2
1 . TABLE ACCESS CLUSTER SEG$ 1 60 2 00:00:01
2 .. INDEX UNIQUE SCAN I_FILE#_BLOCK# 1 1 00:00:01

Execution Plan After Change:
Plan Id : 980
Plan Hash Value : 3755742892

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 64 2 00:00:01
1 . TABLE ACCESS CLUSTER SEG$ 1 64 2 00:00:01
* 2 .. INDEX UNIQUE SCAN I_FILE#_BLOCK# 1 1 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 852
Schema Name : SYS
SQL ID : 8q9hmgy8uvwtc
Execution Frequency : 29
SQL Text : SELECT (CASE WHEN (S.plan_hash_value=0 OR S.loaded_versions=0) THEN cast(NULL as SQL_PLAN_TABLE_TYPE) ELSE (SELECT /*+ PARAM('_cursor_plan_unparse_enabled', 'FALSE') */ CASE WHEN max(P.plan_hash_value) IS NULL THEN cast(NULL as SQL_PLAN_TABLE_TYPE) ELSE CAST(COLLECT( treat( sql_plan_stat_row_type(null, null, timestamp, remarks, operation, options, object_node, object_owner, object_name, object_alias, null, object_type, optimizer, search_columns, id, parent_id, depth, position, cost, cardinality, bytes, other_tag, partition_start, partition_stop, partition_id, distribution, cpu_cost, io_cost, temp_space, null, null, null, time, qblock_name, other_xml, EXECUTIONS, STARTS, OUTPUT_ROWS, CR_BUFFER_GETS, CU_BUFFER_GETS, DISK_READS, DISK_WRITES, ELAPSED_TIME) AS sql_plan_row_type)) AS SQL_PLAN_TABLE_TYPE) END FROM sys.v_$sql_plan_statistics_all P WHERE P.sql_id = S.sql_id AND P.plan_hash_value = S.plan_hash_value AND P.child_address = S.last_active_child_address) END)FROM sys.v_$sqlarea_plan_hash S WHERE sql_id = :sid AND plan_hash_value = :phv

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .006012 .00007 98.84%
parse_time .005211
cpu_time 0% .00331 0 100%
user_io_time 0
buffer_gets 0% 59 0 100%
cost .13% 1 0 100%
reads .03% 3 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. This SQL statement returned zero rows.
  2. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 747321526

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1
1 . SORT AGGREGATE 1 4768
2 .. FIXED TABLE FIXED INDEX X$QESRSTATALL (ind:4) 1 4768 0
3 . FIXED TABLE FIXED INDEX X$KGLCURSOR_CHILD_SQLIDPH (ind 1 53 0

Execution Plan After Change:
Plan Id : 1094
Plan Hash Value : 747321526

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 53 0 00:00:01
1 . SORT AGGREGATE 1 4768
2 .. FIXED TABLE FIXED INDEX X$QESRSTATALL (ind:4) 1 4768 0 00:00:01
3 . FIXED TABLE FIXED INDEX X$KGLCURSOR_CHILD_SQLIDPH (ind 1 53 0 00:00:01

SQL Details:
Object ID : 989
Schema Name : SYS
SQL ID : grwydz59pu6mc
Execution Frequency : 857
SQL Text : select text from view$ where rowid=:1

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000088 .000051 41.84%
parse_time .000099
cpu_time 0% .00013 0 100%
user_io_time 0
buffer_gets 0% 2 0 100%
cost 0% 1 1 0%
reads .02% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (3):
  1. This SQL statement returned zero rows.
  2. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.
  3. At least one important bind value was missing for this sql statement. The accuracy of the advisor's analysis may depend on all important bind values being supplied.


Execution Plan Before Change:
Plan Hash Value : 3684871272

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT
1 . TABLE ACCESS BY USER ROWID VIEW$

Execution Plan After Change:
Plan Id : 1231
Plan Hash Value : 3684871272

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 15 1 00:00:01
* 1 . TABLE ACCESS BY USER ROWID VIEW$ 1 15 1 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 914
Schema Name : SYS
SQL ID : bhvyz9bgyrhb2
Execution Frequency : 16
SQL Text : SELECT name,datatype,value,flags, nvl(description,'NULL') FROM sys.wri$_adv_parameters WHERE task_id = :1 ORDER BY 1

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000595 .000065 89.08%
parse_time .000209
cpu_time 0% .001125 .000111 90.13%
user_io_time 0
buffer_gets 0% 107 3 97.2%
cost .07% 5 4 20%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 53 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. This SQL statement returned zero rows.
  2. The number of returned rows in execution '10g_trail' is different than in execution '11g_trail'.


Execution Plan Before Change:
Plan Hash Value : 1134671139

Id Operation Name Rows Bytes Cost Time

Execution Plan After Change:
Plan Id : 1156
Plan Hash Value : 1134671139

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 34 4 00:00:01
1 . TABLE ACCESS BY INDEX ROWID WRI$_ADV_PARAMETERS 1 34 4 00:00:01
* 2 .. INDEX RANGE SCAN WRI$_ADV_PARAMETERS_PK 1 3 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 695
Schema Name : SYS
SQL ID : 16q5mbuwtzv6r
Execution Frequency : 767
SQL Text : select local_tran_id, global_tran_fmt, global_oracle_id, global_foreign_id, state, status, heuristic_dflt, session_vector, reco_vector, 3600*24*(sysdate-reco_time), 3600*24*(sysdate-nvl(heuristic_time,fail_time)), global_commit#, type# from pending_trans$ where session_vector != '00000000'

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000181 .000084 53.48%
parse_time .007477
cpu_time 0% .000064 .000111 -73.79%
user_io_time 0
buffer_gets 0% 3 1 67.61%
cost 0% 2 2 0%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (1):
  1. This SQL statement returned zero rows.


Execution Plan Before Change:
Plan Hash Value : 4027303705

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 2
1 . TABLE ACCESS FULL PENDING_TRANS$ 1 166 2 00:00:01

Execution Plan After Change:
Plan Id : 937
Plan Hash Value : 4027303705

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 166 2 00:00:01
* 1 . TABLE ACCESS FULL PENDING_TRANS$ 1 166 2 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 830
Schema Name : SYS
SQL ID : 7s2y99c3nsdz5
Execution Frequency : 297
SQL Text : INSERT INTO wrh$_sqltext (sql_id, dbid, sql_text, command_type, snap_id, ref_count) SELECT :sql_id, :dbid, :sql_text, :command_type, :lah_snap_id, 0 ref_count FROM dual WHERE NOT EXISTS (SELECT 1 FROM wrh$_sqltext stx WHERE stx.sql_id = :sql_id2 AND stx.dbid = :dbid2)

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .003218 .000054 98.32%
parse_time .016518
cpu_time 0% .000162 .000111 31.31%
user_io_time 0
buffer_gets 0% 7 2 72.27%
cost 0% 3 3 0%
reads .01% 0 0 100%
writes 33.33% 0 0 100%
io_interconnect_bytes 0
rows 0 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 4211396698

Id Operation Name Rows Bytes Cost Time
0 INSERT STATEMENT 3
1 . FILTER
2 .. FAST DUAL 1 2 00:00:01
3 .. INDEX UNIQUE SCAN WRH$_SQLTEXT_PK 1 21 1 00:00:01

Execution Plan After Change:
Plan Id : 1072
Plan Hash Value : 4211396698

Id Operation Name Rows Bytes Cost Time
0 INSERT STATEMENT 1 3 00:00:01
1 . LOAD TABLE CONVENTIONAL WRH$_SQLTEXT
* 2 .. FILTER
3 ... FAST DUAL 1 2 00:00:01
* 4 ... INDEX UNIQUE SCAN WRH$_SQLTEXT_PK 1 21 1 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 767
Schema Name : SYS
SQL ID : 4m7m0t6fjcs5x
Execution Frequency : 253
SQL Text : update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000461 .000043 90.68%
parse_time .000107
cpu_time 0% .000391 0 100%
user_io_time 0
buffer_gets 0% 6 1 84.04%
cost 0% 1 1 0%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 1935744642

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1
1 . UPDATE SEQ$
2 .. INDEX UNIQUE SCAN I_SEQ1 1 69 0

Execution Plan After Change:
Plan Id : 1009
Plan Hash Value : 1935744642

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 1 69 1 00:00:01
1 . UPDATE SEQ$
* 2 .. INDEX UNIQUE SCAN I_SEQ1 1 69 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 733
Schema Name : SYS
SQL ID : 2syvqzbxp4k9z
Execution Frequency : 608
SQL Text : select u.name, o.name, a.interface_version#, o.obj# from association$ a, user$ u, obj$ o where a.obj# = :1 and a.property = :2 and a.statstype# = o.obj# and u.user# = o.owner#

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000116 .000068 41.42%
parse_time .000461
cpu_time 0% .000164 .000222 -35%
user_io_time 0
buffer_gets 0% 3 1 67.63%
cost 5.34% 4 2 50%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 0 0
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. The structure of the SQL execution plan has changed.
  2. This SQL statement returned zero rows.


Execution Plan Before Change:
Plan Hash Value : 1369849886

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 4
1 . NESTED LOOPS 1 62 4 00:00:01
2 .. NESTED LOOPS 1 48 3 00:00:01
3 ... TABLE ACCESS FULL ASSOCIATION$ 1 16 2 00:00:01
4 ... TABLE ACCESS BY INDEX ROWID OBJ$ 1 32 1 00:00:01
5 .... INDEX UNIQUE SCAN I_OBJ1 1 0
6 .. TABLE ACCESS CLUSTER USER$ 1 14 1 00:00:01
7 ... INDEX UNIQUE SCAN I_USER# 1 0

Execution Plan After Change:
Plan Id : 975
Plan Hash Value : 2369835006

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 97 2 00:00:01
1 . NESTED LOOPS 1 97 2 00:00:01
2 .. NESTED LOOPS 1 79 1 00:00:01
* 3 ... TABLE ACCESS BY INDEX ROWID ASSOCIATION$ 1 52 0 00:00:01
* 4 .... INDEX RANGE SCAN ASSOC1 1 0 00:00:01
5 ... TABLE ACCESS BY INDEX ROWID OBJ$ 1 27 1 00:00:01
* 6 .... INDEX RANGE SCAN I_OBJ1 1 1 00:00:01
7 .. TABLE ACCESS CLUSTER USER$ 1 18 1 00:00:01
* 8 ... INDEX UNIQUE SCAN I_USER# 1 0 00:00:01

Predicate Information (identified by operation id):

SQL Details:
Object ID : 889
Schema Name : SYS
SQL ID : acg2mzntn54kc
Execution Frequency : 22960
SQL Text : SELECT tname, snapid, flag, flag2, status, master_version, tables, lobmaskvec, query_len, mas_roll_seg, mlink, snapshot, auto_fun, uslog, NVL(rscn, 0), refhnt, objflag, auto_fast, sna_type_oid, sna_type_hashcode, sna_type_owner, sna_type_name, mas_type_oid, mas_type_hashcode, mas_type_owner, mas_type_name, syn_count FROM sys.snap$ WHERE sowner = :1 and vname = :2 and instsite = :3

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 0% .000111 .000071 35.84%
parse_time .011559
cpu_time -.01% .000052 .000111 -112.41%
user_io_time 0
buffer_gets 0% 2 2 2.54%
cost 100.8% 1 0 100%
reads 0% 0 0 100%
writes 0% 0 0 0%
io_interconnect_bytes 0
rows 1 1
Note: time statistics are displayed in seconds

Notes:

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Hash Value : 4758086

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1
1 . TABLE ACCESS BY INDEX ROWID SNAP$ 1 118 1 00:00:01
2 .. INDEX UNIQUE SCAN I_SNAP1 1 0

Execution Plan After Change:
Plan Id : 1131
Plan Hash Value : 4758086

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 657 0 00:00:01
1 . TABLE ACCESS BY INDEX ROWID SNAP$ 1 657 0 00:00:01
* 2 .. INDEX UNIQUE SCAN I_SNAP1 1 0 00:00:01

Predicate Information (identified by operation id):
SQL> spool off