General Information

Task Information:
Workload Information:
Task Name : SH_SPA
Task Owner : SYS
Description :
SQL Tuning Set Name : sh_workload
SQL Tuning Set Owner : SYS
Total SQL Statement Count : 59

Execution Information:
Execution Name : sh_compartivo_cambio_optimizer
Execution Type : COMPARE PERFORMANCE
Description :
Scope : COMPREHENSIVE
Status : COMPLETED
Number of Unsupported SQL : 13
Started : 11/16/2017 10:05:34
Last Updated : 11/16/2017 10:05:35
Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED
Number of Errors : 0

Analysis Information:
Before Change Execution:
After Change Execution:
Execution Name : sh_after_change
Execution Type : TEST EXECUTE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 11/16/2017 09:29:12
Last Updated : 11/16/2017 09:30:04
Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED
Number of Errors : 0
Execution Name : sh_change_optimizer_12.1.0.1
Execution Type : TEST EXECUTE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 11/16/2017 10:01:26
Last Updated : 11/16/2017 10:02:19
Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED
Number of Errors : 0

Comparison Metric: ELAPSED_TIME
Workload Impact Threshold: 1%
SQL Impact Threshold: 1%

Report Summary

Projected Workload Change Impact:
Overall Impact : 2.74%
Improvement Impact : 2.74%
Regression Impact : 0%

SQL Statement Count
SQL Category SQL Count Plan Change Count
Overall 59 1
Improved 2 0
Unchanged 44 1
Unsupported 13 0

Top 46 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
311 3fgwfcsa2k3bz 1.39% 54 286008 195457 31.66% n
309 28da8t4nc8uk9 1.34% 62 259357 183273 29.34% n
333 9b723qjgkmy9z -.72% 49 205658 257487 -25.2% n
319 6n5rbcc9c9tvs -.67% 51 218336 264639 -21.21% n
330 954q4b1vkp8m4 .41% 57 215175 189787 11.8% n
332 9aft7htmzpd1d -.27% 67 149057 163365 -9.6% n
305 1xmq1jkcnp2mf -.26% 52 172516 189733 -9.98% n
345 c6axjhw2bv23t -.18% 42 208141 223152 -7.21% n
303 1p5p7drt7atxd -.17% 51 157684 169715 -7.63% n
343 au7x78c2uju0p -.17% 62 142860 152711 -6.9% n
337 9w0c53r5um1xy -.16% 52 223233 234134 -4.88% n
342 aktx221b6sqc4 -.15% 56 138540 148217 -6.98% n
320 709uggnu40a9a -.15% 46 258583 270137 -4.47% n
314 4kwqmbh8cs1gp .14% 49 155369 145142 6.58% n
312 3zdr12jv606xs .13% 53 192963 184517 4.38% n
358 gx7ymx5myrd2t -.11% 54 162516 169956 -4.58% n
318 5vbwrcz3vw3s0 .11% 61 246455 240068 2.59% n
317 5hppq6bp5wmnm .1% 61 165611 159708 3.56% n
338 a75y9uq84au4p -.1% 38 204695 213663 -4.38% n
306 1zm0sn3qcx0hu -.1% 45 180303 187795 -4.16% n
349 dfdg44tjr89uf -.08% 48 184686 190789 -3.3% n
304 1qr4ps69f36jy -.08% 56 155863 161087 -3.35% n
313 416hz7t3mx918 -.07% 42 186773 192267 -2.94% n
329 8wskn4vvvkd93 .06% 48 199487 195410 2.04% n
357 gpxukn3ft9qgw -.05% 11 208415 225759 -8.32% n
307 222q8cuf6ugt9 -.05% 47 195544 199519 -2.03% n
316 56nj9ptzakm56 -.05% 44 199834 203953 -2.06% n
348 cmkygw8kypnpb -.05% 48 243968 247736 -1.54% n
310 370m5yqjjcr8z .05% 60 147347 144521 1.92% n
300 03ghr9b5m3x43 -.03% 10 152393 162999 -6.96% n
325 83wufms0kwjb0 -.03% 8 135088 146791 -8.66% n
326 8g3y654ptjzrz .02% 58 187166 185680 .79% n
334 9fsjrrksxp4gj .02% 48 143245 141465 1.24% n
335 9h2u2z9y900yk .02% 10 152415 144492 5.2% n
315 4wazxyxv5cpt0 .02% 47 153237 151859 .9% n
328 8k0bufga1dutt -.01% 9 141978 146310 -3.05% n
323 7f6kr3z5p8qxa -.01% 8 144216 148892 -3.24% n
339 a9nt3xdwup082 -.01% 47 159580 160222 -.4% n
327 8hytq9ybsh5xu .01% 6 149083 145742 2.24% n
336 9tkfxkjugywqp 0% 11 117203 115812 1.19% y
354 fx2c47hcyfz9f 0% 8 155689 157286 -1.03% n
352 fd5j4mmztdpmm 0% 8 151871 150531 .88% n
301 08kysb4nyt58n 0% 9 138697 139311 -.44% n
302 0bg8rg9srv8rm 0% 56 237560 237607 -.02% n
353 fwsw6473mw961 0% 47 148999 149054 -.04% n
356 gppx8hpcwsb5g 0% 7 202939 202605 .16% n
Note: time statistics are displayed in microseconds



Report Details


SQL Details:

Object ID : 311
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 3fgwfcsa2k3bz
Execution Frequency : 54
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2008-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time 1.39% .286008 .195457 31.66%
parse_time -3.84% .000538 .001312 -143.87%
cpu_time .81% .276208 .223766 18.99%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4834 4831 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones.


Findings (2):
  1. Ha mejorado el rendimiento de este SQL.
  2. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 899
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4834 00:00:59
* 1 . VIEW 19370 1510860 4834 00:00:59
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4834 00:00:59
3 ... HASH GROUP BY 19370 987870 4834 00:00:59
* 4 .... HASH JOIN 19370 987870 4337 00:00:53
* 5 ..... HASH JOIN 19587 607197 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 958
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4831 00:00:01
* 1 . VIEW 19370 1510860 4831 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4831 00:00:01
3 ... HASH GROUP BY 19370 987870 4831 00:00:01
* 4 .... HASH JOIN 19370 987870 4336 00:00:01
* 5 ..... HASH JOIN 19587 607197 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 309
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 28da8t4nc8uk9
Execution Frequency : 62
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2009-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time 1.34% .259357 .183273 29.34%
parse_time -6.77% .000725 .001914 -164%
cpu_time .69% .255761 .216567 15.32%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4824 4821 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 16 16
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones.


