MERITS Profiler Report

Report Date: Sun 15-Nov-09 20:00:58 (generated by MERITS Profiler release 0.9.4)

Abbreviations

Avg.: Average
CR: Consistent reads
Cum.: Cumulative
Ela.: Elapsed time
Est.: Estimated
MB: Multi block
MBR: Multi block read
Obj.: Object (ALL_OBJECTS.OBJECT_ID)
PR: Physical reads
PW: Physical writes
SB: Single block
SBR: Single block read
n.r.: not resolvable (e.g. no match for a user ID in DB_USERS)

Notes: The portion of 'SQL*Net message from client' waits that execeed the think time threshold of 5 ms are reported as the pseudo wait event "think time"
All timings are in seconds unless noted otherwise.

Trace File Header

Dump file c:\programme\oracle\product\admin\ten\udump\ten_ora_20428.trc
Sun Nov 15 20:00:59 2009
ORACLE V10.2.0.3.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 2
CPU : 2 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:702M/2046M, Ph+PgF:2317M/3940M, VA:1532M/2047M
Instance name: ten
Redo thread mounted by this instance: 1
Oracle process number: 20
Windows thread id: 20428, image: ORACLE.EXE (SHAD)
*** 2009-11-15 20:00:59.562

Session in trace file(s): 1
39.5078

Statspack Snapshots

Begin snapshot: 482
End snapshot: 483

Active Workload Repository Snapshots

Begin snapshot: 2426
End snapshot: 2427
AWR report: awr_report_2426_2427.html
ASH report: ash_report_2426_2427.html

Response Time and Statistics

Accounted-for response time (R): 508.204 s
Measurement interval (delta tim): 509.440 s
Intra Database Call Wait Time: 157.406 s
Inter Database Call Wait Time: 16.312 s
Committed transactions: 4014
Committed transactions/s (based on delta tim): 7.88
Transaction rollbacks: 0
Commits (read only): 2860
Rollbacks (read only): 0
Oracle PARSE ERROR entries: 0
MERITS Profiler parser errors: 109

Resource Profile for Trace File BFILENAME('USER_DUMP_DEST', 'ten_ora_20428.trc')

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
CPU	196.406250	38.6	40502	0.004849
unknown	139.315643	27.3	48	2.902409
PL/SQL lock timer	84.419418	16.6	8011	0.010538
db file sequential read	72.734230	14.3	5518	0.013181
SQL*Net message from client	14.124794	2.8	2859	0.004940
think time	2.168551	0.4	2191	0.000990
log file switch completion	0.153534	0.0	1	0.153534
read by other session	0.057413	0.0	3	0.019138
buffer busy waits	0.037738	0.0	5	0.007548
SQL*Net message to client	0.018189	0.0	2858	0.000006
latch free	0.002644	0.0	2	0.001322
log file sync	0.001034	0.0	2	0.000517
latch: library cache	0.000125	0.0	3	0.000042
latch: cache buffers lru chain	0.000048	0.0	1	0.000048
latch: cache buffers chains	0.000047	0.0	1	0.000047
latch: row cache objects	0.000012	0.0	1	0.000012
Total	509.439667	100.0	62006	0.008216

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	2865	0.080	0.000028	0.000	0.000000	0	0	0	0	0
EXEC	25904	491.813	0.018986	196.406	0.007582	5518	1412923	64889	11650	0
FETCH	11733	0.000	0.000000	0.000	0.000000	0	0	0	220429	0
Total	40502	491.892	0.012145	196.406	0.004849	5518	1412923	64889	232079	0
Avg. per EXEC	1.564	0.018989	n/a	0.007582	n/a	0.213	54.545	2.505	8.959	0.000
Avg. per FETCH	3.452	0.041924	n/a	0.016740	n/a	0.470	120.423	5.530	19.780	0.000
Avg. per Row	0.175	0.002120	n/a	0.000846	n/a	0.024	6.088	0.280	1.000	0.000

Buffer cache hit ratio: 99.6 %
Parse call library cache hit ratio: 100.0 %

Elapsed Time/CPU Usage/Wait Time by Recursive Call Depth

Note: cumulative values in parentheses

Recursive Call Depth 0 1 2
DB Call Elapsed Time (s) 141.217 (491.892) 350.582 (350.676) 0.093
CPU Time (s) 8.875 (196.406) 187.531 (187.531) 0.000
Wait Time (s) 98.600 (171.543) 72.860 (72.943) 0.084

Recursive Call Depth	0	1	2
DB Call Elapsed Time (s)	141.217 (491.892)	350.582 (350.676)	0.093
CPU Time (s)	8.875 (196.406)	187.531 (187.531)	0.000
Wait Time (s)	98.600 (171.543)	72.860 (72.943)	0.084

Hardware

cpu_count=2 (number of CPU cores)

Initialization Parameters

Name	Value	Default	Modified
_ash_sample_all	FALSE	FALSE	MODIFIED
background_dump_dest	C:\PROGRAMME\ORACLE\PRODUCT\ADMIN\TEN\BDUMP	FALSE	FALSE
compatible	10.2.0.1.0	FALSE	FALSE
control_files	C:\ORADATA\TEN\CONTROL01.CTL, C:\ORADATA\TEN\CONTROL02.CTL, C:\ORADATA\TEN\CONTROL03.CTL	FALSE	FALSE
core_dump_dest	C:\PROGRAMME\ORACLE\PRODUCT\ADMIN\TEN\CDUMP	FALSE	FALSE
create_bitmap_area_size	8388608	TRUE	FALSE
db_block_size	8192	FALSE	FALSE
db_cache_size	28M	FALSE	FALSE
db_domain	oradbpro.com	FALSE	FALSE
db_file_multiblock_read_count	16	FALSE	FALSE
db_name	TEN	FALSE	FALSE
db_16k_cache_size	52M	FALSE	FALSE
db_2k_cache_size	8M	FALSE	FALSE
disk_asynch_io	TRUE	TRUE	FALSE
dispatchers	(protocol=tcp)(dispatchers=1)	FALSE	FALSE
hash_area_size	131072	TRUE	FALSE
java_pool_size	32M	FALSE	FALSE
job_queue_processes	1	FALSE	FALSE
large_pool_size	0	FALSE	FALSE
local_listener	listener.oradbpro.com	FALSE	FALSE
log_archive_dest_1	location=c:\temp	FALSE	FALSE
log_buffer	6984704	TRUE	FALSE
max_dump_file_size	UNLIMITED	TRUE	FALSE
open_cursors	300	FALSE	FALSE
optimizer_dynamic_sampling	2	TRUE	FALSE
optimizer_features_enable	10.2.0.3	TRUE	FALSE
optimizer_index_caching	0	TRUE	FALSE
optimizer_index_cost_adj	100	TRUE	FALSE
optimizer_mode	ALL_ROWS	TRUE	FALSE
optimizer_secure_view_merging	TRUE	TRUE	FALSE
parallel_execution_message_size	2148	TRUE	FALSE
pga_aggregate_target	256M	FALSE	FALSE
processes	50	FALSE	FALSE
resource_limit	TRUE	FALSE	FALSE
resource_manager_plan	SYSTEM_PLAN	FALSE	FALSE
service_names	TEN.oradbpro.com	FALSE	FALSE
shared_pool_reserved_size	10M	TRUE	FALSE
shared_pool_size	200M	FALSE	FALSE
shared_servers	0	FALSE	FALSE
sort_area_size	65536	TRUE	FALSE
statistics_level	ALL	FALSE	MODIFIED
streams_pool_size	20M	FALSE	FALSE
undo_management	AUTO	FALSE	FALSE
undo_tablespace	UNDOTBS1	FALSE	FALSE
user_dump_dest	C:\PROGRAMME\ORACLE\PRODUCT\ADMIN\TEN\UDUMP	FALSE	FALSE

System Statistics

Gathered between 2008-10-21 20:50:00.0 and 2008-10-21 20:50:00.0

Parameter Value Description
cpuspeednw 1386.2 Noworkload CPU speed (million operations/s)
ioseektim 10.0 I/O seek time (ms)
iotfrspeed 4096.0 I/O transfer speed (bytes/ms)
cpuspeed undefined Workload CPU speed (million operations/s)
maxthr undefined Maximum I/O system throughput (bytes/s)
mbrc undefined Average mutli block read count
mreadtim undefined Multiblock read time (ms/block)
slavethr undefined Maximum throughput of a parallel execution slave (bytes/s)
sreadtim undefined Single block read time (ms)

Parameter	Value	Description
cpuspeednw	1386.2	Noworkload CPU speed (million operations/s)
ioseektim	10.0	I/O seek time (ms)
iotfrspeed	4096.0	I/O transfer speed (bytes/ms)
cpuspeed	undefined	Workload CPU speed (million operations/s)
maxthr	undefined	Maximum I/O system throughput (bytes/s)
mbrc	undefined	Average mutli block read count
mreadtim	undefined	Multiblock read time (ms/block)
slavethr	undefined	Maximum throughput of a parallel execution slave (bytes/s)
sreadtim	undefined	Single block read time (ms)

Results for Individual Statements

Distinct statements: 29

Top Statements

Service time is the response time spent in the DBMS server plus IPC latency wait time. Thus, service time excludes think time. Service time is the metric used to rank the statements.
The statement response time R includes think time.
Percentage of statement contribution is reported based on delta tim.
Only statements at recursive call depth zero (dep=0) are shown.
Statements at ranks higher than 10 that contribute less than 1.0% to the total service time are not listed
The statement text is truncated to at most 80 characters.

Rank Hash Value/Cursor SQL ID Service Time Percent R (s) Percent Statement Text
1 966758382 1xxksrhwtz3zf 334.128 65.59 % 337.157 66.18 % BEGIN :1 := orderentry.neworder(:2,:3,:4); END;
2 1631089791 07p193phmhx3z 60.631 11.90 % 63.083 12.38 % BEGIN :1 := orderentry.browseproducts(:2,:3,:4); END;
3 4030344732 c0q36xbs3nbhw 46.871 9.20 % 49.250 9.67 % BEGIN :1 := orderentry.browseandupdateorders(:2,:3,:4); END;
4 3589721925 9hjz3yrazdmu5 33.270 6.53 % 35.817 7.03 % BEGIN :1 := orderentry.newcustomer(:2,:3,:4,:5,:6,:7); END;
5 2086907756 5v8hhf9y67bvc 20.181 3.96 % 22.892 4.49 % BEGIN :1 := orderentry.processorders(:2,:3); END;
6 15 0.042 0.01 % 0.042 0.01 % Note: statement text unavailable due to absence of parse call from trace file
Total 495.122 97.19 % 508.240 99.76 %

Rank	Hash Value/Cursor	SQL ID	Service Time	Percent	R (s)	Percent	Statement Text
1	966758382	1xxksrhwtz3zf	334.128	65.59 %	337.157	66.18 %	BEGIN :1 := orderentry.neworder(:2,:3,:4); END;
2	1631089791	07p193phmhx3z	60.631	11.90 %	63.083	12.38 %	BEGIN :1 := orderentry.browseproducts(:2,:3,:4); END;
3	4030344732	c0q36xbs3nbhw	46.871	9.20 %	49.250	9.67 %	BEGIN :1 := orderentry.browseandupdateorders(:2,:3,:4); END;
4	3589721925	9hjz3yrazdmu5	33.270	6.53 %	35.817	7.03 %	BEGIN :1 := orderentry.newcustomer(:2,:3,:4,:5,:6,:7); END;
5	2086907756	5v8hhf9y67bvc	20.181	3.96 %	22.892	4.49 %	BEGIN :1 := orderentry.processorders(:2,:3); END;
6	15		0.042	0.01 %	0.042	0.01 %	Note: statement text unavailable due to absence of parse call from trace file
Total			495.122	97.19 %	508.240	99.76 %

Rank 1: Statement with Hash Value 966758382

Response Time Including Think Time: 337.157 s (66.18 % of delta tim)
Response Time Excluding Think Time: 334.128 s (65.59 % of delta tim)
Recursive Elapsed Time: 222.887 s
SQL ID: 1xxksrhwtz3zf
Force Matching Signature: 0
Command Type: 47 (PL/SQL Block)
Parsing User ID: 78 (SOE)
Parsing Schema ID: 78 (SOE)
Optimizer Goal: 1 (ALL_ROWS)
Recursive Call Depth: 0
Waits: 7008
Total Wait Time: 59.915 s
Intra Database Call Wait Time: 56.338 s
Inter Database Call Wait Time: 3.578 s
IPC latency wait time: 0.549 s
Line Number: 29

Statement Text

BEGIN :1 := orderentry.neworder(:2,:3,:4); END;

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
CPU	184.765625	54.8	1194	0.154745
unknown	92.475548	27.4	1	92.475548
PL/SQL lock timer	56.337296	16.7	5330	0.010570
SQL*Net message from client	2.959790	0.9	597	0.004958
think time	0.613991	0.2	483	0.001271
SQL*Net message to client	0.004076	0.0	597	0.000007
latch free	0.000306	0.0	1	0.000306
Total	337.156586	100.0	8203	0.041102

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	597	0.016	0.000027	0.000	0.000000	0	0	0	0	0
EXEC	597	333.563	0.558732	184.766	0.309490	1841	1357351	42464	597	0
FETCH	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
Total	1194	333.579	0.279379	184.766	0.154745	1841	1357351	42464	597	0
Avg. per EXEC	2.000	0.558758	n/a	0.309490	n/a	3.084	2273.620	71.129	1.000	0.000
Avg. per Row	2.000	0.558758	n/a	0.309490	n/a	3.084	2273.620	71.129	1.000	0.000

Recursive Descendants

The statement has 10 recursive descendants that have contributed an elapsed time of 222.887 s. For each recursive statement, an excerpt of at most 60 characters from the statement text is shown.

