Trace Analyzer Report: 560 6

Trace Identification

Trace Filename:oraxp10g_ora_560_level12.trc
UDUMP Directory:C:\ORACLE\PRODUCT\10.1.0\ADMIN\ORAXP10G\UDUMP
Instance Name:oraxp10g
Host Name:Unknown
RDBMS Version:10.1.0.2.0
Trace Size (bytes):83692
Trace Lines:1347
Trace Start Time:24-JAN-06 08:53:05
Trace End Time:24-JAN-06 08:56:31
Trace Duration (tot secs):205.500
4 Gaps (tot secs):131.149
Analyzed on Same Instance?:Y
Trace Truncated?:N
Report Generated by User:USER0

Trace Header

*** TRACE DUMP CONTINUED FROM FILE  ***


Dump file c:\oracle\product\10.1.0\admin\oraxp10g\udump\oraxp10g_ora_560_level12.trc
Tue Jan 24 08:53:05 2006
ORACLE V10.1.0.2.0 - Production vsnsta=0
vsnsql=13 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1
CPU             : 1 - type 586
Process Affinity: 0x00000000
Memory (A/P)    : PH:31M/287M, PG:1454M/1801M, VA:1744M/2047M
Instance name: oraxp10g

Redo thread mounted by this instance: 1

Oracle process number: 14

Windows thread id: 560, image: ORACLE.EXE (SHAD)


*** 2006-01-24 08:53:05.287
*** ACTION NAME:() 2006-01-24 08:53:05.287
*** MODULE NAME:(SQL*Plus) 2006-01-24 08:53:05.287
*** SERVICE NAME:(SYS$USERS) 2006-01-24 08:53:05.287
*** SESSION ID:(157.59) 2006-01-24 08:53:05.287

SQL Statements Count

StatementsUnique
User:3422
Internal:1913
Total:5335

Time Summary (secs)

User
(Non-Recursive)
User
(Recursive)
Internal
CPU Time (secs):1.1600.1200.310
Elapsed Time (secs):1.4360.1370.531
Wait Time (non-idle):0.0350.0160.147
Wait Time (idle):203.9990.0000.000

User Non-Recursive Totals (user>0 and depth:0)

hide
Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 12 0.530 0.780 37 409 0 0 10
Execute: 13 0.010 0.002 0 0 0 0 1
Fetch: 40 0.620 0.654 4 1191 0 293 0
Total: 65 1.160 1.436 41 1600 0 293 11

Non-idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
db file scattered read: 1 0.031 0.031
SQL*Net more data to client: 2 0.001 0.003
SQL*Net break/reset to client: 2 0.001 0.001
Total non-idle: 5 0.031 0.035

Idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
SQL*Net message from client: 42 77.309 203.999
SQL*Net message to client: 42 0.000 0.000
Total idle: 84 77.309 203.999

User Recursive Totals (user>0 and depth>0)

hide
Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 20 0.070 0.064 0 0 0 0 10
Execute: 20 0.010 0.004 0 0 0 0 0
Fetch: 20 0.040 0.069 21 114 0 20 0
Total: 60 0.120 0.137 21 114 0 20 10

Non-idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
db file scattered read: 3 0.010 0.014
db file sequential read: 6 0.000 0.002
Total non-idle: 9 0.010 0.016

Internal Totals (user:0)

hide
Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 19 0.060 0.060 1 33 0 0 13
Execute: 77 0.230 0.307 4 77 0 0 13
Fetch: 125 0.020 0.164 9 271 0 186 0
Total: 221 0.310 0.531 14 381 0 186 26

Non-idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
db file sequential read: 10 0.027 0.147
Total non-idle: 10 0.027 0.147

Top 20 SQL Statements (according to CPU, Elapsed and Wait Times)

#CPU
Time
(secs)
Elapsed
Time
(secs)
Non-idle
Wait
(secs)
Idle
Wait
(secs)
ExecutionsLogical
Reads
(blocks)
Physical
Reads
(blocks)
Rows
Returned
Parsed
by
Recursive
Depth
ErrorComand
Type
SQL
Ranking
1 0.200 0.353 0.034 27.269 1 267 12 31 131 0 select 1
2 0.340 0.435 0.000 19.804 3 237 23 3 131 0 select 2
3 0.080 0.077 0.000 10.095 1 266 0 124 131 0 select 3
4 0.070 0.075 0.000 9.686 1 266 0 124 131 0 select 4
5 0.300 0.293 0.000 21.480 1 230 0 1 131 0 select 5
6 0.060 0.078 0.000 6.336 1 160 6 4 131 0 select 6
7 0.000 0.000 0.001 77.309 0 0 0 0 131 0 942 select 7
8 0.010 0.022 0.000 6.453 1 107 0 1 131 0 select 8
9 0.070 0.072 0.000 11.079 1 44 0 1 131 0 select 9
10 0.030 0.050 0.020 0.000 21 60 1 18 0 2 select 10
11 0.040 0.066 0.014 0.000 6 53 3 8 0 1 select 11
12 0.020 0.031 0.000 0.000 4 44 1 0 0 1 select 12
13 0.050 0.045 0.010 0.000 4 43 1 6 0 1 select 13
14 0.000 0.073 0.028 0.000 8 40 4 8 0 1 select 14
15 0.020 0.010 0.000 0.000 6 36 0 12 0 1 select 15
16 0.070 0.099 0.000 0.000 6 33 1 0 0 1 select 16
17 0.020 0.026 0.000 5.194 1 23 0 4 131 0 select 17
18 0.010 0.009 0.002 0.000 4 28 5 4 131 1 select 18
19 0.020 0.093 0.054 0.000 7 21 3 114 0 2 select 19
20 0.020 0.020 0.000 0.000 3 21 0 3 0 1 select 20

List of 35 Unique SQL Statements

hide
#CPU
Time
(secs)
Elapsed
Time
(secs)
Non-idle
Wait
(secs)
Idle
Wait
(secs)
ExecutionsLogical
Reads
(blocks)
Physical
Reads
(blocks)
Rows
Returned
Parsed
by
Recursive
Depth
ErrorComand
Type
SQL
Ranking
1 0.010 0.001 0.000 9.294 1 0 0 0 131 0 alter session 21
2 0.070 0.099 0.000 0.000 6 33 1 0 0 1 select 16
3 0.030 0.050 0.020 0.000 21 60 1 18 0 2 select 10
4 0.020 0.093 0.054 0.000 7 21 3 114 0 2 select 19
5 0.040 0.066 0.014 0.000 6 53 3 8 0 1 select 11
6 0.000 0.073 0.028 0.000 8 40 4 8 0 1 select 14
7 0.200 0.353 0.034 27.269 1 267 12 31 131 0 select 1
8 0.020 0.020 0.000 0.000 3 21 0 3 0 1 select 20
9 0.050 0.045 0.010 0.000 4 43 1 6 0 1 select 13
10 0.020 0.010 0.000 0.000 6 36 0 12 0 1 select 15
11 0.010 0.014 0.021 0.000 4 12 0 14 0 1 select 26
12 0.010 0.007 0.000 0.000 3 9 0 3 0 1 select 28
13 0.020 0.031 0.000 0.000 4 44 1 0 0 1 select 12
14 0.010 0.013 0.000 0.000 4 8 0 0 0 1 select 29
15 0.010 0.017 0.000 0.000 2 6 1 2 131 1 select 32
16 0.010 0.009 0.000 0.000 2 2 1 2 131 1 select 33
17 0.020 0.031 0.010 0.000 2 14 5 2 131 1 select 22
18 0.010 0.022 0.002 0.000 2 14 5 2 131 1 select 23
19 0.030 0.021 0.002 0.000 2 8 4 2 131 1 select 27
20 0.340 0.435 0.000 19.804 3 237 23 3 131 0 select 2
21 0.070 0.072 0.000 11.079 1 44 0 1 131 0 select 9
22 0.010 0.009 0.002 0.000 4 28 5 4 131 1 select 18
23 0.000 0.006 0.000 0.000 2 14 0 2 131 1 select 25
24 0.010 0.004 0.000 0.000 1 7 0 1 131 1 select 31
25 0.010 0.008 0.000 0.000 1 7 0 1 131 1 select 30
26 0.060 0.078 0.000 6.336 1 160 6 4 131 0 select 6
27 0.010 0.010 0.000 0.000 2 14 0 2 131 1 select 24
28 0.020 0.026 0.000 5.194 1 23 0 4 131 0 select 17
29 0.080 0.077 0.000 10.095 1 266 0 124 131 0 select 3
30 0.010 0.010 0.000 0.000 1 1 0 0 0 1 select 34
31 0.000 0.000 0.001 77.309 0 0 0 0 131 0 942 select 7
32 0.070 0.075 0.000 9.686 1 266 0 124 131 0 select 4
33 0.010 0.022 0.000 6.453 1 107 0 1 131 0 select 8
34 0.300 0.293 0.000 21.480 1 230 0 1 131 0 select 5
35 0.000 0.004 0.000 0.000 1 0 0 0 131 0 alter session 35

Gaps of no Trace Activity (4)

hide
LineEnd TimeGap
Duration
(secs)
Cursor
Before
Cursor
After
386: 24-JAN-06 08:53:42 21.235 1 20
940: 24-JAN-06 08:54:00 11.105 9 18
1195: 24-JAN-06 08:55:39 77.326 7 4
1333: 24-JAN-06 08:56:30 21.483 5 35

Non-Default Initialization Parameters

hide
ParameterValue
background_dump_dest: C:\ORACLE\PRODUCT\10.1.0\ADMIN\ORAXP10G\BDUMP
compatible: 10.1.0.2.0
control_files: C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORAXP10G\CONTROL01.CTL
control_files: C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORAXP10G\CONTROL02.CTL
control_files: C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORAXP10G\CONTROL03.CTL
core_dump_dest: C:\ORACLE\PRODUCT\10.1.0\ADMIN\ORAXP10G\CDUMP
db_block_size: 8192
db_cache_size: 25165824
db_domain:
db_file_multiblock_read_count: 16
db_name: oraxp10g
db_recovery_file_dest: C:\oracle\product\10.1.0\flash_recovery_area
db_recovery_file_dest_size: 2147483648
dispatchers: (PROTOCOL=TCP) (SERVICE=oraxp10gXDB)
java_pool_size: 50331648
job_queue_processes: 20
large_pool_size: 8388608
open_cursors: 300
pga_aggregate_target: 25165824
processes: 150
remote_login_passwordfile: EXCLUSIVE
shared_pool_size: 83886080
sort_area_size: 65536
undo_management: AUTO
undo_tablespace: UNDOTBS1
user_dump_dest: C:\ORACLE\PRODUCT\10.1.0\ADMIN\ORAXP10G\UDUMP