Findings (2):
  1. Ha mejorado el rendimiento de este SQL.
  2. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 897
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 18948 1477944 4824 00:00:58
* 1 . VIEW 18948 1477944 4824 00:00:58
* 2 .. WINDOW SORT PUSHED RANK 18948 966348 4824 00:00:58
3 ... HASH GROUP BY 18948 966348 4824 00:00:58
* 4 .... HASH JOIN 18948 966348 4337 00:00:53
* 5 ..... HASH JOIN 19161 593991 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 90 1350 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 956
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 18948 1477944 4821 00:00:01
* 1 . VIEW 18948 1477944 4821 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 18948 966348 4821 00:00:01
3 ... HASH GROUP BY 18948 966348 4821 00:00:01
* 4 .... HASH JOIN 18948 966348 4336 00:00:01
* 5 ..... HASH JOIN 19161 593991 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 90 1350 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 333
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 9b723qjgkmy9z
Execution Frequency : 49
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2009-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.72% .205658 .257487 -25.2%
parse_time -2.76% .000528 .001141 -116.1%
cpu_time -.85% .205718 .266626 -29.61%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4834 4831 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 16 16
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 3 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 921
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4834 00:00:59
* 1 . VIEW 19370 1510860 4834 00:00:59
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4834 00:00:59
3 ... HASH GROUP BY 19370 987870 4834 00:00:59
* 4 .... HASH JOIN 19370 987870 4337 00:00:53
* 5 ..... HASH JOIN 19587 607197 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

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

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4831 00:00:01
* 1 . VIEW 19370 1510860 4831 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4831 00:00:01
3 ... HASH GROUP BY 19370 987870 4831 00:00:01
* 4 .... HASH JOIN 19370 987870 4336 00:00:01
* 5 ..... HASH JOIN 19587 607197 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 319
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 6n5rbcc9c9tvs
Execution Frequency : 51
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2012-2' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.67% .218336 .264639 -21.21%
parse_time -6.43% .000708 .002082 -194.07%
cpu_time -.93% .218466 .282623 -29.37%
user_io_time 0% 0 0 0%
buffer_gets 0% 15907 15907 0%
cost 0% 4832 4829 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 3 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 907
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4832 00:00:58
* 1 . VIEW 19159 1494402 4832 00:00:58
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4832 00:00:58
3 ... HASH GROUP BY 19159 977109 4832 00:00:58
* 4 .... HASH JOIN 19159 977109 4337 00:00:53
* 5 ..... HASH JOIN 19374 600594 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

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

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4829 00:00:01
* 1 . VIEW 19159 1494402 4829 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4829 00:00:01
3 ... HASH GROUP BY 19159 977109 4829 00:00:01
* 4 .... HASH JOIN 19159 977109 4336 00:00:01
* 5 ..... HASH JOIN 19374 600594 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 330
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 954q4b1vkp8m4
Execution Frequency : 57
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2008-2' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time .41% .215175 .189787 11.8%
parse_time -3.44% .000544 .001201 -120.77%
cpu_time .3% .214967 .19677 8.47%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4832 4829 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 16 16
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 918
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4832 00:00:58
* 1 . VIEW 19159 1494402 4832 00:00:58
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4832 00:00:58
3 ... HASH GROUP BY 19159 977109 4832 00:00:58
* 4 .... HASH JOIN 19159 977109 4337 00:00:53
* 5 ..... HASH JOIN 19374 600594 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

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

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4829 00:00:01
* 1 . VIEW 19159 1494402 4829 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4829 00:00:01
3 ... HASH GROUP BY 19159 977109 4829 00:00:01
* 4 .... HASH JOIN 19159 977109 4336 00:00:01
* 5 ..... HASH JOIN 19374 600594 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 332
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 9aft7htmzpd1d
Execution Frequency : 67
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2004-2' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.27% .149057 .163365 -9.6%
parse_time -8.42% .000506 .001875 -270.55%
cpu_time -.39% .14931 .16964 -13.62%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4832 4829 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 16 16
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 920
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4832 00:00:58
* 1 . VIEW 19159 1494402 4832 00:00:58
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4832 00:00:58
3 ... HASH GROUP BY 19159 977109 4832 00:00:58
* 4 .... HASH JOIN 19159 977109 4337 00:00:53
* 5 ..... HASH JOIN 19374 600594 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 979
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4829 00:00:01
* 1 . VIEW 19159 1494402 4829 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4829 00:00:01
3 ... HASH GROUP BY 19159 977109 4829 00:00:01
* 4 .... HASH JOIN 19159 977109 4336 00:00:01
* 5 ..... HASH JOIN 19374 600594 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 305
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 1xmq1jkcnp2mf
Execution Frequency : 52
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2006-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.26% .172516 .189733 -9.98%
parse_time -3.73% .000494 .001276 -158.3%
cpu_time -1.04% .172373 .242163 -40.49%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4834 4831 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 16 16
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 893
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4834 00:00:59
* 1 . VIEW 19370 1510860 4834 00:00:59
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4834 00:00:59
3 ... HASH GROUP BY 19370 987870 4834 00:00:59
* 4 .... HASH JOIN 19370 987870 4337 00:00:53
* 5 ..... HASH JOIN 19587 607197 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

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

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4831 00:00:01
* 1 . VIEW 19370 1510860 4831 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4831 00:00:01
3 ... HASH GROUP BY 19370 987870 4831 00:00:01
* 4 .... HASH JOIN 19370 987870 4336 00:00:01
* 5 ..... HASH JOIN 19587 607197 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 345
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : c6axjhw2bv23t
Execution Frequency : 42
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2013-2' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.18% .208141 .223152 -7.21%
parse_time -2.83% .000488 .001222 -150.41%
cpu_time -.23% .208718 .228215 -9.34%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4832 4829 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 933
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4832 00:00:58
* 1 . VIEW 19159 1494402 4832 00:00:58
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4832 00:00:58
3 ... HASH GROUP BY 19159 977109 4832 00:00:58
* 4 .... HASH JOIN 19159 977109 4337 00:00:53
* 5 ..... HASH JOIN 19374 600594 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 992
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4829 00:00:01
* 1 . VIEW 19159 1494402 4829 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4829 00:00:01
3 ... HASH GROUP BY 19159 977109 4829 00:00:01
* 4 .... HASH JOIN 19159 977109 4336 00:00:01
* 5 ..... HASH JOIN 19374 600594 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 303
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 1p5p7drt7atxd
Execution Frequency : 51
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2001-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.17% .157684 .169715 -7.63%
parse_time -3.56% .000608 .001368 -125%
cpu_time -1.12% .157976 .234764 -48.61%
user_io_time 0% 0 0 0%
buffer_gets 0% 15907 15907 0%
cost 0% 4834 4831 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 891
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4834 00:00:59
* 1 . VIEW 19370 1510860 4834 00:00:59
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4834 00:00:59
3 ... HASH GROUP BY 19370 987870 4834 00:00:59
* 4 .... HASH JOIN 19370 987870 4337 00:00:53
* 5 ..... HASH JOIN 19587 607197 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 950
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4831 00:00:01
* 1 . VIEW 19370 1510860 4831 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4831 00:00:01
3 ... HASH GROUP BY 19370 987870 4831 00:00:01
* 4 .... HASH JOIN 19370 987870 4336 00:00:01
* 5 ..... HASH JOIN 19587 607197 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 343
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : au7x78c2uju0p
Execution Frequency : 62
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2005-3' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.17% .14286 .152711 -6.9%
parse_time -5.62% .000565 .001553 -174.87%
cpu_time -.24% .142835 .156143 -9.32%
user_io_time 0% 0 0 0%
buffer_gets 0% 15907 15907 0%
cost 0% 4834 4831 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 7 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 931
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4834 00:00:59
* 1 . VIEW 19370 1510860 4834 00:00:59
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4834 00:00:59
3 ... HASH GROUP BY 19370 987870 4834 00:00:59
* 4 .... HASH JOIN 19370 987870 4337 00:00:53
* 5 ..... HASH JOIN 19587 607197 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 990
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4831 00:00:01
* 1 . VIEW 19370 1510860 4831 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4831 00:00:01
3 ... HASH GROUP BY 19370 987870 4831 00:00:01
* 4 .... HASH JOIN 19370 987870 4336 00:00:01
* 5 ..... HASH JOIN 19587 607197 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 337
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 9w0c53r5um1xy
Execution Frequency : 52
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2011-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.16% .223233 .234134 -4.88%
parse_time -5.14% .000587 .001664 -183.48%
cpu_time -.25% .223716 .240713 -7.6%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4834 4831 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 925
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4834 00:00:59
* 1 . VIEW 19370 1510860 4834 00:00:59
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4834 00:00:59
3 ... HASH GROUP BY 19370 987870 4834 00:00:59
* 4 .... HASH JOIN 19370 987870 4337 00:00:53
* 5 ..... HASH JOIN 19587 607197 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 984
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4831 00:00:01
* 1 . VIEW 19370 1510860 4831 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4831 00:00:01
3 ... HASH GROUP BY 19370 987870 4831 00:00:01
* 4 .... HASH JOIN 19370 987870 4336 00:00:01
* 5 ..... HASH JOIN 19587 607197 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 342
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : aktx221b6sqc4
Execution Frequency : 56
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '1997-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.15% .13854 .148217 -6.98%
parse_time -2.83% .000741 .001291 -74.22%
cpu_time -.22% .138264 .15181 -9.8%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4824 4821 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 16 16
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 7 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 930
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 18948 1477944 4824 00:00:58
* 1 . VIEW 18948 1477944 4824 00:00:58
* 2 .. WINDOW SORT PUSHED RANK 18948 966348 4824 00:00:58
3 ... HASH GROUP BY 18948 966348 4824 00:00:58
* 4 .... HASH JOIN 18948 966348 4337 00:00:53
* 5 ..... HASH JOIN 19161 593991 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 90 1350 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 989
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 18948 1477944 4821 00:00:01
* 1 . VIEW 18948 1477944 4821 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 18948 966348 4821 00:00:01
3 ... HASH GROUP BY 18948 966348 4821 00:00:01
* 4 .... HASH JOIN 18948 966348 4336 00:00:01
* 5 ..... HASH JOIN 19161 593991 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 90 1350 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 320
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 709uggnu40a9a
Execution Frequency : 46
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2012-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.15% .258583 .270137 -4.47%
parse_time -13.46% .00052 .003709 -613.27%
cpu_time -.38% .258294 .286956 -11.1%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4834 4831 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 3 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 3 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 908
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4834 00:00:59
* 1 . VIEW 19370 1510860 4834 00:00:59
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4834 00:00:59
3 ... HASH GROUP BY 19370 987870 4834 00:00:59
* 4 .... HASH JOIN 19370 987870 4337 00:00:53
* 5 ..... HASH JOIN 19587 607197 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

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

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4831 00:00:01
* 1 . VIEW 19370 1510860 4831 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4831 00:00:01
3 ... HASH GROUP BY 19370 987870 4831 00:00:01
* 4 .... HASH JOIN 19370 987870 4336 00:00:01
* 5 ..... HASH JOIN 19587 607197 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 314
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 4kwqmbh8cs1gp
Execution Frequency : 49
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '1998-3' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time .14% .155369 .145142 6.58%
parse_time -4.18% .000688 .001618 -135.17%
cpu_time -.07% .155643 .160309 -3%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4834 4831 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 16 16
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 902
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4834 00:00:59
* 1 . VIEW 19370 1510860 4834 00:00:59
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4834 00:00:59
3 ... HASH GROUP BY 19370 987870 4834 00:00:59
* 4 .... HASH JOIN 19370 987870 4337 00:00:53
* 5 ..... HASH JOIN 19587 607197 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 961
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4831 00:00:01
* 1 . VIEW 19370 1510860 4831 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4831 00:00:01
3 ... HASH GROUP BY 19370 987870 4831 00:00:01
* 4 .... HASH JOIN 19370 987870 4336 00:00:01
* 5 ..... HASH JOIN 19587 607197 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 312
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 3zdr12jv606xs
Execution Frequency : 53
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2011-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time .13% .192963 .184517 4.38%
parse_time -4.05% .000455 .001288 -183.08%
cpu_time -.24% .19237 .208168 -8.21%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4824 4821 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 900
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 18948 1477944 4824 00:00:58
* 1 . VIEW 18948 1477944 4824 00:00:58
* 2 .. WINDOW SORT PUSHED RANK 18948 966348 4824 00:00:58
3 ... HASH GROUP BY 18948 966348 4824 00:00:58
* 4 .... HASH JOIN 18948 966348 4337 00:00:53
* 5 ..... HASH JOIN 19161 593991 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 90 1350 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 959
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 18948 1477944 4821 00:00:01
* 1 . VIEW 18948 1477944 4821 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 18948 966348 4821 00:00:01
3 ... HASH GROUP BY 18948 966348 4821 00:00:01
* 4 .... HASH JOIN 18948 966348 4336 00:00:01
* 5 ..... HASH JOIN 19161 593991 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 90 1350 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 358
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : gx7ymx5myrd2t
Execution Frequency : 54
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2008-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.11% .162516 .169956 -4.58%
parse_time -2.55% .000772 .001286 -66.58%
cpu_time -.15% .162808 .172373 -5.88%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4832 4829 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 946
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4832 00:00:58
* 1 . VIEW 19159 1494402 4832 00:00:58
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4832 00:00:58
3 ... HASH GROUP BY 19159 977109 4832 00:00:58
* 4 .... HASH JOIN 19159 977109 4337 00:00:53
* 5 ..... HASH JOIN 19374 600594 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 1005
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4829 00:00:01
* 1 . VIEW 19159 1494402 4829 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4829 00:00:01
3 ... HASH GROUP BY 19159 977109 4829 00:00:01
* 4 .... HASH JOIN 19159 977109 4336 00:00:01
* 5 ..... HASH JOIN 19374 600594 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 318
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 5vbwrcz3vw3s0
Execution Frequency : 61
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2013-3' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time .11% .246455 .240068 2.59%
parse_time -4.13% .000514 .001252 -143.58%
cpu_time -.19% .246212 .25721 -4.47%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4834 4831 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 906
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4834 00:00:59
* 1 . VIEW 19370 1510860 4834 00:00:59
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4834 00:00:59
3 ... HASH GROUP BY 19370 987870 4834 00:00:59
* 4 .... HASH JOIN 19370 987870 4337 00:00:53
* 5 ..... HASH JOIN 19587 607197 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 965
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4831 00:00:01
* 1 . VIEW 19370 1510860 4831 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4831 00:00:01
3 ... HASH GROUP BY 19370 987870 4831 00:00:01
* 4 .... HASH JOIN 19370 987870 4336 00:00:01
* 5 ..... HASH JOIN 19587 607197 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 317
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 5hppq6bp5wmnm
Execution Frequency : 61
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2006-3' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time .1% .165611 .159708 3.56%
parse_time -3.96% .000538 .001245 -131.41%
cpu_time -.11% .165974 .172307 -3.82%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4834 4831 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 905
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4834 00:00:59
* 1 . VIEW 19370 1510860 4834 00:00:59
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4834 00:00:59
3 ... HASH GROUP BY 19370 987870 4834 00:00:59
* 4 .... HASH JOIN 19370 987870 4337 00:00:53
* 5 ..... HASH JOIN 19587 607197 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

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

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4831 00:00:01
* 1 . VIEW 19370 1510860 4831 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4831 00:00:01
3 ... HASH GROUP BY 19370 987870 4831 00:00:01
* 4 .... HASH JOIN 19370 987870 4336 00:00:01
* 5 ..... HASH JOIN 19587 607197 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 338
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : a75y9uq84au4p
Execution Frequency : 38
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2010-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.1% .204695 .213663 -4.38%
parse_time -2.59% .000554 .001297 -134.12%
cpu_time -.16% .204469 .219216 -7.21%
user_io_time 0% 0 0 0%
buffer_gets 0% 15907 15907 0%
cost 0% 4834 4831 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 926
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4834 00:00:59
* 1 . VIEW 19370 1510860 4834 00:00:59
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4834 00:00:59
3 ... HASH GROUP BY 19370 987870 4834 00:00:59
* 4 .... HASH JOIN 19370 987870 4337 00:00:53
* 5 ..... HASH JOIN 19587 607197 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 985
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4831 00:00:01
* 1 . VIEW 19370 1510860 4831 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4831 00:00:01
3 ... HASH GROUP BY 19370 987870 4831 00:00:01
* 4 .... HASH JOIN 19370 987870 4336 00:00:01
* 5 ..... HASH JOIN 19587 607197 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 306
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 1zm0sn3qcx0hu
Execution Frequency : 45
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2009-2' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.1% .180303 .187795 -4.16%
parse_time -3.91% .000618 .001564 -153.07%
cpu_time -.67% .180572 .232764 -28.9%
user_io_time 0% 0 0 0%
buffer_gets 0% 15907 15907 0%
cost 0% 4832 4829 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 17 17
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 894
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4832 00:00:58
* 1 . VIEW 19159 1494402 4832 00:00:58
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4832 00:00:58
3 ... HASH GROUP BY 19159 977109 4832 00:00:58
* 4 .... HASH JOIN 19159 977109 4337 00:00:53
* 5 ..... HASH JOIN 19374 600594 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 953
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4829 00:00:01
* 1 . VIEW 19159 1494402 4829 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4829 00:00:01
3 ... HASH GROUP BY 19159 977109 4829 00:00:01
* 4 .... HASH JOIN 19159 977109 4336 00:00:01
* 5 ..... HASH JOIN 19374 600594 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 349
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : dfdg44tjr89uf
Execution Frequency : 48
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2010-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.08% .184686 .190789 -3.3%
parse_time -3.04% .000519 .001209 -132.95%
cpu_time -.13% .184972 .19417 -4.97%
user_io_time 0% 0 0 0%
buffer_gets 0% 15907 15907 0%
cost 0% 4824 4821 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 937
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 18948 1477944 4824 00:00:58
* 1 . VIEW 18948 1477944 4824 00:00:58
* 2 .. WINDOW SORT PUSHED RANK 18948 966348 4824 00:00:58
3 ... HASH GROUP BY 18948 966348 4824 00:00:58
* 4 .... HASH JOIN 18948 966348 4337 00:00:53
* 5 ..... HASH JOIN 19161 593991 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 90 1350 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 996
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 18948 1477944 4821 00:00:01
* 1 . VIEW 18948 1477944 4821 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 18948 966348 4821 00:00:01
3 ... HASH GROUP BY 18948 966348 4821 00:00:01
* 4 .... HASH JOIN 18948 966348 4336 00:00:01
* 5 ..... HASH JOIN 19161 593991 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 90 1350 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 304
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 1qr4ps69f36jy
Execution Frequency : 56
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2006-2' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.08% .155863 .161087 -3.35%
parse_time -3.74% .000465 .001192 -156.34%
cpu_time -.93% .155976 .214134 -37.29%
user_io_time 0% 0 0 0%
buffer_gets 0% 15907 15907 0%
cost 0% 4832 4829 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 892
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4832 00:00:58
* 1 . VIEW 19159 1494402 4832 00:00:58
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4832 00:00:58
3 ... HASH GROUP BY 19159 977109 4832 00:00:58
* 4 .... HASH JOIN 19159 977109 4337 00:00:53
* 5 ..... HASH JOIN 19374 600594 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 951
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4829 00:00:01
* 1 . VIEW 19159 1494402 4829 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4829 00:00:01
3 ... HASH GROUP BY 19159 977109 4829 00:00:01
* 4 .... HASH JOIN 19159 977109 4336 00:00:01
* 5 ..... HASH JOIN 19374 600594 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 313
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 416hz7t3mx918
Execution Frequency : 42
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2009-3' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.07% .186773 .192267 -2.94%
parse_time -3.71% .000548 .001511 -175.73%
cpu_time -.33% .186971 .214567 -14.76%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4834 4831 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 901
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4834 00:00:59
* 1 . VIEW 19370 1510860 4834 00:00:59
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4834 00:00:59
3 ... HASH GROUP BY 19370 987870 4834 00:00:59
* 4 .... HASH JOIN 19370 987870 4337 00:00:53
* 5 ..... HASH JOIN 19587 607197 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 960
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4831 00:00:01
* 1 . VIEW 19370 1510860 4831 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4831 00:00:01
3 ... HASH GROUP BY 19370 987870 4831 00:00:01
* 4 .... HASH JOIN 19370 987870 4336 00:00:01
* 5 ..... HASH JOIN 19587 607197 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 329
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 8wskn4vvvkd93
Execution Frequency : 48
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2013-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time .06% .199487 .19541 2.04%
parse_time -3.21% .000573 .001302 -127.23%
cpu_time -.06% .199569 .203769 -2.1%
user_io_time 0% 0 0 0%
buffer_gets 0% 15907 15907 0%
cost 0% 4824 4821 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 917
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 18948 1477944 4824 00:00:58
* 1 . VIEW 18948 1477944 4824 00:00:58
* 2 .. WINDOW SORT PUSHED RANK 18948 966348 4824 00:00:58
3 ... HASH GROUP BY 18948 966348 4824 00:00:58
* 4 .... HASH JOIN 18948 966348 4337 00:00:53
* 5 ..... HASH JOIN 19161 593991 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 90 1350 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 976
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 18948 1477944 4821 00:00:01
* 1 . VIEW 18948 1477944 4821 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 18948 966348 4821 00:00:01
3 ... HASH GROUP BY 18948 966348 4821 00:00:01
* 4 .... HASH JOIN 18948 966348 4336 00:00:01
* 5 ..... HASH JOIN 19161 593991 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 90 1350 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 357
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : gpxukn3ft9qgw
Execution Frequency : 11
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2011-3' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.05% .208415 .225759 -8.32%
parse_time -.75% .000474 .001218 -156.96%
cpu_time -.07% .208468 .229215 -9.95%
user_io_time 0% 0 0 0%
buffer_gets 0% 15907 15907 0%
cost 0% 4834 4831 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 945
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4834 00:00:59
* 1 . VIEW 19370 1510860 4834 00:00:59
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4834 00:00:59
3 ... HASH GROUP BY 19370 987870 4834 00:00:59
* 4 .... HASH JOIN 19370 987870 4337 00:00:53
* 5 ..... HASH JOIN 19587 607197 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

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

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4831 00:00:01
* 1 . VIEW 19370 1510860 4831 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4831 00:00:01
3 ... HASH GROUP BY 19370 987870 4831 00:00:01
* 4 .... HASH JOIN 19370 987870 4336 00:00:01
* 5 ..... HASH JOIN 19587 607197 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 307
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 222q8cuf6ugt9
Execution Frequency : 47
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2010-3' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.05% .195544 .199519 -2.03%
parse_time -4.88% .000475 .001607 -238.32%
cpu_time -.59% .19577 .239713 -22.45%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4834 4831 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 895
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4834 00:00:59
* 1 . VIEW 19370 1510860 4834 00:00:59
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4834 00:00:59
3 ... HASH GROUP BY 19370 987870 4834 00:00:59
* 4 .... HASH JOIN 19370 987870 4337 00:00:53
* 5 ..... HASH JOIN 19587 607197 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

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

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4831 00:00:01
* 1 . VIEW 19370 1510860 4831 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4831 00:00:01
3 ... HASH GROUP BY 19370 987870 4831 00:00:01
* 4 .... HASH JOIN 19370 987870 4336 00:00:01
* 5 ..... HASH JOIN 19587 607197 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 316
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 56nj9ptzakm56
Execution Frequency : 44
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2011-2' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.05% .199834 .203953 -2.06%
parse_time -1.6% .00088 .001276 -45%
cpu_time -.27% .200169 .221716 -10.76%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4832 4829 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 904
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4832 00:00:58
* 1 . VIEW 19159 1494402 4832 00:00:58
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4832 00:00:58
3 ... HASH GROUP BY 19159 977109 4832 00:00:58
* 4 .... HASH JOIN 19159 977109 4337 00:00:53
* 5 ..... HASH JOIN 19374 600594 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 963
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4829 00:00:01
* 1 . VIEW 19159 1494402 4829 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4829 00:00:01
3 ... HASH GROUP BY 19159 977109 4829 00:00:01
* 4 .... HASH JOIN 19159 977109 4336 00:00:01
* 5 ..... HASH JOIN 19374 600594 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 348
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : cmkygw8kypnpb
Execution Frequency : 48
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2013-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.05% .243968 .247736 -1.54%
parse_time -3.48% .000569 .001358 -138.66%
cpu_time -.12% .244213 .252711 -3.48%
user_io_time 0% 0 0 0%
buffer_gets 0% 15907 15907 0%
cost 0% 4834 4831 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 16 16
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 936
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4834 00:00:59
* 1 . VIEW 19370 1510860 4834 00:00:59
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4834 00:00:59
3 ... HASH GROUP BY 19370 987870 4834 00:00:59
* 4 .... HASH JOIN 19370 987870 4337 00:00:53
* 5 ..... HASH JOIN 19587 607197 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 995
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4831 00:00:01
* 1 . VIEW 19370 1510860 4831 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4831 00:00:01
3 ... HASH GROUP BY 19370 987870 4831 00:00:01
* 4 .... HASH JOIN 19370 987870 4336 00:00:01
* 5 ..... HASH JOIN 19587 607197 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 310
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 370m5yqjjcr8z
Execution Frequency : 60
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '1997-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time .05% .147347 .144521 1.92%
parse_time -5.44% .000822 .00181 -120.19%
cpu_time -.35% .147477 .167807 -13.79%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4834 4831 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 17 17
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 898
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4834 00:00:59
* 1 . VIEW 19370 1510860 4834 00:00:59
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4834 00:00:59
3 ... HASH GROUP BY 19370 987870 4834 00:00:59
* 4 .... HASH JOIN 19370 987870 4337 00:00:53
* 5 ..... HASH JOIN 19587 607197 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 957
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4831 00:00:01
* 1 . VIEW 19370 1510860 4831 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4831 00:00:01
3 ... HASH GROUP BY 19370 987870 4831 00:00:01
* 4 .... HASH JOIN 19370 987870 4336 00:00:01
* 5 ..... HASH JOIN 19587 607197 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 300
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 03ghr9b5m3x43
Execution Frequency : 10
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2003-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.03% .152393 .162999 -6.96%
parse_time -1.08% .000523 .001699 -224.86%
cpu_time -.38% .152643 .28429 -86.25%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4834 4831 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 888
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4834 00:00:59
* 1 . VIEW 19370 1510860 4834 00:00:59
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4834 00:00:59
3 ... HASH GROUP BY 19370 987870 4834 00:00:59
* 4 .... HASH JOIN 19370 987870 4337 00:00:53
* 5 ..... HASH JOIN 19587 607197 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

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

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4831 00:00:01
* 1 . VIEW 19370 1510860 4831 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4831 00:00:01
3 ... HASH GROUP BY 19370 987870 4831 00:00:01
* 4 .... HASH JOIN 19370 987870 4336 00:00:01
* 5 ..... HASH JOIN 19587 607197 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 325
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 83wufms0kwjb0
Execution Frequency : 8
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '1995-3' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.03% .135088 .146791 -8.66%
parse_time -.32% .000773 .001208 -56.27%
cpu_time -.04% .135122 .154643 -14.45%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4834 4831 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 7 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 913
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4834 00:00:59
* 1 . VIEW 19370 1510860 4834 00:00:59
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4834 00:00:59
3 ... HASH GROUP BY 19370 987870 4834 00:00:59
* 4 .... HASH JOIN 19370 987870 4337 00:00:53
* 5 ..... HASH JOIN 19587 607197 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 972
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4831 00:00:01
* 1 . VIEW 19370 1510860 4831 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4831 00:00:01
3 ... HASH GROUP BY 19370 987870 4831 00:00:01
* 4 .... HASH JOIN 19370 987870 4336 00:00:01
* 5 ..... HASH JOIN 19587 607197 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 326
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 8g3y654ptjzrz
Execution Frequency : 58
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2010-2' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time .02% .187166 .18568 .79%
parse_time -4.43% .000639 .001471 -130.2%
cpu_time -.12% .187371 .19457 -3.84%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4832 4829 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 914
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4832 00:00:58
* 1 . VIEW 19159 1494402 4832 00:00:58
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4832 00:00:58
3 ... HASH GROUP BY 19159 977109 4832 00:00:58
* 4 .... HASH JOIN 19159 977109 4337 00:00:53
* 5 ..... HASH JOIN 19374 600594 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 973
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4829 00:00:01
* 1 . VIEW 19159 1494402 4829 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4829 00:00:01
3 ... HASH GROUP BY 19159 977109 4829 00:00:01
* 4 .... HASH JOIN 19159 977109 4336 00:00:01
* 5 ..... HASH JOIN 19374 600594 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 334
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 9fsjrrksxp4gj
Execution Frequency : 48
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2000-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time .02% .143245 .141465 1.24%
parse_time -6.77% .000468 .002005 -328.42%
cpu_time -.04% .143145 .146144 -2.1%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4832 4829 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 922
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4832 00:00:58
* 1 . VIEW 19159 1494402 4832 00:00:58
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4832 00:00:58
3 ... HASH GROUP BY 19159 977109 4832 00:00:58
* 4 .... HASH JOIN 19159 977109 4337 00:00:53
* 5 ..... HASH JOIN 19374 600594 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

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

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4829 00:00:01
* 1 . VIEW 19159 1494402 4829 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4829 00:00:01
3 ... HASH GROUP BY 19159 977109 4829 00:00:01
* 4 .... HASH JOIN 19159 977109 4336 00:00:01
* 5 ..... HASH JOIN 19374 600594 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 335
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 9h2u2z9y900yk
Execution Frequency : 10
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '1999-3' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time .02% .152415 .144492 5.2%
parse_time -.71% .000515 .001287 -149.9%
cpu_time .01% .152476 .148977 2.29%
user_io_time 0% 0 0 0%
buffer_gets 0% 15907 15907 0%
cost 0% 4834 4831 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 923
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4834 00:00:59
* 1 . VIEW 19370 1510860 4834 00:00:59
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4834 00:00:59
3 ... HASH GROUP BY 19370 987870 4834 00:00:59
* 4 .... HASH JOIN 19370 987870 4337 00:00:53
* 5 ..... HASH JOIN 19587 607197 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 982
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4831 00:00:01
* 1 . VIEW 19370 1510860 4831 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4831 00:00:01
3 ... HASH GROUP BY 19370 987870 4831 00:00:01
* 4 .... HASH JOIN 19370 987870 4336 00:00:01
* 5 ..... HASH JOIN 19587 607197 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 315
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 4wazxyxv5cpt0
Execution Frequency : 47
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2005-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time .02% .153237 .151859 .9%
parse_time -2.89% .000588 .001259 -114.12%
cpu_time -.17% .15331 .166308 -8.48%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4824 4821 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 903
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 18948 1477944 4824 00:00:58
* 1 . VIEW 18948 1477944 4824 00:00:58
* 2 .. WINDOW SORT PUSHED RANK 18948 966348 4824 00:00:58
3 ... HASH GROUP BY 18948 966348 4824 00:00:58
* 4 .... HASH JOIN 18948 966348 4337 00:00:53
* 5 ..... HASH JOIN 19161 593991 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 90 1350 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 962
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 18948 1477944 4821 00:00:01
* 1 . VIEW 18948 1477944 4821 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 18948 966348 4821 00:00:01
3 ... HASH GROUP BY 18948 966348 4821 00:00:01
* 4 .... HASH JOIN 18948 966348 4336 00:00:01
* 5 ..... HASH JOIN 19161 593991 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 90 1350 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 328
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 8k0bufga1dutt
Execution Frequency : 9
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2002-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.01% .141978 .14631 -3.05%
parse_time -.7% .000548 .001393 -154.2%
cpu_time -.03% .142121 .15281 -7.52%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4824 4821 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 16 16
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 7 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 916
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 18948 1477944 4824 00:00:58
* 1 . VIEW 18948 1477944 4824 00:00:58
* 2 .. WINDOW SORT PUSHED RANK 18948 966348 4824 00:00:58
3 ... HASH GROUP BY 18948 966348 4824 00:00:58
* 4 .... HASH JOIN 18948 966348 4337 00:00:53
* 5 ..... HASH JOIN 19161 593991 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 90 1350 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

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

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 18948 1477944 4821 00:00:01
* 1 . VIEW 18948 1477944 4821 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 18948 966348 4821 00:00:01
3 ... HASH GROUP BY 18948 966348 4821 00:00:01
* 4 .... HASH JOIN 18948 966348 4336 00:00:01
* 5 ..... HASH JOIN 19161 593991 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 90 1350 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 323
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 7f6kr3z5p8qxa
Execution Frequency : 8
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2002-2' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.01% .144216 .148892 -3.24%
parse_time -.62% .000515 .001353 -162.72%
cpu_time -.03% .144311 .157809 -9.35%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4832 4829 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 16 16
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 911
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4832 00:00:58
* 1 . VIEW 19159 1494402 4832 00:00:58
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4832 00:00:58
3 ... HASH GROUP BY 19159 977109 4832 00:00:58
* 4 .... HASH JOIN 19159 977109 4337 00:00:53
* 5 ..... HASH JOIN 19374 600594 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 970
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4829 00:00:01
* 1 . VIEW 19159 1494402 4829 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4829 00:00:01
3 ... HASH GROUP BY 19159 977109 4829 00:00:01
* 4 .... HASH JOIN 19159 977109 4336 00:00:01
* 5 ..... HASH JOIN 19374 600594 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 339
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : a9nt3xdwup082
Execution Frequency : 47
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2007-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time -.01% .15958 .160222 -.4%
parse_time -3.83% .000551 .001438 -160.98%
cpu_time -.06% .159809 .164641 -3.02%
user_io_time 0% 0 0 0%
buffer_gets 0% 15907 15907 0%
cost 0% 4824 4821 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 16 16
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 927
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 18948 1477944 4824 00:00:58
* 1 . VIEW 18948 1477944 4824 00:00:58
* 2 .. WINDOW SORT PUSHED RANK 18948 966348 4824 00:00:58
3 ... HASH GROUP BY 18948 966348 4824 00:00:58
* 4 .... HASH JOIN 18948 966348 4337 00:00:53
* 5 ..... HASH JOIN 19161 593991 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 90 1350 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 986
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 18948 1477944 4821 00:00:01
* 1 . VIEW 18948 1477944 4821 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 18948 966348 4821 00:00:01
3 ... HASH GROUP BY 18948 966348 4821 00:00:01
* 4 .... HASH JOIN 18948 966348 4336 00:00:01
* 5 ..... HASH JOIN 19161 593991 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 90 1350 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 327
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 8hytq9ybsh5xu
Execution Frequency : 6
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2000-2' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time .01% .149083 .145742 2.24%
parse_time -.54% .000611 .001584 -159.25%
cpu_time -.01% .14931 .152476 -2.12%
user_io_time 0% 0 0 0%
buffer_gets 0% 15907 15907 0%
cost 0% 4832 4829 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 16 16
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 915
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4832 00:00:58
* 1 . VIEW 19159 1494402 4832 00:00:58
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4832 00:00:58
3 ... HASH GROUP BY 19159 977109 4832 00:00:58
* 4 .... HASH JOIN 19159 977109 4337 00:00:53
* 5 ..... HASH JOIN 19374 600594 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 974
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4829 00:00:01
* 1 . VIEW 19159 1494402 4829 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4829 00:00:01
3 ... HASH GROUP BY 19159 977109 4829 00:00:01
* 4 .... HASH JOIN 19159 977109 4336 00:00:01
* 5 ..... HASH JOIN 19374 600594 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 336
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 9tkfxkjugywqp
Execution Frequency : 11
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2014-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time 0% .117203 .115812 1.19%
parse_time -.18% .000885 .001065 -20.34%
cpu_time -.01% .117107 .119356 -1.92%
user_io_time 0% 0 0 0%
buffer_gets 0% 11520 11520 0%
cost 0% 4834 4831 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 0 0
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 8 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 8 ejecuciones.