Hash Value Elapsed Time (s) Parsing ID Statement Text (Excerpt)
2863564559 189.555 78 (SOE) SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTIO
2084491117 15.531 78 (SOE) SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LAN
2637862082 9.927 78 (SOE) SELECT QUANTITY_ON_HAND FROM PRODUCT_INFORMATION P, INVENTOR
2236920324 7.722 78 (SOE) INSERT INTO ORDER_ITEMS(ORDER_ID, LINE_ITEM_ID, PRODUCT_ID,
255718823 0.089 78 (SOE) COMMIT
3524147527 0.044 78 (SOE) INSERT INTO ORDERS(ORDER_ID, ORDER_DATE, CUSTOMER_ID) VALUES
124468195 0.016 78 (SOE) SELECT SYSDATE FROM DUAL
4196474773 0.001 78 (SOE) INSERT INTO LOGON VALUES (:B2 , :B1 )
1344628361 0.001 78 (SOE) UPDATE INVENTORIES SET QUANTITY_ON_HAND = QUANTITY_ON_HAND -
2990726627 0.001 78 (SOE) UPDATE ORDERS SET ORDER_MODE = 'online', ORDER_STATUS = FLOO

Hash Value	Elapsed Time (s)	Parsing ID	Statement Text (Excerpt)
2863564559	189.555	78 (SOE)	SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTIO
2084491117	15.531	78 (SOE)	SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LAN
2637862082	9.927	78 (SOE)	SELECT QUANTITY_ON_HAND FROM PRODUCT_INFORMATION P, INVENTOR
2236920324	7.722	78 (SOE)	INSERT INTO ORDER_ITEMS(ORDER_ID, LINE_ITEM_ID, PRODUCT_ID,
255718823	0.089	78 (SOE)	COMMIT
3524147527	0.044	78 (SOE)	INSERT INTO ORDERS(ORDER_ID, ORDER_DATE, CUSTOMER_ID) VALUES
124468195	0.016	78 (SOE)	SELECT SYSDATE FROM DUAL
4196474773	0.001	78 (SOE)	INSERT INTO LOGON VALUES (:B2 , :B1 )
1344628361	0.001	78 (SOE)	UPDATE INVENTORIES SET QUANTITY_ON_HAND = QUANTITY_ON_HAND -
2990726627	0.001	78 (SOE)	UPDATE ORDERS SET ORDER_MODE = 'online', ORDER_STATUS = FLOO

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 152401265 0 0 3522870812 15-Nov-09 19:47:59
1 152401265 0 0 3750204916 15-Nov-09 20:01:17

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 75.801 176.586 0 .014 0 94.299 9.695 0 3116 0 3116 0
1 106.953 222.731 0 0 0 65.184 8.884 0 2122 0 2124 0

CHILD_NUMBER	OLD_HASH_VALUE	FORCE_MATCHING_SIGNATURE	PLAN_HASH_VALUE	OPTIMIZER_ENV_HASH_VALUE	LAST_ACTIVE_TIME	SQL_PROFILE	OUTLINE_CATEGORY	OUTLINE_TYPE
0	152401265	0	0	3522870812	15-Nov-09 19:47:59
1	152401265	0	0	3750204916	15-Nov-09 20:01:17

CHILD_NUMBER	CPU Time (s)	Elapsed (s)	App. Wait Time (s)	Conc. Wait Time (s)	Cluster Wait Time (s)	User I/O Wait Time (s)	PL/SQL Exec. Time (s)	Java Exec. Time (s)	EXECUTIONS	SORTS	PARSE_CALLS	OPTIMIZER_COST
0	75.801	176.586	0	.014	0	94.299	9.695	0	3116	0	3116	0
1	106.953	222.731	0	0	0	65.184	8.884	0	2122	0	2124	0

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	SQL_PROFILE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2427	2870266532	1	0	0	ALL_ROWS	3750204916		.401429	.296318	2329.5
2424	2870266532	1	0	0	ALL_ROWS	3522870812		.054758	.023659	2331.8

Captured Bind Variables

Note: trace file contains 597 bind section(s) (report limited to 10)

Bind Section 1:

Position	Type	Value
0
1	NUMBER	413006
2	NUMBER	4
3	NUMBER	6

Bind Section 2:

Position	Type	Value
0
1	NUMBER	146609
2	NUMBER	4
3	NUMBER	6

Bind Section 3:

Position	Type	Value
0
1	NUMBER	398168
2	NUMBER	4
3	NUMBER	6

Bind Section 4:

Position	Type	Value
0
1	NUMBER	912983
2	NUMBER	4
3	NUMBER	6

Bind Section 5:

Position	Type	Value
0
1	NUMBER	299795
2	NUMBER	4
3	NUMBER	6

Bind Section 6:

Position	Type	Value
0
1	NUMBER	988201
2	NUMBER	4
3	NUMBER	6

Bind Section 7:

Position	Type	Value
0
1	NUMBER	174073
2	NUMBER	4
3	NUMBER	6

Bind Section 8:

Position	Type	Value
0
1	NUMBER	114327
2	NUMBER	4
3	NUMBER	6

Bind Section 9:

Position	Type	Value
0
1	NUMBER	669288
2	NUMBER	4
3	NUMBER	6

Bind Section 10:

Position	Type	Value
0
1	NUMBER	177360
2	NUMBER	4
3	NUMBER	6

Rank 2: Statement with Hash Value 2863564559

Response Time: 192.387 s (37.76 % of delta tim)
SQL ID: dw2zgaapax1sg
Force Matching Signature: 3138049466602010507
MD5 Hash Value: 3A7DCCD2344B7F8D7C3C39C1B56E88C5
Command Type: 3 (SELECT)
Parsing User ID: 78 (SOE)
Parsing Schema ID: 78 (SOE)
Optimizer Goal: 1 (ALL_ROWS)
Recursive Call Depth: 1
Module: 'New Order'
Action: 'getProductDetailsByCategory'
Waits: 3
Total Wait Time: 0.002 s
Intra Database Call Wait Time: 0.002 s
Line Number: 115

Statement Text

SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, CATEGORY_ID, WEIGHT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID, PRODUCT_STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL, QUANTITY_ON_HAND FROM PRODUCTS, INVENTORIES WHERE PRODUCTS.CATEGORY_ID = :B1 AND INVENTORIES.PRODUCT_ID = PRODUCTS.PRODUCT_ID ORDER BY INVENTORIES.WAREHOUSE_ID

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
CPU	176.390625	91.7	3616	0.048781
unknown	15.994375	8.3	1	15.994375
latch free	0.002338	0.0	1	0.002338
latch: cache buffers chains	0.000047	0.0	1	0.000047
latch: row cache objects	0.000012	0.0	1	0.000012
Total	192.387390	100.0	3620	0.053146

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
EXEC	1808	10.588	0.005856	0.891	0.000493	0	0	0	0	0
FETCH	1808	181.799	0.100553	175.500	0.097069	0	1342062	0	180800	0
Total	3616	192.387	0.053204	176.391	0.048781	0	1342062	0	180800	0
Avg. per EXEC	2.000	0.106409	n/a	0.097561	n/a	0.000	742.291	0.000	100.000	0.000
Avg. per FETCH	2.000	0.106409	n/a	0.097561	n/a	0.000	742.291	0.000	100.000	0.000
Avg. per Row	0.020	0.001064	n/a	0.000976	n/a	0.000	7.423	0.000	1.000	0.000

Execution Plan 1 (1 occurrence(s)):

ID	PID	Rows	Operation	Ela. (Self)	Ela. (Cum.)	CR (Self)	CR (Cum.)	PR (Self)	PR (Cum.)
1	0	196900	SORT ORDER BY	9.117331	196.017914	0	1461541	0	247
2	1	1137680	HASH JOIN	128.150055	186.900589	0	1461541	0	247
3	2	56884	NESTED LOOPS OUTER	0.750809	2.791523	0	27566	0	0
4	3	56884	TABLE ACCESS FULL PRODUCT_INFORMATION	0.526682	0.526682	23628	23628	0	0
5	3	0	TABLE ACCESS BY INDEX ROWID PRODUCT_DESCRIPTIONS	0.871611	1.514032	0	3938	0	0
6	5	0	INDEX UNIQUE SCAN PRD_DESC_PK	0.642421	0.642421	3938	3938	0	0
7	2	11341440	TABLE ACCESS FULL INVENTORIES	55.959023	55.959023	1433975	1433975	247	247

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 3803592478 3138049466602010507 2108007948 3522870812 15-Nov-09 19:47:59
1 3803592478 3138049466602010507 2108007948 3750204916 15-Nov-09 20:01:17

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 105.449 119.373 0 0 0 .108 0 0 15461 15461 2 235
1 36.696 41.496 0 0 0 .274 0 0 412 412 2 235

CHILD_NUMBER	OLD_HASH_VALUE	FORCE_MATCHING_SIGNATURE	PLAN_HASH_VALUE	OPTIMIZER_ENV_HASH_VALUE	LAST_ACTIVE_TIME	SQL_PROFILE	OUTLINE_CATEGORY	OUTLINE_TYPE
0	3803592478	3138049466602010507	2108007948	3522870812	15-Nov-09 19:47:59
1	3803592478	3138049466602010507	2108007948	3750204916	15-Nov-09 20:01:17

CHILD_NUMBER	CPU Time (s)	Elapsed (s)	App. Wait Time (s)	Conc. Wait Time (s)	Cluster Wait Time (s)	User I/O Wait Time (s)	PL/SQL Exec. Time (s)	Java Exec. Time (s)	EXECUTIONS	SORTS	PARSE_CALLS	OPTIMIZER_COST
0	105.449	119.373	0	0	0	.108	0	0	15461	15461	2	235
1	36.696	41.496	0	0	0	.274	0	0	412	412	2	235

Correlation with V$SQL_PLAN_STATISTICS_ALL using DBMS_XPLAN.DISPLAY_CURSOR

SQL_ID  dw2zgaapax1sg, child number 0
-------------------------------------
SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, CATEGORY_ID, WEIGHT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID, 
PRODUCT_STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL, QUANTITY_ON_HAND FROM PRODUCTS, INVENTORIES WHERE PRODUCTS.CATEGORY_ID = :B1 
AND INVENTORIES.PRODUCT_ID = PRODUCTS.PRODUCT_ID ORDER BY INVENTORIES.WAREHOUSE_ID
 
Plan hash value: 2108007948
 
-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                 | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY                 |                      |    580 |   288K|   632K|   235   (1)| 00:00:03 |   302K|   302K|  268K (0)|
|*  2 |   HASH JOIN                    |                      |    580 |   288K|       |   169   (1)| 00:00:03 |   690K|   690K| 1177K (0)|
|   3 |    NESTED LOOPS OUTER          |                      |     29 | 14500 |       |     4   (0)| 00:00:01 |       |       |          |
|*  4 |     TABLE ACCESS FULL          | PRODUCT_INFORMATION  |     29 |  6351 |       |     4   (0)| 00:00:01 |       |       |          |
|   5 |     TABLE ACCESS BY INDEX ROWID| PRODUCT_DESCRIPTIONS |      1 |   281 |       |     0   (0)|          |       |       |          |
|*  6 |      INDEX UNIQUE SCAN         | PRD_DESC_PK          |      1 |       |       |     0   (0)|          |       |       |          |
|   7 |    TABLE ACCESS FULL           | INVENTORIES          |   5760 | 57600 |       |   164   (0)| 00:00:02 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$F5BB74E1
   4 - SEL$F5BB74E1 / I@SEL$2
   5 - SEL$F5BB74E1 / D@SEL$2
   6 - SEL$F5BB74E1 / D@SEL$2
   7 - SEL$F5BB74E1 / INVENTORIES@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$F5BB74E1" "I"@"SEL$2")
      INDEX_RS_ASC(@"SEL$F5BB74E1" "D"@"SEL$2" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))
      FULL(@"SEL$F5BB74E1" "INVENTORIES"@"SEL$1")
      LEADING(@"SEL$F5BB74E1" "I"@"SEL$2" "D"@"SEL$2" "INVENTORIES"@"SEL$1")
      USE_NL(@"SEL$F5BB74E1" "D"@"SEL$2")
      USE_HASH(@"SEL$F5BB74E1" "INVENTORIES"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - (NUMBER): 1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("INVENTORIES"."PRODUCT_ID"="I"."PRODUCT_ID")
   4 - filter("I"."CATEGORY_ID"=:B1)
   6 - access("D"."PRODUCT_ID"="I"."PRODUCT_ID" AND "D"."LANGUAGE_ID"=SYS_CONTEXT('USERENV','LANG'))
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Execution Plans Captured by Statspack

Old hash value: 3803592478
SQL ID: dw2zgaapax1sg

Execution plan with plan hash value 2108007948 (last active 15-Nov-09 20:09:28)
Optimization: ALL_ROWS
Cost: 235
Statspack snapshot 483 of instance 1

ID PID Operation Object Object ID Rows Bytes Cost I/O Cost Temp. Space
0 SELECT STATEMENT 235
1 0 SORT ORDER BY 580 295800 235 233 648000
2 1   HASH JOIN 580 295800 169 168
3 2    NESTED LOOPS OUTER 29 14500 4 4
4 3     TABLE ACCESS FULL SOE.PRODUCT_INFORMATION 60741 29 6351 4 4
5 3     TABLE ACCESS BY INDEX ROWID SOE.PRODUCT_DESCRIPTIONS 60743 1 281 0 0
6 5      INDEX UNIQUE SCAN SOE.PRD_DESC_PK 60770 1 0 0
7 2    TABLE ACCESS FULL SOE.INVENTORIES 60740 5760 57600 164 164

ID	PID	Operation	Object	Object ID	Rows	Bytes	Cost	I/O Cost	Temp. Space
0		SELECT STATEMENT					235
1	0	SORT ORDER BY			580	295800	235	233	648000
2	1	HASH JOIN			580	295800	169	168
3	2	NESTED LOOPS OUTER			29	14500	4	4
4	3	TABLE ACCESS FULL	SOE.PRODUCT_INFORMATION	60741	29	6351	4	4
5	3	TABLE ACCESS BY INDEX ROWID	SOE.PRODUCT_DESCRIPTIONS	60743	1	281	0	0
6	5	INDEX UNIQUE SCAN	SOE.PRD_DESC_PK	60770	1		0	0
7	2	TABLE ACCESS FULL	SOE.INVENTORIES	60740	5760	57600	164	164

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	SQL_PROFILE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2427	2870266532	1	2108007948	235	ALL_ROWS	3750204916		.102932	.091322	742.4
2424	2870266532	1	2108007948	235	ALL_ROWS	3522870812		.006799	.006344	742

AWR execution plan with plan hash value 2108007948 for SQL ID dw2zgaapax1sg

SQL_ID dw2zgaapax1sg
--------------------
SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, CATEGORY_ID, WEIGHT_CLASS, 
WARRANTY_PERIOD, SUPPLIER_ID, PRODUCT_STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL, QUANTITY_ON_HAND FROM 
PRODUCTS, INVENTORIES WHERE PRODUCTS.CATEGORY_ID = :B1 AND INVENTORIES.PRODUCT_ID = PRODUCTS.PRODUCT_ID 
ORDER BY INVENTORIES.WAREHOUSE_ID
 
Plan hash value: 2108007948
 
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                 | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                      |        |       |       |   235 (100)|          |
|   1 |  SORT ORDER BY                 |                      |    580 |   288K|   632K|   235   (1)| 00:00:03 |
|   2 |   HASH JOIN                    |                      |    580 |   288K|       |   169   (1)| 00:00:03 |
|   3 |    NESTED LOOPS OUTER          |                      |     29 | 14500 |       |     4   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL          | PRODUCT_INFORMATION  |     29 |  6351 |       |     4   (0)| 00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID| PRODUCT_DESCRIPTIONS |      1 |   281 |       |     0   (0)|          |
|   6 |      INDEX UNIQUE SCAN         | PRD_DESC_PK          |      1 |       |       |     0   (0)|          |
|   7 |    TABLE ACCESS FULL           | INVENTORIES          |   5760 | 57600 |       |   164   (0)| 00:00:02 |
----------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$F5BB74E1
   4 - SEL$F5BB74E1 / I@SEL$2
   5 - SEL$F5BB74E1 / D@SEL$2
   6 - SEL$F5BB74E1 / D@SEL$2
   7 - SEL$F5BB74E1 / INVENTORIES@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$F5BB74E1" "I"@"SEL$2")
      INDEX_RS_ASC(@"SEL$F5BB74E1" "D"@"SEL$2" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" 
              "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))
      FULL(@"SEL$F5BB74E1" "INVENTORIES"@"SEL$1")
      LEADING(@"SEL$F5BB74E1" "I"@"SEL$2" "D"@"SEL$2" "INVENTORIES"@"SEL$1")
      USE_NL(@"SEL$F5BB74E1" "D"@"SEL$2")
      USE_HASH(@"SEL$F5BB74E1" "INVENTORIES"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - :B1 (NUMBER): 1
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Captured Bind Variables

Note: trace file contains 1807 bind section(s) (report limited to 10)

Bind Section 1:

Position	Type	Value
0	NUMBER	7

Bind Section 2:

Position	Type	Value
0	NUMBER	1

Bind Section 3:

Position	Type	Value
0	NUMBER	4

Bind Section 4:

Position	Type	Value
0	NUMBER	7

Bind Section 5:

Position	Type	Value
0	NUMBER	9

Bind Section 6:

Position	Type	Value
0	NUMBER	3

Bind Section 7:

Position	Type	Value
0	NUMBER	5

Bind Section 8:

Position	Type	Value
0	NUMBER	1

Bind Section 9:

Position	Type	Value
0	NUMBER	9

Bind Section 10:

Position	Type	Value
0	NUMBER	8

Rank 3: Statement with Hash Value 2084491117

Response Time: 65.339 s (12.83 % of delta tim)
SQL ID: 75621g9y3xmvd
Force Matching Signature: 9616035054348117015
MD5 Hash Value: 826587046C2C6BDA8E44DAEF2DF4FF0
Command Type: 3 (SELECT)
Parsing User ID: 78 (SOE)
Parsing Schema ID: 78 (SOE)
Optimizer Goal: 1 (ALL_ROWS)
Recursive Call Depth: 1
Module: 'New Order'
Action: 'getCustomerDetails'
Waits: 2994
Total Wait Time: 43.867 s
Intra Database Call Wait Time: 43.867 s
Line Number: 64

Statement Text

SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LANGUAGE, NLS_TERRITORY, CREDIT_LIMIT, CUST_EMAIL, ACCOUNT_MGR_ID FROM CUSTOMERS WHERE CUSTOMER_ID = :B1

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
db file sequential read	43.866928	67.1	2993	0.014657
unknown	19.909872	30.5	1	19.909872
CPU	1.562500	2.4	4524	0.000345
latch: cache buffers lru chain	0.000048	0.0	1	0.000048
Total	65.339378	100.0	7519	0.008690

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
EXEC	2262	20.972	0.009271	1.203	0.000532	0	0	0	0	0
FETCH	2262	44.367	0.019614	0.359	0.000159	2993	9056	0	2262	0
Total	4524	65.339	0.014443	1.563	0.000345	2993	9056	0	2262	0
Avg. per EXEC	2.000	0.028886	n/a	0.000691	n/a	1.323	4.004	0.000	1.000	0.000
Avg. per FETCH	2.000	0.028886	n/a	0.000691	n/a	1.323	4.004	0.000	1.000	0.000
Avg. per Row	2.000	0.028886	n/a	0.000691	n/a	1.323	4.004	0.000	1.000	0.000

Execution Plan 1 (1 occurrence(s)):

ID	PID	Rows	Operation	Ela. (Self)	Ela. (Cum.)	CR (Self)	CR (Cum.)	PR (Self)	PR (Cum.)	PW (Self)	PW (Cum.)
1	0	2476	TABLE ACCESS BY INDEX ROWID CUSTOMERS	26.244965	47.947411	2477	9916	1793	3257	0	0
2	1	2476	INDEX UNIQUE SCAN CUSTOMERS_PK	21.702448	21.702448	7439	7439	1464	1464	0	0

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 3623764046 9616035054348117015 4238351645 3522870812 15-Nov-09 19:47:59
1 3623764046 9616035054348117015 4238351645 3750204916 15-Nov-09 20:01:17
2 3623764046 9616035054348117015 4238351645 3750204916 15-Nov-09 20:01:17

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 23.61 354.895 0 0 0 333.697 0 0 20203 0 2 3
1 .248 8.243 0 0 0 8.198 0 0 314 0 1 3
2 .279 5.511 0 0 0 4.547 0 0 246 0 1 3

CHILD_NUMBER	OLD_HASH_VALUE	FORCE_MATCHING_SIGNATURE	PLAN_HASH_VALUE	OPTIMIZER_ENV_HASH_VALUE	LAST_ACTIVE_TIME
0	3623764046	9616035054348117015	4238351645	3522870812	15-Nov-09 19:47:59
1	3623764046	9616035054348117015	4238351645	3750204916	15-Nov-09 20:01:17
2	3623764046	9616035054348117015	4238351645	3750204916	15-Nov-09 20:01:17

CHILD_NUMBER	CPU Time (s)	Elapsed (s)	User I/O Wait Time (s)	EXECUTIONS	PARSE_CALLS	OPTIMIZER_COST
0	23.61	354.895	333.697	20203	2	3
1	.248	8.243	8.198	314	1	3
2	.279	5.511	4.547	246	1	3

Correlation with V$SQL_PLAN_STATISTICS_ALL using DBMS_XPLAN.DISPLAY_CURSOR

SQL_ID  75621g9y3xmvd, child number 0
-------------------------------------
SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LANGUAGE, NLS_TERRITORY, 
CREDIT_LIMIT, CUST_EMAIL, ACCOUNT_MGR_ID FROM CUSTOMERS WHERE CUSTOMER_ID = :B1
 
Plan hash value: 4238351645
 
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |      1 |    66 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | CUSTOMERS_PK |      1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / CUSTOMERS@SEL$1
   2 - SEL$1 / CUSTOMERS@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "CUSTOMERS"@"SEL$1" ("CUSTOMERS"."CUSTOMER_ID"))
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - (NUMBER): 74530
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CUSTOMER_ID"=:B1)
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Execution Plans Captured by Statspack

Old hash value: 3623764046
SQL ID: 75621g9y3xmvd

Execution plan with plan hash value 4238351645 (last active 15-Nov-09 20:09:28)
Optimization: ALL_ROWS
Cost: 3
Statspack snapshot 483 of instance 1

ID PID Operation Object Object ID Rows Bytes Cost I/O Cost Temp. Space
0 SELECT STATEMENT 3
1 0 TABLE ACCESS BY INDEX ROWID SOE.CUSTOMERS 60736 1 66 3 3
2 1 INDEX UNIQUE SCAN SOE.CUSTOMERS_PK 60764 1 2 2

ID	PID	Operation	Object	Object ID	Rows	Bytes	Cost	I/O Cost
0		SELECT STATEMENT					3
1	0	TABLE ACCESS BY INDEX ROWID	SOE.CUSTOMERS	60736	1	66	3	3
2	1	INDEX UNIQUE SCAN	SOE.CUSTOMERS_PK	60764	1		2	2

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	SQL_PROFILE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2427	2870266532	1	4238351645	3	ALL_ROWS	3750204916		.024276	.000991	4
2424	2870266532	1	4238351645	3	ALL_ROWS	3522870812		.016533	.000673	4

AWR execution plan with plan hash value 4238351645 for SQL ID 75621g9y3xmvd

SQL_ID 75621g9y3xmvd
--------------------
SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LANGUAGE, NLS_TERRITORY, 
CREDIT_LIMIT, CUST_EMAIL, ACCOUNT_MGR_ID FROM CUSTOMERS WHERE CUSTOMER_ID = :B1
 
Plan hash value: 4238351645
 
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |        |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |      1 |    66 |     3   (0)| 00:00:01 |
|   2 |   INDEX UNIQUE SCAN         | CUSTOMERS_PK |      1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / CUSTOMERS@SEL$1
   2 - SEL$1 / CUSTOMERS@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "CUSTOMERS"@"SEL$1" ("CUSTOMERS"."CUSTOMER_ID"))
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - :B1 (NUMBER): 74530
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Physical Reads by Database Object

Obj. ID	SB Reads	SBR Time	Avg. SBR Time	MB Reads	MBR Time	Avg. MBR Time	MBR Blocks	Avg. Blocks	Owner & Segment
60736	1630	23.724	0.015	0	0.000	0.000	0	0.0	SOE.CUSTOMERS
60764	1363	20.143	0.015	0	0.000	0.000	0	0.0	SOE.CUSTOMERS_PK

Captured Bind Variables

Note: trace file contains 2261 bind section(s) (report limited to 10)

Bind Section 1:

Position	Type	Value
0	NUMBER	146609

Bind Section 2:

Position	Type	Value
0	NUMBER	16803

Bind Section 3:

Position	Type	Value
0	NUMBER	1084193

Bind Section 4:

Position	Type	Value
0	NUMBER	1084194

Bind Section 5:

Position	Type	Value
0	NUMBER	398168

Bind Section 6:

Position	Type	Value
0	NUMBER	912983

Bind Section 7:

Position	Type	Value
0	NUMBER	1084201

Bind Section 8:

Position	Type	Value
0	NUMBER	319705

Bind Section 9:

Position	Type	Value
0	NUMBER	299795

Bind Section 10:

Position	Type	Value
0	NUMBER	988201

Rank 4: Statement with Hash Value 1631089791

Response Time Including Think Time: 63.083 s (12.38 % of delta tim)
Response Time Excluding Think Time: 60.631 s (11.90 % of delta tim)
Recursive Elapsed Time: 19.755 s
SQL ID: 07p193phmhx3z
Force Matching Signature: 0
Command Type: 47 (PL/SQL Block)
Parsing User ID: 78 (SOE)
Parsing Schema ID: 78 (SOE)
Optimizer Goal: 1 (ALL_ROWS)
Recursive Call Depth: 0
Module: 'New Order'
Action: 'getProductQuantity'
Waits: 3099
Total Wait Time: 19.657 s
Intra Database Call Wait Time: 16.625 s
Inter Database Call Wait Time: 3.032 s
IPC latency wait time: 0.581 s
Line Number: 3770

Statement Text

BEGIN :1 := orderentry.browseproducts(:2,:3,:4); END;

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
unknown	38.597214	61.2	1	38.597214
PL/SQL lock timer	16.625151	26.4	1589	0.010463
CPU	4.828125	7.7	1088	0.004438
SQL*Net message from client	2.687899	4.3	544	0.004941
think time	0.341218	0.5	422	0.000809
SQL*Net message to client	0.003070	0.0	544	0.000006
Total	63.082676	100.0	4188	0.015063

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	544	0.015	0.000027	0.000	0.000000	0	0	0	0	0
EXEC	544	60.036	0.110360	4.828	0.008875	968	42110	2101	544	0
FETCH	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
Total	1088	60.050	0.055193	4.828	0.004438	968	42110	2101	544	0
Avg. per EXEC	2.000	0.110387	n/a	0.008875	n/a	1.779	77.408	3.862	1.000	0.000
Avg. per Row	2.000	0.110387	n/a	0.008875	n/a	1.779	77.408	3.862	1.000	0.000

Recursive Descendants

The statement has 5 recursive descendants that have contributed an elapsed time of 19.755 s. For each recursive statement, an excerpt of at most 60 characters from the statement text is shown.

Hash Value Elapsed Time (s) Parsing ID Statement Text (Excerpt)
2084491117 14.237 78 (SOE) SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LAN
622695190 5.506 78 (SOE) SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTIO
124468195 0.012 78 (SOE) SELECT SYSDATE FROM DUAL
4196474773 0.000 78 (SOE) INSERT INTO LOGON VALUES (:B2 , :B1 )
255718823 0.000 78 (SOE) COMMIT

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 1094891389 0 0 3522870812 15-Nov-09 19:47:59
1 1094891389 0 0 3750204916 15-Nov-09 20:01:18

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 8.81 80.374 0 0 0 71.303 3.751 0 3117 0 3117 0
1 13.198 81.098 0 0 0 46.319 3.193 0 2085 0 2085 0

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	SQL_PROFILE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2427	2870266532	1	0	0	ALL_ROWS	3750204916		.052338	.005842	82.2
2424	2870266532	1	0	0	ALL_ROWS	3522870812		.025131	.00281	83.4

Captured Bind Variables

Note: trace file contains 544 bind section(s) (report limited to 10)

Bind Section 1:

Position	Type	Value
0
1	NUMBER	103369
2	NUMBER	4
3	NUMBER	6

Bind Section 2:

Position	Type	Value
0
1	NUMBER	432449
2	NUMBER	4
3	NUMBER	6

Bind Section 3:

Position	Type	Value
0
1	NUMBER	82535
2	NUMBER	4
3	NUMBER	6

Bind Section 4:

Position	Type	Value
0
1	NUMBER	259806
2	NUMBER	4
3	NUMBER	6

Bind Section 5:

Position	Type	Value
0
1	NUMBER	73960
2	NUMBER	4
3	NUMBER	6

Bind Section 6:

Position	Type	Value
0
1	NUMBER	268737
2	NUMBER	4
3	NUMBER	6

Bind Section 7:

Position	Type	Value
0
1	NUMBER	238642
2	NUMBER	4
3	NUMBER	6

Bind Section 8:

Position	Type	Value
0
1	NUMBER	165341
2	NUMBER	4
3	NUMBER	6

Bind Section 9:

Position	Type	Value
0
1	NUMBER	114913
2	NUMBER	4
3	NUMBER	6

Bind Section 10:

Position	Type	Value
0
1	NUMBER	112828
2	NUMBER	4
3	NUMBER	6

Rank 5: Statement with Hash Value 4030344732

Response Time Including Think Time: 49.250 s (9.67 % of delta tim)
Response Time Excluding Think Time: 46.871 s (9.20 % of delta tim)
Recursive Elapsed Time: 15.329 s
SQL ID: c0q36xbs3nbhw
Force Matching Signature: 0
Command Type: 47 (PL/SQL Block)
Parsing User ID: 78 (SOE)
Parsing Schema ID: 78 (SOE)
Optimizer Goal: 1 (ALL_ROWS)
Recursive Call Depth: 0
Module: 'New Order'
Action: 'getProductQuantity'
Waits: 2228
Total Wait Time: 10.279 s
Intra Database Call Wait Time: 7.187 s
Inter Database Call Wait Time: 3.092 s
IPC latency wait time: 0.713 s
Line Number: 689

Statement Text

BEGIN :1 := orderentry.browseandupdateorders(:2,:3,:4); END;

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
unknown	36.580395	74.3	1	36.580395
PL/SQL lock timer	7.186737	14.6	690	0.010416
SQL*Net message from client	2.774911	5.6	562	0.004938
CPU	2.390625	4.9	1124	0.002127
think time	0.313827	0.6	413	0.000760
SQL*Net message to client	0.003233	0.0	562	0.000006
log file sync	0.000256	0.0	1	0.000256
Total	49.249985	100.0	3353	0.014688

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	562	0.016	0.000028	0.000	0.000000	0	0	0	0	0
EXEC	562	46.142	0.082103	2.391	0.004254	1023	3397	2174	562	0
FETCH	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
Total	1124	46.158	0.041066	2.391	0.002127	1023	3397	2174	562	0
Avg. per EXEC	2.000	0.082132	n/a	0.004254	n/a	1.820	6.044	3.868	1.000	0.000
Avg. per Row	2.000	0.082132	n/a	0.004254	n/a	1.820	6.044	3.868	1.000	0.000

Recursive Descendants

The statement has 5 recursive descendants that have contributed an elapsed time of 15.329 s. For each recursive statement, an excerpt of at most 60 characters from the statement text is shown.

Hash Value Elapsed Time (s) Parsing ID Statement Text (Excerpt)
2084491117 14.566 78 (SOE) SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LAN
820158466 0.751 78 (SOE) SELECT ORDER_ID, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS, ORDE
124468195 0.012 78 (SOE) SELECT SYSDATE FROM DUAL
4196474773 0.000 78 (SOE) INSERT INTO LOGON VALUES (:B2 , :B1 )
255718823 0.000 78 (SOE) COMMIT

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 727831036 0 0 3522870812 15-Nov-09 19:47:59
1 727831036 0 0 3750204916 15-Nov-09 20:01:17

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 4.375 74.085 0 0 0 70.935 1.188 0 3053 0 3053 0
1 7.985 73.515 0 0 0 49.448 1.156 0 2106 0 2106 0

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	SQL_PROFILE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2427	2870266532	1	0	0	ALL_ROWS	3750204916		.048079	.002381	10
2424	2870266532	1	0	0	ALL_ROWS	3522870812		.023961	.001399	10

Captured Bind Variables

Note: trace file contains 562 bind section(s) (report limited to 10)

Bind Section 1:

Position	Type	Value
0
1	NUMBER	16803
2	NUMBER	4
3	NUMBER	6

Bind Section 2:

Position	Type	Value
0
1	NUMBER	319705
2	NUMBER	4
3	NUMBER	6

Bind Section 3:

Position	Type	Value
0
1	NUMBER	65945
2	NUMBER	4
3	NUMBER	6

Bind Section 4:

Position	Type	Value
0
1	NUMBER	976867
2	NUMBER	4
3	NUMBER	6

Bind Section 5:

Position	Type	Value
0
1	NUMBER	207370
2	NUMBER	4
3	NUMBER	6

Bind Section 6:

Position	Type	Value
0
1	NUMBER	705210
2	NUMBER	4
3	NUMBER	6

Bind Section 7:

Position	Type	Value
0
1	NUMBER	871184
2	NUMBER	4
3	NUMBER	6

Bind Section 8:

Position	Type	Value
0
1	NUMBER	419672
2	NUMBER	4
3	NUMBER	6

Bind Section 9:

Position	Type	Value
0
1	NUMBER	294145
2	NUMBER	4
3	NUMBER	6

Bind Section 10:

Position	Type	Value
0
1	NUMBER	560920
2	NUMBER	4
3	NUMBER	6

Rank 6: Statement with Hash Value 3589721925

Response Time Including Think Time: 35.817 s (7.03 % of delta tim)
Response Time Excluding Think Time: 33.270 s (6.53 % of delta tim)
Recursive Elapsed Time: 0.169 s
SQL ID: 9hjz3yrazdmu5
Force Matching Signature: 0
Command Type: 47 (PL/SQL Block)
Parsing User ID: 78 (SOE)
Parsing Schema ID: 78 (SOE)
Optimizer Goal: 1 (ALL_ROWS)
Recursive Call Depth: 0
Module: 'Browse and Update Orders'
Action: 'getOrdersByCustomer'
Waits: 1556
Total Wait Time: 3.124 s
Intra Database Call Wait Time: 0.000 s
Inter Database Call Wait Time: 3.124 s
IPC latency wait time: 0.577 s
Line Number: 767

Statement Text

BEGIN :1 := orderentry.newcustomer(:2,:3,:4,:5,:6,:7); END;

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
unknown	29.786287	83.2	1	29.786287
CPU	2.906250	8.1	1118	0.002600
SQL*Net message from client	2.763140	7.7	559	0.004943
think time	0.357152	1.0	438	0.000815
SQL*Net message to client	0.003925	0.0	559	0.000007
Total	35.816757	100.0	2675	0.013389

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	559	0.016	0.000029	0.000	0.000000	0	0	0	0	0
EXEC	559	32.677	0.058455	2.906	0.005199	1414	2264	12783	559	0
FETCH	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
Total	1118	32.693	0.029242	2.906	0.002600	1414	2264	12783	559	0
Avg. per EXEC	2.000	0.058484	n/a	0.005199	n/a	2.530	4.050	22.868	1.000	0.000
Avg. per Row	2.000	0.058484	n/a	0.005199	n/a	2.530	4.050	22.868	1.000	0.000

Recursive Descendants

The statement has 6 recursive descendants that have contributed an elapsed time of 0.169 s. For each recursive statement, an excerpt of at most 60 characters from the statement text is shown.

Hash Value Elapsed Time (s) Parsing ID Statement Text (Excerpt)
2482701865 0.052 78 (SOE) SELECT CUSTOMER_SEQ.NEXTVAL FROM DUAL
255718823 0.049 78 (SOE) COMMIT
2084491117 0.034 78 (SOE) SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LAN
580506954 0.022 78 (SOE) INSERT INTO CUSTOMERS(CUSTOMER_ID ,CUST_FIRST_NAME ,CUST_LAS
124468195 0.013 78 (SOE) SELECT SYSDATE FROM DUAL
4196474773 0.000 78 (SOE) INSERT INTO LOGON VALUES (:B2 , :B1 )

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 2445712067 0 0 3522870812 15-Nov-09 19:47:59
1 2445712067 0 0 3750204916 15-Nov-09 20:01:15

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 4.325 80.63 0 .007 0 76.631 1.013 0 3000 0 3000 0
1 11.629 78.227 0 0 0 51.857 .811 0 2163 0 2163 0

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	SQL_PROFILE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2427	2870266532	1	0	0	ALL_ROWS	3750204916		.044229	.003157	27
2424	2870266532	1	0	0	ALL_ROWS	3522870812		.026442	.001414	27.2

Captured Bind Variables

Note: trace file contains 559 bind section(s) (report limited to 10)

Bind Section 1:

Position	Type	Value
0
1	(N)VARCHAR2	"Alonso"
2	(N)VARCHAR2	"Fonda"
3	(N)VARCHAR2	"th"
4	(N)VARCHAR2	"THAILAND"
5	NUMBER	4
6	NUMBER	6

Bind Section 2:

Position	Type	Value
0
1	(N)VARCHAR2	"Carol"
2	(N)VARCHAR2	"von Sydow"
3	(N)VARCHAR2	"zhs"
4	(N)VARCHAR2	"CHINA"
5	NUMBER	4
6	NUMBER	6

Bind Section 3:

Position	Type	Value
0
1	(N)VARCHAR2	"Alexander"
2	(N)VARCHAR2	"Wilder"
3	(N)VARCHAR2	"i"
4	(N)VARCHAR2	"ITALY"
5	NUMBER	4
6	NUMBER	6

Bind Section 4:

Position	Type	Value
0
1	(N)VARCHAR2	"Klaus"
2	(N)VARCHAR2	"Wilder"
3	(N)VARCHAR2	"us"
4	(N)VARCHAR2	"AMERICA"
5	NUMBER	4
6	NUMBER	6

Bind Section 5:

Position	Type	Value
0
1	(N)VARCHAR2	"Dominic"
2	(N)VARCHAR2	"Pacino"
3	(N)VARCHAR2	"th"
4	(N)VARCHAR2	"THAILAND"
5	NUMBER	4
6	NUMBER	6

Bind Section 6:

Position	Type	Value
0
1	(N)VARCHAR2	"Harry dean"
2	(N)VARCHAR2	"Quinlan"
3	(N)VARCHAR2	"zhs"
4	(N)VARCHAR2	"CHINA"
5	NUMBER	4
6	NUMBER	6

Bind Section 7:

Position	Type	Value
0
1	(N)VARCHAR2	"Eddie"
2	(N)VARCHAR2	"Fonda"
3	(N)VARCHAR2	"ja"
4	(N)VARCHAR2	"JAPAN"
5	NUMBER	4
6	NUMBER	6

Bind Section 8:

Position	Type	Value
0
1	(N)VARCHAR2	"Kristin"
2	(N)VARCHAR2	"Mahoney"
3	(N)VARCHAR2	"zhs"
4	(N)VARCHAR2	"CHINA"
5	NUMBER	4
6	NUMBER	6

Bind Section 9:

Position	Type	Value
0
1	(N)VARCHAR2	"Orson"
2	(N)VARCHAR2	"Prashant"
3	(N)VARCHAR2	"us"
4	(N)VARCHAR2	"AMERICA"
5	NUMBER	4
6	NUMBER	6

Bind Section 10:

Position	Type	Value
0
1	(N)VARCHAR2	"Ridley"
2	(N)VARCHAR2	"Buckley"
3	(N)VARCHAR2	"d"
4	(N)VARCHAR2	"SWITZERLAND"
5	NUMBER	4
6	NUMBER	6

Rank 7: Statement with Hash Value 2086907756

Response Time Including Think Time: 22.892 s (4.49 % of delta tim)
Response Time Excluding Think Time: 20.181 s (3.96 % of delta tim)
Recursive Elapsed Time: 4.155 s
SQL ID: 5v8hhf9y67bvc
Force Matching Signature: 0
Command Type: 47 (PL/SQL Block)
Parsing User ID: 78 (SOE)
Parsing Schema ID: 78 (SOE)
Optimizer Goal: 1 (ALL_ROWS)
Recursive Call Depth: 0
Module: 'New Customer'
Action: 'getCustomerDetails'
Waits: 2031
Total Wait Time: 7.749 s
Intra Database Call Wait Time: 4.270 s
Inter Database Call Wait Time: 3.479 s
IPC latency wait time: 0.768 s
Line Number: 1066

Statement Text

BEGIN :1 := orderentry.processorders(:2,:3); END;

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
unknown	13.626615	59.5	1	13.626615
PL/SQL lock timer	4.270232	18.7	402	0.010622
SQL*Net message from client	2.934054	12.8	596	0.004923
CPU	1.515625	6.6	1192	0.001271
think time	0.541128	2.4	434	0.001247
SQL*Net message to client	0.003885	0.0	596	0.000007
latch: library cache	0.000125	0.0	3	0.000042
Total	22.891665	100.0	3224	0.007100

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	596	0.017	0.000029	0.000	0.000000	0	0	0	0	0
EXEC	596	19.395	0.032543	1.516	0.002543	272	7801	5367	596	0
FETCH	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
Total	1192	19.413	0.016286	1.516	0.001271	272	7801	5367	596	0
Avg. per EXEC	2.000	0.032571	n/a	0.002543	n/a	0.456	13.089	9.005	1.000	0.000
Avg. per Row	2.000	0.032571	n/a	0.002543	n/a	0.456	13.089	9.005	1.000	0.000

Recursive Descendants

The statement has 3 recursive descendants that have contributed an elapsed time of 4.155 s. For each recursive statement, an excerpt of at most 60 characters from the statement text is shown.

Hash Value Elapsed Time (s) Parsing ID Statement Text (Excerpt)
746589640 4.154 78 (SOE) SELECT /*+ first_rows index(customers, customers_pk) index(
914625479 0.001 78 (SOE) UPDATE /*+ index(orders, order_pk) */ ORDERS SET ORDER_STATU
255718823 0.000 78 (SOE) COMMIT

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 2757634508 0 0 3522870812 15-Nov-09 19:47:59
1 2757634508 0 0 3750204916 15-Nov-09 20:01:15

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 2.711 21.124 0 0 0 18.685 .695 0 3107 0 3107 0
1 7.901 25.88 0 0 0 11.01 .553 0 2105 0 2105 0

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	SQL_PROFILE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2427	2870266532	1	0	0	ALL_ROWS	3750204916		.016636	.002225	22.2
2424	2870266532	1	0	0	ALL_ROWS	3522870812		.006531	.00087	21.4

Captured Bind Variables

Note: trace file contains 596 bind section(s) (report limited to 10)

Bind Section 1:

Position	Type	Value
0
1	NUMBER	4
2	NUMBER	6

Bind Section 2:

Position	Type	Value
0
1	NUMBER	4
2	NUMBER	6

Bind Section 3:

Position	Type	Value
0
1	NUMBER	4
2	NUMBER	6

Bind Section 4:

Position	Type	Value
0
1	NUMBER	4
2	NUMBER	6

Bind Section 5:

Position	Type	Value
0
1	NUMBER	4
2	NUMBER	6

Bind Section 6:

Position	Type	Value
0
1	NUMBER	4
2	NUMBER	6

Bind Section 7:

Position	Type	Value
0
1	NUMBER	4
2	NUMBER	6

Bind Section 8:

Position	Type	Value
0
1	NUMBER	4
2	NUMBER	6

Bind Section 9:

Position	Type	Value
0
1	NUMBER	4
2	NUMBER	6

Bind Section 10:

Position	Type	Value
0
1	NUMBER	4
2	NUMBER	6

Rank 8: Statement with Hash Value 580506954

Response Time: 16.314 s (3.20 % of delta tim)
Recursive Elapsed Time: 0.015 s
SQL ID: 0bzhqhhj9mpaa
Force Matching Signature: 0
MD5 Hash Value: 77CC6F97CF779A8E4BEE0673948A073
Command Type: 2 (INSERT)
Parsing User ID: 78 (SOE)
Parsing Schema ID: 78 (SOE)
Optimizer Goal: 1 (ALL_ROWS)
Recursive Call Depth: 1
Module: 'New Customer'
Action: 'getOrdersByCustomer'
Waits: 1408
Total Wait Time: 15.372 s
Intra Database Call Wait Time: 15.372 s
Line Number: 876

Statement Text

INSERT INTO CUSTOMERS(CUSTOMER_ID ,CUST_FIRST_NAME ,CUST_LAST_NAME ,NLS_LANGUAGE ,NLS_TERRITORY ,CREDIT_LIMIT ,CUST_EMAIL ,ACCOUNT_MGR_ID ) VALUES (:B9 , :B4 , :B3 , :B8 , :B7 , FLOOR(DBMS_RANDOM.VALUE(:B6 , :B5 )), :B4 ||'.'||:B3 ||'@'||'oracle.com', FLOOR(DBMS_RANDOM.VALUE(:B2 , :B1 )))

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
db file sequential read	15.372345	94.2	1408	0.010918
CPU	0.765625	4.7	559	0.001370
unknown	0.176006	1.1	1	0.176006
Total	16.313976	100.0	1968	0.008290

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
EXEC	559	16.314	0.029184	0.766	0.001370	1411	20	10096	559	0
FETCH	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
Total	559	16.314	0.029184	0.766	0.001370	1411	20	10096	559	0
Avg. per EXEC	1.000	0.029184	n/a	0.001370	n/a	2.524	0.036	18.061	1.000	0.000
Avg. per Row	1.000	0.029184	n/a	0.001370	n/a	2.524	0.036	18.061	1.000	0.000

Recursive Descendants

The statement has 2 recursive descendants that have contributed an elapsed time of 0.015 s. For each recursive statement, an excerpt of at most 60 characters from the statement text is shown.

Hash Value Elapsed Time (s) Parsing ID Statement Text (Excerpt)
2379717279 0.015 0 (SYS) update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,max
1570213724 0.000 0 (SYS) select file# from file$ where ts#=:1

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 3172424902 0 0 3522870812 15-Nov-09 19:47:59
1 3172424902 0 0 3750204916 15-Nov-09 20:01:15

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 6.243 128.15 0 0 0 123.355 .135 0 5030 0 2 1
1 .158 4.896 0 0 0 4.83 .003 0 133 0 2 1

Correlation with V$SQL_PLAN_STATISTICS_ALL using DBMS_XPLAN.DISPLAY_CURSOR

SQL_ID  0bzhqhhj9mpaa, child number 0
                                     
INSERT INTO CUSTOMERS(CUSTOMER_ID ,CUST_FIRST_NAME ,CUST_LAST_NAME 
,NLS_LANGUAGE ,NLS_TERRITORY ,CREDIT_LIMIT ,CUST_EMAIL ,ACCOUNT_MGR_ID 
) VALUES (:B9 , :B4 , :B3 , :B8 , :B7 , FLOOR(DBMS_RANDOM.VALUE(:B6 , 
:B5 )), :B4 ||'.'||:B3 ||'@'||'oracle.com', FLOOR(DBMS_RANDOM.VALUE(:B2 
, :B1 )))
 
NOTE: cannot fetch plan for SQL_ID: 0bzhqhhj9mpaa, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER; 
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	SQL_PROFILE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2427	2870266532	1	0	1	ALL_ROWS	3750204916		.031852	.001138	18.1
2424	2870266532	1	0	1	ALL_ROWS	3522870812		.025459	.00098	18.2

Physical Reads by Database Object

Obj. ID	SB Reads	SBR Time	Avg. SBR Time	Owner & Segment
60783	553	6.322	0.011	SOE.CUST_EMAIL_IX
60786	552	5.002	0.009	SOE.CUST_UPPER_NAME_IX
60782	293	3.959	0.014	SOE.CUST_LNAME_IX
60736	10	0.090	0.009	SOE.CUSTOMERS

Captured Bind Variables

Note: trace file contains 558 bind section(s) (report limited to 10)

Bind Section 1:

Position	Type	Value
0	NUMBER	1084194
1	(N)VARCHAR2	"Carol"
2	(N)VARCHAR2	"von Sydow"
3	(N)VARCHAR2	"zhs"
4	(N)VARCHAR2	"CHINA"
5	NUMBER	100
6	NUMBER	5000
7	(N)VARCHAR2	"Carol"
8	(N)VARCHAR2	"von Sydow"
9	NUMBER	145
10	NUMBER	171

Bind Section 2:

Position	Type	Value
0	NUMBER	1084201
1	(N)VARCHAR2	"Alexander"
2	(N)VARCHAR2	"Wilder"
3	(N)VARCHAR2	"i"
4	(N)VARCHAR2	"ITALY"
5	NUMBER	100
6	NUMBER	5000
7	(N)VARCHAR2	"Alexander"
8	(N)VARCHAR2	"Wilder"
9	NUMBER	145
10	NUMBER	171

Bind Section 3:

Position	Type	Value
0	NUMBER	1084206
1	(N)VARCHAR2	"Klaus"
2	(N)VARCHAR2	"Wilder"
3	(N)VARCHAR2	"us"
4	(N)VARCHAR2	"AMERICA"
5	NUMBER	100
6	NUMBER	5000
7	(N)VARCHAR2	"Klaus"
8	(N)VARCHAR2	"Wilder"
9	NUMBER	145
10	NUMBER	171

Bind Section 4:

Position	Type	Value
0	NUMBER	1084207
1	(N)VARCHAR2	"Dominic"
2	(N)VARCHAR2	"Pacino"
3	(N)VARCHAR2	"th"
4	(N)VARCHAR2	"THAILAND"
5	NUMBER	100
6	NUMBER	5000
7	(N)VARCHAR2	"Dominic"
8	(N)VARCHAR2	"Pacino"
9	NUMBER	145
10	NUMBER	171

Bind Section 5:

Position	Type	Value
0	NUMBER	1084210
1	(N)VARCHAR2	"Harry dean"
2	(N)VARCHAR2	"Quinlan"
3	(N)VARCHAR2	"zhs"
4	(N)VARCHAR2	"CHINA"
5	NUMBER	100
6	NUMBER	5000
7	(N)VARCHAR2	"Harry dean"
8	(N)VARCHAR2	"Quinlan"
9	NUMBER	145
10	NUMBER	171

Bind Section 6:

Position	Type	Value
0	NUMBER	1084211
1	(N)VARCHAR2	"Eddie"
2	(N)VARCHAR2	"Fonda"
3	(N)VARCHAR2	"ja"
4	(N)VARCHAR2	"JAPAN"
5	NUMBER	100
6	NUMBER	5000
7	(N)VARCHAR2	"Eddie"
8	(N)VARCHAR2	"Fonda"
9	NUMBER	145
10	NUMBER	171

Bind Section 7:

Position	Type	Value
0	NUMBER	1084221
1	(N)VARCHAR2	"Kristin"
2	(N)VARCHAR2	"Mahoney"
3	(N)VARCHAR2	"zhs"
4	(N)VARCHAR2	"CHINA"
5	NUMBER	100
6	NUMBER	5000
7	(N)VARCHAR2	"Kristin"
8	(N)VARCHAR2	"Mahoney"
9	NUMBER	145
10	NUMBER	171

Bind Section 8:

Position	Type	Value
0	NUMBER	1084224
1	(N)VARCHAR2	"Orson"
2	(N)VARCHAR2	"Prashant"
3	(N)VARCHAR2	"us"
4	(N)VARCHAR2	"AMERICA"
5	NUMBER	100
6	NUMBER	5000
7	(N)VARCHAR2	"Orson"
8	(N)VARCHAR2	"Prashant"
9	NUMBER	145
10	NUMBER	171

Bind Section 9:

Position	Type	Value
0	NUMBER	1084225
1	(N)VARCHAR2	"Ridley"
2	(N)VARCHAR2	"Buckley"
3	(N)VARCHAR2	"d"
4	(N)VARCHAR2	"SWITZERLAND"
5	NUMBER	100
6	NUMBER	5000
7	(N)VARCHAR2	"Ridley"
8	(N)VARCHAR2	"Buckley"
9	NUMBER	145
10	NUMBER	171

Bind Section 10:

Position	Type	Value
0	NUMBER	1084227
1	(N)VARCHAR2	"Cyndi"
2	(N)VARCHAR2	"McQueen"
3	(N)VARCHAR2	"us"
4	(N)VARCHAR2	"AMERICA"
5	NUMBER	100
6	NUMBER	5000
7	(N)VARCHAR2	"Cyndi"
8	(N)VARCHAR2	"McQueen"
9	NUMBER	145
10	NUMBER	171

Rank 9: Statement with Hash Value 2236920324

Response Time: 15.579 s (3.06 % of delta tim)
Recursive Elapsed Time: 0.003 s
SQL ID: 0yas01u2p9ch4
Force Matching Signature: 0
MD5 Hash Value: A249221FFF1E35A1E35F609C91908
Command Type: 2 (INSERT)
Parsing User ID: 78 (SOE)
Parsing Schema ID: 78 (SOE)
Optimizer Goal: 1 (ALL_ROWS)
Recursive Call Depth: 1
Module: 'New Order'
Action: 'getProductQuantity'
Waits: 593
Total Wait Time: 5.994 s
Intra Database Call Wait Time: 5.994 s
Line Number: 214

Statement Text

INSERT INTO ORDER_ITEMS(ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY) VALUES (:B4 , :B3 , :B2 , :B1 , 1)

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
unknown	8.054321	51.7	1	8.054321
db file sequential read	5.993674	38.5	593	0.010107
CPU	1.531250	9.8	2088	0.000733
Total	15.579245	100.0	2682	0.005809

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
EXEC	2088	15.579	0.007461	1.531	0.000733	594	165	25739	2088	0
FETCH	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
Total	2088	15.579	0.007461	1.531	0.000733	594	165	25739	2088	0
Avg. per EXEC	1.000	0.007461	n/a	0.000733	n/a	0.284	0.079	12.327	1.000	0.000
Avg. per Row	1.000	0.007461	n/a	0.000733	n/a	0.284	0.079	12.327	1.000	0.000

Recursive Descendants

The statement has 3 recursive descendants that have contributed an elapsed time of 0.003 s. For each recursive statement, an excerpt of at most 60 characters from the statement text is shown.

Hash Value Elapsed Time (s) Parsing ID Statement Text (Excerpt)
2379717279 0.002 0 (SYS) update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,max
3840466760 0.001 0 (SYS) update tsq$ set blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,
1570213724 0.000 0 (SYS) select file# from file$ where ts#=:1

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 2698772428 0 0 3522870812 15-Nov-09 19:47:59
1 2698772428 0 0 3750204916 15-Nov-09 20:01:16

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 13.339 36.743 0 0 0 23.236 0 0 17878 0 2 1
1 .552 3.154 0 0 0 2.34 0 0 464 0 2 1

Correlation with V$SQL_PLAN_STATISTICS_ALL using DBMS_XPLAN.DISPLAY_CURSOR

SQL_ID  0yas01u2p9ch4, child number 0
                                     
INSERT INTO ORDER_ITEMS(ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY) 
VALUES (:B4 , :B3 , :B2 , :B1 , 1)
 
NOTE: cannot fetch plan for SQL_ID: 0yas01u2p9ch4, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER; 
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	SQL_PROFILE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2427	2870266532	1	0	1	ALL_ROWS	3750204916		.00613	.000936	12.4
2424	2870266532	1	0	1	ALL_ROWS	3522870812		.001416	.0003	12.3

Physical Reads by Database Object

Obj. ID	SB Reads	SBR Time	Avg. SBR Time	Owner & Segment
60775	536	5.275	0.010	SOE.ITEM_ORDER_IX
60776	27	0.329	0.012	SOE.ITEM_PRODUCT_IX
60767	17	0.218	0.013	SOE.ORDER_ITEMS_UK
60738	7	0.087	0.012	SOE.ORDER_ITEMS
60766	6	0.084	0.014	SOE.ORDER_ITEMS_PK

Captured Bind Variables

Note: trace file contains 2087 bind section(s) (report limited to 10)

Bind Section 1:

Position	Type	Value
0	NUMBER	45092
1	NUMBER	2
2	NUMBER	194
3	NUMBER	345

Bind Section 2:

Position	Type	Value
0	NUMBER	45094
1	NUMBER	1
2	NUMBER	189
3	NUMBER	3

Bind Section 3:

Position	Type	Value
0	NUMBER	45094
1	NUMBER	2
2	NUMBER	199
3	NUMBER	18

Bind Section 4:

Position	Type	Value
0	NUMBER	45094
1	NUMBER	3
2	NUMBER	209
3	NUMBER	65

Bind Section 5:

Position	Type	Value
0	NUMBER	45094
1	NUMBER	4
2	NUMBER	219
3	NUMBER	60

Bind Section 6:

Position	Type	Value
0	NUMBER	45095
1	NUMBER	1
2	NUMBER	189
3	NUMBER	3

Bind Section 7:

Position	Type	Value
0	NUMBER	45095
1	NUMBER	2
2	NUMBER	199
3	NUMBER	18

Bind Section 8:

Position	Type	Value
0	NUMBER	45096
1	NUMBER	1
2	NUMBER	186
3	NUMBER	3

Bind Section 9:

Position	Type	Value
0	NUMBER	45096
1	NUMBER	2
2	NUMBER	196
3	NUMBER	231

Bind Section 10:

Position	Type	Value
0	NUMBER	45096
1	NUMBER	3
2	NUMBER	206
3	NUMBER	23

Rank 10: Statement with Hash Value 2637862082

Response Time: 14.922 s (2.93 % of delta tim)
SQL ID: 8z3542ffmp562
Force Matching Signature: 17991828121679737456
MD5 Hash Value: 1038CE2923B248A5A4871B1688AA63EA
Command Type: 3 (SELECT)
Parsing User ID: 78 (SOE)
Parsing Schema ID: 78 (SOE)
Optimizer Goal: 1 (ALL_ROWS)
Recursive Call Depth: 1
Module: 'New Order'
Action: 'getProductQuantity'
Waits: 0
Total Wait Time: 0.000 s
Intra Database Call Wait Time: 0.000 s
Line Number: 184

Statement Text

SELECT QUANTITY_ON_HAND FROM PRODUCT_INFORMATION P, INVENTORIES I WHERE I.PRODUCT_ID = :B2 AND I.PRODUCT_ID = P.PRODUCT_ID AND I.WAREHOUSE_ID = :B1

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
unknown	13.844324	92.8	1	13.844324
CPU	1.078125	7.2	4176	0.000258
Total	14.922449	100.0	4177	0.003573

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
EXEC	2088	14.839	0.007107	0.844	0.000404	0	0	0	0	0
FETCH	2088	0.083	0.000040	0.234	0.000112	0	6266	0	2088	0
Total	4176	14.922	0.003573	1.078	0.000258	0	6266	0	2088	0
Avg. per EXEC	2.000	0.007147	n/a	0.000516	n/a	0.000	3.001	0.000	1.000	0.000
Avg. per FETCH	2.000	0.007147	n/a	0.000516	n/a	0.000	3.001	0.000	1.000	0.000
Avg. per Row	2.000	0.007147	n/a	0.000516	n/a	0.000	3.001	0.000	1.000	0.000

Execution Plan 1 (1 occurrence(s)):

ID	PID	Rows	Operation	Ela. (Self)	Ela. (Cum.)	CR (Self)	CR (Cum.)	PR (Self)	PR (Cum.)	PW (Self)	PW (Cum.)
1	0	2263	TABLE ACCESS BY INDEX ROWID INVENTORIES	0.060333	0.104309	2265	6791	0	0	0	0
2	1	2263	INDEX UNIQUE SCAN INVENTORY_PK	0.043976	0.043976	4526	4526	0	0	0	0

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 2308915322 17991828121679737456 3661684950 3522870812 15-Nov-09 19:47:59
1 2308915322 17991828121679737456 3661684950 3750204916 15-Nov-09 20:01:16

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 7.406 11.414 0 0 0 .041 0 0 17878 0 2 2
1 .153 1.232 0 0 0 0 0 0 464 0 2 2

Correlation with V$SQL_PLAN_STATISTICS_ALL using DBMS_XPLAN.DISPLAY_CURSOR

SQL_ID  8z3542ffmp562, child number 0
-------------------------------------
SELECT QUANTITY_ON_HAND FROM PRODUCT_INFORMATION P, INVENTORIES I WHERE I.PRODUCT_ID 
= :B2 AND I.PRODUCT_ID = P.PRODUCT_ID AND I.WAREHOUSE_ID = :B1
 
Plan hash value: 3661684950
 
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| INVENTORIES  |      1 |    10 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | INVENTORY_PK |      1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$A43D1678 / I@SEL$1
   2 - SEL$A43D1678 / I@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$A43D1678")
      ELIMINATE_JOIN(@"SEL$1" "P"@"SEL$1")
      OUTLINE(@"SEL$1")
      INDEX_RS_ASC(@"SEL$A43D1678" "I"@"SEL$1" ("INVENTORIES"."PRODUCT_ID" 
              "INVENTORIES"."WAREHOUSE_ID"))
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - (NUMBER): 191
   2 - (NUMBER): 6
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("I"."PRODUCT_ID"=:B2 AND "I"."WAREHOUSE_ID"=:B1)
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Execution Plans Captured by Statspack

Old hash value: 2308915322
SQL ID: 8z3542ffmp562

Execution plan with plan hash value 3661684950 (last active 15-Nov-09 20:09:28)
Optimization: ALL_ROWS
Cost: 2
Statspack snapshot 483 of instance 1

ID PID Operation Object Object ID Rows Bytes Cost I/O Cost Temp. Space
0 SELECT STATEMENT 2
1 0 TABLE ACCESS BY INDEX ROWID SOE.INVENTORIES 60740 1 10 2 2
2 1 INDEX UNIQUE SCAN SOE.INVENTORY_PK 60771 1 1 1

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	SQL_PROFILE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2427	2870266532	1	3661684950	2	ALL_ROWS	3750204916		.003002	.000616	3
2423	2870266532	1	3661684950	2	ALL_ROWS	3522870812		.001424	.000961	3

AWR execution plan with plan hash value 3661684950 for SQL ID 8z3542ffmp562

SQL_ID 8z3542ffmp562
--------------------
SELECT QUANTITY_ON_HAND FROM PRODUCT_INFORMATION P, INVENTORIES I WHERE I.PRODUCT_ID 
= :B2 AND I.PRODUCT_ID = P.PRODUCT_ID AND I.WAREHOUSE_ID = :B1
 
Plan hash value: 3661684950
 
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |        |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| INVENTORIES  |      1 |    10 |     2   (0)| 00:00:01 |
|   2 |   INDEX UNIQUE SCAN         | INVENTORY_PK |      1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$A43D1678 / I@SEL$1
   2 - SEL$A43D1678 / I@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$A43D1678")
      ELIMINATE_JOIN(@"SEL$1" "P"@"SEL$1")
      OUTLINE(@"SEL$1")
      INDEX_RS_ASC(@"SEL$A43D1678" "I"@"SEL$1" ("INVENTORIES"."PRODUCT_ID" 
              "INVENTORIES"."WAREHOUSE_ID"))
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - :B2 (NUMBER): 191
   2 - :B1 (NUMBER): 6
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Captured Bind Variables

Note: trace file contains 2087 bind section(s) (report limited to 10)

Bind Section 1:

Position	Type	Value
0	NUMBER	194
1	NUMBER	7

Bind Section 2:

Position	Type	Value
0	NUMBER	189
1	NUMBER	8

Bind Section 3:

Position	Type	Value
0	NUMBER	199
1	NUMBER	5

Bind Section 4:

Position	Type	Value
0	NUMBER	209
1	NUMBER	2

Bind Section 5:

Position	Type	Value
0	NUMBER	219
1	NUMBER	5

Bind Section 6:

Position	Type	Value
0	NUMBER	189
1	NUMBER	7

Bind Section 7:

Position	Type	Value
0	NUMBER	199
1	NUMBER	7

Bind Section 8:

Position	Type	Value
0	NUMBER	186
1	NUMBER	2

Bind Section 9:

Position	Type	Value
0	NUMBER	196
1	NUMBER	3

Bind Section 10:

Position	Type	Value
0	NUMBER	206
1	NUMBER	7

Rank 11: Statement with Hash Value 124468195

Response Time: 12.656 s (2.48 % of delta tim)
SQL ID: c749bc43qqfz3
Force Matching Signature: 2672114946588399948
MD5 Hash Value: 656B19F5D4B3F329E09EB72361276532
Command Type: 3 (SELECT)
Parsing User ID: 78 (SOE)
Parsing Schema ID: 78 (SOE)
Optimizer Goal: 1 (ALL_ROWS)
Recursive Call Depth: 1
Module: 'New Order'
Action: 'logon'
Waits: 0
Total Wait Time: 0.000 s
Intra Database Call Wait Time: 0.000 s
Line Number: 76

Statement Text

SELECT SYSDATE FROM DUAL

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
unknown	11.921153	94.2	1	11.921153
CPU	0.734375	5.8	4524	0.000162
Total	12.655528	100.0	4525	0.002797

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
EXEC	2262	12.603	0.005572	0.656	0.000290	0	0	0	0	0
FETCH	2262	0.052	0.000023	0.078	0.000035	0	0	0	2262	0
Total	4524	12.656	0.002797	0.734	0.000162	0	0	0	2262	0
Avg. per EXEC	2.000	0.005595	n/a	0.000325	n/a	0.000	0.000	0.000	1.000	0.000
Avg. per FETCH	2.000	0.005595	n/a	0.000325	n/a	0.000	0.000	0.000	1.000	0.000
Avg. per Row	2.000	0.005595	n/a	0.000325	n/a	0.000	0.000	0.000	1.000	0.000

Execution Plan 1 (1 occurrence(s)):

ID	PID	Rows	Operation	Ela. (Self)	Ela. (Cum.)	CR (Self)	CR (Cum.)	PR (Self)	PR (Cum.)	PW (Self)	PW (Cum.)
1	0	2476	FAST DUAL	0.041030	0.041030	0	0	0	0	0	0

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 2293415029 2672114946588399948 1388734953 3522870812 15-Nov-09 19:47:59
1 2293415029 2672114946588399948 1388734953 3750204916 15-Nov-09 20:01:17

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 7.096 15.242 0 0 0 0 0 0 20203 0 2 2
1 .06 .599 0 0 0 0 0 0 560 0 2 2

Correlation with V$SQL_PLAN_STATISTICS_ALL using DBMS_XPLAN.DISPLAY_CURSOR

SQL_ID  c749bc43qqfz3, child number 0
-------------------------------------
SELECT SYSDATE FROM DUAL
 
Plan hash value: 1388734953
 
------------------------------------------------------------
| Id  | Operation  | Name | E-Rows | Cost (%CPU)| E-Time   |
------------------------------------------------------------
|   1 |  FAST DUAL |      |      1 |     2   (0)| 00:00:01 |
------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / DUAL@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      END_OUTLINE_DATA
  */
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Execution Plans Captured by Statspack

Old hash value: 2293415029
SQL ID: c749bc43qqfz3

Execution plan with plan hash value 1388734953 (last active 15-Nov-09 20:09:28)
Optimization: ALL_ROWS
Cost: 2
Statspack snapshot 483 of instance 1

ID PID Operation Object Object ID Rows Bytes Cost I/O Cost Temp. Space
0 SELECT STATEMENT 2
1 0 FAST DUAL 1 2 2

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	SQL_PROFILE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2427	2870266532	1	1388734953	2	ALL_ROWS	3750204916		.002358	.000493	0
2423	2870266532	1	1388734953	2	ALL_ROWS	3522870812		.001726	.000826	0

AWR execution plan with plan hash value 1388734953 for SQL ID c749bc43qqfz3

SQL_ID c749bc43qqfz3
--------------------
SELECT SYSDATE FROM DUAL
 
Plan hash value: 1388734953
 
------------------------------------------------------------------
| Id  | Operation        | Name | E-Rows | Cost (%CPU)| E-Time   |
------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |        |     2 (100)|          |
|   1 |  FAST DUAL       |      |      1 |     2   (0)| 00:00:01 |
------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / DUAL@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      END_OUTLINE_DATA
  */
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Rank 12: Statement with Hash Value 746589640

Response Time: 9.517 s (1.87 % of delta tim)
SQL ID: f0cxkf0q803f8
Force Matching Signature: 11220520521603652454
MD5 Hash Value: 9C4288ACCED4483E7D39E4680446440
Command Type: 3 (SELECT)
Parsing User ID: 78 (SOE)
Parsing Schema ID: 78 (SOE)
Optimizer Goal: 2 (FIRST_ROWS(_n))
Recursive Call Depth: 1
Module: 'Process Orders'
Action: 'getCustomerDetails'
Waits: 269
Total Wait Time: 3.984 s
Intra Database Call Wait Time: 3.984 s
Line Number: 1090

Statement Text

SELECT /*+  first_rows index(customers, customers_pk) index(orders, order_status_ix) */ O.ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY, ORDER_MODE, ORDER_STATUS, ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID, C.CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CREDIT_LIMIT, CUST_EMAIL, ORDER_DATE FROM ORDERS O , ORDER_ITEMS OI, CUSTOMERS C WHERE O.ORDER_ID = OI.ORDER_ID AND O.CUSTOMER_ID = C.CUSTOMER_ID AND O.ORDER_STATUS <= 4

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
unknown	4.922930	51.7	1	4.922930
db file sequential read	3.926987	41.3	264	0.014875
CPU	0.609375	6.4	1192	0.000511
read by other session	0.057413	0.6	3	0.019138
buffer busy waits	0.000061	0.0	2	0.000030
Total	9.516766	100.0	1462	0.006509

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
EXEC	596	5.387	0.009039	0.516	0.000865	0	0	0	0	0
FETCH	596	4.130	0.006929	0.094	0.000157	264	6533	0	596	0
Total	1192	9.517	0.007984	0.609	0.000511	264	6533	0	596	0
Avg. per EXEC	2.000	0.015968	n/a	0.001022	n/a	0.443	10.961	0.000	1.000	0.000
Avg. per FETCH	2.000	0.015968	n/a	0.001022	n/a	0.443	10.961	0.000	1.000	0.000
Avg. per Row	2.000	0.015968	n/a	0.001022	n/a	0.443	10.961	0.000	1.000	0.000

Execution Plan 1 (1 occurrence(s)):

ID	PID	Rows	Operation	Ela. (Self)	Ela. (Cum.)	CR (Self)	CR (Cum.)	PR (Self)	PR (Cum.)
1	0	653	TABLE ACCESS BY INDEX ROWID ORDER_ITEMS	0.121282	4.293909	711	7128	7	276
2	1	1306	NESTED LOOPS	0.023199	4.172627	0	6417	0	269
3	2	653	NESTED LOOPS	0.013446	4.102182	0	5002	0	266
4	3	653	TABLE ACCESS BY INDEX ROWID ORDERS	0.042359	0.062821	721	2390	2	2
5	4	653	INDEX RANGE SCAN ORD_STATUS_IX	0.020462	0.020462	1669	1669	0	0
6	3	653	TABLE ACCESS BY INDEX ROWID CUSTOMERS	2.325740	4.025915	653	2612	145	264
7	6	653	INDEX UNIQUE SCAN CUSTOMERS_PK	1.700175	1.700175	1959	1959	119	119
8	2	653	INDEX RANGE SCAN ORDER_ITEMS_PK	0.047246	0.047246	1415	1415	3	3

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 3437483139 11220520521603652454 3151665200 2727794671 15-Nov-09 19:47:59
1 3437483139 11220520521603652454 3151665200 699142823 15-Nov-09 20:01:15

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 4.363 33.08 0 0 0 27.107 0 0 5072 0 2 24988
1 .052 1.156 0 0 0 .686 0 0 140 0 2 24988

Correlation with V$SQL_PLAN_STATISTICS_ALL using DBMS_XPLAN.DISPLAY_CURSOR

SQL_ID  f0cxkf0q803f8, child number 0
-------------------------------------
SELECT /*+  first_rows index(customers, customers_pk) index(orders, order_status_ix) */ 
O.ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY, ORDER_MODE, ORDER_STATUS, 
ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID, C.CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, 
CREDIT_LIMIT, CUST_EMAIL, ORDER_DATE FROM ORDERS O , ORDER_ITEMS OI, CUSTOMERS C WHERE 
O.ORDER_ID = OI.ORDER_ID AND O.CUSTOMER_ID = C.CUSTOMER_ID AND O.ORDER_STATUS <= 4
 
Plan hash value: 3151665200
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID   | ORDER_ITEMS    |      4 |    68 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                 |                |  23905 |  2264K| 24988   (1)| 00:05:00 |
|   3 |    NESTED LOOPS                |                |   6302 |   492K| 12816   (1)| 00:02:34 |
|   4 |     TABLE ACCESS BY INDEX ROWID| ORDERS         |   6302 |   178K|   206   (0)| 00:00:03 |
|*  5 |      INDEX RANGE SCAN          | ORD_STATUS_IX  |   6302 |       |    19   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| CUSTOMERS      |      1 |    51 |     2   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN         | CUSTOMERS_PK   |      1 |       |     1   (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN            | ORDER_ITEMS_PK |      4 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / OI@SEL$1
   4 - SEL$1 / O@SEL$1
   5 - SEL$1 / O@SEL$1
   6 - SEL$1 / C@SEL$1
   7 - SEL$1 / C@SEL$1
   8 - SEL$1 / OI@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      FIRST_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "O"@"SEL$1" ("ORDERS"."ORDER_STATUS"))
      INDEX_RS_ASC(@"SEL$1" "C"@"SEL$1" ("CUSTOMERS"."CUSTOMER_ID"))
      INDEX(@"SEL$1" "OI"@"SEL$1" ("ORDER_ITEMS"."ORDER_ID" "ORDER_ITEMS"."LINE_ITEM_ID"))
      LEADING(@"SEL$1" "O"@"SEL$1" "C"@"SEL$1" "OI"@"SEL$1")
      USE_NL(@"SEL$1" "C"@"SEL$1")
      USE_NL(@"SEL$1" "OI"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access("O"."ORDER_STATUS"<=4)
   7 - access("O"."CUSTOMER_ID"="C"."CUSTOMER_ID")
   8 - access("O"."ORDER_ID"="OI"."ORDER_ID")
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Execution Plans Captured by Statspack

Old hash value: 3437483139
SQL ID: f0cxkf0q803f8

Execution plan with plan hash value 3151665200 (last active 15-Nov-09 20:09:28)
Optimization: FIRST_ROWS
Cost: 24988
Statspack snapshot 483 of instance 1

ID PID Operation Object Object ID Rows Bytes Cost I/O Cost Temp. Space
0 SELECT STATEMENT 24988
1 0 TABLE ACCESS BY INDEX ROWID SOE.ORDER_ITEMS 60738 4 68 2 2
2 1   NESTED LOOPS 23905 2318785 24988 24975
3 2    NESTED LOOPS 6302 504160 12816 12810
4 3     TABLE ACCESS BY INDEX ROWID SOE.ORDERS 60739 6302 182758 206 206
5 4      INDEX RANGE SCAN SOE.ORD_STATUS_IX 60780 6302 19 19
6 3     TABLE ACCESS BY INDEX ROWID SOE.CUSTOMERS 60736 1 51 2 2
7 6      INDEX UNIQUE SCAN SOE.CUSTOMERS_PK 60764 1 1 1
8 2    INDEX RANGE SCAN SOE.ORDER_ITEMS_PK 60766 4 1 1

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	SQL_PROFILE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2424	2870266532	1	3151665200	24988	FIRST_ROWS	2727794671		.005684	.000293	10.3
2427	2870266532	1	3151665200	24988	FIRST_ROWS	699142823		.012233	.000976	11

AWR execution plan with plan hash value 3151665200 for SQL ID f0cxkf0q803f8

SQL_ID f0cxkf0q803f8
--------------------
SELECT /*+  first_rows index(customers, customers_pk) index(orders, order_status_ix) */ 
O.ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY, ORDER_MODE, ORDER_STATUS, 
ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID, C.CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, 
CREDIT_LIMIT, CUST_EMAIL, ORDER_DATE FROM ORDERS O , ORDER_ITEMS OI, CUSTOMERS C WHERE 
O.ORDER_ID = OI.ORDER_ID AND O.CUSTOMER_ID = C.CUSTOMER_ID AND O.ORDER_STATUS <= 4
 
Plan hash value: 3151665200
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |        |       | 24988 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID   | ORDER_ITEMS    |      4 |    68 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                 |                |  23905 |  2264K| 24988   (1)| 00:05:00 |
|   3 |    NESTED LOOPS                |                |   6302 |   492K| 12816   (1)| 00:02:34 |
|   4 |     TABLE ACCESS BY INDEX ROWID| ORDERS         |   6302 |   178K|   206   (0)| 00:00:03 |
|   5 |      INDEX RANGE SCAN          | ORD_STATUS_IX  |   6302 |       |    19   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| CUSTOMERS      |      1 |    51 |     2   (0)| 00:00:01 |
|   7 |      INDEX UNIQUE SCAN         | CUSTOMERS_PK   |      1 |       |     1   (0)| 00:00:01 |
|   8 |    INDEX RANGE SCAN            | ORDER_ITEMS_PK |      4 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / OI@SEL$1
   4 - SEL$1 / O@SEL$1
   5 - SEL$1 / O@SEL$1
   6 - SEL$1 / C@SEL$1
   7 - SEL$1 / C@SEL$1
   8 - SEL$1 / OI@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      FIRST_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "O"@"SEL$1" ("ORDERS"."ORDER_STATUS"))
      INDEX_RS_ASC(@"SEL$1" "C"@"SEL$1" ("CUSTOMERS"."CUSTOMER_ID"))
      INDEX(@"SEL$1" "OI"@"SEL$1" ("ORDER_ITEMS"."ORDER_ID" "ORDER_ITEMS"."LINE_ITEM_ID"))
      LEADING(@"SEL$1" "O"@"SEL$1" "C"@"SEL$1" "OI"@"SEL$1")
      USE_NL(@"SEL$1" "C"@"SEL$1")
      USE_NL(@"SEL$1" "OI"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Physical Reads by Database Object

Obj. ID	SB Reads	SBR Time	Avg. SBR Time	Owner & Segment
60736	138	2.182	0.016	SOE.CUSTOMERS
60764	115	1.608	0.014	SOE.CUSTOMERS_PK
60738	6	0.081	0.014	SOE.ORDER_ITEMS
60766	3	0.029	0.010	SOE.ORDER_ITEMS_PK
60739	2	0.027	0.013	SOE.ORDERS

Rank 13: Statement with Hash Value 622695190

Response Time: 8.152 s (1.60 % of delta tim)
SQL ID: 21wygrskjv4sq
Force Matching Signature: 18279731078113474405
MD5 Hash Value: E18E55DBAD4D82036D558F8B4F2CD4D
Command Type: 3 (SELECT)
Parsing User ID: 78 (SOE)
Parsing Schema ID: 78 (SOE)
Optimizer Goal: 1 (ALL_ROWS)
Recursive Call Depth: 1
Module: 'Browse Products'
Action: 'getProductDetails'
Waits: 0
Total Wait Time: 0.000 s
Intra Database Call Wait Time: 0.000 s
Line Number: 3836

Statement Text

SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, CATEGORY_ID, WEIGHT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID, PRODUCT_STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL, QUANTITY_ON_HAND FROM PRODUCTS, INVENTORIES WHERE PRODUCTS.PRODUCT_ID = :B1 AND INVENTORIES.PRODUCT_ID = PRODUCTS.PRODUCT_ID

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
unknown	5.854839	71.8	1	5.854839
CPU	2.296875	28.2	3184	0.000721
Total	8.151714	100.0	3185	0.002559

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
EXEC	1592	6.741	0.004234	0.844	0.000530	0	0	0	0	0
FETCH	1592	1.411	0.000886	1.453	0.000913	0	39880	0	31840	0
Total	3184	8.152	0.002560	2.297	0.000721	0	39880	0	31840	0
Avg. per EXEC	2.000	0.005120	n/a	0.001443	n/a	0.000	25.050	0.000	20.000	0.000
Avg. per FETCH	2.000	0.005120	n/a	0.001443	n/a	0.000	25.050	0.000	20.000	0.000
Avg. per Row	0.100	0.000256	n/a	0.000072	n/a	0.000	1.253	0.000	1.000	0.000

Execution Plan 1 (1 occurrence(s)):

ID	PID	Rows	Operation	Ela. (Self)	Ela. (Cum.)	CR (Self)	CR (Cum.)
1	0	35060	NESTED LOOPS	0.303757	1.146587	0	43910
2	1	1753	NESTED LOOPS OUTER	0.050734	0.202796	0	5259
3	2	1753	TABLE ACCESS BY INDEX ROWID PRODUCT_INFORMATION	0.052275	0.091084	1753	3506
4	3	1753	INDEX UNIQUE SCAN PRODUCT_INFORMATION_PK	0.038809	0.038809	1753	1753
5	2	0	TABLE ACCESS BY INDEX ROWID PRODUCT_DESCRIPTIONS	0.026856	0.060978	0	1753
6	5	0	INDEX UNIQUE SCAN PRD_DESC_PK	0.034122	0.034122	1753	1753
7	1	35060	TABLE ACCESS BY INDEX ROWID INVENTORIES	0.410040	0.640034	35062	38651
8	7	35060	INDEX RANGE SCAN INV_PRODUCT_IX	0.229994	0.229994	3589	3589

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 3352865145 18279731078113474405 2108772796 3522870812 15-Nov-09 19:47:59
1 3352865145 18279731078113474405 2108772796 3750204916 15-Nov-09 20:01:18

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 10.867 16.186 0 0 0 1.217 0 0 15298 0 2 24
1 .405 1.417 0 0 0 0 0 0 441 0 2 24

Correlation with V$SQL_PLAN_STATISTICS_ALL using DBMS_XPLAN.DISPLAY_CURSOR

SQL_ID  21wygrskjv4sq, child number 0
-------------------------------------
SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, CATEGORY_ID, WEIGHT_CLASS, 
WARRANTY_PERIOD, SUPPLIER_ID, PRODUCT_STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL, 
QUANTITY_ON_HAND FROM PRODUCTS, INVENTORIES WHERE PRODUCTS.PRODUCT_ID = :B1 AND 
INVENTORIES.PRODUCT_ID = PRODUCTS.PRODUCT_ID
 
Plan hash value: 2108772796
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                   | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                 |                        |     20 | 10160 |    24   (0)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER          |                        |      1 |   500 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION    |      1 |   219 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | PRODUCT_INFORMATION_PK |      1 |       |     0   (0)|          |
|   5 |    TABLE ACCESS BY INDEX ROWID| PRODUCT_DESCRIPTIONS   |      1 |   281 |     1   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN         | PRD_DESC_PK            |      1 |       |     0   (0)|          |
|   7 |   TABLE ACCESS BY INDEX ROWID | INVENTORIES            |     20 |   160 |    22   (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN           | INV_PRODUCT_IX         |     20 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$F5BB74E1
   3 - SEL$F5BB74E1 / I@SEL$2
   4 - SEL$F5BB74E1 / I@SEL$2
   5 - SEL$F5BB74E1 / D@SEL$2
   6 - SEL$F5BB74E1 / D@SEL$2
   7 - SEL$F5BB74E1 / INVENTORIES@SEL$1
   8 - SEL$F5BB74E1 / INVENTORIES@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      INDEX_RS_ASC(@"SEL$F5BB74E1" "I"@"SEL$2" ("PRODUCT_INFORMATION"."PRODUCT_ID"))
      INDEX_RS_ASC(@"SEL$F5BB74E1" "D"@"SEL$2" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" 
              "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))
      INDEX_RS_ASC(@"SEL$F5BB74E1" "INVENTORIES"@"SEL$1" ("INVENTORIES"."PRODUCT_ID"))
      LEADING(@"SEL$F5BB74E1" "I"@"SEL$2" "D"@"SEL$2" "INVENTORIES"@"SEL$1")
      USE_NL(@"SEL$F5BB74E1" "D"@"SEL$2")
      USE_NL(@"SEL$F5BB74E1" "INVENTORIES"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - (NUMBER): 92
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("I"."PRODUCT_ID"=:B1)
   6 - access("D"."PRODUCT_ID"=:B1 AND "D"."LANGUAGE_ID"=SYS_CONTEXT('USERENV','LANG'))
   8 - access("INVENTORIES"."PRODUCT_ID"=:B1)
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Execution Plans Captured by Statspack

Old hash value: 3352865145
SQL ID: 21wygrskjv4sq

Execution plan with plan hash value 2108772796 (last active 15-Nov-09 20:09:28)
Optimization: ALL_ROWS
Cost: 24
Statspack snapshot 483 of instance 1

ID PID Operation Object Object ID Rows Bytes Cost I/O Cost Temp. Space
0 SELECT STATEMENT 24
1 0 NESTED LOOPS 20 10160 24 24
2 1   NESTED LOOPS OUTER 1 500 2 2
3 2    TABLE ACCESS BY INDEX ROWID SOE.PRODUCT_INFORMATION 60741 1 219 1 1
4 3     INDEX UNIQUE SCAN SOE.PRODUCT_INFORMATION_PK 60769 1 0 0
5 2    TABLE ACCESS BY INDEX ROWID SOE.PRODUCT_DESCRIPTIONS 60743 1 281 1 1
6 5     INDEX UNIQUE SCAN SOE.PRD_DESC_PK 60770 1 0 0
7 1   TABLE ACCESS BY INDEX ROWID SOE.INVENTORIES 60740 20 160 22 22
8 7    INDEX RANGE SCAN SOE.INV_PRODUCT_IX 60773 20 1 1

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	SQL_PROFILE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2427	2870266532	1	2108772796	24	ALL_ROWS	3750204916		.002412	.001306	25
2424	2870266532	1	2108772796	24	ALL_ROWS	3522870812		.000348	.000348	25

AWR execution plan with plan hash value 2108772796 for SQL ID 21wygrskjv4sq

SQL_ID 21wygrskjv4sq
--------------------
SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, CATEGORY_ID, WEIGHT_CLASS, 
WARRANTY_PERIOD, SUPPLIER_ID, PRODUCT_STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL, 
QUANTITY_ON_HAND FROM PRODUCTS, INVENTORIES WHERE PRODUCTS.PRODUCT_ID = :B1 AND 
INVENTORIES.PRODUCT_ID = PRODUCTS.PRODUCT_ID
 
Plan hash value: 2108772796
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                   | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                        |        |       |    24 (100)|          |
|   1 |  NESTED LOOPS                 |                        |     20 | 10160 |    24   (0)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER          |                        |      1 |   500 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION    |      1 |   219 |     1   (0)| 00:00:01 |
|   4 |     INDEX UNIQUE SCAN         | PRODUCT_INFORMATION_PK |      1 |       |     0   (0)|          |
|   5 |    TABLE ACCESS BY INDEX ROWID| PRODUCT_DESCRIPTIONS   |      1 |   281 |     1   (0)| 00:00:01 |
|   6 |     INDEX UNIQUE SCAN         | PRD_DESC_PK            |      1 |       |     0   (0)|          |
|   7 |   TABLE ACCESS BY INDEX ROWID | INVENTORIES            |     20 |   160 |    22   (0)| 00:00:01 |
|   8 |    INDEX RANGE SCAN           | INV_PRODUCT_IX         |     20 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$F5BB74E1
   3 - SEL$F5BB74E1 / I@SEL$2
   4 - SEL$F5BB74E1 / I@SEL$2
   5 - SEL$F5BB74E1 / D@SEL$2
   6 - SEL$F5BB74E1 / D@SEL$2
   7 - SEL$F5BB74E1 / INVENTORIES@SEL$1
   8 - SEL$F5BB74E1 / INVENTORIES@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      INDEX_RS_ASC(@"SEL$F5BB74E1" "I"@"SEL$2" ("PRODUCT_INFORMATION"."PRODUCT_ID"))
      INDEX_RS_ASC(@"SEL$F5BB74E1" "D"@"SEL$2" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" 
              "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))
      INDEX_RS_ASC(@"SEL$F5BB74E1" "INVENTORIES"@"SEL$1" ("INVENTORIES"."PRODUCT_ID"))
      LEADING(@"SEL$F5BB74E1" "I"@"SEL$2" "D"@"SEL$2" "INVENTORIES"@"SEL$1")
      USE_NL(@"SEL$F5BB74E1" "D"@"SEL$2")
      USE_NL(@"SEL$F5BB74E1" "INVENTORIES"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - :B1 (NUMBER): 92
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Captured Bind Variables

Note: trace file contains 1591 bind section(s) (report limited to 10)

Bind Section 1:

Position	Type	Value
0	NUMBER	53

Bind Section 2:

Position	Type	Value
0	NUMBER	29

Bind Section 3:

Position	Type	Value
0	NUMBER	220

Bind Section 4:

Position	Type	Value
0	NUMBER	6

Bind Section 5:

Position	Type	Value
0	NUMBER	56

Bind Section 6:

Position	Type	Value
0	NUMBER	150

Bind Section 7:

Position	Type	Value
0	NUMBER	49

Bind Section 8:

Position	Type	Value
0	NUMBER	38

Bind Section 9:

Position	Type	Value
0	NUMBER	128

Bind Section 10:

Position	Type	Value
0	NUMBER	196

Rank 14: Statement with Hash Value 2482701865

Response Time: 5.194 s (1.02 % of delta tim)
SQL ID: 4z3ktqk9zq1j9
Force Matching Signature: 9734920470490269382
MD5 Hash Value: 21C0676FB5AE43822590BFE913819C3A
Command Type: 3 (SELECT)
Parsing User ID: 78 (SOE)
Parsing Schema ID: 78 (SOE)
Optimizer Goal: 1 (ALL_ROWS)
Recursive Call Depth: 1
Module: 'New Customer'
Action: 'getOrdersByCustomer'
Waits: 0
Total Wait Time: 0.000 s
Intra Database Call Wait Time: 0.000 s
Line Number: 811

Statement Text

SELECT CUSTOMER_SEQ.NEXTVAL FROM DUAL

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
unknown	4.943586	95.2	1	4.943586
CPU	0.250000	4.8	1118	0.000224
Total	5.193586	100.0	1119	0.004641

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
EXEC	559	5.166	0.009242	0.250	0.000447	0	0	0	0	0
FETCH	559	0.027	0.000049	0.000	0.000000	0	0	0	559	0
Total	1118	5.194	0.004645	0.250	0.000224	0	0	0	559	0
Avg. per EXEC	2.000	0.009291	n/a	0.000447	n/a	0.000	0.000	0.000	1.000	0.000
Avg. per FETCH	2.000	0.009291	n/a	0.000447	n/a	0.000	0.000	0.000	1.000	0.000
Avg. per Row	2.000	0.009291	n/a	0.000447	n/a	0.000	0.000	0.000	1.000	0.000

Execution Plan 1 (1 occurrence(s)):

ID	PID	Rows	Operation	Ela. (Self)	Ela. (Cum.)	CR (Self)	CR (Cum.)	PR (Self)	PR (Cum.)	PW (Self)	PW (Cum.)
1	0	608	SEQUENCE CUSTOMER_SEQ	0.021833	0.031601	0	0	0	0	0	0
2	1	608	FAST DUAL	0.009768	0.009768	0	0	0	0	0	0

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 2690112681 9734920470490269382 1544532951 3522870812 15-Nov-09 19:47:59
1 2690112681 9734920470490269382 1544532951 3750204916 15-Nov-09 20:01:15

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 3.237 5.082 0 0 0 .097 0 0 5030 0 2 2
1 .04 .099 0 0 0 0 0 0 133 0 2 2

Correlation with V$SQL_PLAN_STATISTICS_ALL using DBMS_XPLAN.DISPLAY_CURSOR

SQL_ID  4z3ktqk9zq1j9, child number 0
-------------------------------------
SELECT CUSTOMER_SEQ.NEXTVAL FROM DUAL
 
Plan hash value: 1544532951
 
---------------------------------------------------------------------
| Id  | Operation   | Name         | E-Rows | Cost (%CPU)| E-Time   |
---------------------------------------------------------------------
|   1 |  SEQUENCE   | CUSTOMER_SEQ |        |            |          |
|   2 |   FAST DUAL |              |      1 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   2 - SEL$1 / DUAL@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      END_OUTLINE_DATA
  */
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Execution Plans Captured by Statspack

Old hash value: 2690112681
SQL ID: 4z3ktqk9zq1j9

Execution plan with plan hash value 1544532951 (last active 15-Nov-09 20:09:27)
Optimization: ALL_ROWS
Cost: 2
Statspack snapshot 483 of instance 1

ID PID Operation Object Object ID Rows Bytes Cost I/O Cost Temp. Space
0 SELECT STATEMENT 2
1 0 SEQUENCE SOE.CUSTOMER_SEQ 60745
2 1 FAST DUAL 1 2 2

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	SQL_PROFILE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2427	2870266532	1	1544532951	2	ALL_ROWS	3750204916		.003848	.000719	0
2423	2870266532	1	1544532951	2	ALL_ROWS	3522870812		.00225	.001487	0

AWR execution plan with plan hash value 1544532951 for SQL ID 4z3ktqk9zq1j9

SQL_ID 4z3ktqk9zq1j9
--------------------
SELECT CUSTOMER_SEQ.NEXTVAL FROM DUAL
 
Plan hash value: 1544532951
 
--------------------------------------------------------------------------
| Id  | Operation        | Name         | E-Rows | Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |        |     2 (100)|          |
|   1 |  SEQUENCE        | CUSTOMER_SEQ |        |            |          |
|   2 |   FAST DUAL      |              |      1 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   2 - SEL$1 / DUAL@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      END_OUTLINE_DATA
  */
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Rank 15: Statement with Hash Value 3524147527

Response Time: 4.235 s (0.83 % of delta tim)
Recursive Elapsed Time: 0.075 s
SQL ID: gvgdv2v90wfa7
Force Matching Signature: 0
MD5 Hash Value: 2D4615A2CDDE6CC2710572FF58021
Command Type: 2 (INSERT)
Parsing User ID: 78 (SOE)
Parsing Schema ID: 78 (SOE)
Optimizer Goal: 1 (ALL_ROWS)
Recursive Call Depth: 1
Module: 'New Order'
Action: 'getProductDetailsByCategory'
Waits: 182
Total Wait Time: 2.494 s
Intra Database Call Wait Time: 2.494 s
Line Number: 166

Statement Text

INSERT INTO ORDERS(ORDER_ID, ORDER_DATE, CUSTOMER_ID) VALUES (ORDERS_SEQ.NEXTVAL , SYSTIMESTAMP , :B1 ) RETURNING ORDER_ID INTO :O0

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
db file sequential read	2.483177	58.6	181	0.013719
unknown	1.459880	34.5	1	1.459880
CPU	0.281250	6.6	597	0.000471
buffer busy waits	0.010726	0.3	1	0.010726
Total	4.235033	100.0	780	0.005430

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
EXEC	597	4.235	0.007094	0.281	0.000471	187	32	7142	597	0
FETCH	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
Total	597	4.235	0.007094	0.281	0.000471	187	32	7142	597	0
Avg. per EXEC	1.000	0.007094	n/a	0.000471	n/a	0.313	0.054	11.963	1.000	0.000
Avg. per Row	1.000	0.007094	n/a	0.000471	n/a	0.313	0.054	11.963	1.000	0.000

Recursive Descendants

The statement has 1 recursive descendants that have contributed an elapsed time of 0.075 s. For each recursive statement, an excerpt of at most 60 characters from the statement text is shown.

Hash Value Elapsed Time (s) Parsing ID Statement Text (Excerpt)
2635489469 0.075 0 (SYS) update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#

Execution Plan 1 (1 occurrence(s)):

ID	PID	Rows	Operation	Ela. (Self)	Ela. (Cum.)	CR (Self)	CR (Cum.)	PR (Self)	PR (Cum.)	PW (Self)	PW (Cum.)
1	0	647	SEQUENCE ORDERS_SEQ	0.092525	0.092525	2	2	4	4	0	0

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 2082097592 0 494735477 3522870812 15-Nov-09 19:47:59
1 2082097592 0 494735477 3750204916 15-Nov-09 20:01:16

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 4.274 18.993 0 0 0 13.761 0 0 5098 0 2 1
1 .106 3.726 0 0 0 3.525 0 0 140 0 2 1

Correlation with V$SQL_PLAN_STATISTICS_ALL using DBMS_XPLAN.DISPLAY_CURSOR

SQL_ID  gvgdv2v90wfa7, child number 0
-------------------------------------
INSERT INTO ORDERS(ORDER_ID, ORDER_DATE, CUSTOMER_ID) VALUES 
(ORDERS_SEQ.NEXTVAL , SYSTIMESTAMP , :B1 ) RETURNING ORDER_ID INTO :O0
 
Plan hash value: 494735477
 
--------------------------------
| Id  | Operation | Name       |
--------------------------------
|   1 |  SEQUENCE | ORDERS_SEQ |
--------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - INS$1
 
Note
-----
   - rule based optimizer used (consider using cbo)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Execution Plans Captured by Statspack

Old hash value: 2082097592
SQL ID: gvgdv2v90wfa7

Execution plan with plan hash value 494735477 (last active 15-Nov-09 20:09:28)
Optimization: ALL_ROWS
Cost: 1
Statspack snapshot 483 of instance 1

ID PID Operation Object Object ID Rows Bytes Cost I/O Cost Temp. Space
0 INSERT STATEMENT 1
1 0 SEQUENCE SOE.ORDERS_SEQ 60744

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	SQL_PROFILE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2427	2870266532	1	494735477	1	ALL_ROWS	3750204916		.005623	.000708	12
2424	2870266532	1	494735477	1	ALL_ROWS	3522870812		.003395	.000451	12.1

AWR execution plan with plan hash value 494735477 for SQL ID gvgdv2v90wfa7

SQL_ID gvgdv2v90wfa7
--------------------
INSERT INTO ORDERS(ORDER_ID, ORDER_DATE, CUSTOMER_ID) VALUES 
(ORDERS_SEQ.NEXTVAL , SYSTIMESTAMP , :B1 ) RETURNING ORDER_ID INTO :O0
 
Plan hash value: 494735477
 
-----------------------------------------------
| Id  | Operation        | Name       | Cost  |
-----------------------------------------------
|   0 | INSERT STATEMENT |            |     1 |
|   1 |  SEQUENCE        | ORDERS_SEQ |       |
-----------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - INS$1
 
Note
-----
   - cpu costing is off (consider enabling it)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Physical Reads by Database Object

Obj. ID	SB Reads	SBR Time	Avg. SBR Time	Owner & Segment
60779	149	2.048	0.014	SOE.ORD_ORDER_DATE_IX
60778	23	0.299	0.013	SOE.ORD_CUSTOMER_IX
60764	3	0.057	0.019	SOE.CUSTOMERS_PK
60739	4	0.052	0.013	SOE.ORDERS
0	2	0.027	0.014

Buffer Busy Waits with Wait Time >= 1 ms

File & Block	Count	Wait Time (s)
9.19264	1	0.011

Captured Bind Variables

Note: trace file contains 596 bind section(s) (report limited to 10)

Bind Section 1:

Position	Type	Value
0	NUMBER	146609
1	NUMBER

Bind Section 2:

Position	Type	Value
0	NUMBER	398168
1	NUMBER

Bind Section 3:

Position	Type	Value
0	NUMBER	912983
1	NUMBER

Bind Section 4:

Position	Type	Value
0	NUMBER	299795
1	NUMBER

Bind Section 5:

Position	Type	Value
0	NUMBER	988201
1	NUMBER

Bind Section 6:

Position	Type	Value
0	NUMBER	174073
1	NUMBER

Bind Section 7:

Position	Type	Value
0	NUMBER	114327
1	NUMBER

Bind Section 8:

Position	Type	Value
0	NUMBER	669288
1	NUMBER

Bind Section 9:

Position	Type	Value
0	NUMBER	177360
1	NUMBER

Bind Section 10:

Position	Type	Value
0	NUMBER	167351
1	NUMBER

Rank 16: Statement with Hash Value 820158466

Response Time: 3.300 s (0.65 % of delta tim)
SQL ID: 05s4vdwsf5802
Force Matching Signature: 17860808987328922672
MD5 Hash Value: A2F07C5562DA690C3A0BB7F8C258D3
Command Type: 3 (SELECT)
Parsing User ID: 78 (SOE)
Parsing Schema ID: 78 (SOE)
Optimizer Goal: 1 (ALL_ROWS)
Recursive Call Depth: 1
Module: 'Browse and Update Orders'
Action: 'getOrdersByCustomer'
Waits: 46
Total Wait Time: 0.726 s
Intra Database Call Wait Time: 0.726 s
Line Number: 754

Statement Text

SELECT ORDER_ID, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS, ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID FROM ORDERS WHERE CUSTOMER_ID = :B1

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
unknown	2.371145	71.8	1	2.371145
db file sequential read	0.726203	22.0	46	0.015787
CPU	0.203125	6.2	1124	0.000181
Total	3.300473	100.0	1171	0.002819

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
EXEC	562	2.551	0.004540	0.141	0.000250	0	0	0	0	0
FETCH	562	0.749	0.001333	0.063	0.000111	46	1148	0	20	0
Total	1124	3.300	0.002936	0.203	0.000181	46	1148	0	20	0
Avg. per EXEC	2.000	0.005873	n/a	0.000361	n/a	0.082	2.043	0.000	0.036	0.000
Avg. per FETCH	2.000	0.005873	n/a	0.000361	n/a	0.082	2.043	0.000	0.036	0.000
Avg. per Row	56.200	0.165024	n/a	0.010156	n/a	2.300	57.400	0.000	1.000	0.000

Execution Plan 1 (1 occurrence(s)):

ID	PID	Rows	Operation	Ela. (Self)	Ela. (Cum.)	CR (Self)	CR (Cum.)	PR (Self)	PR (Cum.)	PW (Self)	PW (Cum.)
1	0	23	TABLE ACCESS BY INDEX ROWID ORDERS	0.359290	1.290014	23	1271	23	80	0	0
2	1	23	INDEX RANGE SCAN ORD_CUSTOMER_IX	0.930724	0.930724	1248	1248	57	57	0	0

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 287228808 17860808987328922672 3237218561 3522870812 15-Nov-09 19:47:59
1 287228808 17860808987328922672 3237218561 3750204916 15-Nov-09 20:01:17

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 3.384 9.269 0 0 0 4.276 0 0 5021 0 2 3
1 .095 1.272 0 0 0 1.065 0 0 138 0 2 3

Correlation with V$SQL_PLAN_STATISTICS_ALL using DBMS_XPLAN.DISPLAY_CURSOR

SQL_ID  05s4vdwsf5802, child number 0
-------------------------------------
SELECT ORDER_ID, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS, ORDER_TOTAL, SALES_REP_ID, 
PROMOTION_ID FROM ORDERS WHERE CUSTOMER_ID = :B1
 
Plan hash value: 3237218561
 
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| ORDERS          |      1 |    29 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ORD_CUSTOMER_IX |      1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / ORDERS@SEL$1
   2 - SEL$1 / ORDERS@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "ORDERS"@"SEL$1" ("ORDERS"."CUSTOMER_ID"))
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - (NUMBER): 852767
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CUSTOMER_ID"=:B1)
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Execution Plans Captured by Statspack

Old hash value: 287228808
SQL ID: 05s4vdwsf5802

Execution plan with plan hash value 3237218561 (last active 15-Nov-09 20:09:28)
Optimization: ALL_ROWS
Cost: 3
Statspack snapshot 483 of instance 1

ID PID Operation Object Object ID Rows Bytes Cost I/O Cost Temp. Space
0 SELECT STATEMENT 3
1 0 TABLE ACCESS BY INDEX ROWID SOE.ORDERS 60739 1 29 3 3
2 1 INDEX RANGE SCAN SOE.ORD_CUSTOMER_IX 60778 1 1 1

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	SQL_PROFILE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2427	2870266532	1	3237218561	3	ALL_ROWS	3750204916		.003373	.000606	2
2424	2870266532	1	3237218561	3	ALL_ROWS	3522870812		.000974	.000302	2

AWR execution plan with plan hash value 3237218561 for SQL ID 05s4vdwsf5802

SQL_ID 05s4vdwsf5802
--------------------
SELECT ORDER_ID, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS, ORDER_TOTAL, SALES_REP_ID, 
PROMOTION_ID FROM ORDERS WHERE CUSTOMER_ID = :B1
 
Plan hash value: 3237218561
 
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |        |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| ORDERS          |      1 |    29 |     3   (0)| 00:00:01 |
|   2 |   INDEX RANGE SCAN          | ORD_CUSTOMER_IX |      1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / ORDERS@SEL$1
   2 - SEL$1 / ORDERS@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "ORDERS"@"SEL$1" ("ORDERS"."CUSTOMER_ID"))
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - :B1 (NUMBER): 852767
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Physical Reads by Database Object

Obj. ID	SB Reads	SBR Time	Avg. SBR Time	MB Reads	MBR Time	Avg. MBR Time	MBR Blocks	Avg. Blocks	Owner & Segment
60778	26	0.424	0.016	0	0.000	0.000	0	0.0	SOE.ORD_CUSTOMER_IX
60739	20	0.302	0.015	0	0.000	0.000	0	0.0	SOE.ORDERS

Captured Bind Variables

Note: trace file contains 561 bind section(s) (report limited to 10)

Bind Section 1:

Position	Type	Value
0	NUMBER	319705

Bind Section 2:

Position	Type	Value
0	NUMBER	65945

Bind Section 3:

Position	Type	Value
0	NUMBER	976867

Bind Section 4:

Position	Type	Value
0	NUMBER	207370

Bind Section 5:

Position	Type	Value
0	NUMBER	705210

Bind Section 6:

Position	Type	Value
0	NUMBER	871184

Bind Section 7:

Position	Type	Value
0	NUMBER	419672

Bind Section 8:

Position	Type	Value
0	NUMBER	294145

Bind Section 9:

Position	Type	Value
0	NUMBER	560920

Bind Section 10:

Position	Type	Value
0	NUMBER	153181

Rank 17: Statement with Hash Value 4196474773

Response Time: 1.004 s (0.20 % of delta tim)
SQL ID: 5raw2bzx227wp
Force Matching Signature: 0
MD5 Hash Value: EF4C89DC011FEC5EFA4F29ABB1C4AA
Command Type: 2 (INSERT)
Parsing User ID: 78 (SOE)
Parsing Schema ID: 78 (SOE)
Optimizer Goal: 1 (ALL_ROWS)
Recursive Call Depth: 1
Module: 'New Order'
Action: 'logon'
Waits: 11
Total Wait Time: 0.256 s
Intra Database Call Wait Time: 0.256 s
Line Number: 94

Statement Text

INSERT INTO LOGON VALUES (:B2 , :B1 )

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
CPU	0.718750	71.6	2262	0.000318
log file switch completion	0.153534	15.3	1	0.153534
db file sequential read	0.102718	10.2	10	0.010272
unknown	0.028675	2.9	1	0.028675
Total	1.003677	100.0	2274	0.000441

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
EXEC	2262	1.004	0.000444	0.719	0.000318	10	1	6458	2262	0
FETCH	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
Total	2262	1.004	0.000444	0.719	0.000318	10	1	6458	2262	0
Avg. per EXEC	1.000	0.000444	n/a	0.000318	n/a	0.004	0.000	2.855	1.000	0.000
Avg. per Row	1.000	0.000444	n/a	0.000318	n/a	0.004	0.000	2.855	1.000	0.000

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 1809837244 0 0 3522870812 15-Nov-09 19:47:59
1 1809837244 0 0 3750204916 15-Nov-09 20:01:17

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 4.183 4.877 0 0 0 .862 0 0 20203 0 2 1
1 .059 .074 0 0 0 .018 0 0 560 0 2 1

Correlation with V$SQL_PLAN_STATISTICS_ALL using DBMS_XPLAN.DISPLAY_CURSOR

SQL_ID  5raw2bzx227wp, child number 0
                                     
INSERT INTO LOGON VALUES (:B2 , :B1 )
 
NOTE: cannot fetch plan for SQL_ID: 5raw2bzx227wp, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER; 
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	SQL_PROFILE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2427	2870266532	1	0	1	ALL_ROWS	3750204916		.000269	.000195	2.9
2424	2870266532	1	0	1	ALL_ROWS	3522870812		.00014	.0001	3

Physical Reads by Database Object

Obj. ID	SB Reads	SBR Time	Avg. SBR Time	MB Reads	MBR Time	Avg. MBR Time	MBR Blocks	Avg. Blocks	Owner & Segment
60742	7	0.067	0.010	0	0.000	0.000	0	0.0	SOE.LOGON
0	3	0.035	0.012	0	0.000	0.000	0	0.0

Captured Bind Variables

Note: trace file contains 2261 bind section(s) (report limited to 10)

Bind Section 1:

Position	Type	Value
0	NUMBER	146609
1	DATE	"11/15/2009 20:1:0"

Bind Section 2:

Position	Type	Value
0	NUMBER	16803
1	DATE	"11/15/2009 20:1:0"

Bind Section 3:

Position	Type	Value
0	NUMBER	1084193
1	DATE	"11/15/2009 20:1:0"

Bind Section 4:

Position	Type	Value
0	NUMBER	1084194
1	DATE	"11/15/2009 20:1:1"

Bind Section 5:

Position	Type	Value
0	NUMBER	398168
1	DATE	"11/15/2009 20:1:1"

Bind Section 6:

Position	Type	Value
0	NUMBER	912983
1	DATE	"11/15/2009 20:1:2"

Bind Section 7:

Position	Type	Value
0	NUMBER	1084201
1	DATE	"11/15/2009 20:1:3"

Bind Section 8:

Position	Type	Value
0	NUMBER	319705
1	DATE	"11/15/2009 20:1:3"

Bind Section 9:

Position	Type	Value
0	NUMBER	299795
1	DATE	"11/15/2009 20:1:3"

Bind Section 10:

Position	Type	Value
0	NUMBER	988201
1	DATE	"11/15/2009 20:1:4"

Rank 18: Statement with Hash Value 1344628361

Response Time: 0.798 s (0.16 % of delta tim)
SQL ID: 7r7636982atn9
Force Matching Signature: 8080224161820109919
MD5 Hash Value: 5B3C2D88667C5BF0F214C915A2A8CCE
Command Type: 6 (UPDATE)
Parsing User ID: 78 (SOE)
Parsing Schema ID: 78 (SOE)
Optimizer Goal: 1 (ALL_ROWS)
Recursive Call Depth: 1
Module: 'New Order'
Action: 'getProductQuantity'
Waits: 0
Total Wait Time: 0.000 s
Intra Database Call Wait Time: 0.000 s
Line Number: 314

Statement Text

UPDATE INVENTORIES SET QUANTITY_ON_HAND = QUANTITY_ON_HAND - :B1 WHERE PRODUCT_ID = :B3 AND WAREHOUSE_ID = :B2

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
unknown	0.407807	51.1	1	0.407807
CPU	0.390625	48.9	597	0.000654
Total	0.798432	100.0	598	0.001335

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
EXEC	597	0.798	0.001337	0.391	0.000654	0	3282	4246	2088	0
FETCH	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
Total	597	0.798	0.001337	0.391	0.000654	0	3282	4246	2088	0
Avg. per EXEC	1.000	0.001337	n/a	0.000654	n/a	0.000	5.497	7.112	3.497	0.000
Avg. per Row	0.286	0.000382	n/a	0.000187	n/a	0.000	1.572	2.034	1.000	0.000

Execution Plan 1 (1 occurrence(s)):

ID	PID	Rows	Operation	Ela. (Self)	Ela. (Cum.)	CR (Self)	CR (Cum.)	PR (Self)	PR (Cum.)	PW (Self)	PW (Cum.)
1	0	2263	UPDATE INVENTORIES	0.094864	0.128712	0	3557	0	0	0	0
2	1	2263	INDEX UNIQUE SCAN INVENTORY_PK	0.033848	0.033848	3557	3557	0	0	0	0

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 2385983258 8080224161820109919 2141863993 3522870812 15-Nov-09 19:47:59
1 2385983258 8080224161820109919 2141863993 3750204916 15-Nov-09 20:01:16

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 3.172 3.291 0 0 0 0 0 0 5098 0 2 2
1 .059 .124 0 0 0 0 0 0 140 0 2 2

Correlation with V$SQL_PLAN_STATISTICS_ALL using DBMS_XPLAN.DISPLAY_CURSOR

SQL_ID  7r7636982atn9, child number 0
-------------------------------------
UPDATE INVENTORIES SET QUANTITY_ON_HAND = QUANTITY_ON_HAND - :B1 WHERE 
PRODUCT_ID = :B3 AND WAREHOUSE_ID = :B2
 
Plan hash value: 2141863993
 
------------------------------------------------------------------------------------
| Id  | Operation          | Name         | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
------------------------------------------------------------------------------------
|   1 |  UPDATE            | INVENTORIES  |        |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| INVENTORY_PK |      1 |    10 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - UPD$1
   2 - UPD$1 / INVENTORIES@UPD$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"UPD$1")
      INDEX(@"UPD$1" "INVENTORIES"@"UPD$1" ("INVENTORIES"."PRODUCT_ID" 
              "INVENTORIES"."WAREHOUSE_ID"))
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   2 - (NUMBER): 191
   3 - (NUMBER): 6
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("PRODUCT_ID"=:B3 AND "WAREHOUSE_ID"=:B2)
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Execution Plans Captured by Statspack

Old hash value: 2385983258
SQL ID: 7r7636982atn9

Execution plan with plan hash value 2141863993 (last active 15-Nov-09 20:09:28)
Optimization: ALL_ROWS
Cost: 2
Statspack snapshot 483 of instance 1

ID PID Operation Object Object ID Rows Bytes Cost I/O Cost Temp. Space
0 UPDATE STATEMENT 2
1 0 UPDATE SOE.INVENTORIES
2 1 INDEX UNIQUE SCAN SOE.INVENTORY_PK 60771 1 10 1 1

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	SQL_PROFILE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2427	2870266532	1	2141863993	2	ALL_ROWS	3750204916		.000718	.000718	12.7
2423	2870266532	1	2141863993	2	ALL_ROWS	3522870812		.001385	.001339	12.7

AWR execution plan with plan hash value 2141863993 for SQL ID 7r7636982atn9

SQL_ID 7r7636982atn9
--------------------
UPDATE INVENTORIES SET QUANTITY_ON_HAND = QUANTITY_ON_HAND - :B1 WHERE 
PRODUCT_ID = :B3 AND WAREHOUSE_ID = :B2
 
Plan hash value: 2141863993
 
------------------------------------------------------------------------------------
| Id  | Operation          | Name         | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |              |        |       |     2 (100)|          |
|   1 |  UPDATE            | INVENTORIES  |        |       |            |          |
|   2 |   INDEX UNIQUE SCAN| INVENTORY_PK |      1 |    10 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - UPD$1
   2 - UPD$1 / INVENTORIES@UPD$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"UPD$1")
      INDEX(@"UPD$1" "INVENTORIES"@"UPD$1" ("INVENTORIES"."PRODUCT_ID" 
              "INVENTORIES"."WAREHOUSE_ID"))
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   2 - :B3 (NUMBER): 191
   3 - :B2 (NUMBER): 6
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Captured Bind Variables

Note: trace file contains 2086 bind section(s) (report limited to 10)

Bind Section 1:

Position	Type	Value
0	NUMBER	1
1	NUMBER	189
2	NUMBER	8

Bind Section 2:

Position	Type	Value
0	NUMBER	1
1	NUMBER	199
2	NUMBER	5

Bind Section 3:

Position	Type	Value
0	NUMBER	1
1	NUMBER	209
2	NUMBER	2

Bind Section 4:

Position	Type	Value
0	NUMBER	1
1	NUMBER	219
2	NUMBER	5

Bind Section 5:

Position	Type	Value
0	NUMBER	1
1	NUMBER	189
2	NUMBER	7

Bind Section 6:

Position	Type	Value
0	NUMBER	1
1	NUMBER	199
2	NUMBER	7

Bind Section 7:

Position	Type	Value
0	NUMBER	1
1	NUMBER	186
2	NUMBER	2

Bind Section 8:

Position	Type	Value
0	NUMBER	1
1	NUMBER	196
2	NUMBER	3

Bind Section 9:

Position	Type	Value
0	NUMBER	1
1	NUMBER	206
2	NUMBER	7

Bind Section 10:

Position	Type	Value
0	NUMBER	1
1	NUMBER	192
2	NUMBER	3

Rank 19: Statement with Hash Value 255718823

Response Time: 0.479 s (0.09 % of delta tim)
Command Type: 44 (COMMIT)
Parsing User ID: 78 (SOE)
Parsing Schema ID: 78 (SOE)
Optimizer Goal: 0 (optimizer goal for og=0 unavailable)
Recursive Call Depth: 1
Module: 'New Order'
Action: 'logon'
Waits: 0
Total Wait Time: 0.000 s
Intra Database Call Wait Time: 0.000 s
Line Number: 100

Statement Text

COMMIT

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
CPU	0.328125	68.4	4014	0.000082
unknown	0.151319	31.6	1	0.151319
Total	0.479444	100.0	4015	0.000119

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
EXEC	4014	0.479	0.000119	0.328	0.000082	0	0	4015	0	0
FETCH	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
Total	4014	0.479	0.000119	0.328	0.000082	0	0	4015	0	0
Avg. per EXEC	1.000	0.000119	n/a	0.000082	n/a	0.000	0.000	1.000	0.000	0.000

Rank 20: Statement with Hash Value 914625479

Response Time: 0.443 s (0.09 % of delta tim)
SQL ID: f9u2k84v884y7
Force Matching Signature: 2971448457592404441
MD5 Hash Value: D2A19D4354945E474DD3C59C3ED7F856
Command Type: 6 (UPDATE)
Parsing User ID: 78 (SOE)
Parsing Schema ID: 78 (SOE)
Optimizer Goal: 1 (ALL_ROWS)
Recursive Call Depth: 1
Module: 'Process Orders'
Action: 'getCustomerDetails'
Waits: 8
Total Wait Time: 0.086 s
Intra Database Call Wait Time: 0.086 s
Line Number: 1115

Statement Text

UPDATE /*+ index(orders, order_pk) */ ORDERS SET ORDER_STATUS = FLOOR(DBMS_RANDOM.VALUE(:B3 + 1, :B2 )) WHERE ORDER_ID = :B1

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
CPU	0.187500	42.3	596	0.000315
unknown	0.170282	38.4	1	0.170282
db file sequential read	0.085638	19.3	8	0.010705
Total	0.443420	100.0	605	0.000733

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
EXEC	596	0.443	0.000744	0.188	0.000315	8	1268	4771	596	0
FETCH	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
Total	596	0.443	0.000744	0.188	0.000315	8	1268	4771	596	0
Avg. per EXEC	1.000	0.000744	n/a	0.000315	n/a	0.013	2.128	8.005	1.000	0.000
Avg. per Row	1.000	0.000744	n/a	0.000315	n/a	0.013	2.128	8.005	1.000	0.000

Execution Plan 1 (1 occurrence(s)):

ID	PID	Rows	Operation	Ela. (Self)	Ela. (Cum.)	CR (Self)	CR (Cum.)	PR (Self)	PR (Cum.)	PW (Self)	PW (Cum.)
1	0	653	UPDATE ORDERS	0.198536	0.253075	10	1389	9	12	0	0
2	1	653	INDEX UNIQUE SCAN ORDER_PK	0.054539	0.054539	1379	1379	3	3	0	0

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 4220433507 2971448457592404441 1628223527 3522870812 15-Nov-09 19:47:59
1 4220433507 2971448457592404441 1628223527 3750204916 15-Nov-09 20:01:15

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 1.952 3.722 0 0 0 1.81 .06 0 5072 0 2 2
1 .049 .132 0 0 0 .092 .002 0 140 0 2 2

Correlation with V$SQL_PLAN_STATISTICS_ALL using DBMS_XPLAN.DISPLAY_CURSOR

SQL_ID  f9u2k84v884y7, child number 0
-------------------------------------
UPDATE /*+ index(orders, order_pk) */ ORDERS SET ORDER_STATUS = 
FLOOR(DBMS_RANDOM.VALUE(:B3 + 1, :B2 )) WHERE ORDER_ID = :B1
 
Plan hash value: 1628223527
 
--------------------------------------------------------------------------------
| Id  | Operation          | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------
|   1 |  UPDATE            | ORDERS   |        |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| ORDER_PK |      1 |     7 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - UPD$1
   2 - UPD$1 / ORDERS@UPD$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"UPD$1")
      INDEX(@"UPD$1" "ORDERS"@"UPD$1" ("ORDERS"."ORDER_ID"))
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   3 - (NUMBER): 39668
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ORDER_ID"=:B1)
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Execution Plans Captured by Statspack

Old hash value: 4220433507
SQL ID: f9u2k84v884y7

Execution plan with plan hash value 1628223527 (last active 15-Nov-09 20:09:28)
Optimization: ALL_ROWS
Cost: 2
Statspack snapshot 483 of instance 1

ID PID Operation Object Object ID Rows Bytes Cost I/O Cost Temp. Space
0 UPDATE STATEMENT 2
1 0 UPDATE SOE.ORDERS
2 1 INDEX UNIQUE SCAN SOE.ORDER_PK 60768 1 15 1 1

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	SQL_PROFILE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2427	2870266532	1	1628223527	2	ALL_ROWS	3750204916		.000523	.000414	10.2
2424	2870266532	1	1628223527	2	ALL_ROWS	3522870812		.000402	.000227	10.1

AWR execution plan with plan hash value 1628223527 for SQL ID f9u2k84v884y7

SQL_ID f9u2k84v884y7
--------------------
UPDATE /*+ index(orders, order_pk) */ ORDERS SET ORDER_STATUS = 
FLOOR(DBMS_RANDOM.VALUE(:B3 + 1, :B2 )) WHERE ORDER_ID = :B1
 
Plan hash value: 1628223527
 
--------------------------------------------------------------------------------
| Id  | Operation          | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |          |        |       |     2 (100)|          |
|   1 |  UPDATE            | ORDERS   |        |       |            |          |
|   2 |   INDEX UNIQUE SCAN| ORDER_PK |      1 |     7 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - UPD$1
   2 - UPD$1 / ORDERS@UPD$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"UPD$1")
      INDEX(@"UPD$1" "ORDERS"@"UPD$1" ("ORDERS"."ORDER_ID"))
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   3 - :B1 (NUMBER): 39668
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Physical Reads by Database Object

Obj. ID	SB Reads	SBR Time	Avg. SBR Time	MB Reads	MBR Time	Avg. MBR Time	MBR Blocks	Avg. Blocks	Owner & Segment
60780	5	0.045	0.009	0	0.000	0.000	0	0.0	SOE.ORD_STATUS_IX
60768	3	0.040	0.013	0	0.000	0.000	0	0.0	SOE.ORDER_PK

Captured Bind Variables

Note: trace file contains 595 bind section(s) (report limited to 10)

Bind Section 1:

Position	Type	Value
0	NUMBER	4
1	NUMBER	10
2	NUMBER	45096

Bind Section 2:

Position	Type	Value
0	NUMBER	4
1	NUMBER	10
2	NUMBER	41236

Bind Section 3:

Position	Type	Value
0	NUMBER	4
1	NUMBER	10
2	NUMBER	45098

Bind Section 4:

Position	Type	Value
0	NUMBER	4
1	NUMBER	10
2	NUMBER	45102

Bind Section 5:

Position	Type	Value
0	NUMBER	4
1	NUMBER	10
2	NUMBER	45104

Bind Section 6:

Position	Type	Value
0	NUMBER	4
1	NUMBER	10
2	NUMBER	45116

Bind Section 7:

Position	Type	Value
0	NUMBER	4
1	NUMBER	10
2	NUMBER	45125

Bind Section 8:

Position	Type	Value
0	NUMBER	4
1	NUMBER	10
2	NUMBER	45126

Bind Section 9:

Position	Type	Value
0	NUMBER	4
1	NUMBER	10
2	NUMBER	45127

Bind Section 10:

Position	Type	Value
0	NUMBER	4
1	NUMBER	10
2	NUMBER	45129

Rank 21: Statement with Hash Value 2990726627

Response Time: 0.355 s (0.07 % of delta tim)
SQL ID: 5mddt5kt45rg3
Force Matching Signature: 14366533292145951164
MD5 Hash Value: 99AECE571A5750FE12462E1861AC1325
Command Type: 6 (UPDATE)
Parsing User ID: 78 (SOE)
Parsing Schema ID: 78 (SOE)
Optimizer Goal: 1 (ALL_ROWS)
Recursive Call Depth: 1
Module: 'New Order'
Action: 'getProductQuantity'
Waits: 7
Total Wait Time: 0.044 s
Intra Database Call Wait Time: 0.044 s
Line Number: 275

Statement Text

UPDATE ORDERS SET ORDER_MODE = 'online', ORDER_STATUS = FLOOR(DBMS_RANDOM.VALUE(0, :B3 )), ORDER_TOTAL = :B2 WHERE ORDER_ID = :B1

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
CPU	0.203125	57.2	597	0.000340
unknown	0.108121	30.4	1	0.108121
buffer busy waits	0.026951	7.6	2	0.013476
db file sequential read	0.017108	4.8	5	0.003422
Total	0.355305	100.0	605	0.000587

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
EXEC	597	0.355	0.000595	0.203	0.000340	5	1365	2422	597	0
FETCH	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
Total	597	0.355	0.000595	0.203	0.000340	5	1365	2422	597	0
Avg. per EXEC	1.000	0.000595	n/a	0.000340	n/a	0.008	2.286	4.057	1.000	0.000
Avg. per Row	1.000	0.000595	n/a	0.000340	n/a	0.008	2.286	4.057	1.000	0.000

Execution Plan 1 (1 occurrence(s)):

ID	PID	Rows	Operation	Ela. (Self)	Ela. (Cum.)	CR (Self)	CR (Cum.)	PR (Self)	PR (Cum.)	PW (Self)	PW (Cum.)
1	0	647	UPDATE ORDERS	0.135025	0.152977	1	1487	6	6	0	0
2	1	647	INDEX UNIQUE SCAN ORDER_PK	0.017952	0.017952	1486	1486	0	0	0	0

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 3812575787 14366533292145951164 1628223527 3522870812 15-Nov-09 19:47:59
1 3812575787 14366533292145951164 1628223527 3750204916 15-Nov-09 20:01:16

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 1.598 1.779 0 0 0 .142 .076 0 5098 0 2 2
1 .037 .037 0 0 0 0 .002 0 140 0 2 2

Correlation with V$SQL_PLAN_STATISTICS_ALL using DBMS_XPLAN.DISPLAY_CURSOR

SQL_ID  5mddt5kt45rg3, child number 0
-------------------------------------
UPDATE ORDERS SET ORDER_MODE = 'online', ORDER_STATUS = 
FLOOR(DBMS_RANDOM.VALUE(0, :B3 )), ORDER_TOTAL = :B2 WHERE ORDER_ID = 
:B1
 
Plan hash value: 1628223527
 
--------------------------------------------------------------------------------
| Id  | Operation          | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------
|   1 |  UPDATE            | ORDERS   |        |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| ORDER_PK |      1 |    15 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - UPD$1
   2 - UPD$1 / ORDERS@UPD$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"UPD$1")
      INDEX(@"UPD$1" "ORDERS"@"UPD$1" ("ORDERS"."ORDER_ID"))
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   3 - (NUMBER): 39885
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ORDER_ID"=:B1)
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Execution Plans Captured by Statspack

Old hash value: 3812575787
SQL ID: 5mddt5kt45rg3

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	SQL_PROFILE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2427	2870266532	1	1628223527	2	ALL_ROWS	3750204916		.000384	.000349	6.4
2424	2870266532	1	1628223527	2	ALL_ROWS	3522870812		.000192	.000168	6.1

AWR execution plan with plan hash value 1628223527 for SQL ID 5mddt5kt45rg3

SQL_ID 5mddt5kt45rg3
--------------------
UPDATE ORDERS SET ORDER_MODE = 'online', ORDER_STATUS = 
FLOOR(DBMS_RANDOM.VALUE(0, :B3 )), ORDER_TOTAL = :B2 WHERE ORDER_ID = 
:B1
 
Plan hash value: 1628223527
 
--------------------------------------------------------------------------------
| Id  | Operation          | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |          |        |       |     2 (100)|          |
|   1 |  UPDATE            | ORDERS   |        |       |            |          |
|   2 |   INDEX UNIQUE SCAN| ORDER_PK |      1 |    15 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - UPD$1
   2 - UPD$1 / ORDERS@UPD$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"UPD$1")
      INDEX(@"UPD$1" "ORDERS"@"UPD$1" ("ORDERS"."ORDER_ID"))
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   3 - :B1 (NUMBER): 39885
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Physical Reads by Database Object

Obj. ID	SB Reads	SBR Time	Avg. SBR Time	MB Reads	MBR Time	Avg. MBR Time	MBR Blocks	Avg. Blocks	Owner & Segment
60780	5	0.017	0.003	0	0.000	0.000	0	0.0	SOE.ORD_STATUS_IX

Buffer Busy Waits with Wait Time >= 1 ms

File & Block	Count	Wait Time (s)
7.12198	2	0.027

Captured Bind Variables

Note: trace file contains 596 bind section(s) (report limited to 10)

Bind Section 1:

Position	Type	Value
0	NUMBER	4
1	NUMBER	146
2	NUMBER	45094

Bind Section 2:

Position	Type	Value
0	NUMBER	4
1	NUMBER	21
2	NUMBER	45095

Bind Section 3:

Position	Type	Value
0	NUMBER	4
1	NUMBER	257
2	NUMBER	45096

Bind Section 4:

Position	Type	Value
0	NUMBER	4
1	NUMBER	203
2	NUMBER	45098

Bind Section 5:

Position	Type	Value
0	NUMBER	4
1	NUMBER	556
2	NUMBER	45100

Bind Section 6:

Position	Type	Value
0	NUMBER	4
1	NUMBER	153
2	NUMBER	45102

Bind Section 7:

Position	Type	Value
0	NUMBER	4
1	NUMBER	297
2	NUMBER	45105

Bind Section 8:

Position	Type	Value
0	NUMBER	4
1	NUMBER	556
2	NUMBER	45107

Bind Section 9:

Position	Type	Value
0	NUMBER	4
1	NUMBER	112
2	NUMBER	45109

Bind Section 10:

Position	Type	Value
0	NUMBER	4
1	NUMBER	21
2	NUMBER	45111

Rank 22: Statement with Hash Value 2635489469

Response Time: 0.075 s (0.01 % of delta tim)
SQL ID: 4m7m0t6fjcs5x
Force Matching Signature: 15379430395219187294
MD5 Hash Value: 77474481C59122417CF527A18419AE9
Command Type: 6 (UPDATE)
Parsing User ID: 0 (SYS)
Parsing Schema ID: 0 (SYS)
Optimizer Goal: 4 (CHOOSE)
Recursive Call Depth: 2
Module: 'New Order'
Action: 'getProductDetailsByCategory'
Waits: 4
Total Wait Time: 0.072 s
Intra Database Call Wait Time: 0.072 s
Line Number: 121757

Statement Text

update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
db file sequential read	0.072157	95.9	4	0.018039
unknown	0.003062	4.1	1	0.003062
CPU	0.000000	0.0	4	0.000000
Total	0.075219	100.0	9	0.008358

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	2	0.000	0.000034	0.000	0.000000	0	0	0	0	0
EXEC	2	0.075	0.037576	0.000	0.000000	4	2	4	2	0
FETCH	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
Total	4	0.075	0.018805	0.000	0.000000	4	2	4	2	0
Avg. per EXEC	2.000	0.037610	n/a	0.000000	n/a	2.000	1.000	2.000	1.000	0.000
Avg. per Row	2.000	0.037610	n/a	0.000000	n/a	2.000	1.000	2.000	1.000	0.000

Execution Plan 1 (2 occurrence(s)):

ID	PID	Rows	Operation	Ela. (Self)	Ela. (Cum.)	CR (Self)	CR (Cum.)	PR (Self)	PR (Cum.)	PW (Self)	PW (Cum.)
1	0	1	UPDATE SEQ$	0.005205	0.037617	0	1	1	2	0	0
2	1	1	INDEX UNIQUE SCAN I_SEQ1	0.032412	0.032412	1	1	1	1	0	0

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 3716207873 15379430395219187294 1935744642 4215778051 15-Nov-09 19:47:21
1 3716207873 15379430395219187294 1935744642 2621684515 15-Nov-09 19:35:31
2 3716207873 15379430395219187294 1935744642 2417164752 15-Nov-09 20:04:28

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 .043 1.294 0 0 0 1.177 0 0 126 0 126 1
1 .013 .073 0 0 0 .053 0 0 10 0 10 1
2 .107 .57 0 0 0 .443 0 0 31 0 31 1

Correlation with V$SQL_PLAN_STATISTICS_ALL using DBMS_XPLAN.DISPLAY_CURSOR

SQL_ID  4m7m0t6fjcs5x, child number 0
-------------------------------------
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:
6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1
 
Plan hash value: 1935744642
 
-------------------------------------------------------------------
| Id  | Operation          | Name   | E-Rows |E-Bytes| Cost (%CPU)|
-------------------------------------------------------------------
|   1 |  UPDATE            | SEQ$   |        |       |            |
|*  2 |   INDEX UNIQUE SCAN| I_SEQ1 |      1 |    70 |     0   (0)|
-------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - UPD$1
   2 - UPD$1 / SEQ$@UPD$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      OUTLINE_LEAF(@"UPD$1")
      INDEX(@"UPD$1" "SEQ$"@"UPD$1" ("SEQ$"."OBJ#"))
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("OBJ#"=:1)
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Execution Plans Captured by Statspack

Old hash value: 3716207873
SQL ID: 4m7m0t6fjcs5x

Execution plan with plan hash value 1935744642 (last active 15-Nov-09 20:08:18)
Optimization: CHOOSE
Cost: 1
Statspack snapshot 483 of instance 1

ID PID Operation Object Object ID Rows Bytes Cost I/O Cost Temp. Space
0 UPDATE STATEMENT 1
1 0 UPDATE SYS.SEQ$
2 1 INDEX UNIQUE SCAN SYS.I_SEQ1 102 1 70 0 0

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	SQL_PROFILE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2383	2870266532	1	1935744642	1	CHOOSE	4215778051		.041323	.007859	3.5
2425	2870266532	1	1935744642	1	CHOOSE	2417164752		.004093	.002114	3.1

AWR execution plan with plan hash value 1935744642 for SQL ID 4m7m0t6fjcs5x

SQL_ID 4m7m0t6fjcs5x
--------------------
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:
6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1
 
Plan hash value: 1935744642
 
-------------------------------------------------------------------
| Id  | Operation          | Name   | E-Rows |E-Bytes| Cost (%CPU)|
-------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |        |        |       |     1 (100)|
|   1 |  UPDATE            | SEQ$   |        |       |            |
|   2 |   INDEX UNIQUE SCAN| I_SEQ1 |      1 |    69 |     0   (0)|
-------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - UPD$1
   2 - UPD$1 / SEQ$@UPD$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      OUTLINE_LEAF(@"UPD$1")
      INDEX(@"UPD$1" "SEQ$"@"UPD$1" ("SEQ$"."OBJ#"))
      END_OUTLINE_DATA
  */
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Physical Reads by Database Object

Obj. ID	SB Reads	SBR Time	Avg. SBR Time	MB Reads	MBR Time	Avg. MBR Time	MBR Blocks	Avg. Blocks	Owner & Segment
-1	2	0.037	0.019	0	0.000	0.000	0	0.0
60736	2	0.035	0.017	0	0.000	0.000	0	0.0	SOE.CUSTOMERS

Captured Bind Variables

Note: trace file contains 2 bind sections(s)

Bind Section 1:

Position	Type	Value
0	NUMBER	1
1	NUMBER	1
2	NUMBER	999999999999999999999999999
3	NUMBER	0
4	NUMBER	0
5	NUMBER	800
6	NUMBER	46285
7	(N)VARCHAR2	"--------------------------------"
8	NUMBER	8
9	NUMBER	60744

Bind Section 2:

Position	Type	Value
0	NUMBER	1
1	NUMBER	1
2	NUMBER	999999999999999999999999999
3	NUMBER	0
4	NUMBER	0
5	NUMBER	800
6	NUMBER	47085
7	(N)VARCHAR2	"--------------------------------"
8	NUMBER	8
9	NUMBER	60744

Rank 23: Cursor 15

Response Time Including Think Time: 0.042 s (0.01 % of delta tim)
Response Time Excluding Think Time: 0.042 s (0.01 % of delta tim)
Recursive Call Depth: 0
Module: 'New Order'
Action: 'getProductDetailsByCategory'
Waits: 2
Total Wait Time: 0.042 s
Intra Database Call Wait Time: 0.042 s
Inter Database Call Wait Time: 0.000 s
IPC latency wait time: 0.000 s
Line Number: 163

Statement Text

Note: statement text unavailable due to absence of parse call from trace file

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
db file sequential read	0.042067	100.0	2	0.021033
Total	0.042067	100.0	2	0.021033

Note: trace file contains no database calls for this statement

Physical Reads by Database Object

Obj. ID	SB Reads	SBR Time	Avg. SBR Time	MB Reads	MBR Time	Avg. MBR Time	MBR Blocks	Avg. Blocks	Owner & Segment
60778	1	0.023	0.023	0	0.000	0.000	0	0.0	SOE.ORD_CUSTOMER_IX
60779	1	0.019	0.019	0	0.000	0.000	0	0.0	SOE.ORD_ORDER_DATE_IX

Rank 24: Cursor 26

Response Time: 0.019 s (0.00 % of delta tim)
Recursive Call Depth: 1
Module: 'New Customer'
Action: 'getOrdersByCustomer'
Waits: 2
Total Wait Time: 0.019 s
Intra Database Call Wait Time: 0.019 s
Line Number: 873

Statement Text

Note: statement text unavailable due to absence of parse call from trace file

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
db file sequential read	0.019355	100.0	2	0.009678
Total	0.019355	100.0	2	0.009678

Note: trace file contains no database calls for this statement

Physical Reads by Database Object

Obj. ID	SB Reads	SBR Time	Avg. SBR Time	MB Reads	MBR Time	Avg. MBR Time	MBR Blocks	Avg. Blocks	Owner & Segment
60783	1	0.012	0.012	0	0.000	0.000	0	0.0	SOE.CUST_EMAIL_IX
60786	1	0.007	0.007	0	0.000	0.000	0	0.0	SOE.CUST_UPPER_NAME_IX

Rank 25: Statement with Hash Value 2379717279

Response Time: 0.017 s (0.00 % of delta tim)
SQL ID: 9qgtwh66xg6nz
Force Matching Signature: 0
MD5 Hash Value: 4219A4AAFF2E6E93487864459D24E93B
Command Type: 6 (UPDATE)
Parsing User ID: 0 (SYS)
Parsing Schema ID: 0 (SYS)
Optimizer Goal: 4 (CHOOSE)
Recursive Call Depth: 2
Module: 'New Order'
Action: 'getProductQuantity'
Waits: 1
Total Wait Time: 0.011 s
Intra Database Call Wait Time: 0.011 s
Line Number: 43496

Statement Text

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

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
db file sequential read	0.011438	68.3	1	0.011438
unknown	0.005297	31.7	1	0.005297
CPU	0.000000	0.0	4	0.000000
Total	0.016735	100.0	6	0.002789

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	2	0.000	0.000031	0.000	0.000000	0	0	0	0	0
EXEC	2	0.017	0.008336	0.000	0.000000	1	10	2	2	0
FETCH	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
Total	4	0.017	0.004184	0.000	0.000000	1	10	2	2	0
Avg. per EXEC	2.000	0.008368	n/a	0.000000	n/a	0.500	5.000	1.000	1.000	0.000
Avg. per Row	2.000	0.008368	n/a	0.000000	n/a	0.500	5.000	1.000	1.000	0.000

Execution Plan 1 (2 occurrence(s)):

ID	PID	Rows	Operation	Ela. (Self)	Ela. (Cum.)	CR (Self)	CR (Cum.)
1	0	0	UPDATE SEG$	0.000102	0.000204	0	5
2	1	1	TABLE ACCESS CLUSTER SEG$	0.000078	0.000102	3	5
3	2	1	INDEX UNIQUE SCAN I_FILE#_BLOCK#	0.000024	0.000024	2	2

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 2850132846 0 2170058777 779777996 15-Nov-09 19:47:50
1 2850132846 0 2170058777 2417164752 15-Nov-09 20:01:57
2 2850132846 0 2170058777 2621684515 15-Nov-09 19:35:23

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 .153 .669 0 0 0 .481 0 0 401 0 401 2
1 .185 .72 0 0 0 .537 0 0 62 0 62 2
2 .056 .121 0 0 0 .073 0 0 14 0 14 2

Correlation with V$SQL_PLAN_STATISTICS_ALL using DBMS_XPLAN.DISPLAY_CURSOR

SQL_ID  9qgtwh66xg6nz, child number 0
-------------------------------------
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,ex
tpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, 
:13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, 
spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and 
block#=:3
 
Plan hash value: 2170058777
 
-----------------------------------------------------------------------------------------
| Id  | Operation             | Name           | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------
|   1 |  UPDATE               | SEG$           |        |       |            |          |
|   2 |   TABLE ACCESS CLUSTER| SEG$           |      1 |    60 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN  | I_FILE#_BLOCK# |      1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - UPD$1
   2 - UPD$1 / SEG$@UPD$1
   3 - UPD$1 / SEG$@UPD$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      OUTLINE_LEAF(@"UPD$1")
      INDEX(@"UPD$1" "SEG$"@"UPD$1" "I_FILE#_BLOCK#")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("TS#"=:1 AND "FILE#"=:2 AND "BLOCK#"=:3)
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Execution Plans Captured by Statspack

Old hash value: 2850132846
SQL ID: 9qgtwh66xg6nz

Execution plan with plan hash value 2170058777 (last active 15-Nov-09 20:10:16)
Optimization: CHOOSE
Cost: 2
Statspack snapshot 483 of instance 1

ID PID Operation Object Object ID Rows Bytes Cost I/O Cost Temp. Space
0 UPDATE STATEMENT 2
1 0 UPDATE SYS.SEG$
2 1 TABLE ACCESS CLUSTER SYS.SEG$ 14 1 60 2 2
3 2 INDEX UNIQUE SCAN SYS.I_FILE#_BLOCK# 9 1 1 1

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2397	2870266532	1	2170058777	2	CHOOSE	779777996	.000301	.000301	6
2423	2870266532	1	2170058777	2	CHOOSE	2621684515	.012229	.00364	6
2427	2870266532	1	2170058777	2	CHOOSE	2417164752	.020067	.002886	6

AWR execution plan with plan hash value 2170058777 for SQL ID 9qgtwh66xg6nz

SQL_ID 9qgtwh66xg6nz
--------------------
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,ex
tpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, 
:13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, 
spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and 
block#=:3
 
Plan hash value: 2170058777
 
-----------------------------------------------------------------------------------------
| Id  | Operation             | Name           | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |                |        |       |     2 (100)|          |
|   1 |  UPDATE               | SEG$           |        |       |            |          |
|   2 |   TABLE ACCESS CLUSTER| SEG$           |      1 |    60 |     2   (0)| 00:00:01 |
|   3 |    INDEX UNIQUE SCAN  | I_FILE#_BLOCK# |      1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - UPD$1
   2 - UPD$1 / SEG$@UPD$1
   3 - UPD$1 / SEG$@UPD$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      OUTLINE_LEAF(@"UPD$1")
      INDEX(@"UPD$1" "SEG$"@"UPD$1" "I_FILE#_BLOCK#")
      END_OUTLINE_DATA
  */
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Physical Reads by Database Object

Obj. ID	SB Reads	SBR Time	Avg. SBR Time	MB Reads	MBR Time	Avg. MBR Time	MBR Blocks	Avg. Blocks	Owner & Segment
60736	1	0.011	0.011	0	0.000	0.000	0	0.0	SOE.CUSTOMERS

Captured Bind Variables

Note: trace file contains 2 bind sections(s)

Bind Section 1:

Position	Type	Value
0	NUMBER	6
1	NUMBER	481
2	NUMBER	37
3	NUMBER	1
4	NUMBER	2147483645
5	NUMBER	13
6	NUMBER	0
7	NUMBER	78
8	NUMBER	13
9	NUMBER	0
10		no oacdef (NULL?)

Bind Section 2:

Position	Type	Value
0	NUMBER	5
1	NUMBER	10374
2	NUMBER	798
3	NUMBER	1
4	NUMBER	2147483645
5	NUMBER	13
6	NUMBER	0
7	NUMBER	78
8	NUMBER	13
9	NUMBER	0
10		no oacdef (NULL?)

Rank 26: Cursor 17

Response Time: 0.014 s (0.00 % of delta tim)
Recursive Call Depth: 1
Module: 'New Order'
Action: 'getProductQuantity'
Waits: 1
Total Wait Time: 0.014 s
Intra Database Call Wait Time: 0.014 s
Line Number: 212

Statement Text

Note: statement text unavailable due to absence of parse call from trace file

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
db file sequential read	0.014435	100.0	1	0.014435
Total	0.014435	100.0	1	0.014435

Note: trace file contains no database calls for this statement

Physical Reads by Database Object

Obj. ID	SB Reads	SBR Time	Avg. SBR Time	MB Reads	MBR Time	Avg. MBR Time	MBR Blocks	Avg. Blocks	Owner & Segment
60775	1	0.014	0.014	0	0.000	0.000	0	0.0	SOE.ITEM_ORDER_IX

Rank 27: Statement with Hash Value 3840466760

Response Time: 0.001 s (0.00 % of delta tim)
SQL ID: aq4js2gkfjru8
Force Matching Signature: 17023979957667835857
MD5 Hash Value: 8EDE7CB982E8F918882DA87188C4DF9
Command Type: 6 (UPDATE)
Parsing User ID: 0 (SYS)
Parsing Schema ID: 0 (SYS)
Optimizer Goal: 4 (CHOOSE)
Recursive Call Depth: 2
Module: 'New Order'
Action: 'getProductQuantity'
Waits: 0
Total Wait Time: 0.000 s
Intra Database Call Wait Time: 0.000 s
Line Number: 43445

Statement Text

update tsq$ set blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,priv2=:7,priv3=:8 where ts#=:1 and user#=:2

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
CPU	0.000000	0.0	2	0.000000
Total	0.000927	100.0	2	0.000464

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	1	0.000	0.000026	0.000	0.000000	0	0	0	0	0
EXEC	1	0.001	0.000901	0.000	0.000000	0	4	1	1	0
FETCH	0	0.000	0.000000	0.000	0.000000	0	0	0	0	0
Total	2	0.001	0.000464	0.000	0.000000	0	4	1	1	0
Avg. per EXEC	2.000	0.000927	n/a	0.000000	n/a	0.000	4.000	1.000	1.000	0.000
Avg. per Row	2.000	0.000927	n/a	0.000000	n/a	0.000	4.000	1.000	1.000	0.000

Execution Plan 1 (1 occurrence(s)):

ID	PID	Rows	Operation	Ela. (Self)	Ela. (Cum.)	CR (Self)	CR (Cum.)
1	0	0	UPDATE TSQ$	0.000126	0.000221	0	4
2	1	1	TABLE ACCESS CLUSTER TSQ$	0.000075	0.000095	3	4
3	2	1	INDEX UNIQUE SCAN I_USER#	0.000020	0.000020	1	1

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 1380021873 17023979957667835857 1119449133 779777996 15-Nov-09 19:47:48
1 1380021873 17023979957667835857 1119449133 2417164752 15-Nov-09 20:01:57

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 .019 .056 0 0 0 .049 0 0 13 0 13 1
1 .014 .014 0 0 0 0 0 0 10 0 10 1

Correlation with V$SQL_PLAN_STATISTICS_ALL using DBMS_XPLAN.DISPLAY_CURSOR

SQL_ID  aq4js2gkfjru8, child number 0
-------------------------------------
update tsq$ set blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,priv2=:7,priv3
=:8 where ts#=:1 and user#=:2
 
Plan hash value: 1119449133
 
----------------------------------------------------------------------------------
| Id  | Operation             | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------------
|   1 |  UPDATE               | TSQ$    |        |       |            |          |
|*  2 |   TABLE ACCESS CLUSTER| TSQ$    |      1 |    19 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN  | I_USER# |      1 |       |     0   (0)|          |
----------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - UPD$1
   2 - UPD$1 / TSQ$@UPD$1
   3 - UPD$1 / TSQ$@UPD$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      OUTLINE_LEAF(@"UPD$1")
      INDEX(@"UPD$1" "TSQ$"@"UPD$1" "I_USER#")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("TS#"=:1)
   3 - access("USER#"=:2)
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	SQL_PROFILE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2423	2870266532	1	1119449133	1	CHOOSE	2417164752		.00147	.00147	4

AWR execution plan with plan hash value 1119449133 for SQL ID aq4js2gkfjru8

SQL_ID aq4js2gkfjru8
--------------------
update tsq$ set blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,priv2=:7,priv3
=:8 where ts#=:1 and user#=:2
 
Plan hash value: 1119449133
 
----------------------------------------------------------------------------------
| Id  | Operation             | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |         |        |       |     1 (100)|          |
|   1 |  UPDATE               | TSQ$    |        |       |            |          |
|   2 |   TABLE ACCESS CLUSTER| TSQ$    |      1 |    19 |     1   (0)| 00:00:01 |
|   3 |    INDEX UNIQUE SCAN  | I_USER# |      1 |       |     0   (0)|          |
----------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - UPD$1
   2 - UPD$1 / TSQ$@UPD$1
   3 - UPD$1 / TSQ$@UPD$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      OUTLINE_LEAF(@"UPD$1")
      INDEX(@"UPD$1" "TSQ$"@"UPD$1" "I_USER#")
      END_OUTLINE_DATA
  */
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Captured Bind Variables

Note: trace file contains 1 bind sections(s)

Bind Section 1:

Position	Type	Value
0	NUMBER	19500
1	NUMBER	0
2	NUMBER	0
3	NUMBER	0
4	NUMBER	0
5	NUMBER	0
6	NUMBER	13
7	NUMBER	78

Rank 28: Cursor 8

Response Time: 0.007 s (0.00 % of delta tim)
Waits: 3
Total Wait Time: 0.007 s
Intra Database Call Wait Time: 0.001 s
Line Number: 25

Statement Text

Note: statement text unavailable due to absence of parse call from trace file

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
SQL*Net message from client	0.005000	71.3	1	0.005000
think time	0.001235	17.6	1	0.001235
log file sync	0.000778	11.1	1	0.000778
Total	0.007013	100.0	3	0.002338

Note: trace file contains no database calls for this statement

Rank 29: Statement with Hash Value 1570213724

Response Time: 0.001 s (0.00 % of delta tim)
SQL ID: bsa0wjtftg3uw
Force Matching Signature: 0
MD5 Hash Value: 8474F628DBF91581BAFDF9C3E7651785
Command Type: 3 (SELECT)
Parsing User ID: 0 (SYS)
Parsing Schema ID: 0 (SYS)
Optimizer Goal: 4 (CHOOSE)
Recursive Call Depth: 2
Module: 'New Order'
Action: 'getProductQuantity'
Waits: 0
Total Wait Time: 0.000 s
Intra Database Call Wait Time: 0.000 s
Line Number: 43429

Statement Text

select file# from file$ where ts#=:1

Statement Level Resource Profile

Response Time Contributor	Duration (s)	Percent (%)	Count	Average (s)
CPU	0.000000	0.0	8	0.000000
Total	0.000601	100.0	8	0.000075

Database Call Statistics

DB Call	Count	Elapsed (s)	Average Ela. (s)	CPU (s)	Average CPU (s)	Disk	Query	Block Changes	Rows	Cursor Misses
PARSE	2	0.000	0.000040	0.000	0.000000	0	0	0	0	0
EXEC	2	0.000	0.000197	0.000	0.000000	0	0	0	0	0
FETCH	4	0.000	0.000032	0.000	0.000000	0	7	0	2	0
Total	8	0.001	0.000075	0.000	0.000000	0	7	0	2	0
Avg. per EXEC	4.000	0.000301	n/a	0.000000	n/a	0.000	3.500	0.000	1.000	0.000
Avg. per FETCH	2.000	0.000150	n/a	0.000000	n/a	0.000	1.750	0.000	0.500	0.000
Avg. per Row	4.000	0.000301	n/a	0.000000	n/a	0.000	3.500	0.000	1.000	0.000

Execution Plan 1 (2 occurrence(s)):

ID	PID	Rows	Operation	Ela. (Self)	Ela. (Cum.)	CR (Self)	CR (Cum.)	PR (Self)	PR (Cum.)	PW (Self)	PW (Cum.)
1	0	1	TABLE ACCESS FULL FILE$	0.000045	0.000045	3	3	0	0	0	0

Correlation with V$SQL

Note: The result is split into two rows where each row is identified by the child cursor number. Resource usage is since instance startup (not interval-based).

CHILD_NUMBER OLD_HASH_VALUE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_PROFILE OUTLINE_CATEGORY OUTLINE_TYPE
0 1254950678 0 1512486435 779777996 15-Nov-09 19:59:28
1 1254950678 0 1512486435 2417164752 15-Nov-09 20:01:57
2 1254950678 0 1512486435 2621684515 15-Nov-09 19:35:23

CHILD_NUMBER CPU Time (s) Elapsed (s) App. Wait Time (s) Conc. Wait Time (s) Cluster Wait Time (s) User I/O Wait Time (s) PL/SQL Exec. Time (s) Java Exec. Time (s) EXECUTIONS SORTS PARSE_CALLS OPTIMIZER_COST
0 3.048 23.125 0 0 0 .097 0 0 35540 0 35540 2
1 .068 .083 0 0 0 .015 0 0 380 0 380 2
2 .016 .029 0 0 0 0 0 0 41 0 41 2

Correlation with V$SQL_PLAN_STATISTICS_ALL using DBMS_XPLAN.DISPLAY_CURSOR

SQL_ID  bsa0wjtftg3uw, child number 0
-------------------------------------
select file# from file$ where ts#=:1
 
Plan hash value: 1512486435
 
----------------------------------------------------------------------------
| Id  | Operation         | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| FILE$ |      1 |     6 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / FILE$@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "FILE$"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("TS#"=:1)
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Execution Plans Captured by Statspack

Old hash value: 1254950678
SQL ID: bsa0wjtftg3uw

Execution plan with plan hash value 1512486435 (last active 15-Nov-09 20:10:16)
Optimization: CHOOSE
Cost: 2
Statspack snapshot 483 of instance 1

ID PID Operation Object Object ID Rows Bytes Cost I/O Cost Temp. Space
0 SELECT STATEMENT 2
1 0 TABLE ACCESS FULL SYS.FILE$ 17 1 6 2 2

Statement Execution Captured by AWR

SNAP_ID	DBID	INSTANCE_NUMBER	PLAN_HASH_VALUE	OPTIMIZER_COST	OPTIMIZER_MODE	OPTIMIZER_ENV_HASH_VALUE	Ela. per EXEC	CPU per EXEC	Buffer Gets per EXEC
2419	2870266532	1	1512486435	2	CHOOSE	779777996	.000085	.000085	3.9
2423	2870266532	1	1512486435	2	CHOOSE	2621684515	.000195	.000195	3.9
2427	2870266532	1	1512486435	2	CHOOSE	2417164752	.000122	.000122	3.9

AWR execution plan with plan hash value 1512486435 for SQL ID bsa0wjtftg3uw

SQL_ID bsa0wjtftg3uw
--------------------
select file# from file$ where ts#=:1
 
Plan hash value: 1512486435
 
----------------------------------------------------------------------------
| Id  | Operation         | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |        |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| FILE$ |      1 |     6 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / FILE$@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "FILE$"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Captured Bind Variables

Note: trace file contains 2 bind sections(s)

Bind Section 1:

Position	Type	Value
0	NUMBER	13

Bind Section 2:

Position	Type	Value
0	NUMBER	12

Wait Event Histograms

Histogram for Wait Event 'PL/SQL lock timer'

Range	Elapsed (s)	Pct. Elapsed	Count	Percent	Avg. Ela. (s)	Cum. Ela. (s)	Pct. Cum. Ela.	Cum. Count	Pct. Cum. Count
<= 4 µs	0.000004	0.0	1	0.0	0.000004	0.000004	0.0	1	0.0
<= 8 µs	0.000007	0.0	1	0.0	0.000007	0.000011	0.0	2	0.0
<= 16 µs	0.000037	0.0	4	0.0	0.000009	0.000048	0.0	6	0.1
<= 32 µs	0.000000	0.0	0	0.0	0.000000	0.000048	0.0	6	0.1
<= 64 µs	0.000000	0.0	0	0.0	0.000000	0.000048	0.0	6	0.1
<= 128 µs	0.000000	0.0	0	0.0	0.000000	0.000048	0.0	6	0.1
<= 256 µs	0.000000	0.0	0	0.0	0.000000	0.000048	0.0	6	0.1
<= 512 µs	0.000000	0.0	0	0.0	0.000000	0.000048	0.0	6	0.1
<= 1 ms	0.000000	0.0	0	0.0	0.000000	0.000048	0.0	6	0.1
<= 2 ms	0.000000	0.0	0	0.0	0.000000	0.000048	0.0	6	0.1
<= 4 ms	0.000000	0.0	0	0.0	0.000000	0.000048	0.0	6	0.1
<= 8 ms	0.000000	0.0	0	0.0	0.000000	0.000048	0.0	6	0.1
<= 16 ms	82.836639	98.1	7938	99.1	0.010435	82.836685	98.1	7944	99.2
<= 32 ms	1.173797	1.4	58	0.7	0.020238	84.010490	99.5	8002	99.9
<= 64 ms	0.408884	0.5	9	0.1	0.045432	84.419365	100.0	8011	100.0
Total	84.419365	100.0	8011	100.0	0.010537

Histogram for Wait Event 'SQL*Net message from client'

Range	Elapsed (s)	Pct. Elapsed	Count	Percent	Avg. Ela. (s)	Cum. Ela. (s)	Pct. Cum. Ela.	Cum. Count	Pct. Cum. Count
<= 512 µs	0.000347	0.0	1	0.0	0.000347	0.000347	0.0	1	0.0
<= 1 ms	0.000000	0.0	0	0.0	0.000000	0.000347	0.0	1	0.0
<= 2 ms	0.000000	0.0	0	0.0	0.000000	0.000347	0.0	1	0.0
<= 4 ms	0.208653	1.5	47	1.6	0.004439	0.209000	1.5	48	1.7
<= 8 ms	13.915794	98.5	2811	98.3	0.004950	14.124794	100.0	2859	100.0
Total	14.124794	100.0	2859	100.0	0.004940

Histogram for Wait Event 'SQL*Net message to client'

Range	Elapsed (s)	Pct. Elapsed	Count	Percent	Avg. Ela. (s)	Cum. Ela. (s)	Pct. Cum. Ela.	Cum. Count	Pct. Cum. Count
<= 4 µs	0.002482	13.6	634	22.2	0.000004	0.002482	13.6	634	22.2
<= 8 µs	0.012453	68.5	2141	74.9	0.000006	0.014935	82.1	2775	97.1
<= 16 µs	0.000388	2.1	38	1.3	0.000010	0.015323	84.2	2813	98.4
<= 32 µs	0.000556	3.1	20	0.7	0.000028	0.015879	87.3	2833	99.1
<= 64 µs	0.001010	5.6	21	0.7	0.000048	0.016889	92.9	2854	99.9
<= 128 µs	0.000083	0.5	1	0.0	0.000083	0.016972	93.3	2855	99.9
<= 256 µs	0.000366	2.0	2	0.1	0.000183	0.017338	95.3	2857	100.0
<= 512 µs	0.000000	0.0	0	0.0	0.000000	0.017338	95.3	2857	100.0
<= 1 ms	0.000851	4.7	1	0.0	0.000851	0.018189	100.0	2858	100.0
Total	0.018189	100.0	2858	100.0	0.000006

Histogram for Wait Event 'buffer busy waits'

Range	Elapsed (s)	Pct. Elapsed	Count	Percent	Avg. Ela. (s)	Cum. Ela. (s)	Pct. Cum. Ela.	Cum. Count	Pct. Cum. Count
<= 8 µs	0.000006	0.0	1	20.0	0.000006	0.000006	0.0	1	20.0
<= 16 µs	0.000000	0.0	0	0.0	0.000000	0.000006	0.0	1	20.0
<= 32 µs	0.000000	0.0	0	0.0	0.000000	0.000006	0.0	1	20.0
<= 64 µs	0.000055	0.1	1	20.0	0.000055	0.000061	0.2	2	40.0
<= 128 µs	0.000000	0.0	0	0.0	0.000000	0.000061	0.2	2	40.0
<= 256 µs	0.000215	0.6	1	20.0	0.000215	0.000276	0.7	3	60.0
<= 512 µs	0.000000	0.0	0	0.0	0.000000	0.000276	0.7	3	60.0
<= 1 ms	0.000000	0.0	0	0.0	0.000000	0.000276	0.7	3	60.0
<= 2 ms	0.000000	0.0	0	0.0	0.000000	0.000276	0.7	3	60.0
<= 4 ms	0.000000	0.0	0	0.0	0.000000	0.000276	0.7	3	60.0
<= 8 ms	0.000000	0.0	0	0.0	0.000000	0.000276	0.7	3	60.0
<= 16 ms	0.010726	28.4	1	20.0	0.010726	0.011002	29.2	4	80.0
<= 32 ms	0.026736	70.8	1	20.0	0.026736	0.037738	100.0	5	100.0
Total	0.037738	100.0	5	100.0	0.007547

Histogram for Wait Event 'db file sequential read'

Range	Elapsed (s)	Pct. Elapsed	Count	Percent	Avg. Ela. (s)	Cum. Ela. (s)	Pct. Cum. Ela.	Cum. Count	Pct. Cum. Count
<= 256 µs	0.026260	0.0	115	2.1	0.000228	0.026260	0.0	115	2.1
<= 512 µs	0.034146	0.0	107	1.9	0.000319	0.060406	0.1	222	4.0
<= 1 ms	0.018521	0.0	25	0.5	0.000741	0.078927	0.1	247	4.5
<= 2 ms	0.145643	0.2	68	1.2	0.002142	0.224570	0.3	315	5.7
<= 4 ms	1.030752	1.4	293	5.3	0.003518	1.255322	1.7	608	11.0
<= 8 ms	5.975937	8.2	873	15.8	0.006845	7.231259	9.9	1481	26.8
<= 16 ms	33.962849	46.7	2762	50.1	0.012296	41.194107	56.6	4243	76.9
<= 32 ms	25.136169	34.6	1167	21.1	0.021539	66.330276	91.2	5410	98.0
<= 64 ms	3.465955	4.8	80	1.4	0.043324	69.796234	96.0	5490	99.5
<= 128 ms	1.750970	2.4	22	0.4	0.079590	71.547203	98.4	5512	99.9
<= 256 ms	0.657077	0.9	4	0.1	0.164269	72.204277	99.3	5516	100.0
<= 512 ms	0.529978	0.7	2	0.0	0.264989	72.734253	100.0	5518	100.0
Total	72.734253	100.0	5518	100.0	0.013181

Histogram for Wait Event 'latch free'

Range	Elapsed (s)	Pct. Elapsed	Count	Percent	Avg. Ela. (s)	Cum. Ela. (s)	Pct. Cum. Ela.	Cum. Count	Pct. Cum. Count
<= 512 µs	0.000306	11.6	1	50.0	0.000306	0.000306	11.6	1	50.0
<= 1 ms	0.000000	0.0	0	0.0	0.000000	0.000306	11.6	1	50.0
<= 2 ms	0.002338	88.4	1	50.0	0.002338	0.002644	100.0	2	100.0
Total	0.002644	100.0	2	100.0	0.001322

Histogram for Wait Event 'latch: cache buffers chains'

Range	Elapsed (s)	Pct. Elapsed	Count	Percent	Avg. Ela. (s)	Cum. Ela. (s)	Pct. Cum. Ela.	Cum. Count	Pct. Cum. Count
<= 64 µs	0.000047	100.0	1	100.0	0.000047	0.000047	100.0	1	100.0
Total	0.000047	100.0	1	100.0	0.000047

Histogram for Wait Event 'latch: cache buffers lru chain'

Range	Elapsed (s)	Pct. Elapsed	Count	Percent	Avg. Ela. (s)	Cum. Ela. (s)	Pct. Cum. Ela.	Cum. Count	Pct. Cum. Count
<= 64 µs	0.000048	100.0	1	100.0	0.000048	0.000048	100.0	1	100.0
Total	0.000048	100.0	1	100.0	0.000048

Histogram for Wait Event 'latch: library cache'

Range	Elapsed (s)	Pct. Elapsed	Count	Percent	Avg. Ela. (s)	Cum. Ela. (s)	Pct. Cum. Ela.	Cum. Count	Pct. Cum. Count
<= 32 µs	0.000025	20.0	1	33.3	0.000025	0.000025	20.0	1	33.3
<= 64 µs	0.000100	80.0	2	66.7	0.000050	0.000125	100.0	3	100.0
Total	0.000125	100.0	3	100.0	0.000041

Histogram for Wait Event 'latch: row cache objects'

Range	Elapsed (s)	Pct. Elapsed	Count	Percent	Avg. Ela. (s)	Cum. Ela. (s)	Pct. Cum. Ela.	Cum. Count	Pct. Cum. Count
<= 16 µs	0.000012	100.0	1	100.0	0.000012	0.000012	100.0	1	100.0
Total	0.000012	100.0	1	100.0	0.000012

Histogram for Wait Event 'log file switch completion'

Range	Elapsed (s)	Pct. Elapsed	Count	Percent	Avg. Ela. (s)	Cum. Ela. (s)	Pct. Cum. Ela.	Cum. Count	Pct. Cum. Count
<= 256 ms	0.153534	100.0	1	100.0	0.153534	0.153534	100.0	1	100.0
Total	0.153534	100.0	1	100.0	0.153534

Histogram for Wait Event 'log file sync'

Range	Elapsed (s)	Pct. Elapsed	Count	Percent	Avg. Ela. (s)	Cum. Ela. (s)	Pct. Cum. Ela.	Cum. Count	Pct. Cum. Count
<= 256 µs	0.000256	24.8	1	50.0	0.000256	0.000256	24.8	1	50.0
<= 512 µs	0.000000	0.0	0	0.0	0.000000	0.000256	24.8	1	50.0
<= 1 ms	0.000778	75.2	1	50.0	0.000778	0.001034	100.0	2	100.0
Total	0.001034	100.0	2	100.0	0.000517

Histogram for Wait Event 'read by other session'

Range	Elapsed (s)	Pct. Elapsed	Count	Percent	Avg. Ela. (s)	Cum. Ela. (s)	Pct. Cum. Ela.	Cum. Count	Pct. Cum. Count
<= 16 ms	0.012735	22.2	1	33.3	0.012735	0.012735	22.2	1	33.3
<= 32 ms	0.044678	77.8	2	66.7	0.022339	0.057413	100.0	3	100.0
Total	0.057413	100.0	3	100.0	0.019137

Histogram for Wait Event 'think time'

Range	Elapsed (s)	Pct. Elapsed	Count	Percent	Avg. Ela. (s)	Cum. Ela. (s)	Pct. Cum. Ela.	Cum. Count	Pct. Cum. Count
<= 1 µs	0.000003	0.0	3	0.1	0.000001	0.000003	0.0	3	0.1
<= 2 µs	0.000010	0.0	5	0.2	0.000002	0.000013	0.0	8	0.4
<= 4 µs	0.000014	0.0	4	0.2	0.000004	0.000027	0.0	12	0.5
<= 8 µs	0.000039	0.0	6	0.3	0.000006	0.000066	0.0	18	0.8
<= 16 µs	0.000165	0.0	12	0.5	0.000014	0.000231	0.0	30	1.4
<= 32 µs	0.000536	0.0	21	1.0	0.000026	0.000767	0.0	51	2.3
<= 64 µs	0.001814	0.1	38	1.7	0.000048	0.002581	0.1	89	4.1
<= 128 µs	0.007940	0.4	81	3.7	0.000098	0.010521	0.5	170	7.8
<= 256 µs	0.031666	1.5	163	7.4	0.000194	0.042187	1.9	333	15.2
<= 512 µs	0.150216	6.9	385	17.6	0.000390	0.192403	8.9	718	32.8
<= 1 ms	1.328181	61.2	1367	62.4	0.000972	1.520584	70.1	2085	95.2
<= 2 ms	0.159002	7.3	92	4.2	0.001728	1.679586	77.5	2177	99.4
<= 4 ms	0.018391	0.8	6	0.3	0.003065	1.697977	78.3	2183	99.6
<= 8 ms	0.011282	0.5	2	0.1	0.005641	1.709259	78.8	2185	99.7
<= 16 ms	0.009156	0.4	1	0.0	0.009156	1.718415	79.2	2186	99.8
<= 32 ms	0.000000	0.0	0	0.0	0.000000	1.718415	79.2	2186	99.8
<= 64 ms	0.048744	2.2	1	0.0	0.048744	1.767159	81.5	2187	99.8
<= 128 ms	0.269047	12.4	3	0.1	0.089682	2.036206	93.9	2190	100.0
<= 256 ms	0.132345	6.1	1	0.0	0.132345	2.168551	100.0	2191	100.0
Total	2.168551	100.0	2191	100.0	0.000989

Optimizer Environments

This section contains cost based optimizer (CBO) parameter settings pertaining to execution plans used by the SQL statements in the trace file. An optimizer environment is a distinct set of parameters that govern the operation of the optimizer. An optimizer environment hash value (V$SQL.OPTIMIZER_ENV_HASH_VALUE) identifies each unique set of parameter values.

The optimizer used 8 distinct optimizer environments.

Optimizer Environment with Hash Value 779777996

This optimizer environment was used when generating an execution plan for the cursor identified by SQL ID bsa0wjtftg3uw and child number 0.

Row# Name Value Default
1 active_instance_count 1 YES
2 bitmap_merge_area_size 1048576 YES
3 cpu_count 2 YES
4 cursor_sharing exact YES
5 hash_area_size 131072 YES
6 optimizer_dynamic_sampling 2 YES
7 optimizer_features_enable 10.2.0.3 YES
8 optimizer_index_caching 0 YES
9 optimizer_index_cost_adj 100 YES
10 optimizer_mode choose NO
11 optimizer_secure_view_merging true YES
12 parallel_ddl_mode enabled YES
13 parallel_dml_mode disabled YES
14 parallel_execution_enabled true YES
15 parallel_query_mode enabled YES
16 parallel_threads_per_cpu 2 YES
17 pga_aggregate_target 262144 KB YES
18 query_rewrite_enabled true YES
19 query_rewrite_integrity enforced YES
20 skip_unusable_indexes true YES
21 sort_area_retained_size 0 YES
22 sort_area_size 65536 YES
23 star_transformation_enabled false YES
24 statistics_level typical YES
25 workarea_size_policy auto YES

Optimizer Environment with Hash Value 4215778051

This optimizer environment was used when generating an execution plan for the cursor identified by SQL ID 4m7m0t6fjcs5x and child number 0.

Row# Name Value Default
1 active_instance_count 1 YES
2 bitmap_merge_area_size 1048576 YES
3 cpu_count 2 YES
4 cursor_sharing exact YES
5 hash_area_size 131072 YES
6 optimizer_dynamic_sampling 2 YES
7 optimizer_features_enable 10.2.0.3 YES
8 optimizer_index_caching 0 YES
9 optimizer_index_cost_adj 100 YES
10 optimizer_mode choose NO
11 optimizer_secure_view_merging true YES
12 parallel_ddl_mode enabled YES
13 parallel_dml_mode disabled YES
14 parallel_execution_enabled false NO
15 parallel_query_mode enabled YES
16 parallel_threads_per_cpu 2 YES
17 pga_aggregate_target 262144 KB YES
18 query_rewrite_enabled true YES
19 query_rewrite_integrity enforced YES
20 skip_unusable_indexes true YES
21 sort_area_retained_size 0 YES
22 sort_area_size 65536 YES
23 star_transformation_enabled false YES
24 statistics_level typical YES
25 workarea_size_policy auto YES

Optimizer Environment with Hash Value 3522870812

This optimizer environment was used when generating an execution plan for the cursor identified by SQL ID 0bzhqhhj9mpaa and child number 0.

Row# Name Value Default
1 active_instance_count 1 YES
2 bitmap_merge_area_size 1048576 YES
3 cpu_count 2 YES
4 cursor_sharing exact YES
5 hash_area_size 131072 YES
6 optimizer_dynamic_sampling 2 YES
7 optimizer_features_enable 10.2.0.3 YES
8 optimizer_index_caching 0 YES
9 optimizer_index_cost_adj 100 YES
10 optimizer_mode all_rows YES
11 optimizer_secure_view_merging true YES
12 parallel_ddl_mode enabled YES
13 parallel_dml_mode disabled YES
14 parallel_execution_enabled true YES
15 parallel_query_mode enabled YES
16 parallel_threads_per_cpu 2 YES
17 pga_aggregate_target 262144 KB YES
18 query_rewrite_enabled true YES
19 query_rewrite_integrity enforced YES
20 skip_unusable_indexes true YES
21 sort_area_retained_size 0 YES
22 sort_area_size 65536 YES
23 star_transformation_enabled false YES
24 statistics_level typical YES
25 workarea_size_policy auto YES

Optimizer Environment with Hash Value 2621684515

This optimizer environment was used when generating an execution plan for the cursor identified by SQL ID bsa0wjtftg3uw and child number 2.

Row# Name Value Default
1 active_instance_count 1 YES
2 bitmap_merge_area_size 1048576 YES
3 cpu_count 2 YES
4 cursor_sharing exact YES
5 hash_area_size 131072 YES
6 optimizer_dynamic_sampling 2 YES
7 optimizer_features_enable 10.2.0.3 YES
8 optimizer_index_caching 0 YES
9 optimizer_index_cost_adj 100 YES
10 optimizer_mode choose NO
11 optimizer_secure_view_merging true YES
12 parallel_ddl_mode enabled YES
13 parallel_dml_mode disabled YES
14 parallel_execution_enabled true YES
15 parallel_query_mode enabled YES
16 parallel_threads_per_cpu 2 YES
17 pga_aggregate_target 262144 KB YES
18 query_rewrite_enabled true YES
19 query_rewrite_integrity enforced YES
20 skip_unusable_indexes true YES
21 sort_area_retained_size 0 YES
22 sort_area_size 65536 YES
23 sqlstat_enabled true NO
24 star_transformation_enabled false YES
25 statistics_level typical YES
26 workarea_size_policy auto YES

Optimizer Environment with Hash Value 2727794671

This optimizer environment was used when generating an execution plan for the cursor identified by SQL ID f0cxkf0q803f8 and child number 0.

Row# Name Value Default
1 active_instance_count 1 YES
2 bitmap_merge_area_size 1048576 YES
3 cpu_count 2 YES
4 cursor_sharing exact YES
5 hash_area_size 131072 YES
6 optimizer_dynamic_sampling 2 YES
7 optimizer_features_enable 10.2.0.3 YES
8 optimizer_index_caching 0 YES
9 optimizer_index_cost_adj 100 YES
10 optimizer_mode first_rows NO
11 optimizer_mode_hinted true NO
12 optimizer_secure_view_merging true YES
13 parallel_ddl_mode enabled YES
14 parallel_dml_mode disabled YES
15 parallel_execution_enabled true YES
16 parallel_query_mode enabled YES
17 parallel_threads_per_cpu 2 YES
18 pga_aggregate_target 262144 KB YES
19 query_rewrite_enabled true YES
20 query_rewrite_integrity enforced YES
21 skip_unusable_indexes true YES
22 sort_area_retained_size 0 YES
23 sort_area_size 65536 YES
24 star_transformation_enabled false YES
25 statistics_level typical YES
26 workarea_size_policy auto YES

Optimizer Environment with Hash Value 3750204916

This optimizer environment was used when generating an execution plan for the cursor identified by SQL ID 0bzhqhhj9mpaa and child number 1.

Row# Name Value Default
1 active_instance_count 1 YES
2 bitmap_merge_area_size 1048576 YES
3 cpu_count 2 YES
4 cursor_sharing exact YES
5 hash_area_size 131072 YES
6 optimizer_dynamic_sampling 2 YES
7 optimizer_features_enable 10.2.0.3 YES
8 optimizer_index_caching 0 YES
9 optimizer_index_cost_adj 100 YES
10 optimizer_mode all_rows YES
11 optimizer_secure_view_merging true YES
12 parallel_ddl_mode enabled YES
13 parallel_dml_mode disabled YES
14 parallel_execution_enabled true YES
15 parallel_query_mode enabled YES
16 parallel_threads_per_cpu 2 YES
17 pga_aggregate_target 262144 KB YES
18 query_rewrite_enabled true YES
19 query_rewrite_integrity enforced YES
20 skip_unusable_indexes true YES
21 sort_area_retained_size 0 YES
22 sort_area_size 65536 YES
23 sqlstat_enabled true NO
24 star_transformation_enabled false YES
25 statistics_level all NO
26 workarea_size_policy auto YES

Optimizer Environment with Hash Value 2417164752

This optimizer environment was used when generating an execution plan for the cursor identified by SQL ID bsa0wjtftg3uw and child number 1.

Row# Name Value Default
1 active_instance_count 1 YES
2 bitmap_merge_area_size 1048576 YES
3 cpu_count 2 YES
4 cursor_sharing exact YES
5 hash_area_size 131072 YES
6 optimizer_dynamic_sampling 2 YES
7 optimizer_features_enable 10.2.0.3 YES
8 optimizer_index_caching 0 YES
9 optimizer_index_cost_adj 100 YES
10 optimizer_mode choose NO
11 optimizer_secure_view_merging true YES
12 parallel_ddl_mode enabled YES
13 parallel_dml_mode disabled YES
14 parallel_execution_enabled true YES
15 parallel_query_mode enabled YES
16 parallel_threads_per_cpu 2 YES
17 pga_aggregate_target 262144 KB YES
18 query_rewrite_enabled true YES
19 query_rewrite_integrity enforced YES
20 skip_unusable_indexes true YES
21 sort_area_retained_size 0 YES
22 sort_area_size 65536 YES
23 sqlstat_enabled true NO
24 star_transformation_enabled false YES
25 statistics_level all NO
26 workarea_size_policy auto YES

Optimizer Environment with Hash Value 699142823

This optimizer environment was used when generating an execution plan for the cursor identified by SQL ID f0cxkf0q803f8 and child number 1.

Row# Name Value Default
1 active_instance_count 1 YES
2 bitmap_merge_area_size 1048576 YES
3 cpu_count 2 YES
4 cursor_sharing exact YES
5 hash_area_size 131072 YES
6 optimizer_dynamic_sampling 2 YES
7 optimizer_features_enable 10.2.0.3 YES
8 optimizer_index_caching 0 YES
9 optimizer_index_cost_adj 100 YES
10 optimizer_mode first_rows NO
11 optimizer_mode_hinted true NO
12 optimizer_secure_view_merging true YES
13 parallel_ddl_mode enabled YES
14 parallel_dml_mode disabled YES
15 parallel_execution_enabled true YES
16 parallel_query_mode enabled YES
17 parallel_threads_per_cpu 2 YES
18 pga_aggregate_target 262144 KB YES
19 query_rewrite_enabled true YES
20 query_rewrite_integrity enforced YES
21 skip_unusable_indexes true YES
22 sort_area_retained_size 0 YES
23 sort_area_size 65536 YES
24 sqlstat_enabled true NO
25 star_transformation_enabled false YES
26 statistics_level all NO
27 workarea_size_policy auto YES

Buffer Cache Contents

This section shows the contents of the buffer cache. Segments that are smaller than 1 MB are ignored according to the setting of the MERITS profiler parameter cached_table_threshold_mb. The overall load on the disk subsystem may be reduced significantly if separate buffer pools (KEEP, RECYCLE, DB_nK_CACHE_SIZE) are used for large tables or indexes that cause many physical reads. Consult the Statspack level 7 report section on 'Segments by Physical Reads' to identify segments that cause many physical reads and are hence insufficiently cached.

Owner & Name Object Type Block Size (KB) Buffer Pool Cached (MB) Cached (%) Cached (Blocks) Segment Size (MB) Segment Size (Blocks)
SYS.C_OBJ# CLUSTER 8 DEFAULT 7.1 88.96 911 8 1024
SOE.INVENTORIES TABLE 8 DEFAULT 4.8 81.83 617 5.9 754
SYS.OBJ$ TABLE 8 DEFAULT 4.9 98.75 632 5 640
SYS.C_FILE#_BLOCK# CLUSTER 8 DEFAULT 1.2 59.38 152 2 256

Data Dictionary Correlation

This section contains structural information and cost based optimizer (CBO) statistics pertaining to the tables and indexes referenced by the trace file.

Structure, Indexes, and Statistics for Table SOE.CUSTOMERS

Table Overview

Owner & Name	Avg. Row Length	Rows (Statistics)	Rows (Est. Actual)	Blocks (Stats)	Blocks (Segment)	Empty Blocks	Average Space	Chain Count	Global Stats	User Stats	Sample Size	Last Analyze
SOE . CUSTOMERS	77	1062568	1103416	9989	10374	0	0	0	YES	NO	265642	13-Jun-09 15:27:58

Table Size and Estimated Space Efficiency

Tablespace	Block Size	Table Size (MB)	Est. Space Efficiency	Buffer Pool	Degree	Cluster	IOT Type	IOT Name
SOE	8 KB	81	113.3%	DEFAULT	1

Storage Parameters and Modifications

PCTFREE	PCTUSED	INITRANS	Monitoring	Modified (%)	INSERT	UPDATE	DELETE
10		1	YES	1,39%	14756	0	0

Column Statistics

Column Name	Data Type	Distinct Values	Avg. Length	Density	Buckets	Global Stats	User Stats	Sample Size	Last Analyze
CUSTOMER_ID	NUMBER(12) NOT NULL	1062568	6	0.000001	1	YES	NO	265642	13-Jun-09 15:27:58
CUST_FIRST_NAME	VARCHAR2(30) NOT NULL	174	7	0.005747	1	YES	NO	265642	13-Jun-09 15:27:58
CUST_LAST_NAME	VARCHAR2(30) NOT NULL	179	8	0.005587	1	YES	NO	265642	13-Jun-09 15:27:58
NLS_LANGUAGE	VARCHAR2(3)	7	3	0.142857	1	YES	NO	265642	13-Jun-09 15:27:58
NLS_TERRITORY	VARCHAR2(30)	8	8	0.125	1	YES	NO	265642	13-Jun-09 15:27:58
CREDIT_LIMIT	NUMBER(9,2)	4900	4	0.000204	1	YES	NO	265642	13-Jun-09 15:27:58
CUST_EMAIL	VARCHAR2(100)	30724	26	0.000033	1	YES	NO	265642	13-Jun-09 15:27:58
ACCOUNT_MGR_ID	NUMBER(6)	26	4	0.038462	1	YES	NO	265642	13-Jun-09 15:27:58

Index Overview

Index Owner	Index Name	Index Type	Partitioned	Created	Last DDL Time	Last Analyze	Status	Sample Size	Tablespace	Buffer Pool	Degree
SOE	CUST_ACCOUNT_MANAGER_IX	NORMAL	NO	13-Jun-09 15:26:47	13-Jun-09 15:26:47	13-Jun-09 15:28:03	VALID	536909	SOEINDEX	DEFAULT	1
SOE	CUST_LNAME_IX	NORMAL	NO	13-Jun-09 15:27:02	13-Jun-09 15:27:02	13-Jun-09 15:28:07	VALID	432421	SOEINDEX	DEFAULT	1
SOE	CUST_EMAIL_IX	NORMAL	NO	13-Jun-09 15:27:17	13-Jun-09 15:27:17	13-Jun-09 15:28:09	VALID	264038	SOEINDEX	DEFAULT	1
SOE	CUST_UPPER_NAME_IX	FUNCTION-BASED NORMAL	NO	13-Jun-09 15:27:33	13-Jun-09 15:27:33	13-Jun-09 15:28:14	VALID	312663	SOEINDEX	DEFAULT	1
SOE	CUSTOMERS_PK	NORMAL	NO	13-Jun-09 15:26:07	13-Jun-09 15:26:07	13-Jun-09 15:28:18	VALID	546019	SOEINDEX	DEFAULT	1

Index Statistics

Index Owner	Index Name	Index Type	Unique	B-Tree Level	Leaf Blocks	Distinct Keys	Rows	Avg. Leaf Blocks per Key	Avg. Data Blocks per Key	Clustering Factor	Global Stats	User Stats
SOE	CUST_ACCOUNT_MANAGER_IX	NORMAL	NO	2	2237	26	1068443	86	9914	257781	YES	NO
SOE	CUST_LNAME_IX	NORMAL	NO	2	2729	179	1060396	15	4268	764056	YES	NO
SOE	CUST_EMAIL_IX	NORMAL	NO	2	5340	30724	1056152	1	34	1053684	YES	NO
SOE	CUST_UPPER_NAME_IX	FUNCTION-BASED NORMAL	NO	2	3672	29778	1044614	1	34	1042042	YES	NO
SOE	CUSTOMERS_PK	NORMAL	YES	2	2281	1087523	1087523	1	1	11313	YES	NO

Indexed Columns

Index Owner	Index Name	Column Name	Position	Data Type
SOE	CUST_ACCOUNT_MANAGER_IX	ACCOUNT_MGR_ID	1	NUMBER(6)
SOE	CUST_EMAIL_IX	CUST_EMAIL	1	VARCHAR2(100)
SOE	CUST_LNAME_IX	CUST_LAST_NAME	1	VARCHAR2(30) NOT NULL
SOE	CUSTOMERS_PK	CUSTOMER_ID	1	NUMBER(12) NOT NULL

Structure, Indexes, and Statistics for Table SOE.INVENTORIES

Table Overview

Owner & Name	Avg. Row Length	Rows (Statistics)	Rows (Est. Actual)	Blocks (Stats)	Blocks (Segment)	Empty Blocks	Average Space	Chain Count	Global Stats	User Stats	Sample Size	Last Analyze
SOE . INVENTORIES	10	5760	5830	744	754	0	0	0	YES	NO	5760	02-Aug-09 18:04:56

Table Size and Estimated Space Efficiency

Tablespace	Block Size	Table Size (MB)	Est. Space Efficiency	Buffer Pool	Degree	Cluster	IOT Type	IOT Name
SOE	8 KB	5.9	339%	DEFAULT	1

Storage Parameters and Modifications

PCTFREE	PCTUSED	INITRANS	Monitoring	Modified (%)	INSERT	UPDATE	DELETE
98		1	YES

Column Statistics

Column Name	Data Type	Distinct Values	Avg. Length	Density	Buckets	Global Stats	User Stats	Sample Size	Last Analyze
PRODUCT_ID	NUMBER(6) NOT NULL	286	4	0.003497	1	YES	NO	1510	02-Aug-09 18:04:56
WAREHOUSE_ID	NUMBER(6) NOT NULL	20	3	0.05	1	YES	NO	1510	02-Aug-09 18:04:56
QUANTITY_ON_HAND	NUMBER(8) NOT NULL	75	4	0.013333	1	YES	NO	1510	02-Aug-09 18:04:56

Index Overview

Index Owner	Index Name	Index Type	Partitioned	Created	Last DDL Time	Last Analyze	Status	Sample Size	Tablespace	Buffer Pool	Degree
SOE	INVENTORY_PK	NORMAL	NO	13-Jun-09 15:26:41	13-Jun-09 15:26:41	02-Aug-09 18:04:56	VALID	5760	SOE	DEFAULT	1
SOE	INV_PRODUCT_IX	NORMAL	NO	13-Jun-09 15:26:42	13-Jun-09 15:26:42	02-Aug-09 18:04:56	VALID	5760	SOEINDEX	DEFAULT	1
SOE	INV_WAREHOUSE_IX	NORMAL	NO	13-Jun-09 15:26:42	13-Jun-09 15:26:42	02-Aug-09 18:04:56	VALID	5760	SOEINDEX	DEFAULT	1

Index Statistics

Index Owner	Index Name	Index Type	Unique	B-Tree Level	Leaf Blocks	Distinct Keys	Rows	Avg. Leaf Blocks per Key	Avg. Data Blocks per Key	Clustering Factor	Global Stats	User Stats
SOE	INVENTORY_PK	NORMAL	YES	1	14	5760	5760	1	1	5760	YES	NO
SOE	INV_PRODUCT_IX	NORMAL	NO	1	12	288	5760	1	20	5760	YES	NO
SOE	INV_WAREHOUSE_IX	NORMAL	NO	1	12	20	5760	1	36	720	YES	NO

Indexed Columns

Index Owner	Index Name	Column Name	Position	Data Type
SOE	INVENTORY_PK	PRODUCT_ID	1	NUMBER(6) NOT NULL
SOE	INVENTORY_PK	WAREHOUSE_ID	2	NUMBER(6) NOT NULL
SOE	INV_PRODUCT_IX	PRODUCT_ID	1	NUMBER(6) NOT NULL
SOE	INV_WAREHOUSE_IX	WAREHOUSE_ID	1	NUMBER(6) NOT NULL

Structure, Indexes, and Statistics for Table SOE.LOGON

Table Overview

Owner & Name	Avg. Row Length	Rows (Statistics)	Rows (Est. Actual)	Blocks (Stats)	Blocks (Segment)	Empty Blocks	Average Space	Chain Count	Global Stats	User Stats	Sample Size	Last Analyze
SOE . LOGON	13	59068	87661	157	234	0	0	0	YES	NO	14767	02-Aug-09 18:04:43

Table Size and Estimated Space Efficiency

Tablespace	Block Size	Table Size (MB)	Est. Space Efficiency	Buffer Pool	Degree	Cluster	IOT Type	IOT Name
SOE	8 KB	1.8	67.6%	DEFAULT	1

Storage Parameters and Modifications

PCTFREE	PCTUSED	INITRANS	Monitoring	Modified (%)	INSERT	UPDATE	DELETE
10		1	YES

Column Statistics

Column Name	Data Type	Distinct Values	Avg. Length	Density	Buckets	NULLs	Global Stats	User Stats	Sample Size	Last Analyze
CUSTOMER_ID	NUMBER	58852	6	0.000017	1	0	YES	NO	14767	02-Aug-09 18:04:43
LOGON_DATE	DATE	3698	8	0.00027	1	0	YES	NO	14767	02-Aug-09 18:04:43

Structure, Indexes, and Statistics for Table SOE.ORDERS

Table Overview

Owner & Name	Avg. Row Length	Rows (Statistics)	Rows (Est. Actual)	Blocks (Stats)	Blocks (Segment)	Empty Blocks	Average Space	Chain Count	Global Stats	User Stats	Sample Size	Last Analyze
SOE . ORDERS	29	39480	49806	195	247	0	0	0	YES	NO	9870	02-Aug-09 18:04:53

Table Size and Estimated Space Efficiency

Tablespace	Block Size	Table Size (MB)	Est. Space Efficiency	Buffer Pool	Degree	Cluster	IOT Type	IOT Name
SOE	8 KB	1.9	81.2%	DEFAULT	1

Storage Parameters and Modifications

PCTFREE	PCTUSED	INITRANS	Monitoring	Modified (%)	INSERT	UPDATE	DELETE
10		1	YES

Column Statistics

Column Name	Data Type	Distinct Values	Avg. Length	Density	Buckets	NULLs	Global Stats	User Stats	Sample Size	Last Analyze
ORDER_ID	NUMBER(18) NOT NULL	39480	5	0.000025	1	0	YES	NO	9870	02-Aug-09 18:04:53
ORDER_DATE	TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL	3904	11	0.000256	1	0	YES	NO	9870	02-Aug-09 18:04:53
ORDER_MODE	VARCHAR2(8)	1	4	1	1	24776	YES	NO	3676	02-Aug-09 18:04:53
CUSTOMER_ID	NUMBER(12) NOT NULL	39324	6	0.000025	1	0	YES	NO	9870	02-Aug-09 18:04:53
ORDER_STATUS	NUMBER(2)	7	2	0.142857	1	24776	YES	NO	3676	02-Aug-09 18:04:53
ORDER_TOTAL	NUMBER(8,2)	93	4	0.010753	1	0	YES	NO	9870	02-Aug-09 18:04:53
SALES_REP_ID	NUMBER(6)	0	0	0	0	39480	YES	NO		02-Aug-09 18:04:53
PROMOTION_ID	NUMBER(6)	0	0	0	0	39480	YES	NO		02-Aug-09 18:04:53

Index Overview

Index Owner	Index Name	Index Type	Partitioned	Created	Last DDL Time	Last Analyze	Status	Sample Size	Tablespace	Buffer Pool	Degree
SOE	ORD_SALES_REP_IX	NORMAL/REV	NO	13-Jun-09 15:26:45	13-Jun-09 15:26:45	02-Aug-09 18:04:53	VALID	0	SOEINDEX	DEFAULT	1
SOE	ORD_CUSTOMER_IX	NORMAL/REV	NO	13-Jun-09 15:26:45	13-Jun-09 15:26:45	02-Aug-09 18:04:54	VALID	39668	SOEINDEX	DEFAULT	1
SOE	ORD_ORDER_DATE_IX	NORMAL/REV	NO	13-Jun-09 15:26:46	13-Jun-09 15:26:46	02-Aug-09 18:04:55	VALID	39668	SOEINDEX	DEFAULT	1
SOE	ORD_STATUS_IX	NORMAL	NO	13-Jun-09 15:26:46	13-Jun-09 15:26:46	02-Aug-09 18:04:55	VALID	14668	SOEINDEX	DEFAULT	1
SOE	ORDER_PK	NORMAL	NO	13-Jun-09 15:26:26	13-Jun-09 15:26:26	02-Aug-09 18:04:55	VALID	39668	SOEINDEX	DEFAULT	1

Index Statistics

Index Owner	Index Name	Index Type	Unique	B-Tree Level	Leaf Blocks	Distinct Keys	Rows	Avg. Leaf Blocks per Key	Avg. Data Blocks per Key	Clustering Factor	Global Stats	User Stats
SOE	ORD_SALES_REP_IX	NORMAL/REV	NO	0	0	0	0	0	0	0	YES	NO
SOE	ORD_CUSTOMER_IX	NORMAL/REV	NO	1	112	38932	39668	1	1	39455	YES	NO
SOE	ORD_ORDER_DATE_IX	NORMAL/REV	NO	1	169	14239	39668	1	1	14590	YES	NO
SOE	ORD_STATUS_IX	NORMAL	NO	1	40	8	14668	5	54	433	YES	NO
SOE	ORDER_PK	NORMAL	YES	1	80	39668	39668	1	1	7868	YES	NO

Indexed Columns

Index Owner	Index Name	Column Name	Position	Data Type
SOE	ORD_CUSTOMER_IX	CUSTOMER_ID	1	NUMBER(12) NOT NULL
SOE	ORDER_PK	ORDER_ID	1	NUMBER(18) NOT NULL
SOE	ORD_ORDER_DATE_IX	ORDER_DATE	1	TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL
SOE	ORD_SALES_REP_IX	SALES_REP_ID	1	NUMBER(6)
SOE	ORD_STATUS_IX	ORDER_STATUS	1	NUMBER(2)

Structure, Indexes, and Statistics for Table SOE.ORDER_ITEMS

Table Overview

Owner & Name	Avg. Row Length	Rows (Statistics)	Rows (Est. Actual)	Blocks (Stats)	Blocks (Segment)	Empty Blocks	Average Space	Chain Count	Global Stats	User Stats	Sample Size	Last Analyze
SOE . ORDER_ITEMS	17	139332	169622	437	533	0	0	0	YES	NO	34833	02-Aug-09 18:05:10

Table Size and Estimated Space Efficiency

Tablespace	Block Size	Table Size (MB)	Est. Space Efficiency	Buffer Pool	Degree	Cluster	IOT Type	IOT Name
SOE	8 KB	4.2	75%	DEFAULT	1

Storage Parameters and Modifications

PCTFREE	PCTUSED	INITRANS	Monitoring	Modified (%)	INSERT	UPDATE	DELETE
10		1	YES

Column Statistics

Column Name	Data Type	Distinct Values	Avg. Length	Density	Buckets	Global Stats	User Stats	Sample Size	Last Analyze
ORDER_ID	NUMBER(18) NOT NULL	36730	5	0.000027	1	YES	NO	34833	02-Aug-09 18:05:10
LINE_ITEM_ID	NUMBER(3) NOT NULL	5	3	0.2	1	YES	NO	34833	02-Aug-09 18:05:10
PRODUCT_ID	NUMBER(6) NOT NULL	287	4	0.003484	1	YES	NO	34833	02-Aug-09 18:05:10
UNIT_PRICE	NUMBER(8,2)	44	4	0.022727	1	YES	NO	34833	02-Aug-09 18:05:10
QUANTITY	NUMBER(8)	2	3	0.5	1	YES	NO	34833	02-Aug-09 18:05:10

Index Overview

Index Owner	Index Name	Index Type	Partitioned	Created	Last DDL Time	Last Analyze	Status	Sample Size	Tablespace	Buffer Pool	Degree
SOE	ORDER_ITEMS_PK	NORMAL	NO	13-Jun-09 15:26:24	13-Jun-09 15:26:24	02-Aug-09 18:05:12	VALID	138975	SOEINDEX	DEFAULT	1
SOE	ORDER_ITEMS_UK	NORMAL	NO	13-Jun-09 15:26:25	13-Jun-09 15:26:25	02-Aug-09 18:05:13	VALID	138975	SOEINDEX	DEFAULT	1
SOE	ITEM_ORDER_IX	NORMAL/REV	NO	13-Jun-09 15:26:42	13-Jun-09 15:26:42	02-Aug-09 18:05:15	VALID	138975	SOEINDEX	DEFAULT	1
SOE	ITEM_PRODUCT_IX	NORMAL/REV	NO	13-Jun-09 15:26:44	13-Jun-09 15:26:44	02-Aug-09 18:05:17	VALID	138975	SOEINDEX	DEFAULT	1

Index Statistics

Index Owner	Index Name	Index Type	Unique	B-Tree Level	Leaf Blocks	Distinct Keys	Rows	Avg. Leaf Blocks per Key	Avg. Data Blocks per Key	Clustering Factor	Global Stats	User Stats
SOE	ORDER_ITEMS_PK	NORMAL	YES	1	358	138975	138975	1	1	5706	YES	NO
SOE	ORDER_ITEMS_UK	NORMAL	YES	1	372	138975	138975	1	1	6005	YES	NO
SOE	ITEM_ORDER_IX	NORMAL/REV	NO	1	383	39668	138975	1	1	39991	YES	NO
SOE	ITEM_PRODUCT_IX	NORMAL/REV	NO	1	380	287	138975	1	208	59756	YES	NO

Indexed Columns

Index Owner	Index Name	Column Name	Position	Data Type
SOE	ITEM_ORDER_IX	ORDER_ID	1	NUMBER(18) NOT NULL
SOE	ITEM_PRODUCT_IX	PRODUCT_ID	1	NUMBER(6) NOT NULL
SOE	ORDER_ITEMS_PK	ORDER_ID	1	NUMBER(18) NOT NULL
SOE	ORDER_ITEMS_PK	LINE_ITEM_ID	2	NUMBER(3) NOT NULL
SOE	ORDER_ITEMS_UK	ORDER_ID	1	NUMBER(18) NOT NULL
SOE	ORDER_ITEMS_UK	PRODUCT_ID	2	NUMBER(6) NOT NULL

Structure, Indexes, and Statistics for Table SOE.PRODUCT_DESCRIPTIONS

Table Overview

Owner & Name	Avg. Row Length	Rows (Statistics)	Rows (Est. Actual)	Blocks (Stats)	Blocks (Segment)	Empty Blocks	Average Space	Chain Count	Global Stats	User Stats	Sample Size	Last Analyze
SOE . PRODUCT_DESCRIPTIONS	281	288	327	22	26	0	0	0	YES	NO	288	13-Jun-09 15:28:24

Table Size and Estimated Space Efficiency

Tablespace	Block Size	Table Size (MB)	Est. Space Efficiency	Buffer Pool	Degree	Cluster	IOT Type	IOT Name
SOE	8 KB	.2	50.9%	DEFAULT	1

Storage Parameters and Modifications

PCTFREE	PCTUSED	INITRANS	Monitoring	Modified (%)	INSERT	UPDATE	DELETE
10		1	YES

Column Statistics

Column Name	Data Type	Distinct Values	Avg. Length	Density	Buckets	Global Stats	User Stats	Sample Size	Last Analyze
PRODUCT_ID	NUMBER(6) NOT NULL	288	4	0.003472	1	YES	NO	288	13-Jun-09 15:28:24
LANGUAGE_ID	VARCHAR2(3) NOT NULL	1	3	1	1	YES	NO	288	13-Jun-09 15:28:24
TRANSLATED_NAME	NVARCHAR2 NOT NULL	287	31	0.003484	1	YES	NO	288	13-Jun-09 15:28:24
TRANSLATED_DESCRIPTION	NVARCHAR2 NOT NULL	288	244	0.003472	1	YES	NO	288	13-Jun-09 15:28:24

Index Overview

Index Owner	Index Name	Index Type	Partitioned	Created	Last DDL Time	Last Analyze	Status	Sample Size	Tablespace	Buffer Pool	Degree
SOE	PRD_DESC_PK	NORMAL	NO	13-Jun-09 15:26:27	13-Jun-09 15:26:27	13-Jun-09 15:28:24	VALID	288	SOEINDEX	DEFAULT	1
SOE	PROD_NAME_IX	NORMAL	NO	13-Jun-09 15:27:33	13-Jun-09 15:27:33	13-Jun-09 15:28:24	VALID	288	SOEINDEX	DEFAULT	1

Index Statistics

Index Owner	Index Name	Index Type	Unique	B-Tree Level	Leaf Blocks	Distinct Keys	Rows	Avg. Leaf Blocks per Key	Avg. Data Blocks per Key	Clustering Factor	Global Stats	User Stats
SOE	PRD_DESC_PK	NORMAL	YES	0	1	288	288	1	1	156	YES	NO
SOE	PROD_NAME_IX	NORMAL	NO	1	2	287	288	1	1	98	YES	NO

Indexed Columns

Index Owner	Index Name	Column Name	Position	Data Type
SOE	PRD_DESC_PK	PRODUCT_ID	1	NUMBER(6) NOT NULL
SOE	PRD_DESC_PK	LANGUAGE_ID	2	VARCHAR2(3) NOT NULL
SOE	PROD_NAME_IX	TRANSLATED_NAME	1	NVARCHAR2 NOT NULL

Structure, Indexes, and Statistics for Table SOE.PRODUCT_INFORMATION

Table Overview

Owner & Name	Avg. Row Length	Rows (Statistics)	Rows (Est. Actual)	Blocks (Stats)	Blocks (Segment)	Empty Blocks	Average Space	Chain Count	Global Stats	User Stats	Sample Size	Last Analyze
SOE . PRODUCT_INFORMATION	219	288	346	10	13	0	0	0	YES	NO	288	13-Jun-09 15:28:24

Table Size and Estimated Space Efficiency

Tablespace	Block Size	Table Size (MB)	Est. Space Efficiency	Buffer Pool	Degree	Cluster	IOT Type	IOT Name
SOE	8 KB	.1	87.2%	DEFAULT	1

Storage Parameters and Modifications

PCTFREE	PCTUSED	INITRANS	Monitoring	Modified (%)	INSERT	UPDATE	DELETE
10		1	YES

Column Statistics

Column Name	Data Type	Distinct Values	Avg. Length	Density	Buckets	NULLs	Global Stats	User Stats	Sample Size	Last Analyze
PRODUCT_ID	NUMBER(6) NOT NULL	288	4	0.003472	1	0	YES	NO	288	13-Jun-09 15:28:24
PRODUCT_NAME	VARCHAR2(50)	287	16	0.003484	1	0	YES	NO	288	13-Jun-09 15:28:24
PRODUCT_DESCRIPTION	VARCHAR2(2000)	288	123	0.003472	1	0	YES	NO	288	13-Jun-09 15:28:24
CATEGORY_ID	NUMBER(2)	10	3	0.1	1	0	YES	NO	288	13-Jun-09 15:28:24
WEIGHT_CLASS	NUMBER(1)	5	3	0.2	1	0	YES	NO	288	13-Jun-09 15:28:24
WARRANTY_PERIOD	INTERVAL YEAR(2) TO MONTH	15	5	0.066667	1	0	YES	NO	288	13-Jun-09 15:28:24
SUPPLIER_ID	NUMBER(6)	62	5	0.016129	1	0	YES	NO	288	13-Jun-09 15:28:24
PRODUCT_STATUS	VARCHAR2(20)	4	11	0.25	1	0	YES	NO	288	13-Jun-09 15:28:24
LIST_PRICE	NUMBER(8,2)	170	4	0.005882	1	2	YES	NO	286	13-Jun-09 15:28:24
MIN_PRICE	NUMBER(8,2)	184	4	0.005435	1	2	YES	NO	286	13-Jun-09 15:28:24
CATALOG_URL	VARCHAR2(50)	288	46	0.003472	1	0	YES	NO	288	13-Jun-09 15:28:24

Index Overview

Index Owner	Index Name	Index Type	Partitioned	Created	Last DDL Time	Last Analyze	Status	Sample Size	Tablespace	Buffer Pool	Degree
SOE	PROD_SUPPLIER_IX	NORMAL	NO	13-Jun-09 15:27:33	13-Jun-09 15:27:33	13-Jun-09 15:28:24	VALID	288	SOEINDEX	DEFAULT	1
SOE	PRODUCT_INFORMATION_PK	NORMAL	NO	13-Jun-09 15:26:27	13-Jun-09 15:26:27	13-Jun-09 15:28:24	VALID	288	SOE	DEFAULT	1

Index Statistics

Index Owner	Index Name	Index Type	Unique	B-Tree Level	Leaf Blocks	Distinct Keys	Rows	Avg. Leaf Blocks per Key	Avg. Data Blocks per Key	Clustering Factor	Global Stats	User Stats
SOE	PROD_SUPPLIER_IX	NORMAL	NO	0	1	62	288	1	1	122	YES	NO
SOE	PRODUCT_INFORMATION_PK	NORMAL	YES	0	1	288	288	1	1	10	YES	NO

Indexed Columns

Index Owner	Index Name	Column Name	Position	Data Type
SOE	PROD_SUPPLIER_IX	SUPPLIER_ID	1	NUMBER(6)
SOE	PRODUCT_INFORMATION_PK	PRODUCT_ID	1	NUMBER(6) NOT NULL

Hash Value	Elapsed Time (s)	Parsing ID	Statement Text (Excerpt)
2084491117	14.237	78 (SOE)	SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LAN
622695190	5.506	78 (SOE)	SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTIO
124468195	0.012	78 (SOE)	SELECT SYSDATE FROM DUAL
4196474773	0.000	78 (SOE)	INSERT INTO LOGON VALUES (:B2 , :B1 )
255718823	0.000	78 (SOE)	COMMIT

CHILD_NUMBER	OLD_HASH_VALUE	FORCE_MATCHING_SIGNATURE	PLAN_HASH_VALUE	OPTIMIZER_ENV_HASH_VALUE	LAST_ACTIVE_TIME	SQL_PROFILE	OUTLINE_CATEGORY	OUTLINE_TYPE
0	1094891389	0	0	3522870812	15-Nov-09 19:47:59
1	1094891389	0	0	3750204916	15-Nov-09 20:01:18

CHILD_NUMBER	CPU Time (s)	Elapsed (s)	App. Wait Time (s)	Conc. Wait Time (s)	Cluster Wait Time (s)	User I/O Wait Time (s)	PL/SQL Exec. Time (s)	Java Exec. Time (s)	EXECUTIONS	SORTS	PARSE_CALLS	OPTIMIZER_COST
0	8.81	80.374	0	0	0	71.303	3.751	0	3117	0	3117	0
1	13.198	81.098	0	0	0	46.319	3.193	0	2085	0	2085	0

Hash Value	Elapsed Time (s)	Parsing ID	Statement Text (Excerpt)
2084491117	14.566	78 (SOE)	SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LAN
820158466	0.751	78 (SOE)	SELECT ORDER_ID, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS, ORDE
124468195	0.012	78 (SOE)	SELECT SYSDATE FROM DUAL
4196474773	0.000	78 (SOE)	INSERT INTO LOGON VALUES (:B2 , :B1 )
255718823	0.000	78 (SOE)	COMMIT

CHILD_NUMBER	OLD_HASH_VALUE	FORCE_MATCHING_SIGNATURE	PLAN_HASH_VALUE	OPTIMIZER_ENV_HASH_VALUE	LAST_ACTIVE_TIME	SQL_PROFILE	OUTLINE_CATEGORY	OUTLINE_TYPE
0	727831036	0	0	3522870812	15-Nov-09 19:47:59
1	727831036	0	0	3750204916	15-Nov-09 20:01:17

CHILD_NUMBER	CPU Time (s)	Elapsed (s)	App. Wait Time (s)	Conc. Wait Time (s)	Cluster Wait Time (s)	User I/O Wait Time (s)	PL/SQL Exec. Time (s)	Java Exec. Time (s)	EXECUTIONS	SORTS	PARSE_CALLS	OPTIMIZER_COST
0	4.375	74.085	0	0	0	70.935	1.188	0	3053	0	3053	0
1	7.985	73.515	0	0	0	49.448	1.156	0	2106	0	2106	0

Hash Value	Elapsed Time (s)	Parsing ID	Statement Text (Excerpt)
2482701865	0.052	78 (SOE)	SELECT CUSTOMER_SEQ.NEXTVAL FROM DUAL
255718823	0.049	78 (SOE)	COMMIT
2084491117	0.034	78 (SOE)	SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LAN
580506954	0.022	78 (SOE)	INSERT INTO CUSTOMERS(CUSTOMER_ID ,CUST_FIRST_NAME ,CUST_LAS
124468195	0.013	78 (SOE)	SELECT SYSDATE FROM DUAL
4196474773	0.000	78 (SOE)	INSERT INTO LOGON VALUES (:B2 , :B1 )

CHILD_NUMBER	OLD_HASH_VALUE	FORCE_MATCHING_SIGNATURE	PLAN_HASH_VALUE	OPTIMIZER_ENV_HASH_VALUE	LAST_ACTIVE_TIME	SQL_PROFILE	OUTLINE_CATEGORY	OUTLINE_TYPE
0	2445712067	0	0	3522870812	15-Nov-09 19:47:59
1	2445712067	0	0	3750204916	15-Nov-09 20:01:15

CHILD_NUMBER	CPU Time (s)	Elapsed (s)	App. Wait Time (s)	Conc. Wait Time (s)	Cluster Wait Time (s)	User I/O Wait Time (s)	PL/SQL Exec. Time (s)	Java Exec. Time (s)	EXECUTIONS	SORTS	PARSE_CALLS	OPTIMIZER_COST
0	4.325	80.63	0	.007	0	76.631	1.013	0	3000	0	3000	0
1	11.629	78.227	0	0	0	51.857	.811	0	2163	0	2163	0

Hash Value	Elapsed Time (s)	Parsing ID	Statement Text (Excerpt)
746589640	4.154	78 (SOE)	SELECT /*+ first_rows index(customers, customers_pk) index(
914625479	0.001	78 (SOE)	UPDATE /+ index(orders, order_pk) / ORDERS SET ORDER_STATU
255718823	0.000	78 (SOE)	COMMIT

CHILD_NUMBER	OLD_HASH_VALUE	FORCE_MATCHING_SIGNATURE	PLAN_HASH_VALUE	OPTIMIZER_ENV_HASH_VALUE	LAST_ACTIVE_TIME	SQL_PROFILE	OUTLINE_CATEGORY	OUTLINE_TYPE
0	2757634508	0	0	3522870812	15-Nov-09 19:47:59
1	2757634508	0	0	3750204916	15-Nov-09 20:01:15

CHILD_NUMBER	CPU Time (s)	Elapsed (s)	App. Wait Time (s)	Conc. Wait Time (s)	Cluster Wait Time (s)	User I/O Wait Time (s)	PL/SQL Exec. Time (s)	Java Exec. Time (s)	EXECUTIONS	SORTS	PARSE_CALLS	OPTIMIZER_COST
0	2.711	21.124	0	0	0	18.685	.695	0	3107	0	3107	0
1	7.901	25.88	0	0	0	11.01	.553	0	2105	0	2105	0

Hash Value	Elapsed Time (s)	Parsing ID	Statement Text (Excerpt)
2379717279	0.015	0 (SYS)	update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,max
1570213724	0.000	0 (SYS)	select file# from file$ where ts#=:1

CHILD_NUMBER	OLD_HASH_VALUE	FORCE_MATCHING_SIGNATURE	PLAN_HASH_VALUE	OPTIMIZER_ENV_HASH_VALUE	LAST_ACTIVE_TIME	SQL_PROFILE	OUTLINE_CATEGORY	OUTLINE_TYPE
0	3172424902	0	0	3522870812	15-Nov-09 19:47:59
1	3172424902	0	0	3750204916	15-Nov-09 20:01:15

CHILD_NUMBER	CPU Time (s)	Elapsed (s)	App. Wait Time (s)	Conc. Wait Time (s)	Cluster Wait Time (s)	User I/O Wait Time (s)	PL/SQL Exec. Time (s)	Java Exec. Time (s)	EXECUTIONS	SORTS	PARSE_CALLS	OPTIMIZER_COST
0	6.243	128.15	0	0	0	123.355	.135	0	5030	0	2	1
1	.158	4.896	0	0	0	4.83	.003	0	133	0	2	1

Hash Value	Elapsed Time (s)	Statement Text (Excerpt)
2379717279	0.002	update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,max
3840466760	0.001	update tsq$ set blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,
1570213724	0.000	select file# from file$ where ts#=:1

CHILD_NUMBER	OLD_HASH_VALUE	FORCE_MATCHING_SIGNATURE	PLAN_HASH_VALUE	OPTIMIZER_ENV_HASH_VALUE	LAST_ACTIVE_TIME	SQL_PROFILE	OUTLINE_CATEGORY	OUTLINE_TYPE
0	2698772428	0	0	3522870812	15-Nov-09 19:47:59
1	2698772428	0	0	3750204916	15-Nov-09 20:01:16

CHILD_NUMBER	OLD_HASH_VALUE	FORCE_MATCHING_SIGNATURE	PLAN_HASH_VALUE	OPTIMIZER_ENV_HASH_VALUE	LAST_ACTIVE_TIME	SQL_PROFILE	OUTLINE_CATEGORY	OUTLINE_TYPE
0	2308915322	17991828121679737456	3661684950	3522870812	15-Nov-09 19:47:59
1	2308915322	17991828121679737456	3661684950	3750204916	15-Nov-09 20:01:16

CHILD_NUMBER	OLD_HASH_VALUE	FORCE_MATCHING_SIGNATURE	PLAN_HASH_VALUE	OPTIMIZER_ENV_HASH_VALUE	LAST_ACTIVE_TIME	SQL_PROFILE	OUTLINE_CATEGORY	OUTLINE_TYPE
0	2293415029	2672114946588399948	1388734953	3522870812	15-Nov-09 19:47:59
1	2293415029	2672114946588399948	1388734953	3750204916	15-Nov-09 20:01:17

CHILD_NUMBER	OLD_HASH_VALUE	FORCE_MATCHING_SIGNATURE	PLAN_HASH_VALUE	OPTIMIZER_ENV_HASH_VALUE	LAST_ACTIVE_TIME	SQL_PROFILE	OUTLINE_CATEGORY	OUTLINE_TYPE
0	3437483139	11220520521603652454	3151665200	2727794671	15-Nov-09 19:47:59
1	3437483139	11220520521603652454	3151665200	699142823	15-Nov-09 20:01:15

CHILD_NUMBER	CPU Time (s)	Elapsed (s)	App. Wait Time (s)	Conc. Wait Time (s)	Cluster Wait Time (s)	User I/O Wait Time (s)	PL/SQL Exec. Time (s)	Java Exec. Time (s)	EXECUTIONS	SORTS	PARSE_CALLS	OPTIMIZER_COST
0	4.363	33.08	0	0	0	27.107	0	0	5072	0	2	24988
1	.052	1.156	0	0	0	.686	0	0	140	0	2	24988

CHILD_NUMBER	OLD_HASH_VALUE	FORCE_MATCHING_SIGNATURE	PLAN_HASH_VALUE	OPTIMIZER_ENV_HASH_VALUE	LAST_ACTIVE_TIME	SQL_PROFILE	OUTLINE_CATEGORY	OUTLINE_TYPE
0	3352865145	18279731078113474405	2108772796	3522870812	15-Nov-09 19:47:59
1	3352865145	18279731078113474405	2108772796	3750204916	15-Nov-09 20:01:18

ID	PID	Operation	Object	Object ID	Rows	Bytes	Cost	I/O Cost
0		SELECT STATEMENT					24
1	0	NESTED LOOPS			20	10160	24	24
2	1	NESTED LOOPS OUTER			1	500	2	2
3	2	TABLE ACCESS BY INDEX ROWID	SOE.PRODUCT_INFORMATION	60741	1	219	1	1
4	3	INDEX UNIQUE SCAN	SOE.PRODUCT_INFORMATION_PK	60769	1		0	0
5	2	TABLE ACCESS BY INDEX ROWID	SOE.PRODUCT_DESCRIPTIONS	60743	1	281	1	1
6	5	INDEX UNIQUE SCAN	SOE.PRD_DESC_PK	60770	1		0	0
7	1	TABLE ACCESS BY INDEX ROWID	SOE.INVENTORIES	60740	20	160	22	22
8	7	INDEX RANGE SCAN	SOE.INV_PRODUCT_IX	60773	20		1	1

CHILD_NUMBER	OLD_HASH_VALUE	FORCE_MATCHING_SIGNATURE	PLAN_HASH_VALUE	OPTIMIZER_ENV_HASH_VALUE	LAST_ACTIVE_TIME	SQL_PROFILE	OUTLINE_CATEGORY	OUTLINE_TYPE
0	2690112681	9734920470490269382	1544532951	3522870812	15-Nov-09 19:47:59
1	2690112681	9734920470490269382	1544532951	3750204916	15-Nov-09 20:01:15

ID	PID	Operation	Object	Object ID	Rows	Cost	I/O Cost
0		SELECT STATEMENT				2
1	0	SEQUENCE	SOE.CUSTOMER_SEQ	60745
2	1	FAST DUAL			1	2	2

CHILD_NUMBER	OLD_HASH_VALUE	FORCE_MATCHING_SIGNATURE	PLAN_HASH_VALUE	OPTIMIZER_ENV_HASH_VALUE	LAST_ACTIVE_TIME	SQL_PROFILE	OUTLINE_CATEGORY	OUTLINE_TYPE
0	2082097592	0	494735477	3522870812	15-Nov-09 19:47:59
1	2082097592	0	494735477	3750204916	15-Nov-09 20:01:16

CHILD_NUMBER	OLD_HASH_VALUE	FORCE_MATCHING_SIGNATURE	PLAN_HASH_VALUE	OPTIMIZER_ENV_HASH_VALUE	LAST_ACTIVE_TIME	SQL_PROFILE	OUTLINE_CATEGORY	OUTLINE_TYPE
0	287228808	17860808987328922672	3237218561	3522870812	15-Nov-09 19:47:59
1	287228808	17860808987328922672	3237218561	3750204916	15-Nov-09 20:01:17

CHILD_NUMBER	OLD_HASH_VALUE	FORCE_MATCHING_SIGNATURE	PLAN_HASH_VALUE	OPTIMIZER_ENV_HASH_VALUE	LAST_ACTIVE_TIME	SQL_PROFILE	OUTLINE_CATEGORY	OUTLINE_TYPE
0	1809837244	0	0	3522870812	15-Nov-09 19:47:59
1	1809837244	0	0	3750204916	15-Nov-09 20:01:17

CHILD_NUMBER	OLD_HASH_VALUE	FORCE_MATCHING_SIGNATURE	PLAN_HASH_VALUE	OPTIMIZER_ENV_HASH_VALUE	LAST_ACTIVE_TIME	SQL_PROFILE	OUTLINE_CATEGORY	OUTLINE_TYPE
0	2385983258	8080224161820109919	2141863993	3522870812	15-Nov-09 19:47:59
1	2385983258	8080224161820109919	2141863993	3750204916	15-Nov-09 20:01:16

ID	PID	Operation	Object	Object ID	Rows	Bytes	Cost	I/O Cost
0		UPDATE STATEMENT					2
1	0	UPDATE	SOE.INVENTORIES
2	1	INDEX UNIQUE SCAN	SOE.INVENTORY_PK	60771	1	10	1	1