SQL Statements Details

hash:3164292706 length:69 depth:0(non-recursive) user:131(user) line:30 rank:21 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 0 0.000 0.000 0 0 0 0 0
Execute: 1 0.010 0.001 0 0 0 0 1
Fetch: 0 0.000 0.000 0 0 0 0 0
Total: 1 0.010 0.001 0 0 0 0 1

Idle Wait Events

Idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
SQL*Net message from client: 1 9.294 9.294
SQL*Net message to client: 1 0.000 0.000
Total idle: 2 9.294 9.294

hash:1471956217 length:69 depth:1(recursive) user:0(internal) line:37 rank:16 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 2 0.000 0.002 0 0 0 0 1
Execute: 6 0.070 0.096 1 21 0 0 1
Fetch: 6 0.000 0.001 0 12 0 0 0
Total: 14 0.070 0.099 1 33 0 0 2

Execution/Fetch Performance: First and Top 10 (according to CPU, Elapsed and Wait Times)

Exec
Line
Call
Count
CPU
Time
(secs)
Elapsed
Time
(secs)
Non-idle
Wait
(secs)
Idle
Wait
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Exec
Ranking
134: 3 0.060 0.096 0.000 0.000 1 23 0 0 2 1
285: 2 0.000 0.000 0.000 0.000 0 2 0 0 0 3
313: 2 0.000 0.000 0.000 0.000 0 2 0 0 0 4
680: 3 0.010 0.001 0.000 0.000 0 2 0 0 0 2
709: 2 0.000 0.000 0.000 0.000 0 2 0 0 0 5
733: 2 0.000 0.000 0.000 0.000 0 2 0 0 0 6

Bind Variables Values: First and Top 10 Executions (according to CPU, Elapsed and Wait Times)

Exec
Line
Exec
Ranking
1 Values
(according to position within SQL)
134: 1 60756
285: 3 60753
313: 4 60754
680: 2 60741
709: 5 60742
733: 6 60740

hash:864012087 length:210 depth:2(recursive) user:0(internal) line:42 rank:10 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 2 0.020 0.002 0 0 0 0 1
Execute: 21 0.010 0.024 0 0 0 0 1
Fetch: 21 0.000 0.024 1 60 0 18 0
Total: 44 0.030 0.050 1 60 0 18 2

Non-idle Wait Events

Non-idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
db file sequential read: 1 0.020 0.020
Total non-idle: 1 0.020 0.020

Execution/Fetch Performance: First and Top 10 (according to CPU, Elapsed and Wait Times)

Exec
Line
Call
Count
CPU
Time
(secs)
Elapsed
Time
(secs)
Non-idle
Wait
(secs)
Idle
Wait
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Exec
Ranking
53: 3 0.010 0.008 0.000 0.000 0 3 0 1 2 2
62: 2 0.000 0.003 0.000 0.000 0 3 0 1 0 5
157: 2 0.000 0.003 0.000 0.000 0 3 0 1 0 6
166: 2 0.010 0.003 0.000 0.000 0 3 0 1 0 3
175: 2 0.000 0.002 0.000 0.000 0 3 0 1 0 7
224: 2 0.000 0.001 0.000 0.000 0 3 0 1 0 8
246: 2 0.000 0.021 0.020 0.000 1 3 0 1 0 1
256: 2 0.000 0.001 0.000 0.000 0 3 0 1 0 9
413: 3 0.010 0.001 0.000 0.000 0 3 0 1 0 4
452: 2 0.000 0.001 0.000 0.000 0 3 0 1 0 10

Bind Variables Values: First and Top 10 Executions (according to CPU, Elapsed and Wait Times)

Exec
Line
Exec
Ranking
2 Values
(according to position within SQL)
53: 2 31 15
62: 5 31 6
157: 6 31 3
166: 3 31 9
175: 7 31 8
224: 8 31 11
246: 1 32 6
256: 9 32 4
413: 4 675 4
452: 10 677 4

hash:3150898423 length:121 depth:2(recursive) user:0(internal) line:65 rank:19 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 1 0.000 0.002 0 0 0 0 1
Execute: 7 0.020 0.023 0 0 0 0 1
Fetch: 7 0.000 0.068 3 21 0 114 0
Total: 15 0.020 0.093 3 21 0 114 2

Non-idle Wait Events

Non-idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
db file sequential read: 3 0.027 0.054
Total non-idle: 3 0.027 0.054

Execution/Fetch Performance: First and Top 10 (according to CPU, Elapsed and Wait Times)

Exec
Line
Call
Count
CPU
Time
(secs)
Elapsed
Time
(secs)
Non-idle
Wait
(secs)
Idle
Wait
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Exec
Ranking
79: 3 0.000 0.045 0.027 0.000 1 3 0 20 2 1
92: 2 0.000 0.004 0.000 0.000 0 3 0 20 0 5
104: 2 0.010 0.004 0.000 0.000 0 3 0 20 0 4
116: 2 0.000 0.004 0.000 0.000 0 3 0 20 0 6
128: 2 0.000 0.004 0.000 0.000 0 3 0 14 0 7
214: 2 0.010 0.020 0.017 0.000 1 3 0 9 0 2
268: 2 0.000 0.012 0.011 0.000 1 3 0 11 0 3

Bind Variables Values: First and Top 10 Executions (according to CPU, Elapsed and Wait Times)

Exec
Line
Exec
Ranking
3 Values
(according to position within SQL)
79: 1 31 6 0
92: 5 31 6 1
104: 4 31 6 2
116: 6 31 6 3
128: 7 31 6 4
214: 2 31 13 0
268: 3 32 4 0

hash:2107929772 length:146 depth:1(recursive) user:0(internal) line:137 rank:11 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 2 0.000 0.002 0 0 0 0 1
Execute: 6 0.040 0.048 1 26 0 0 1
Fetch: 14 0.000 0.016 2 27 0 8 0
Total: 22 0.040 0.066 3 53 0 8 2

Non-idle Wait Events

Non-idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
db file sequential read: 2 0.007 0.014
Total non-idle: 2 0.007 0.014

Execution/Fetch Performance: First and Top 10 (according to CPU, Elapsed and Wait Times)

Exec
Line
Call
Count
CPU
Time
(secs)
Elapsed
Time
(secs)
Non-idle
Wait
(secs)
Idle
Wait
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Exec
Ranking
230: 4 0.040 0.063 0.014 0.000 3 29 0 1 2 1
291: 4 0.000 0.001 0.000 0.000 0 6 0 2 0 3
319: 6 0.000 0.001 0.000 0.000 0 10 0 4 0 2
691: 3 0.000 0.001 0.000 0.000 0 2 0 0 0 5
715: 2 0.000 0.000 0.000 0.000 0 2 0 0 0 6
739: 3 0.000 0.001 0.000 0.000 0 4 0 1 0 4

Bind Variables Values: First and Top 10 Executions (according to CPU, Elapsed and Wait Times)

Exec
Line
Exec
Ranking
1 Values
(according to position within SQL)
230: 1 60756
291: 3 60753
319: 2 60754
691: 5 60741
715: 6 60742
739: 4 60740

hash:3849548163 length:70 depth:1(recursive) user:0(internal) line:235 rank:14 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 2 0.000 0.002 0 0 0 0 1
Execute: 8 0.000 0.041 2 9 0 0 1
Fetch: 16 0.000 0.030 2 31 0 8 0
Total: 26 0.000 0.073 4 40 0 8 2

Non-idle Wait Events

Non-idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
db file sequential read: 2 0.015 0.028
Total non-idle: 2 0.015 0.028

Execution/Fetch Performance: First and Top 10 (according to CPU, Elapsed and Wait Times)

Exec
Line
Call
Count
CPU
Time
(secs)
Elapsed
Time
(secs)
Non-idle
Wait
(secs)
Idle
Wait
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Exec
Ranking
275: 4 0.000 0.070 0.028 0.000 4 12 0 1 2 1
297: 3 0.000 0.000 0.000 0.000 0 4 0 1 0 3
305: 3 0.000 0.000 0.000 0.000 0 4 0 1 0 4
325: 3 0.000 0.000 0.000 0.000 0 4 0 1 0 5
333: 3 0.000 0.000 0.000 0.000 0 4 0 1 0 6
341: 3 0.000 0.000 0.000 0.000 0 4 0 1 0 7
349: 3 0.000 0.000 0.000 0.000 0 4 0 1 0 8
755: 4 0.000 0.001 0.000 0.000 0 4 0 1 0 2

Bind Variables Values: First and Top 10 Executions (according to CPU, Elapsed and Wait Times)

Exec
Line
Exec
Ranking
1 Values
(according to position within SQL)
275: 1 20549
297: 3 20543
305: 4 20544
325: 5 20545
333: 6 20546
341: 7 20547
349: 8 20548
755: 2 20542

hash:831464412 length:670 depth:0(non-recursive) user:131(user) line:354 rank:1 next top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 1 0.140 0.262 8 108 0 0 1
Execute: 1 0.000 0.000 0 0 0 0 0
Fetch: 4 0.060 0.091 4 159 0 31 0
Total: 6 0.200 0.353 12 267 0 31 1

Non-idle Wait Events

Non-idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
db file scattered read: 1 0.031 0.031
SQL*Net more data to client: 2 0.001 0.003
Total non-idle: 3 0.031 0.034

Idle Wait Events

Idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
SQL*Net message from client: 4 21.228 27.269
SQL*Net message to client: 4 0.000 0.000
Total idle: 8 21.228 27.269

Row Source Plan (Real Execution Path)