Findings (3):
  1. La estructura del plan de ejecución SQL ha cambiado.
  2. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.
  3. Esta sentencia SQL ha devuelto cero filas.


Execution Plan Before Change:
Plan Id : 924
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19264 1502592 4834 00:00:59
* 1 . VIEW 19264 1502592 4834 00:00:59
* 2 .. WINDOW SORT PUSHED RANK 19264 982464 4834 00:00:59
3 ... HASH GROUP BY 19264 982464 4834 00:00:59
* 4 .... HASH JOIN 19264 982464 4337 00:00:53
* 5 ..... HASH JOIN 19480 603880 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 983
Plan Hash Value : 3363775485

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19264 1502592 4831 00:00:01
* 1 . VIEW 19264 1502592 4831 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19264 982464 4831 00:00:01
3 ... HASH GROUP BY 19264 982464 4831 00:00:01
4 .... NESTED LOOPS
5 ..... NESTED LOOPS 19264 982464 4336 00:00:01
* 6 ...... HASH JOIN 19480 603880 3139 00:00:01
* 7 ....... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
8 ....... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
* 9 ...... INDEX UNIQUE SCAN CUSTOMERS_PK
10 ..... TABLE ACCESS BY INDEX ROWID CUSTOMERS 1 20 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 354
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : fx2c47hcyfz9f
Execution Frequency : 8
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2006-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time 0% .155689 .157286 -1.03%
parse_time -.52% .000556 .001266 -127.7%
cpu_time -.01% .155643 .159809 -2.68%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4824 4821 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 942
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 18948 1477944 4824 00:00:58
* 1 . VIEW 18948 1477944 4824 00:00:58
* 2 .. WINDOW SORT PUSHED RANK 18948 966348 4824 00:00:58
3 ... HASH GROUP BY 18948 966348 4824 00:00:58
* 4 .... HASH JOIN 18948 966348 4337 00:00:53
* 5 ..... HASH JOIN 19161 593991 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 90 1350 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 1001
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 18948 1477944 4821 00:00:01
* 1 . VIEW 18948 1477944 4821 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 18948 966348 4821 00:00:01
3 ... HASH GROUP BY 18948 966348 4821 00:00:01
* 4 .... HASH JOIN 18948 966348 4336 00:00:01
* 5 ..... HASH JOIN 19161 593991 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 90 1350 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 352
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : fd5j4mmztdpmm
Execution Frequency : 8
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2003-3' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time 0% .151871 .150531 .88%
parse_time -.48% .000562 .00121 -115.3%
cpu_time 0% .151976 .15331 -.88%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4834 4831 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 940
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4834 00:00:59
* 1 . VIEW 19370 1510860 4834 00:00:59
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4834 00:00:59
3 ... HASH GROUP BY 19370 987870 4834 00:00:59
* 4 .... HASH JOIN 19370 987870 4337 00:00:53
* 5 ..... HASH JOIN 19587 607197 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 999
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4831 00:00:01
* 1 . VIEW 19370 1510860 4831 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4831 00:00:01
3 ... HASH GROUP BY 19370 987870 4831 00:00:01
* 4 .... HASH JOIN 19370 987870 4336 00:00:01
* 5 ..... HASH JOIN 19587 607197 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 301
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 08kysb4nyt58n
Execution Frequency : 9
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '1995-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time 0% .138697 .139311 -.44%
parse_time -.47% .00055 .00112 -103.64%
cpu_time -.22% .138978 .222966 -60.43%
user_io_time 0% 0 0 0%
buffer_gets 0% 15907 15907 0%
cost 0% 4834 4831 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 7 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 7 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 889
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4834 00:00:59
* 1 . VIEW 19370 1510860 4834 00:00:59
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4834 00:00:59
3 ... HASH GROUP BY 19370 987870 4834 00:00:59
* 4 .... HASH JOIN 19370 987870 4337 00:00:53
* 5 ..... HASH JOIN 19587 607197 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 948
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4831 00:00:01
* 1 . VIEW 19370 1510860 4831 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4831 00:00:01
3 ... HASH GROUP BY 19370 987870 4831 00:00:01
* 4 .... HASH JOIN 19370 987870 4336 00:00:01
* 5 ..... HASH JOIN 19587 607197 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 302
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : 0bg8rg9srv8rm
Execution Frequency : 56
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2012-3' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time 0% .23756 .237607 -.02%
parse_time -3.2% .00052 .001143 -119.81%
cpu_time -1.81% .237214 .350196 -47.63%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4834 4831 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 890
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4834 00:00:59
* 1 . VIEW 19370 1510860 4834 00:00:59
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4834 00:00:59
3 ... HASH GROUP BY 19370 987870 4834 00:00:59
* 4 .... HASH JOIN 19370 987870 4337 00:00:53
* 5 ..... HASH JOIN 19587 607197 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 949
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19370 1510860 4831 00:00:01
* 1 . VIEW 19370 1510860 4831 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19370 987870 4831 00:00:01
3 ... HASH GROUP BY 19370 987870 4831 00:00:01
* 4 .... HASH JOIN 19370 987870 4336 00:00:01
* 5 ..... HASH JOIN 19587 607197 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 92 1380 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 353
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : fwsw6473mw961
Execution Frequency : 47
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2004-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time 0% .148999 .149054 -.04%
parse_time -3.38% .000532 .001316 -147.37%
cpu_time -.04% .148977 .15181 -1.9%
user_io_time 0% 0 0 0%
buffer_gets 0% 15907 15907 0%
cost 0% 4832 4829 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 17 17
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 941
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4832 00:00:58
* 1 . VIEW 19159 1494402 4832 00:00:58
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4832 00:00:58
3 ... HASH GROUP BY 19159 977109 4832 00:00:58
* 4 .... HASH JOIN 19159 977109 4337 00:00:53
* 5 ..... HASH JOIN 19374 600594 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

