Try to look at ASH analytics for a week, report will run in about 30+ seconds.
I'm playing with the range of times and start to get an EM error "snapshots for the selected database are not in the repository". Theres an oracle note on this, (Doc ID 2135197.1) which doesnt apply, anyway if I log out and back in again of my session on the EM, the reports work then. I suspect a bug of some sort, anyway, my main reason for this post is I trace the EM session on the database as not happy with the ~30 seconds and we see the following, its all taken up with PX Deq: Execute Reply
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
direct path write temp 7 0.01 0.03
db file scattered read 65 0.01 0.09
enq: TO - contention 2 0.00 0.00
db file sequential read 2 0.00 0.00
enq: RO - fast object reuse 43 0.00 0.02
reliable message 26 0.00 0.01
PX Deq: Join ACK 44 0.00 0.01
PX Deq: reap credit 409 0.00 0.00
IPC send completion sync 22 0.00 0.00
PX Deq Credit: send blkd 50 0.00 0.01
PX Deq Credit: need buffer 1 0.00 0.00
PX Deq: Parse Reply 22 0.10 0.21
PX Deq: Execute Reply 22 2.39 34.83
PX Deq: Signal ACK EXT 22 0.00 0.02
PX Deq: Slave Session Stats 22 0.00 0.00
enq: PS - contention 16 0.02 0.03
latch free 1 0.00 0.00
latch: ges resource hash list 1 0.00 0.00
PX qref latch 505 0.00 0.00
direct path read 8 0.01 0.02
gcs drm freeze in enter server mode 3 0.21 0.27
gc cr grant 2-way 1 0.00 0.00
gc cr multi block request 12 0.00 0.00
********************************************************************************
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
1 1 1 NESTED LOOPS (cr=198 pr=1 pw=0 time=91777 us cost=68 size=107 card=1)
1 1 1 VIEW (cr=5 pr=1 pw=0 time=635 us cost=15 size=73 card=1)
1 1 1 SORT GROUP BY (cr=5 pr=1 pw=0 time=568 us)
9 9 9 VIEW (cr=5 pr=1 pw=0 time=342 us cost=15 size=305125 card=2441)
9 9 9 TABLE ACCESS FULL SYS_TEMP_0FD9D758E_37F3485E (cr=5 pr=1 pw=0 time=341 us cost=15 size=305125 card=2441)
1 1 1 VIEW (cr=193 pr=0 pw=0 time=91136 us cost=53 size=34 card=1)
1 1 1 SORT AGGREGATE (cr=193 pr=0 pw=0 time=91132 us cost=53 size=50 card=1)
2 2 2 SORT GROUP BY (cr=193 pr=0 pw=0 time=87947 us cost=53 size=50 card=1)
204 204 204 VIEW (cr=193 pr=0 pw=0 time=81170 us cost=52 size=50 card=1)
204 204 204 SORT GROUP BY (cr=193 pr=0 pw=0 time=73980 us cost=52 size=42 card=1)
1446 1446 1446 VIEW (cr=193 pr=0 pw=0 time=8933 us cost=51 size=1134 card=27)
1446 1446 1446 SORT GROUP BY (cr=193 pr=0 pw=0 time=6403 us cost=51 size=4293 card=27)
1446 1446 1446 HASH JOIN RIGHT OUTER (cr=193 pr=0 pw=0 time=2893 us cost=50 size=388119 card=2441)
9 9 9 VIEW (cr=3 pr=0 pw=0 time=32 us cost=15 size=119609 card=2441)
9 9 9 TABLE ACCESS FULL SYS_TEMP_0FD9D758E_37F3485E (cr=3 pr=0 pw=0 time=22 us cost=15 size=305125 card=2441)
1446 1446 1446 VIEW (cr=190 pr=0 pw=0 time=1107 us cost=35 size=268510 card=2441)
16872 16872 16872 TABLE ACCESS FULL SYS_TEMP_0FD9D758D_37F3485E (cr=190 pr=0 pw=0 time=5516 us cost=35 size=798207 card=2441)
10 10 10 COUNT STOPKEY (cr=0 pr=0 pw=0 time=35491609 us)
10 10 10 PX COORDINATOR (cr=0 pr=0 pw=0 time=35423408 us)
0 0 0 PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=0 size=52300 card=100)
0 0 0 VIEW GV$SQL (cr=0 pr=0 pw=0 time=0 us)
0 0 0 FIXED TABLE FULL X$KGLCURSOR_CHILD (cr=0 pr=0 pw=0 time=0 us cost=0 size=52300 card=100)
10 10 10 COUNT STOPKEY (cr=40 pr=0 pw=0 time=598 us)
10 10 10 TABLE ACCESS BY INDEX ROWID BATCHED WRH$_SQLTEXT (cr=40 pr=0 pw=0 time=512 us cost=5 size=998 card=1)
10 10 10 INDEX RANGE SCAN WRH$_SQLTEXT_PK (cr=30 pr=0 pw=0 time=376 us cost=3 size=0 card=1)(object id 10669275)
1 1 1 VIEW (cr=238 pr=8 pw=0 time=35583663 us cost=44 size=34 card=1)
1 1 1 SORT GROUP BY (cr=238 pr=8 pw=0 time=35583354 us)
28 28 28 VIEW (cr=190 pr=0 pw=0 time=57232 us cost=39 size=172 card=1)
28 28 28 SORT GROUP BY (cr=190 pr=0 pw=0 time=56636 us cost=39 size=209 card=1)
6997 6997 6997 VIEW VW_DAG_0 (cr=190 pr=0 pw=0 time=54835 us cost=38 size=209 card=1)
6997 6997 6997 SORT GROUP BY (cr=190 pr=0 pw=0 time=52798 us cost=38 size=159 card=1)
7454 7454 7454 VIEW (cr=190 pr=0 pw=0 time=44452 us cost=37 size=159 card=1)
7454 7454 7454 WINDOW BUFFER (cr=190 pr=0 pw=0 time=40662 us cost=37 size=146 card=1)
7454 7454 7454 SORT GROUP BY (cr=190 pr=0 pw=0 time=32803 us cost=37 size=146 card=1)
7454 7454 7454 VIEW (cr=190 pr=0 pw=0 time=27877 us cost=36 size=356386 card=2441)
7454 7454 7454 WINDOW SORT (cr=190 pr=0 pw=0 time=22822 us cost=36 size=324653 card=2441)
7454 7454 7454 VIEW (cr=190 pr=0 pw=0 time=3096 us cost=35 size=324653 card=2441)
16872 16872 16872 TABLE ACCESS FULL SYS_TEMP_0FD9D758D_37F3485E (cr=190 pr=0 pw=0 time=4136 us cost=35 size=798207 card=2441)
1 1 1 VIEW (cr=190 pr=188 pw=0 time=135649 us cost=39 size=34 card=1)
1 1 1 SORT GROUP BY (cr=190 pr=188 pw=0 time=135207 us)
37 37 37 VIEW (cr=190 pr=188 pw=0 time=132785 us cost=39 size=214 card=1)
37 37 37 SORT GROUP BY (cr=190 pr=188 pw=0 time=132746 us cost=39 size=374 card=1)
6303 6303 6303 VIEW VW_DAG_1 (cr=190 pr=188 pw=0 time=135267 us cost=38 size=374 card=1)
6303 6303 6303 SORT GROUP BY (cr=190 pr=188 pw=0 time=127071 us cost=38 size=214 card=1)
7872 7872 7872 VIEW (cr=190 pr=188 pw=0 time=107449 us cost=37 size=214 card=1)
7872 7872 7872 WINDOW BUFFER (cr=190 pr=188 pw=0 time=101354 us cost=37 size=201 card=1)
7872 7872 7872 SORT GROUP BY (cr=190 pr=188 pw=0 time=88813 us cost=37 size=201 card=1)
7872 7872 7872 VIEW (cr=190 pr=188 pw=0 time=73733 us cost=36 size=490641 card=2441)
7872 7872 7872 WINDOW SORT (cr=190 pr=188 pw=0 time=68017 us cost=36 size=458908 card=2441)
7872 7872 7872 VIEW (cr=190 pr=188 pw=0 time=28830 us cost=35 size=458908 card=2441)
16872 16872 16872 TABLE ACCESS FULL SYS_TEMP_0FD9D758D_37F3485E (cr=190 pr=188 pw=0 time=7433 us cost=35 size=798207 card=2441)
1 1 1 VIEW (cr=190 pr=0 pw=0 time=9241 us cost=39 size=34 card=1)
1 1 1 SORT GROUP BY (cr=190 pr=0 pw=0 time=8716 us)
61 61 61 VIEW (cr=190 pr=0 pw=0 time=4874 us cost=39 size=185 card=1)
61 61 61 SORT GROUP BY (cr=190 pr=0 pw=0 time=4811 us cost=39 size=287 card=1)
61 61 61 VIEW VW_DAG_2 (cr=190 pr=0 pw=0 time=4636 us cost=38 size=287 card=1)
61 61 61 SORT GROUP BY (cr=190 pr=0 pw=0 time=4511 us cost=38 size=185 card=1)
61 61 61 VIEW (cr=190 pr=0 pw=0 time=4410 us cost=37 size=185 card=1)
61 61 61 WINDOW BUFFER (cr=190 pr=0 pw=0 time=4349 us cost=37 size=172 card=1)
61 61 61 SORT GROUP BY (cr=190 pr=0 pw=0 time=4186 us cost=37 size=172 card=1)
83 83 83 VIEW (cr=190 pr=0 pw=0 time=4071 us cost=36 size=419852 card=2441)
83 83 83 WINDOW SORT (cr=190 pr=0 pw=0 time=4068 us cost=36 size=388119 card=2441)
83 83 83 VIEW (cr=190 pr=0 pw=0 time=3685 us cost=35 size=388119 card=2441)
16872 16872 16872 TABLE ACCESS FULL SYS_TEMP_0FD9D758D_37F3485E (cr=190 pr=0 pw=0 time=6053 us cost=35 size=798207 card=2441)
1 1 1 TEMP TABLE TRANSFORMATION (cr=5526 pr=834 pw=835 time=843240 us)
0 0 0 LOAD AS SELECT (cr=5334 pr=647 pw=834 time=825379 us)
16872 16872 16872 VIEW (cr=5334 pr=647 pw=647 time=791528 us cost=3363 size=798207 card=2441)
16872 16872 16872 TEMP TABLE TRANSFORMATION (cr=5334 pr=647 pw=647 time=785263 us)
0 0 0 LOAD AS SELECT (cr=3237 pr=0 pw=477 time=247319 us)
27251 27251 27251 VIEW (cr=3237 pr=0 pw=0 time=179805 us cost=3349 size=277173 card=1339)
27251 27251 27251 UNION-ALL (cr=3237 pr=0 pw=0 time=169313 us)
0 0 0 FILTER (cr=0 pr=0 pw=0 time=1 us)
0 0 0 PX COORDINATOR (cr=0 pr=0 pw=0 time=0 us)
0 0 0 PX SEND QC (RANDOM) :TQ20000 (cr=0 pr=0 pw=0 time=0 us cost=0 size=139 card=1)
0 0 0 VIEW GV$ACTIVE_SESSION_HISTORY (cr=0 pr=0 pw=0 time=0 us)
0 0 0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=0 size=139 card=1)
0 0 0 FIXED TABLE FULL X$KEWASH (cr=0 pr=0 pw=0 time=0 us cost=0 size=25 card=1)
0 0 0 FIXED TABLE FIXED INDEX X$ASH (ind:1) (cr=0 pr=0 pw=0 time=0 us cost=0 size=114 card=1)
27251 27251 27251 FILTER (cr=3237 pr=0 pw=0 time=121451 us)
27251 27251 27251 HASH JOIN RIGHT OUTER (cr=3237 pr=0 pw=0 time=112623 us cost=3166 size=228798 card=1338)
1699 1699 1699 TABLE ACCESS FULL WRH$_EVENT_NAME (cr=23 pr=0 pw=0 time=276 us cost=8 size=79853 card=1699)
27251 27251 27251 HASH JOIN RIGHT OUTER (cr=3214 pr=0 pw=0 time=82795 us cost=3158 size=165912 card=1338)
15 15 15 TABLE ACCESS FULL WRM$_SNAPSHOT (cr=38 pr=0 pw=0 time=47 us cost=12 size=255 card=15)
27251 27251 27251 PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=3176 pr=0 pw=0 time=56775 us cost=3146 size=143166 card=1338)
27251 27251 27251 TABLE ACCESS BY LOCAL INDEX ROWID BATCHED WRH$_ACTIVE_SESSION_HISTORY PARTITION: KEY KEY (cr=3176 pr=0 pw=0 time=48959 us cost=3146 size=143166 card=1338)
28362 28362 28362 INDEX RANGE SCAN WRH$_ACTIVE_SESSION_HISTORY_PK PARTITION: KEY KEY (cr=314 pr=0 pw=0 time=27105 us cost=297 size=0 card=22299)(object id 10669157)
0 0 0 LOAD AS SELECT (cr=482 pr=477 pw=15 time=141680 us)
1446 1446 1446 SORT GROUP BY ROLLUP (cr=482 pr=477 pw=0 time=136554 us cost=3 size=207 card=1)
27251 27251 27251 TABLE ACCESS FULL SYS_TEMP_0FD9D758F_37F3485E (cr=482 pr=477 pw=0 time=15131 us cost=2 size=828 card=4)
0 0 0 LOAD AS SELECT (cr=480 pr=0 pw=71 time=108704 us)
7462 7462 7462 SORT GROUP BY (cr=480 pr=0 pw=0 time=95388 us cost=3 size=207 card=1)
27251 27251 27251 TABLE ACCESS FULL SYS_TEMP_0FD9D758F_37F3485E (cr=480 pr=0 pw=0 time=18340 us cost=2 size=828 card=4)
0 0 0 LOAD AS SELECT (cr=480 pr=0 pw=82 time=127837 us)
7872 7872 7872 SORT GROUP BY (cr=480 pr=0 pw=0 time=115128 us cost=3 size=207 card=1)
27251 27251 27251 TABLE ACCESS FULL SYS_TEMP_0FD9D758F_37F3485E (cr=480 pr=0 pw=0 time=19480 us cost=2 size=828 card=4)
0 0 0 LOAD AS SELECT (cr=480 pr=0 pw=2 time=144453 us)
92 92 92 SORT GROUP BY ROLLUP (cr=480 pr=0 pw=0 time=134980 us cost=3 size=207 card=1)
27251 27251 27251 TABLE ACCESS FULL SYS_TEMP_0FD9D758F_37F3485E (cr=480 pr=0 pw=0 time=21400 us cost=2 size=828 card=4)
16872 16872 16872 VIEW (cr=175 pr=170 pw=0 time=9362 us cost=2 size=303 card=1)
16872 16872 16872 TABLE ACCESS FULL SYS_TEMP_0FD9D7590_37F3485E (cr=175 pr=170 pw=0 time=3609 us cost=2 size=207 card=1)
0 0 0 LOAD AS SELECT (cr=192 pr=187 pw=1 time=17594 us)
9 9 9 HASH GROUP BY (cr=192 pr=187 pw=0 time=15236 us cost=37 size=305125 card=2441)
9 9 9 VIEW (cr=192 pr=187 pw=0 time=14626 us cost=36 size=305125 card=2441)
9 9 9 WINDOW SORT (cr=192 pr=187 pw=0 time=14607 us cost=36 size=305125 card=2441)
9 9 9 VIEW (cr=192 pr=187 pw=0 time=14560 us cost=35 size=305125 card=2441)
16872 16872 16872 TABLE ACCESS FULL SYS_TEMP_0FD9D758D_37F3485E (cr=192 pr=187 pw=0 time=6705 us cost=35 size=798207 card=2441)
1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=2 us cost=2 size=0 card=1)