Rows
Returned
Exec
Order
Row Source OperationObject
Id
Logical
Reads
(blocks)
Physical
Reads
(blocks)
Physical
Writes
(blocks)
Elapsed
Time
(secs)
31 8 SORT ORDER BY 0 159 4 0 0.000
31 7  HASH JOIN 0 159 4 0 0.000
31 5   HASH JOIN 0 52 0 0 0.000
30 3    INLIST ITERATOR 0 21 0 0 0.000
30 2     TABLE ACCESS BY INDEX ROWID TEAMS 60754 21 0 0 0.000
116 1      INDEX RANGE SCAN TEAMS_PK 60755 8 0 0 0.000
126 4    TABLE ACCESS FULL MANAGERS 60753 31 0 0 0.000
15350 6   TABLE ACCESS FULL MASTER 60756 107 4 0 0.000

Explain Plan (Generated by Trace Analyzer)

IdExec
Order
Explain Plan Operation
0: 9 SELECT STATEMENT
1: 8  SORT ORDER BY
2: 7   HASH JOIN
3: 5    HASH JOIN
4: 3     INLIST ITERATOR
5: 2      TABLE ACCESS BY INDEX ROWID USER0.TEAMS
6: 1       INDEX RANGE SCAN USER0.TEAMS_PK
7: 4     TABLE ACCESS FULL USER0.MANAGERS
8: 6    TABLE ACCESS FULL USER0.MASTER

IdObject
Type
Parent
Id
PosObj
Inst
Search
Cols
Estim
Card
Estim
Bytes
CostCPU
Cost
IO
Cost
TimeTemp
Space
0: 41 1 192 41 27459765 38
1: 0 1 1 192 41 27459765 38
2: 1 1 1 192 40 16601361 38
3: 2 1 1 168 15 6908001 14
4: 3 1
5: TABLE 4 1 1 36 5364 6 126447 6
6: INDEX (UNIQUE) 5 1 1 72 2 28843 2
7: TABLE 3 2 2 90 1710 8 1337952 8
8: TABLE 2 2 3 15350 368400 24 2729008 24

IdColumn NameColumn Value
2: Access Predicates "MG"."LAHMANID"="MA"."LAHMANID"
3: Access Predicates "TE"."DATE_YYYY"="MG"."DATE_YYYY" AND "TE"."TEAM_ID"="MG"."TEAM_ID" AND "TE"."LEAGUE_ID"="MG"."LEAGUE_ID"
5: Filter Predicates "ATTENDENCE_QTY">=2000000 AND "GAMES_BEHIND_QTY">=10
6: Access Predicates "TE"."DATE_YYYY"=1996 OR "TE"."DATE_YYYY"=1997 OR "TE"."DATE_YYYY"=1998 OR "TE"."DATE_YYYY"=1999
7: Filter Predicates "MG"."DATE_YYYY"=1996 OR "MG"."DATE_YYYY"=1997 OR "MG"."DATE_YYYY"=1998 OR "MG"."DATE_YYYY"=1999

Tables Referenced by Explain Plan

Owner.Table NameCurrent
COUNT(*)
Num*
Rows
Sample*
Size
Last*
Analyzed
Avg*
Row
Len
Chain*
Cnt
Blocks* Empty*
Blocks
Avg*
Space
Global*
Stats
USER0.MANAGERS28952895289524-JAN-06 08:51:31490284 2842NO
USER0.MASTER1535015350432424-JAN-06 08:51:314301039 1331NO
USER0.TEAMS23272327232724-JAN-06 08:51:322560888 1253NO
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Owner.Table NameTemporaryPartitionedLoggingDegreeCacheIOT
Type
Ini
Trans
Max
Trans
Freelist
Groups
Freelists
USER0.MANAGERSNNOYES 1 N1255
USER0.MASTERNNOYES 1 N1255
USER0.TEAMSNNOYES 1 N1255

Indexes on Tables Referenced by Explain Plan

Table Owner.Table NameIndex Owner.Index NameIndex
Type
UniquenessIndexed Columns
USER0.TEAMSUSER0.TEAMS_PKNORMALUNIQUEDATE_YYYY LEAGUE_ID TEAM_ID

Table Owner.Table NameIndex Owner.Index NameNum*
Rows
Sample*
Size
Last*
Analyzed
Distinct*
Keys
Blevel*Leaf*
Blocks
Avg*
Leaf
Blocks
per
Key
Avg*
Data
Blocks
per
Key
Clustering*
Factor
Global*
Stats
USER0.TEAMSUSER0.TEAMS_PK2327232724-JAN-06 08:51:322327111 11105NO
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Table Owner.Table NameIndex Owner.Index NameTemporaryPartitionedLoggingDegreeIni
Trans
Max
Trans
Freelist
Groups
FreelistsStatusDomidx
Status
Funcidx
Status
USER0.TEAMSUSER0.TEAMS_PKNNOYES12255VALID

hash:2584065658 length:493 depth:1(recursive) user:0(internal) line:397 rank:20 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 1 0.010 0.004 0 0 0 0 1
Execute: 3 0.010 0.015 0 9 0 0 1
Fetch: 3 0.000 0.001 0 12 0 3 0
Total: 7 0.020 0.020 0 21 0 3 2

Execution/Fetch Performance: First and Top 10 (according to CPU, Elapsed and Wait Times)

Exec
Line
Call
Count
CPU
Time
(secs)
Elapsed
Time
(secs)
Non-idle
Wait
(secs)
Idle
Wait
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Exec
Ranking
437: 3 0.020 0.018 0.000 0.000 0 13 0 1 2 1
520: 2 0.000 0.001 0.000 0.000 0 4 0 1 0 2
556: 2 0.000 0.001 0.000 0.000 0 4 0 1 0 3

Bind Variables Values: First and Top 10 Executions (according to CPU, Elapsed and Wait Times)

Exec
Line
Exec
Ranking
1 Values
(according to position within SQL)
437: 1 60741
520: 2 60742
556: 3 60740

Row Source Plan (Real Execution Path)

Rows
Returned
Exec
Order
Row Source OperationObject
Id
Logical
Reads
(blocks)
Physical
Reads
(blocks)
Physical
Writes
(blocks)
Elapsed
Time
(secs)
3 5 NESTED LOOPS OUTER 0 12 0 0 0.000
3 2  TABLE ACCESS CLUSTER OBJ#(4) 4 9 0 0 0.000
3 1   INDEX UNIQUE SCAN OBJ#(3) 3 6 0 0 0.000
0 4  TABLE ACCESS BY INDEX ROWID OBJ#(675) 675 3 0 0 0.000
0 3   INDEX RANGE SCAN OBJ#(676) 676 3 0 0 0.000

hash:2885603983 length:773 depth:1(recursive) user:0(internal) line:441 rank:13 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 1 0.010 0.005 0 0 0 0 1
Execute: 4 0.020 0.022 0 9 0 0 1
Fetch: 10 0.020 0.018 1 34 0 6 0
Total: 15 0.050 0.045 1 43 0 6 2

Non-idle Wait Events

Non-idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
db file sequential read: 1 0.010 0.010
Total non-idle: 1 0.010 0.010

Execution/Fetch Performance: First and Top 10 (according to CPU, Elapsed and Wait Times)

Exec
Line
Call
Count
CPU
Time
(secs)
Elapsed
Time
(secs)
Non-idle
Wait
(secs)
Idle
Wait
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Exec
Ranking
477: 4 0.030 0.027 0.000 0.000 0 16 0 1 2 1
528: 3 0.000 0.002 0.000 0.000 0 7 0 1 0 4
564: 4 0.010 0.003 0.000 0.000 0 10 0 2 0 3
601: 4 0.010 0.013 0.010 0.000 1 10 0 2 0 2

Bind Variables Values: First and Top 10 Executions (according to CPU, Elapsed and Wait Times)

Exec
Line
Exec
Ranking
2 Values
(according to position within SQL)
477: 1 60741 No separate bind buffer exists
528: 4 60742 No separate bind buffer exists
564: 3 60740 No separate bind buffer exists
601: 2 90 No separate bind buffer exists

hash:2280069326 length:67 depth:1(recursive) user:0(internal) line:480 rank:15 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 1 0.000 0.002 0 0 0 0 1
Execute: 6 0.020 0.006 0 0 0 0 1
Fetch: 18 0.000 0.002 0 36 0 12 0
Total: 25 0.020 0.010 0 36 0 12 2

Execution/Fetch Performance: First and Top 10 (according to CPU, Elapsed and Wait Times)

Exec
Line
Call
Count
CPU
Time
(secs)
Elapsed
Time
(secs)
Non-idle
Wait
(secs)
Idle
Wait
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Exec
Ranking
488: 4 0.010 0.007 0.000 0.000 0 4 0 1 2 3
534: 3 0.010 0.001 0.000 0.000 0 4 0 1 0 4
570: 3 0.000 0.001 0.000 0.000 0 4 0 1 0 5
578: 3 0.000 0.000 0.000 0.000 0 4 0 1 0 6
608: 5 0.000 0.001 0.000 0.000 0 8 0 3 0 2
618: 7 0.000 0.001 0.000 0.000 0 12 0 5 0 1

Bind Variables Values: First and Top 10 Executions (according to CPU, Elapsed and Wait Times)

Exec
Line
Exec
Ranking
1 Values
(according to position within SQL)
488: 3 60743
534: 4 60746
570: 5 60745
578: 6 60744
608: 2 108
618: 1 107

hash:2512561537 length:348 depth:1(recursive) user:0(internal) line:493 rank:26 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 1 0.000 0.003 0 0 0 0 1
Execute: 4 0.010 0.009 0 0 0 0 1
Fetch: 18 0.000 0.002 0 12 0 14 0
Total: 23 0.010 0.014 0 12 0 14 2

Non-idle Wait Events

Non-idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
db file sequential read: 1 0.021 0.021
Total non-idle: 1 0.021 0.021

Execution/Fetch Performance: First and Top 10 (according to CPU, Elapsed and Wait Times)

Exec
Line
Call
Count
CPU
Time
(secs)
Elapsed
Time
(secs)
Non-idle
Wait
(secs)
Idle
Wait
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Exec
Ranking
501: 5 0.010 0.011 0.000 0.000 0 3 0 2 2 1
542: 4 0.000 0.001 0.000 0.000 0 3 0 2 0 3
586: 4 0.000 0.001 0.000 0.000 0 3 0 2 0 4
630: 10 0.000 0.001 0.021 0.000 0 3 0 8 0 2

Bind Variables Values: First and Top 10 Executions (according to CPU, Elapsed and Wait Times)

Exec
Line
Exec
Ranking
1 Values
(according to position within SQL)
501: 1 60741
542: 3 60742
586: 4 60740
630: 2 90