Execution Plan After Change:
Plan Id : 1000
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4829 00:00:01
* 1 . VIEW 19159 1494402 4829 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4829 00:00:01
3 ... HASH GROUP BY 19159 977109 4829 00:00:01
* 4 .... HASH JOIN 19159 977109 4336 00:00:01
* 5 ..... HASH JOIN 19374 600594 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):


SQL Details:

Object ID : 356
Schema Name : SH
Container Name : Unknown (con_dbid: 2934716302)
SQL ID : gppx8hpcwsb5g
Execution Frequency : 7
SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2012-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16

Execution Statistics:

Stat Name
Impact on
Workload
Value
Before
Value
After
Impact
on SQL
elapsed_time 0% .202939 .202605 .16%
parse_time -.47% .000504 .001236 -145.24%
cpu_time -.01% .202219 .206218 -1.98%
user_io_time 0% 0 0 0%
buffer_gets 0% 15906 15906 0%
cost 0% 4832 4829 .06%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 15 15
Note: time statistics are displayed in seconds

Notes:
Before Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones.

After Change:
  1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers.
  2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones.


Findings (1):
  1. La estructura del plan SQL en ejecución 'sh_after_change' es distinta del plan correspondiente almacenado en el juego de ajustes SQL.


Execution Plan Before Change:
Plan Id : 944
Plan Hash Value : 4028546188

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4832 00:00:58
* 1 . VIEW 19159 1494402 4832 00:00:58
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4832 00:00:58
3 ... HASH GROUP BY 19159 977109 4832 00:00:58
* 4 .... HASH JOIN 19159 977109 4337 00:00:53
* 5 ..... HASH JOIN 19374 600594 3140 00:00:38
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:38
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:15

Predicate Information (identified by operation id):

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

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19159 1494402 4829 00:00:01
* 1 . VIEW 19159 1494402 4829 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 19159 977109 4829 00:00:01
3 ... HASH GROUP BY 19159 977109 4829 00:00:01
* 4 .... HASH JOIN 19159 977109 4336 00:00:01
* 5 ..... HASH JOIN 19374 600594 3139 00:00:01
* 6 ...... INDEX RANGE SCAN IDX$$_00A90001 91 1365 2 00:00:01
7 ...... TABLE ACCESS FULL SALES 1472847 23565552 3134 00:00:01
8 ..... TABLE ACCESS FULL CUSTOMERS 162592 3251840 1197 00:00:01

Predicate Information (identified by operation id):