hash:854877822 length:116 depth:1(recursive) user:0(internal) line:506 rank:28 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 1 0.000 0.002 0 0 0 0 1
Execute: 3 0.010 0.005 0 0 0 0 1
Fetch: 3 0.000 0.000 0 9 0 3 0
Total: 7 0.010 0.007 0 9 0 3 2

Execution/Fetch Performance: First and Top 10 (according to CPU, Elapsed and Wait Times)

Exec
Line
Call
Count
CPU
Time
(secs)
Elapsed
Time
(secs)
Non-idle
Wait
(secs)
Idle
Wait
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Exec
Ranking
514: 3 0.010 0.007 0.000 0.000 0 3 0 1 2 1
550: 2 0.000 0.000 0.000 0.000 0 3 0 1 0 2
594: 2 0.000 0.000 0.000 0.000 0 3 0 1 0 3

Bind Variables Values: First and Top 10 Executions (according to CPU, Elapsed and Wait Times)

Exec
Line
Exec
Ranking
1 Values
(according to position within SQL)
514: 1 60743
550: 2 60746
594: 3 60745

hash:1173719687 length:169 depth:1(recursive) user:0(internal) line:641 rank:12 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 2 0.010 0.022 1 33 0 0 1
Execute: 4 0.010 0.008 0 3 0 0 1
Fetch: 4 0.000 0.001 0 8 0 0 0
Total: 10 0.020 0.031 1 44 0 0 2

Execution/Fetch Performance: First and Top 10 (according to CPU, Elapsed and Wait Times)

Exec
Line
Call
Count
CPU
Time
(secs)
Elapsed
Time
(secs)
Non-idle
Wait
(secs)
Idle
Wait
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Exec
Ranking
658: 3 0.020 0.029 0.000 0.000 1 38 0 0 2 1
697: 2 0.000 0.000 0.000 0.000 0 2 0 0 0 3
721: 2 0.000 0.000 0.000 0.000 0 2 0 0 0 4
1021: 3 0.000 0.001 0.000 0.000 0 2 0 0 0 2

Bind Variables Values: First and Top 10 Executions (according to CPU, Elapsed and Wait Times)

Exec
Line
Exec
Ranking
1 Values
(according to position within SQL)
658: 1 60741
697: 3 60742
721: 4 60740
1021: 2 60732

hash:4139184264 length:151 depth:1(recursive) user:0(internal) line:661 rank:29 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 2 0.010 0.006 0 0 0 0 1
Execute: 4 0.000 0.006 0 0 0 0 1
Fetch: 4 0.000 0.001 0 8 0 0 0
Total: 10 0.010 0.013 0 8 0 0 2

Execution/Fetch Performance: First and Top 10 (according to CPU, Elapsed and Wait Times)

Exec
Line
Call
Count
CPU
Time
(secs)
Elapsed
Time
(secs)
Non-idle
Wait
(secs)
Idle
Wait
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Exec
Ranking
669: 3 0.010 0.011 0.000 0.000 0 2 0 0 2 1
703: 2 0.000 0.000 0.000 0.000 0 2 0 0 0 3
727: 2 0.000 0.000 0.000 0.000 0 2 0 0 0 4
1032: 3 0.000 0.001 0.000 0.000 0 2 0 0 0 2

Bind Variables Values: First and Top 10 Executions (according to CPU, Elapsed and Wait Times)

Exec
Line
Exec
Ranking
1 Values
(according to position within SQL)
669: 1 60741
703: 3 60742
727: 4 60740
1032: 2 60732

hash:4176758313 length:413 depth:1(recursive) user:131(user) line:770 rank:32 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 2 0.010 0.005 0 0 0 0 1
Execute: 2 0.000 0.000 0 0 0 0 0
Fetch: 2 0.000 0.012 1 6 0 2 0
Total: 6 0.010 0.017 1 6 0 2 1

Non-idle Wait Events

Non-idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
db file sequential read: 1 0.000 0.000
Total non-idle: 1 0.000 0.000

Execution/Fetch Performance: First and Top 10 (according to CPU, Elapsed and Wait Times)

Exec
Line
Call
Count
CPU
Time
(secs)
Elapsed
Time
(secs)
Non-idle
Wait
(secs)
Idle
Wait
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Exec
Ranking
775: 3 0.010 0.016 0.000 0.000 1 3 0 1 1 1
896: 3 0.000 0.001 0.000 0.000 0 3 0 1 0 2

Row Source Plan (Real Execution Path)

Rows
Returned
Exec
Order
Row Source OperationObject
Id
Logical
Reads
(blocks)
Physical
Reads
(blocks)
Physical
Writes
(blocks)
Elapsed
Time
(secs)
2 2 SORT GROUP BY 0 6 1 0 0.000
200 1  TABLE ACCESS FULL B 60740 6 1 0 0.000

hash:1419693819 length:320 depth:1(recursive) user:131(user) line:781 rank:33 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 2 0.010 0.006 0 0 0 0 1
Execute: 2 0.000 0.001 0 0 0 0 0
Fetch: 2 0.000 0.002 1 2 0 2 0
Total: 6 0.010 0.009 1 2 0 2 1

Non-idle Wait Events

Non-idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
db file sequential read: 1 0.000 0.000
Total non-idle: 1 0.000 0.000

Execution/Fetch Performance: First and Top 10 (according to CPU, Elapsed and Wait Times)

Exec
Line
Call
Count
CPU
Time
(secs)
Elapsed
Time
(secs)
Non-idle
Wait
(secs)
Idle
Wait
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Exec
Ranking
786: 3 0.010 0.007 0.000 0.000 1 1 0 1 1 1
906: 3 0.000 0.001 0.000 0.000 0 1 0 1 0 2

Row Source Plan (Real Execution Path)

Rows
Returned
Exec
Order
Row Source OperationObject
Id
Logical
Reads
(blocks)
Physical
Reads
(blocks)
Physical
Writes
(blocks)
Elapsed
Time
(secs)
2 4 SORT AGGREGATE 0 2 1 0 0.000
200 3  VIEW 0 2 1 0 0.000
200 2   COUNT STOPKEY 0 2 1 0 0.000
200 1    INDEX RANGE SCAN B_STATUS_IDX 60744 2 1 0 0.000

hash:2318564927 length:411 depth:1(recursive) user:131(user) line:804 rank:22 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 2 0.010 0.007 0 0 0 0 1
Execute: 2 0.000 0.000 0 0 0 0 0
Fetch: 2 0.010 0.024 5 14 0 2 0
Total: 6 0.020 0.031 5 14 0 2 1

Non-idle Wait Events

Non-idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
db file scattered read: 1 0.010 0.010
Total non-idle: 1 0.010 0.010

Execution/Fetch Performance: First and Top 10 (according to CPU, Elapsed and Wait Times)

Exec
Line
Call
Count
CPU
Time
(secs)
Elapsed
Time
(secs)
Non-idle
Wait
(secs)
Idle
Wait
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Exec
Ranking
809: 3 0.020 0.024 0.010 0.000 5 7 0 1 1 1
918: 3 0.000 0.007 0.000 0.000 0 7 0 1 0 2

Row Source Plan (Real Execution Path)

Rows
Returned
Exec
Order
Row Source OperationObject
Id
Logical
Reads
(blocks)
Physical
Reads
(blocks)
Physical
Writes
(blocks)
Elapsed
Time
(secs)
2 2 SORT GROUP BY 0 14 5 0 0.000
2000 1  TABLE ACCESS FULL C 60742 14 5 0 0.000

hash:947255996 length:413 depth:1(recursive) user:131(user) line:816 rank:23 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 2 0.010 0.008 0 0 0 0 1
Execute: 2 0.000 0.001 0 0 0 0 0
Fetch: 2 0.000 0.013 5 14 0 2 0
Total: 6 0.010 0.022 5 14 0 2 1

Non-idle Wait Events

Non-idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
db file scattered read: 1 0.002 0.002
Total non-idle: 1 0.002 0.002

Execution/Fetch Performance: First and Top 10 (according to CPU, Elapsed and Wait Times)

Exec
Line
Call
Count
CPU
Time
(secs)
Elapsed
Time
(secs)
Non-idle
Wait
(secs)
Idle
Wait
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Exec
Ranking
821: 3 0.010 0.015 0.002 0.000 5 7 0 1 1 1
874: 3 0.000 0.006 0.000 0.000 0 7 0 1 0 2

Row Source Plan (Real Execution Path)

Rows
Returned
Exec
Order
Row Source OperationObject
Id
Logical
Reads
(blocks)
Physical
Reads
(blocks)
Physical
Writes
(blocks)
Elapsed
Time
(secs)
2 2 SORT GROUP BY 0 14 5 0 0.000
2000 1  TABLE ACCESS FULL A 60741 14 5 0 0.000

hash:4122060127 length:320 depth:1(recursive) user:131(user) line:827 rank:27 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 2 0.010 0.008 0 0 0 0 1
Execute: 2 0.000 0.001 0 0 0 0 0
Fetch: 2 0.020 0.012 4 8 0 2 0
Total: 6 0.030 0.021 4 8 0 2 1

Non-idle Wait Events

Non-idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
db file sequential read: 4 0.000 0.002
Total non-idle: 4 0.000 0.002

Execution/Fetch Performance: First and Top 10 (according to CPU, Elapsed and Wait Times)

Exec
Line
Call
Count
CPU
Time
(secs)
Elapsed
Time
(secs)
Non-idle
Wait
(secs)
Idle
Wait
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Exec
Ranking
832: 3 0.020 0.014 0.002 0.000 4 4 0 1 1 1
884: 3 0.010 0.005 0.000 0.000 0 4 0 1 0 2

Row Source Plan (Real Execution Path)

Rows
Returned
Exec
Order
Row Source OperationObject
Id
Logical
Reads
(blocks)
Physical
Reads
(blocks)
Physical
Writes
(blocks)
Elapsed
Time
(secs)
2 4 SORT AGGREGATE 0 8 4 0 0.000
2000 3  VIEW 0 8 4 0 0.000
2000 2   COUNT STOPKEY 0 8 4 0 0.000
2000 1    INDEX RANGE SCAN A_STATUS_IDX 60743 8 4 0 0.000

hash:3743162748 length:142 depth:0(non-recursive) user:131(user) line:843 rank:2 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 3 0.240 0.333 23 189 0 0 1
Execute: 3 0.000 0.001 0 0 0 0 0
Fetch: 6 0.100 0.101 0 48 0 3 0
Total: 12 0.340 0.435 23 237 0 3 1

Idle Wait Events

Idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
SQL*Net message from client: 6 7.416 19.804
SQL*Net message to client: 6 0.000 0.000
Total idle: 12 7.416 19.804

Execution/Fetch Performance: First and Top 10 (according to CPU, Elapsed and Wait Times)

Exec
Line
Call
Count
CPU
Time
(secs)
Elapsed
Time
(secs)
Non-idle
Wait
(secs)
Idle
Wait
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Exec
Ranking
854: 4 0.270 0.366 0.000 6.695 23 205 0 1 1 1
1277: 4 0.030 0.034 0.000 7.418 0 16 0 1 0 2
1303: 4 0.040 0.035 0.000 5.691 0 16 0 1 0 3

Row Source Plan (Real Execution Path)

Rows
Returned
Exec
Order
Row Source OperationObject
Id
Logical
Reads
(blocks)
Physical
Reads
(blocks)
Physical
Writes
(blocks)
Elapsed
Time
(secs)
3 7 SORT AGGREGATE 0 48 0 0 0.000
30000 6  HASH JOIN 0 48 0 0 0.000
3000 1   TABLE ACCESS FULL A 60741 21 0 0 0.000
3000 5   HASH JOIN 0 27 0 0 0.000
300 3    TABLE ACCESS BY INDEX ROWID B 60740 6 0 0 0.000
300 2     INDEX RANGE SCAN B_STATUS_IDX 60744 3 0 0 0.000
3000 4    TABLE ACCESS FULL C 60742 21 0 0 0.000

Explain Plan (Generated by Trace Analyzer)

IdExec
Order
Explain Plan Operation
0: 8 SELECT STATEMENT
1: 7  SORT AGGREGATE
2: 6   HASH JOIN
3: 1    TABLE ACCESS FULL USER0.A
4: 5    HASH JOIN
5: 3     TABLE ACCESS BY INDEX ROWID USER0.B
6: 2      INDEX RANGE SCAN USER0.B_STATUS_IDX
7: 4     TABLE ACCESS FULL USER0.C

IdObject
Type
Parent
Id
PosObj
Inst
Search
Cols
Estim
Card
Estim
Bytes
CostCPU
Cost
IO
Cost
TimeTemp
Space
0: 9 1 57 9 12228590 8
1: 0 1 1 57
2: 1 1 48930 2789010 9 12228590 8
3: TABLE 2 1 3 1000 19000 3 476972 3
4: 2 2 1000 38000 6 6072416 5
5: TABLE 4 1 1 100 1900 2 51243 2
6: INDEX 5 1 1 100 1 27121 1
7: TABLE 4 2 2 1000 19000 3 476972 3

IdColumn NameColumn Value
2: Access Predicates "A"."STATUS"="B"."STATUS" AND "A"."B_ID"="B"."ID"
3: Filter Predicates "A"."STATUS"='OPEN'
4: Access Predicates "B"."ID"="C"."B_ID"
6: Access Predicates "B"."STATUS"='OPEN'
7: Filter Predicates "C"."STATUS"='OPEN'

Tables Referenced by Explain Plan

Owner.Table NameCurrent
COUNT(*)
Num*
Rows
Sample*
Size
Last*
Analyzed
Avg*
Row
Len
Chain*
Cnt
Blocks* Empty*
Blocks
Avg*
Space
Global*
Stats
USER0.A1000NO
USER0.B100NO
USER0.C1000NO
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Owner.Table NameTemporaryPartitionedLoggingDegreeCacheIOT
Type
Ini
Trans
Max
Trans
Freelist
Groups
Freelists
USER0.ANNOYES 1 N1255
USER0.BNNOYES 1 N1255
USER0.CNNOYES 1 N1255

Indexes on Tables Referenced by Explain Plan

Table Owner.Table NameIndex Owner.Index NameIndex
Type
UniquenessIndexed Columns
USER0.AUSER0.A_STATUS_IDXNORMALNONUNIQUESTATUS
USER0.BUSER0.B_STATUS_IDXNORMALNONUNIQUESTATUS
USER0.BUSER0.PK_B_IDNORMALUNIQUEID
USER0.CUSER0.C_B_ID_IDXNORMALNONUNIQUEB_ID

Table Owner.Table NameIndex Owner.Index NameNum*
Rows
Sample*
Size
Last*
Analyzed
Distinct*
Keys
Blevel*Leaf*
Blocks
Avg*
Leaf
Blocks
per
Key
Avg*
Data
Blocks
per
Key
Clustering*
Factor
Global*
Stats
USER0.AUSER0.A_STATUS_IDX1000100024-JAN-06 08:32:181133 33NO
USER0.BUSER0.B_STATUS_IDX10010024-JAN-06 08:32:181011 11NO
USER0.BUSER0.PK_B_ID10010024-JAN-06 08:32:18100011 11NO
USER0.CUSER0.C_B_ID_IDX1000100024-JAN-06 08:32:18100121 3300NO
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Table Owner.Table NameIndex Owner.Index NameTemporaryPartitionedLoggingDegreeIni
Trans
Max
Trans
Freelist
Groups
FreelistsStatusDomidx
Status
Funcidx
Status
USER0.AUSER0.A_STATUS_IDXNNOYES12255VALID
USER0.BUSER0.B_STATUS_IDXNNOYES12255VALID
USER0.BUSER0.PK_B_IDNNOYES12255VALID
USER0.CUSER0.C_B_ID_IDXNNOYES12255VALID

hash:2234405673 length:179 depth:0(non-recursive) user:131(user) line:923 rank:9 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 1 0.040 0.039 0 28 0 0 1
Execute: 1 0.000 0.000 0 0 0 0 0
Fetch: 2 0.030 0.033 0 16 0 1 0
Total: 4 0.070 0.072 0 44 0 1 1

Idle Wait Events

Idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
SQL*Net message from client: 2 11.076 11.079
SQL*Net message to client: 2 0.000 0.000
Total idle: 4 11.076 11.079

Row Source Plan (Real Execution Path)

Rows
Returned
Exec
Order
Row Source OperationObject
Id
Logical
Reads
(blocks)
Physical
Reads
(blocks)
Physical
Writes
(blocks)
Elapsed
Time
(secs)
1 8 SORT AGGREGATE 0 16 0 0 0.000
10000 7  HASH JOIN 0 16 0 0 0.000
1000 1   TABLE ACCESS FULL C 60742 7 0 0 0.000
1000 6   HASH JOIN 0 9 0 0 0.000
1000 3    TABLE ACCESS BY INDEX ROWID A 60741 7 0 0 0.000
1000 2     INDEX RANGE SCAN A_STATUS_IDX 60743 4 0 0 0.000
100 5    TABLE ACCESS BY INDEX ROWID B 60740 2 0 0 0.000
100 4     INDEX RANGE SCAN B_STATUS_IDX 60744 1 0 0 0.000

Explain Plan (Generated by Trace Analyzer)

IdExec
Order
Explain Plan Operation
0: 9 SELECT STATEMENT
1: 8  SORT AGGREGATE
2: 7   HASH JOIN
3: 1    TABLE ACCESS FULL USER0.C
4: 6    HASH JOIN
5: 3     TABLE ACCESS BY INDEX ROWID USER0.A
6: 2      INDEX RANGE SCAN USER0.A_STATUS_IDX
7: 5     TABLE ACCESS BY INDEX ROWID USER0.B
8: 4      INDEX RANGE SCAN USER0.B_STATUS_IDX

IdObject
Type
Parent
Id
PosObj
Inst
Search
Cols
Estim
Card
Estim
Bytes
CostCPU
Cost
IO
Cost
TimeTemp
Space
0: 16 1 57 16 12594254 15
1: 0 1 1 57
2: 1 1 48930 2789010 16 12594254 15
3: TABLE 2 1 3 1000 19000 3 476972 3
4: 2 2 4893 185934 13 6048781 12
5: TABLE 4 1 1 1000 19000 10 408336 10
6: INDEX 5 1 1 1000 7 256972 7
7: TABLE 4 2 2 100 1900 2 51243 2
8: INDEX 7 1 1 100 1 27121 1

IdColumn NameColumn Value
2: Access Predicates "B"."ID"="C"."B_ID"
3: Filter Predicates "C"."STATUS"='OPEN'
4: Access Predicates "A"."STATUS"="B"."STATUS" AND "A"."B_ID"="B"."ID"
6: Access Predicates "A"."STATUS"='OPEN'
8: Access Predicates "B"."STATUS"='OPEN'

Tables Referenced by Explain Plan

Owner.Table NameCurrent
COUNT(*)
Num*
Rows
Sample*
Size
Last*
Analyzed
Avg*
Row
Len
Chain*
Cnt
Blocks* Empty*
Blocks
Avg*
Space
Global*
Stats
USER0.A1000NO
USER0.B100NO
USER0.C1000NO
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Owner.Table NameTemporaryPartitionedLoggingDegreeCacheIOT
Type
Ini
Trans
Max
Trans
Freelist
Groups
Freelists
USER0.ANNOYES 1 N1255
USER0.BNNOYES 1 N1255
USER0.CNNOYES 1 N1255

Indexes on Tables Referenced by Explain Plan

Table Owner.Table NameIndex Owner.Index NameIndex
Type
UniquenessIndexed Columns
USER0.AUSER0.A_STATUS_IDXNORMALNONUNIQUESTATUS
USER0.BUSER0.B_STATUS_IDXNORMALNONUNIQUESTATUS
USER0.BUSER0.PK_B_IDNORMALUNIQUEID
USER0.CUSER0.C_B_ID_IDXNORMALNONUNIQUEB_ID

Table Owner.Table NameIndex Owner.Index NameNum*
Rows
Sample*
Size
Last*
Analyzed
Distinct*
Keys
Blevel*Leaf*
Blocks
Avg*
Leaf
Blocks
per
Key
Avg*
Data
Blocks
per
Key
Clustering*
Factor
Global*
Stats
USER0.AUSER0.A_STATUS_IDX1000100024-JAN-06 08:32:181133 33NO
USER0.BUSER0.B_STATUS_IDX10010024-JAN-06 08:32:181011 11NO
USER0.BUSER0.PK_B_ID10010024-JAN-06 08:32:18100011 11NO
USER0.CUSER0.C_B_ID_IDX1000100024-JAN-06 08:32:18100121 3300NO
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Table Owner.Table NameIndex Owner.Index NameTemporaryPartitionedLoggingDegreeIni
Trans
Max
Trans
Freelist
Groups
FreelistsStatusDomidx
Status
Funcidx
Status
USER0.AUSER0.A_STATUS_IDXNNOYES12255VALID
USER0.BUSER0.B_STATUS_IDXNNOYES12255VALID
USER0.BUSER0.PK_B_IDNNOYES12255VALID
USER0.CUSER0.C_B_ID_IDXNNOYES12255VALID

hash:1119185712 length:336 depth:1(recursive) user:131(user) line:952 rank:18 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 4 0.000 0.004 0 0 0 0 1
Execute: 4 0.000 0.001 0 0 0 0 0
Fetch: 4 0.010 0.004 5 28 0 4 0
Total: 12 0.010 0.009 5 28 0 4 1

Non-idle Wait Events

Non-idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
db file scattered read: 1 0.002 0.002
Total non-idle: 1 0.002 0.002

Execution/Fetch Performance: First and Top 10 (according to CPU, Elapsed and Wait Times)

Exec
Line
Call
Count
CPU
Time
(secs)
Elapsed
Time
(secs)
Non-idle
Wait
(secs)
Idle
Wait
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Exec
Ranking
957: 3 0.010 0.006 0.002 0.000 5 7 0 1 1 1
978: 3 0.000 0.001 0.000 0.000 0 7 0 1 0 2
998: 3 0.000 0.001 0.000 0.000 0 7 0 1 0 3
1040: 3 0.000 0.001 0.000 0.000 0 7 0 1 0 4

Row Source Plan (Real Execution Path)

Rows
Returned
Exec
Order
Row Source OperationObject
Id
Logical
Reads
(blocks)
Physical
Reads
(blocks)
Physical
Writes
(blocks)
Elapsed
Time
(secs)
4 2 SORT AGGREGATE 0 28 5 0 0.000
56 1  TABLE ACCESS FULL EMP 60732 28 5 0 0.000

Explain Plan (Generated by Trace Analyzer)

IdExec
Order
Explain Plan Operation
0: 3 SELECT STATEMENT
1: 2  SORT AGGREGATE
2: 1   TABLE ACCESS FULL USER0.EMP

IdObject
Type
Parent
Id
PosObj
Inst
Search
Cols
Estim
Card
Estim
Bytes
CostCPU
Cost
IO
Cost
TimeTemp
Space
0: 3 1 6 3 37427 3
1: 0 1 1 6
2: TABLE 1 1 2 14 84 3 37427 3

Tables Referenced by Explain Plan

Owner.Table NameCurrent
COUNT(*)
Num*
Rows
Sample*
Size
Last*
Analyzed
Avg*
Row
Len
Chain*
Cnt
Blocks* Empty*
Blocks
Avg*
Space
Global*
Stats
USER0.EMP14NO
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Owner.Table NameTemporaryPartitionedLoggingDegreeCacheIOT
Type
Ini
Trans
Max
Trans
Freelist
Groups
Freelists
USER0.EMPNNOYES 1 N1255

hash:2918329517 length:356 depth:1(recursive) user:131(user) line:963 rank:25 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 2 0.000 0.005 0 0 0 0 1
Execute: 2 0.000 0.000 0 0 0 0 0
Fetch: 2 0.000 0.001 0 14 0 2 0
Total: 6 0.000 0.006 0 14 0 2 1

Execution/Fetch Performance: First and Top 10 (according to CPU, Elapsed and Wait Times)

Exec
Line
Call
Count
CPU
Time
(secs)
Elapsed
Time
(secs)
Non-idle
Wait
(secs)
Idle
Wait
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Exec
Ranking
968: 3 0.000 0.006 0.000 0.000 0 7 0 1 1 1
988: 3 0.000 0.001 0.000 0.000 0 7 0 1 0 2

Row Source Plan (Real Execution Path)

Rows
Returned
Exec
Order
Row Source OperationObject
Id
Logical
Reads
(blocks)
Physical
Reads
(blocks)
Physical
Writes
(blocks)
Elapsed
Time
(secs)
2 2 SORT GROUP BY 0 14 0 0 0.000
28 1  TABLE ACCESS FULL EMP 60732 14 0 0 0.000

hash:3377837649 length:265 depth:1(recursive) user:131(user) line:1003 rank:31 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 1 0.010 0.004 0 0 0 0 1
Execute: 1 0.000 0.000 0 0 0 0 0
Fetch: 1 0.000 0.000 0 7 0 1 0
Total: 3 0.010 0.004 0 7 0 1 1

Row Source Plan (Real Execution Path)

Rows
Returned
Exec
Order
Row Source OperationObject
Id
Logical
Reads
(blocks)
Physical
Reads
(blocks)
Physical
Writes
(blocks)
Elapsed
Time
(secs)
1 2 SORT AGGREGATE 0 7 0 0 0.000
14 1  TABLE ACCESS FULL EMP 60732 7 0 0 0.000

hash:2717499716 length:297 depth:1(recursive) user:131(user) line:1045 rank:30 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 1 0.000 0.008 0 0 0 0 1
Execute: 1 0.010 0.000 0 0 0 0 0
Fetch: 1 0.000 0.000 0 7 0 1 0
Total: 3 0.010 0.008 0 7 0 1 1

Row Source Plan (Real Execution Path)

Rows
Returned
Exec
Order
Row Source OperationObject
Id
Logical
Reads
(blocks)
Physical
Reads
(blocks)
Physical
Writes
(blocks)
Elapsed
Time
(secs)
1 2 SORT AGGREGATE 0 7 0 0 0.000
14 1  TABLE ACCESS FULL EMP 60732 7 0 0 0.000

hash:2590686023 length:149 depth:0(non-recursive) user:131(user) line:1055 rank:6 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 1 0.050 0.073 6 68 0 0 1
Execute: 1 0.000 0.000 0 0 0 0 0
Fetch: 2 0.010 0.005 0 92 0 4 0
Total: 4 0.060 0.078 6 160 0 4 1

Idle Wait Events

Idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
SQL*Net message from client: 2 6.334 6.336
SQL*Net message to client: 2 0.000 0.000
Total idle: 4 6.334 6.336

Row Source Plan (Real Execution Path)

Rows
Returned
Exec
Order
Row Source OperationObject
Id
Logical
Reads
(blocks)
Physical
Reads
(blocks)
Physical
Writes
(blocks)
Elapsed
Time
(secs)
4 5 FILTER 0 92 0 0 0.000
14 1  TABLE ACCESS FULL EMP 60732 8 0 0 0.000
3 4  FILTER 0 84 0 0 0.000
33 3   SORT GROUP BY 0 84 0 0 0.000
156 2    TABLE ACCESS FULL EMP 60732 84 0 0 0.000

Explain Plan (Generated by Trace Analyzer)

IdExec
Order
Explain Plan Operation
0: 6 SELECT STATEMENT
1: 5  FILTER
2: 1   TABLE ACCESS FULL USER0.EMP
3: 4   FILTER
4: 3    SORT GROUP BY
5: 2     TABLE ACCESS FULL USER0.EMP

IdObject
Type
Parent
Id
PosObj
Inst
Search
Cols
Estim
Card
Estim
Bytes
CostCPU
Cost
IO
Cost
TimeTemp
Space
0: 7 1 20 7 10940225 6
1: 0 1
2: TABLE 1 1 1 1 20 3 39387 3
3: 1 2
4: 3 1 13 416 4 10900838 3
5: TABLE 4 1 2 13 416 3 40267 3

IdColumn NameColumn Value
1: Filter Predicates EXISTS (SELECT 0 FROM "EMP" "EMP" WHERE "JOB"<>'PRESIDENT' GROUP BY "DEPTNO" HAVING MAX("SAL")=:B1)
3: Filter Predicates MAX("SAL")=:B1
5: Filter Predicates "JOB"<>'PRESIDENT'

Tables Referenced by Explain Plan

Owner.Table NameCurrent
COUNT(*)
Num*
Rows
Sample*
Size
Last*
Analyzed
Avg*
Row
Len
Chain*
Cnt
Blocks* Empty*
Blocks
Avg*
Space
Global*
Stats
USER0.EMP14NO
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Owner.Table NameTemporaryPartitionedLoggingDegreeCacheIOT
Type
Ini
Trans
Max
Trans
Freelist
Groups
Freelists
USER0.EMPNNOYES 1 N1255

hash:3624062053 length:364 depth:1(recursive) user:131(user) line:1078 rank:24 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 2 0.010 0.009 0 0 0 0 1
Execute: 2 0.000 0.000 0 0 0 0 0
Fetch: 2 0.000 0.001 0 14 0 2 0
Total: 6 0.010 0.010 0 14 0 2 1

Execution/Fetch Performance: First and Top 10 (according to CPU, Elapsed and Wait Times)

Exec
Line
Call
Count
CPU
Time
(secs)
Elapsed
Time
(secs)
Non-idle
Wait
(secs)
Idle
Wait
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Exec
Ranking
1083: 3 0.010 0.009 0.000 0.000 0 7 0 1 1 1
1093: 3 0.000 0.001 0.000 0.000 0 7 0 1 0 2

Row Source Plan (Real Execution Path)

Rows
Returned
Exec
Order
Row Source OperationObject
Id
Logical
Reads
(blocks)
Physical
Reads
(blocks)
Physical
Writes
(blocks)
Elapsed
Time
(secs)
2 2 SORT AGGREGATE 0 14 0 0 0.000
28 1  TABLE ACCESS FULL EMP 60732 14 0 0 0.000

hash:3382011065 length:150 depth:0(non-recursive) user:131(user) line:1098 rank:17 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 1 0.020 0.025 0 16 0 0 1
Execute: 1 0.000 0.000 0 0 0 0 0
Fetch: 2 0.000 0.001 0 7 0 4 0
Total: 4 0.020 0.026 0 23 0 4 1

Idle Wait Events

Idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
SQL*Net message from client: 2 5.192 5.194
SQL*Net message to client: 2 0.000 0.000
Total idle: 4 5.192 5.194

Row Source Plan (Real Execution Path)

Rows
Returned
Exec
Order
Row Source OperationObject
Id
Logical
Reads
(blocks)
Physical
Reads
(blocks)
Physical
Writes
(blocks)
Elapsed
Time
(secs)
4 4 COUNT STOPKEY 0 7 0 0 0.000
4 3  VIEW 0 7 0 0 0.000
4 2   SORT ORDER BY STOPKEY 0 7 0 0 0.000
9 1    TABLE ACCESS FULL EMP 60732 7 0 0 0.000

Explain Plan (Generated by Trace Analyzer)

IdExec
Order
Explain Plan Operation
0: 5 SELECT STATEMENT
1: 4  COUNT STOPKEY
2: 3   VIEW USER0
3: 2    SORT ORDER BY STOPKEY
4: 1     TABLE ACCESS FULL USER0.EMP

IdObject
Type
Parent
Id
PosObj
Inst
Search
Cols
Estim
Card
Estim
Bytes
CostCPU
Cost
IO
Cost
TimeTemp
Space
0: 3 4 104 3 39542 3
1: 0 1
2: 1 1 1 9 234 3 39542 3
3: 2 1 9 234 3 39542 3
4: TABLE 3 1 2 9 234 3 39542 3

IdColumn NameColumn Value
1: Filter Predicates ROWNUM<5
3: Filter Predicates ROWNUM<5
4: Filter Predicates "JOB"<>'PRESIDENT' AND "JOB"<>'SALESMAN'

Tables Referenced by Explain Plan

Owner.Table NameCurrent
COUNT(*)
Num*
Rows
Sample*
Size
Last*
Analyzed
Avg*
Row
Len
Chain*
Cnt
Blocks* Empty*
Blocks
Avg*
Space
Global*
Stats
USER0.EMP14NO
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Owner.Table NameTemporaryPartitionedLoggingDegreeCacheIOT
Type
Ini
Trans
Max
Trans
Freelist
Groups
Freelists
USER0.EMPNNOYES 1 N1255

hash:3257052414 length:389 depth:0(non-recursive) user:131(user) line:1120 rank:3 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 1 0.020 0.017 0 0 0 0 1
Execute: 1 0.000 0.000 0 0 0 0 0
Fetch: 10 0.060 0.060 0 266 0 124 0
Total: 12 0.080 0.077 0 266 0 124 1

Idle Wait Events

Idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
SQL*Net message from client: 10 6.696 10.095
SQL*Net message to client: 10 0.000 0.000
Total idle: 20 6.696 10.095

Row Source Plan (Real Execution Path)

Rows
Returned
Exec
Order
Row Source OperationObject
Id
Logical
Reads
(blocks)
Physical
Reads
(blocks)
Physical
Writes
(blocks)
Elapsed
Time
(secs)
124 6 SORT ORDER BY 0 266 0 0 0.000
124 5  HASH JOIN 0 266 0 0 0.000
124 3   NESTED LOOPS 0 159 0 0 0.000
126 1    TABLE ACCESS FULL MANAGERS 60753 31 0 0 0.000
124 2    INDEX UNIQUE SCAN TEAMS_PK 60755 128 0 0 0.000
15350 4   TABLE ACCESS FULL MASTER 60756 107 0 0 0.000

Explain Plan (Generated by Trace Analyzer)

IdExec
Order
Explain Plan Operation
0: 7 SELECT STATEMENT
1: 6  SORT ORDER BY
2: 5   HASH JOIN
3: 3    NESTED LOOPS
4: 1     TABLE ACCESS FULL USER0.MANAGERS
5: 2     INDEX UNIQUE SCAN USER0.TEAMS_PK
6: 4    TABLE ACCESS FULL USER0.MASTER

IdObject
Type
Parent
Id
PosObj
Inst
Search
Cols
Estim
Card
Estim
Bytes
CostCPU
Cost
IO
Cost
TimeTemp
Space
0: 34 3 153 34 22625660 32
1: 0 1 3 153 34 22625660 32
2: 1 1 3 153 33 11767042 32
3: 2 1 3 81 8 2073382 8
4: TABLE 3 1 2 90 1710 8 1337952 8
5: INDEX (UNIQUE) 3 2 3 1 8 0 8171 0
6: TABLE 2 2 3 15350 368400 24 2729008 24

IdColumn NameColumn Value
2: Access Predicates "MG"."LAHMANID"="MA"."LAHMANID"
4: Filter Predicates "MG"."DATE_YYYY"=1996 OR "MG"."DATE_YYYY"=1997 OR "MG"."DATE_YYYY"=1998 OR "MG"."DATE_YYYY"=1999
5: Access Predicates "TE"."DATE_YYYY"="MG"."DATE_YYYY" AND "TE"."LEAGUE_ID"="MG"."LEAGUE_ID" AND "TE"."TEAM_ID"="MG"."TEAM_ID"
5: Filter Predicates "TE"."DATE_YYYY"=1996 OR "TE"."DATE_YYYY"=1997 OR "TE"."DATE_YYYY"=1998 OR "TE"."DATE_YYYY"=1999

Tables Referenced by Explain Plan

Owner.Table NameCurrent
COUNT(*)
Num*
Rows
Sample*
Size
Last*
Analyzed
Avg*
Row
Len
Chain*
Cnt
Blocks* Empty*
Blocks
Avg*
Space
Global*
Stats
USER0.MANAGERS28952895289524-JAN-06 08:51:31490284 2842NO
USER0.MASTER1535015350432424-JAN-06 08:51:314301039 1331NO
USER0.TEAMS23272327232724-JAN-06 08:51:322560888 1253NO
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Owner.Table NameTemporaryPartitionedLoggingDegreeCacheIOT
Type
Ini
Trans
Max
Trans
Freelist
Groups
Freelists
USER0.MANAGERSNNOYES 1 N1255
USER0.MASTERNNOYES 1 N1255
USER0.TEAMSNNOYES 1 N1255

Indexes on Tables Referenced by Explain Plan

Table Owner.Table NameIndex Owner.Index NameIndex
Type
UniquenessIndexed Columns
USER0.TEAMSUSER0.TEAMS_PKNORMALUNIQUEDATE_YYYY LEAGUE_ID TEAM_ID

Table Owner.Table NameIndex Owner.Index NameNum*
Rows
Sample*
Size
Last*
Analyzed
Distinct*
Keys
Blevel*Leaf*
Blocks
Avg*
Leaf
Blocks
per
Key
Avg*
Data
Blocks
per
Key
Clustering*
Factor
Global*
Stats
USER0.TEAMSUSER0.TEAMS_PK2327232724-JAN-06 08:51:322327111 11105NO
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Table Owner.Table NameIndex Owner.Index NameTemporaryPartitionedLoggingDegreeIni
Trans
Max
Trans
Freelist
Groups
FreelistsStatusDomidx
Status
Funcidx
Status
USER0.TEAMSUSER0.TEAMS_PKNNOYES12255VALID

hash:1682066536 length:43 depth:1(recursive) user:0(internal) line:1170 rank:34 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 1 0.000 0.006 0 0 0 0 1
Execute: 1 0.010 0.004 0 0 0 0 1
Fetch: 1 0.000 0.000 0 1 0 0 0
Total: 3 0.010 0.010 0 1 0 0 2

Bind Variables Values: First and Top 10 Executions (according to CPU, Elapsed and Wait Times)

Exec
Line
Exec
Ranking
1 Values
(according to position within SQL)
1178: 1 "BASEBALL"

hash:-1 length:456 depth:0(non-recursive) user:131(user) line:1181 rank:7 next prior top


Non-idle Wait Events

Non-idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
SQL*Net break/reset to client: 2 0.001 0.001
Total non-idle: 2 0.001 0.001

Idle Wait Events

Idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
SQL*Net message from client: 1 77.309 77.309
SQL*Net message to client: 1 0.000 0.000
Total idle: 2 77.309 77.309

Explain Plan (Generated by Trace Analyzer)

ORA-00942: table or view does not exist EXPLAIN PLAN SET STATEMENT_ID = '6-48' INTO trca$_plan_table FOR SELECT ma.first_name_tx, ma.last_name_tx, mg.place_finished_nr, ma.debut_season_yyyy FROM baseball.managers mg, baseball.master ma, (select date_yyyy, team_id, league_id from baseball.teams where date_yyyy IN ('1999', '1998', '1997', '1996')) te WHERE te.date_yyyy = mg.date_yyyy AND te.team_id = mg.team_id AND te.league_id = mg.league_id AND mg.lahmanid = ma.lahmanid ORDER BY ma.last_name_tx, mg.place_finished_n

hash:1802248379 length:447 depth:0(non-recursive) user:131(user) line:1198 rank:4 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 1 0.010 0.014 0 0 0 0 1
Execute: 1 0.000 0.000 0 0 0 0 0
Fetch: 10 0.060 0.061 0 266 0 124 0
Total: 12 0.070 0.075 0 266 0 124 1

Idle Wait Events

Idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
SQL*Net message from client: 10 6.243 9.686
SQL*Net message to client: 10 0.000 0.000
Total idle: 20 6.243 9.686

Row Source Plan (Real Execution Path)

Rows
Returned
Exec
Order
Row Source OperationObject
Id
Logical
Reads
(blocks)
Physical
Reads
(blocks)
Physical
Writes
(blocks)
Elapsed
Time
(secs)
124 6 SORT ORDER BY 0 266 0 0 0.000
124 5  HASH JOIN 0 266 0 0 0.000
124 3   NESTED LOOPS 0 159 0 0 0.000
126 1    TABLE ACCESS FULL MANAGERS 60753 31 0 0 0.000
124 2    INDEX UNIQUE SCAN TEAMS_PK 60755 128 0 0 0.000
15350 4   TABLE ACCESS FULL MASTER 60756 107 0 0 0.000

Explain Plan (Generated by Trace Analyzer)

IdExec
Order
Explain Plan Operation
0: 7 SELECT STATEMENT
1: 6  SORT ORDER BY
2: 5   HASH JOIN
3: 3    NESTED LOOPS
4: 1     TABLE ACCESS FULL USER0.MANAGERS
5: 2     INDEX UNIQUE SCAN USER0.TEAMS_PK
6: 4    TABLE ACCESS FULL USER0.MASTER

IdObject
Type
Parent
Id
PosObj
Inst
Search
Cols
Estim
Card
Estim
Bytes
CostCPU
Cost
IO
Cost
TimeTemp
Space
0: 34 3 153 34 22625660 32
1: 0 1 3 153 34 22625660 32
2: 1 1 3 153 33 11767042 32
3: 2 1 3 81 8 2073382 8
4: TABLE 3 1 1 90 1710 8 1337952 8
5: INDEX (UNIQUE) 3 2 3 1 8 0 8171 0
6: TABLE 2 2 2 15350 368400 24 2729008 24

IdColumn NameColumn Value
2: Access Predicates "MG"."LAHMANID"="MA"."LAHMANID"
4: Filter Predicates "MG"."DATE_YYYY"=1996 OR "MG"."DATE_YYYY"=1997 OR "MG"."DATE_YYYY"=1998 OR "MG"."DATE_YYYY"=1999
5: Access Predicates "DATE_YYYY"="MG"."DATE_YYYY" AND "LEAGUE_ID"="MG"."LEAGUE_ID" AND "TEAM_ID"="MG"."TEAM_ID"
5: Filter Predicates "DATE_YYYY"=1996 OR "DATE_YYYY"=1997 OR "DATE_YYYY"=1998 OR "DATE_YYYY"=1999

Tables Referenced by Explain Plan

Owner.Table NameCurrent
COUNT(*)
Num*
Rows
Sample*
Size
Last*
Analyzed
Avg*
Row
Len
Chain*
Cnt
Blocks* Empty*
Blocks
Avg*
Space
Global*
Stats
USER0.MANAGERS28952895289524-JAN-06 08:51:31490284 2842NO
USER0.MASTER1535015350432424-JAN-06 08:51:314301039 1331NO
USER0.TEAMS23272327232724-JAN-06 08:51:322560888 1253NO
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Owner.Table NameTemporaryPartitionedLoggingDegreeCacheIOT
Type
Ini
Trans
Max
Trans
Freelist
Groups
Freelists
USER0.MANAGERSNNOYES 1 N1255
USER0.MASTERNNOYES 1 N1255
USER0.TEAMSNNOYES 1 N1255

Indexes on Tables Referenced by Explain Plan

Table Owner.Table NameIndex Owner.Index NameIndex
Type
UniquenessIndexed Columns
USER0.TEAMSUSER0.TEAMS_PKNORMALUNIQUEDATE_YYYY LEAGUE_ID TEAM_ID

Table Owner.Table NameIndex Owner.Index NameNum*
Rows
Sample*
Size
Last*
Analyzed
Distinct*
Keys
Blevel*Leaf*
Blocks
Avg*
Leaf
Blocks
per
Key
Avg*
Data
Blocks
per
Key
Clustering*
Factor
Global*
Stats
USER0.TEAMSUSER0.TEAMS_PK2327232724-JAN-06 08:51:322327111 11105NO
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Table Owner.Table NameIndex Owner.Index NameTemporaryPartitionedLoggingDegreeIni
Trans
Max
Trans
Freelist
Groups
FreelistsStatusDomidx
Status
Funcidx
Status
USER0.TEAMSUSER0.TEAMS_PKNNOYES12255VALID

hash:1137322733 length:58 depth:0(non-recursive) user:131(user) line:1250 rank:8 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 1 0.000 0.004 0 0 0 0 1
Execute: 1 0.000 0.000 0 0 0 0 0
Fetch: 2 0.010 0.018 0 107 0 1 0
Total: 4 0.010 0.022 0 107 0 1 1

Idle Wait Events

Idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
SQL*Net message from client: 2 6.452 6.453
SQL*Net message to client: 2 0.000 0.000
Total idle: 4 6.452 6.453

Row Source Plan (Real Execution Path)

Rows
Returned
Exec
Order
Row Source OperationObject
Id
Logical
Reads
(blocks)
Physical
Reads
(blocks)
Physical
Writes
(blocks)
Elapsed
Time
(secs)
1 2 SORT AGGREGATE 0 107 0 0 0.000
196 1  TABLE ACCESS FULL MASTER 60756 107 0 0 0.000

Explain Plan (Generated by Trace Analyzer)

IdExec
Order
Explain Plan Operation
0: 3 SELECT STATEMENT
1: 2  SORT AGGREGATE
2: 1   TABLE ACCESS FULL USER0.MASTER

IdObject
Type
Parent
Id
PosObj
Inst
Search
Cols
Estim
Card
Estim
Bytes
CostCPU
Cost
IO
Cost
TimeTemp
Space
0: 24 1 7 24 4878008 24
1: 0 1 1 7
2: TABLE 1 1 1 4 28 24 4878008 24

IdColumn NameColumn Value
2: Filter Predicates "LAST_NAME_TX" LIKE 'WIL%'

Tables Referenced by Explain Plan

Owner.Table NameCurrent
COUNT(*)
Num*
Rows
Sample*
Size
Last*
Analyzed
Avg*
Row
Len
Chain*
Cnt
Blocks* Empty*
Blocks
Avg*
Space
Global*
Stats
USER0.MASTER1535015350432424-JAN-06 08:51:314301039 1331NO
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Owner.Table NameTemporaryPartitionedLoggingDegreeCacheIOT
Type
Ini
Trans
Max
Trans
Freelist
Groups
Freelists
USER0.MASTERNNOYES 1 N1255

hash:3182831140 length:131 depth:0(non-recursive) user:131(user) line:1318 rank:5 next prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 1 0.010 0.009 0 0 0 0 1
Execute: 1 0.000 0.000 0 0 0 0 0
Fetch: 2 0.290 0.284 0 230 0 1 0
Total: 4 0.300 0.293 0 230 0 1 1

Idle Wait Events

Idle Wait EventTimes
Waited
Max Wait
(secs)
Total Time
Waited (secs)
SQL*Net message from client: 2 21.478 21.480
SQL*Net message to client: 2 0.000 0.000
Total idle: 4 21.478 21.480

Row Source Plan (Real Execution Path)

Rows
Returned
Exec
Order
Row Source OperationObject
Id
Logical
Reads
(blocks)
Physical
Reads
(blocks)
Physical
Writes
(blocks)
Elapsed
Time
(secs)
1 6 SORT AGGREGATE 0 230 0 0 0.000
183039 5  HASH JOIN 0 230 0 0 2.000
1116 1   TABLE ACCESS FULL TEAMS 60754 92 0 0 0.000
2867 4   HASH JOIN 0 138 0 0 0.000
2895 2    TABLE ACCESS FULL MANAGERS 60753 31 0 0 0.000
15350 3    TABLE ACCESS FULL MASTER 60756 107 0 0 0.000

Explain Plan (Generated by Trace Analyzer)

IdExec
Order
Explain Plan Operation
0: 7 SELECT STATEMENT
1: 6  SORT AGGREGATE
2: 5   HASH JOIN
3: 1    TABLE ACCESS FULL USER0.TEAMS
4: 4    HASH JOIN
5: 2     TABLE ACCESS FULL USER0.MANAGERS
6: 3     TABLE ACCESS FULL USER0.MASTER

IdObject
Type
Parent
Id
PosObj
Inst
Search
Cols
Estim
Card
Estim
Bytes
CostCPU
Cost
IO
Cost
TimeTemp
Space
0: 55 1 26 55 18039169 53
1: 0 1 1 26
2: 1 1 46817 1217242 55 18039169 53
3: TABLE 2 1 3 1019 5095 21 1464407 21
4: 2 2 2895 60795 33 10703210 32
5: TABLE 4 1 1 2895 34740 8 575750 8
6: TABLE 4 2 2 15350 138150 24 2729008 24

IdColumn NameColumn Value
2: Access Predicates "T"."TEAM_ID"="MG"."TEAM_ID"
3: Filter Predicates "T"."WIN_PCT">.5
4: Access Predicates "MG"."LAHMANID"="MA"."LAHMANID"

Tables Referenced by Explain Plan

Owner.Table NameCurrent
COUNT(*)
Num*
Rows
Sample*
Size
Last*
Analyzed
Avg*
Row
Len
Chain*
Cnt
Blocks* Empty*
Blocks
Avg*
Space
Global*
Stats
USER0.MANAGERS28952895289524-JAN-06 08:51:31490284 2842NO
USER0.MASTER1535015350432424-JAN-06 08:51:314301039 1331NO
USER0.TEAMS23272327232724-JAN-06 08:51:322560888 1253NO
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Owner.Table NameTemporaryPartitionedLoggingDegreeCacheIOT
Type
Ini
Trans
Max
Trans
Freelist
Groups
Freelists
USER0.MANAGERSNNOYES 1 N1255
USER0.MASTERNNOYES 1 N1255
USER0.TEAMSNNOYES 1 N1255

Indexes on Tables Referenced by Explain Plan

Table Owner.Table NameIndex Owner.Index NameIndex
Type
UniquenessIndexed Columns
USER0.TEAMSUSER0.TEAMS_PKNORMALUNIQUEDATE_YYYY LEAGUE_ID TEAM_ID

Table Owner.Table NameIndex Owner.Index NameNum*
Rows
Sample*
Size
Last*
Analyzed
Distinct*
Keys
Blevel*Leaf*
Blocks
Avg*
Leaf
Blocks
per
Key
Avg*
Data
Blocks
per
Key
Clustering*
Factor
Global*
Stats
USER0.TEAMSUSER0.TEAMS_PK2327232724-JAN-06 08:51:322327111 11105NO
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Table Owner.Table NameIndex Owner.Index NameTemporaryPartitionedLoggingDegreeIni
Trans
Max
Trans
Freelist
Groups
FreelistsStatusDomidx
Status
Funcidx
Status
USER0.TEAMSUSER0.TEAMS_PKNNOYES12255VALID

hash:2217940283 length:55 depth:0(non-recursive) user:131(user) line:1342 rank:35 prior top


Call Summary

Call
Type
Call
Count
CPU Time
(secs)
Elapsed Time
(secs)
Disk
(blocks)
Query
(blocks)
Current
(blocks)
Rows
Returned
Cache
Misses
Parse: 1 0.000 0.004 0 0 0 0 1
Execute: 1 0.000 0.000 0 0 0 0 0
Fetch: 0 0.000 0.000 0 0 0 0 0
Total: 2 0.000 0.004 0 0 0 0 1

Trace Analyzer 2.3 secs:17.000 24-JAN-06 09:21:24