eBusiness Suite and Oracle Database 10g

1
eBusiness Suite and Oracle Database 10g

• Ahmed Alomari
• Applications Performance Group
• Oracle Corporation
• June 13, 2005

2
Agenda

• Certification Details
• 10g Features
• Manageability
• Optimizer
• PL/SQL
• Parallel Execution
• JDBC
• AWR
• DB Console
• Applications Performance Improvements
• Q A

3
10g Certification with 11i

• 10gR1 Database (10.1.0.4) Certified with 11i
• 10.1.0.4 patchset
• Apply 10.1.0.4 Merge patch (4359526) for Oracle
  Applications.
• 11.5.9 with Interoperability patch 3129264.
• 11.5.10 with Interoperability patch 4332078.
• MetaLink Note 282038.1 (10gR1 Interoperability
  note for Oracle Applications 11i)

4
10g Certification with 11i

• 10gR1 (10.1.0.4) Certified with 11i

Platform Certification Date
Linux x86 June 3, 2005
Linux x86 64 bit July 2005
Linux Itanium July 2005
Solaris SPARC (64-bit) June 17, 2005
IBM AIX 5L (64-bit) July 13, 2005
HP-UX PA 11i (64-bit) July 8, 2005
HP-UX Itanium August 29, 2005
HP Tru64 UNIX Not planned
Windows (32-bit) July 4, 2005
Windows Itanioum July 26, 2005
5
10g Features - Manageability

• Automatic Workload Repository (AWR)
• Data Warehouse of the Database
• Replacement for StatsPack
• Takes periodic snapshots
• Automatic Database Diagnostic Monitor (ADDM)
• Performance Diagnostic engine in the database
• Automatically diagnoses performance problems
• Provides Root Cause Analysis with recommended
  solutions
• Identifies non-problems areas
• Integrates all components

6
10g Features - Manageability

• Automatic SGA tuning
• Automatically sizes the individual SGA caches
• Buffer cache
• Log buffer
• Shared pool
• Java pool
• Uses Advisory information to dynamically size the
  caches.
• Just set sga_targetltSGA sizegt
• sga_target10G

7
10g Features Optimizer Enhancements

• RBO Rest in Peace!!
• CPU Costing
• Cost Based Transformations
• Sub-query un-nesting
• View merging
• Pushing of join predicates
• Runtime execution plans available via vsql_plan.
• Bind variable values are captured via
  vsql_bind_capture

8
10g Features Optimizer Enhancements

• Plan table includes the following new columns
• CPU_COST estimated CPU cost
• IO_COST estimated I/O cost
• TEMP_SPACE estimated temporary space, in bytes,
  used by the operation Network usage for query
  servers running on different nodes will be
  estimated
• Time an estimate of how long each row source
  will take.

9
10g Features Optimizer Enhancements

• New Format includes CPU cost, and time.

-------------------------------------------------
--------------------------------------------------
--------------- Id Operation
Name Rows
Bytes Cost (CPU) Time ------------------
--------------------------------------------------
----------------------------------------------
0 SELECT STATEMENT
1 74 7
(0) 000001 1 SORT AGGREGATE
1
74 2 TABLE
ACCESS BY INDEX ROWID MTL_ITEM_CATEGORIES
1 9 2 (0) 000001
3 NESTED LOOPS
1 74 7
(0) 000001 4 NESTED LOOPS
1
65 5 (0) 000001 5
MERGE JOIN CARTESIAN
1 42 4 (0) 000001
6 TABLE ACCESS FULL
FINANCIALS_SYSTEM_PARAMS_ALL 1 4
4 (0) 000001 7 BUFFER SORT

1 38 0 (0) 000001 8
TABLE ACCESS BY INDEX ROWID PO_APPROVED_SUPPLIER
_LIST 1 38 0 (0) 000001
9 INDEX RANGE SCAN
PO_APPROVED_SUPPLIER_LIST_N1 1
0 (0) 000001 10 TABLE ACCESS BY
INDEX ROWID PO_ASL_STATUS_RULES
1 23 1 (0) 000001 11
INDEX RANGE SCAN PO_ASL_STATUS_RULES_
U1 4 0 (0) 000001
12 INDEX RANGE SCAN
MTL_ITEM_CATEGORIES_N3 3695
2 (0) 000001 -----------------------------
--------------------------------------------------
----------------------------------- Predicate
Information (identified by operation
id) ---------------------------------------------
------ 2 - filter("MIC"."INVENTORY_ITEM_ID"TO
_NUMBER(ITEM_ID)) 6 - filter(NVL("ORG_ID",NVL(
TO_NUMBER(DECODE(SUBSTRB(B1,1,1),'
',NULL,SUBSTRB(B2,1,10))),(-99)))NVL(TO_NUMBER(D
ECODE(SUBSTRB(B3,1,1),'
',NULL,SUBSTRB(B4,1,10))),(-99))) 8 -
filter("CATEGORY_ID" IS NOT NULL AND
"VENDOR_SITE_ID"TO_NUMBER(VENDOR_SITE_ID) AND
("USING_ORGANIZATION_ID"(-1) OR
"USING_ORGANIZATION_ID"DECODE(TYPE_LOOKUP_CODE,'
BLANKET',"USING_ORGANIZ
ATION_ID",SHIP_TO_ORGANIZATION_ID)) AND
NVL("DISABLE_FLAG",'N')'N') 9 -
access("ITEM_ID" IS NULL AND "VENDOR_ID"TO_NUMBER
(VENDOR_ID)) filter("VENDOR_ID"TO_NUMBER(
VENDOR_ID)) 10 - filter("ASR"."BUSINESS_RULE"'
1_PO_APPROVAL') 11 - access("ASL_STATUS_ID""ASR
"."STATUS_ID") 12 - access("CATEGORY_ID""MIC"."
CATEGORY_ID")
10
10g Features Optimizer Enhancements

• New Format includes CPU cost, time, and temp
  space (if used).

-------------------------------------------------
--------------------------------------------------
Id Operation Name
Rows Bytes TempSpc Cost (CPU) Time
------------------------------------------------
--------------------------------------------------
- 0 SELECT STATEMENT
18M 15G 1681K (7) 053616
1 HASH JOIN
18M 15G 1434M 1681K (7) 053616
2 TABLE ACCESS FULL OE_ORDER_HEADERS_AL
L 3937K 1389M 62020 (2) 001225
3 TABLE ACCESS FULL OE_ORDER_LINES_ALL
18M 9685M 1053K (10) 033041
------------------------------------------------
--------------------------------------------------
- Predicate Information (identified by operation
id) ---------------------------------------------
------ 1 - access("H"."HEADER_ID""L"."HEADER_
ID") 3 - filter("L"."CANCELLED_FLAG"'N')
11
10g Features Optimizer Enhancements

• Workload Statistics
• Workload statistics allows the optimizer to learn
  the patterns of load and the duration of peak
  loads.
• Adaptive execution plans.
• The CBO might choose different execution plans
  based on the workload statistics.

12
10g Features Optimizer Enhancements

• Object Caching Statistics
• The cost of accessing an object is based on
  whether blocks are read from cache or from disk.
• Prior to 10g, the optimizer used defaults and
  heuristics to estimate the amount of cached data.
• In 10g, object level caching statistics is
  integrated in the cost model.
• Improves execution plans by accurately costing
  caching based on actuals.

13
10g Features Optimizer Enhancements

• SQL Tune
• Optimizer spends more time by widening the search
  space and sampling in order to generate a more
  efficient plan.
• SQL Tune profiles are saved.
• Profiles can be created via the SQL Tuning
  Advisor via DB Console.
• Extremely useful for complex Applications SQL for
  which the default plan was not optimal.
• Immediate performance relief.

14
10g Features PL/SQL Enhancements

• Every line of PL/SQL code runs 2X faster.
• Global Optimizer.
• Improved Native Compilation.
• Reduced collections memory footprint (15).

15
10g Features PL/SQL Enhancements

• Global Optimizer
• Eliminates temporary operands generated by the
  PL/SQL compiler. Such elimination causes less
  storage to be used and less time taken
  initializing temporary values.
• Computes certain operations during compilation
  rather that during execution.
• Reuses expression values.
• Converts single row fetches to bulk fetches.

16
10g Features PL/SQL Enhancements

• Native Compilation
• Natively compiled PL/SQL code executes even
  faster than in earlier releases.
• The natively compiled units are stored in the
  database as BLOB data.
• Improves RAC Deployment
• You can switch between native and interpreted
  compilation for stored PL/SQL code for debugging
  purposes.

17
10g Features PL/SQL Enhancements

• Native Compilation
• Init.ora parameters for PL/SQL compilation
• PLSQL_CODE_TYPE interpreted/native
• PLSQL_OPTIMIZE_LEVEL 2 is the default, 0 turns
  it off. A value of 1 refers to basic
  optimization, and 2 refers to advanced
  optimizations.
• The data dictionary views USERALLDBA_PLSQL_OBJEC
  TS displays the settings for a PLSQL object.

18
10g Features

• Rename a Tablespace.
• Online segment shrink.
• Automatic undo retention tuning.
• Semi-static VPD policies.
• Policy function is only invoked when context is
  reset.
• Partitioning
• List Partitioning enhancements
• Global hash partitioned indexes.
• RAC
• Dynamic lock affinity
• LOB operations improved
• Table Scan performance improved
• JDBC Driver improvements

19
10g Features SQL Optimization

• FAST DUAL Optimization

10g SELECT SYSDATE FROM DUAL call count
cpu elapsed disk query
current rows ------- ------ --------
---------- ---------- ---------- ----------
---------- Parse 2 0.00 0.00
0 0 0 0 Execute
40000 4.31 3.90 0 0
0 0 Fetch 40000 0.86
0.66 0 0 0
40000 ------- ------ -------- ----------
---------- ---------- ----------
---------- total 80002 5.17 4.57
0 0 0 40000 Rows
Row Source Operation -------
--------------------------------------------------
- 20000 FAST DUAL (cr0 pr0 pw0 time106978
us) 9iR2 SELECT SYSDATE FROM DUAL call
count cpu elapsed disk query
current rows ------- ------ --------
---------- ---------- ---------- ----------
---------- Parse 2 0.01 0.00
0 0 0 0 Execute
40000 3.70 3.64 0 0
0 0 Fetch 40000 5.94
5.56 0 120000 0
40000 ------- ------ -------- ----------
---------- ---------- ----------
---------- total 80002 9.65 9.22
0 120000 0 40000 Rows
Row Source Operation -------
--------------------------------------------------
- 20000 TABLE ACCESS FULL DUAL (cr60000 pr0
pw0 time2622560 us)
20
10g Features

• 10g Parallel Execution Improvements
• No Slave SQL
• Parallel row sources
• Improved granule algorithm
• Parallel Operations
• Parallel queries Full table scans, fast full
  index scans, ...
• Parallel DDL (PDDL)CREATE INDEX, CREATE TABLE AS
  SELECT, ALTER TABLE MOVE.
• Parallel DML (PDML) Delete, Update, Insert,
  Merge
• Parallel data loading
• Parallel recovery

21
10g Features

• 10g Parallel Execution Improvements No Slave SQL

--------------------------------------------------
--------------------------------------------------
-------------------------- Id Operation
Name Rows Bytes
Cost (CPU) Time TQ IN-OUT PQ
Distrib ----------------------------------------
--------------------------------------------------
------------------------------------ 0
SELECT STATEMENT
4285 301K 168K (5) 003346
1 PX COORDINATOR


2 PX SEND QC (RANDOM) TQ10002
4285 301K 168K (5) 003346
Q1,02 P-gtS QC (RAND) 3 FILTER

Q1,02 PCWC
4 SORT GROUP BY
4285 301K 168K (5)
003346 Q1,02 PCWP 5
PX RECEIVE
85689 6025K 168K (5) 003346 Q1,02
PCWP 6 PX SEND HASH
TQ10001 85689 6025K
168K (5) 003346 Q1,01 P-gtP HASH
7 HASH JOIN
85689 6025K 168K (5) 003346
Q1,01 PCWP 8 PX
RECEIVE 892K
21M 10147 (14) 000202 Q1,01 PCWP
9 PX SEND BROADCAST
TQ10000 892K 21M 10147 (14)
000202 Q1,00 P-gtP BROADCAST 10
PX BLOCK ITERATOR
892K 21M 10147 (14) 000202 Q1,00
PCWC 11 TABLE
ACCESS FULL AP_AE_HEADERS_ALL 892K 21M
10147 (14) 000202 Q1,00 PCWP
12 PX BLOCK ITERATOR
63M 2846M 158K (4) 003141
Q1,01 PCWC 13
TABLE ACCESS FULL AP_AE_LINES_ALL 63M
2846M 158K (4) 003141 Q1,01 PCWP
----------------------------------------
--------------------------------------------------
------------------------------------ Predicate
Information (identified by operation
id) ---------------------------------------------
------ 3 - filter(SUM(NVL("AEL"."ACCOUNTED_CR"
,0))ltgtSUM(NVL("AEL"."ACCOUNTED_DR",0))) 7 -
access("AEL"."AE_HEADER_ID""AEH"."AE_HEADER_ID"
AND NVL("AEL"."ORG_ID",(-99))NVL("AEH"."ORG_ID",(
-99))) 11 - filter(TRUNC("AEH"."ACCOUNTING_DATE"
)ltTRUNC(SYSDATE_at_!) AND "AEH"."GL_TRANSFER_FLAG"'
Y') 13 - filter("AEL"."AE_LINE_TYPE_CODE"'LIABI
LITY')
22
10g Features - JDBC

• Improved connection cache
• Separates physical and logical connection layers.
• Improved performance (10)
• Bundled calls (single round trip per SQL call) as
  opposed to 2 or 3 round-trips PARSE, EXECUTE,
  FETCH in 9i.
• Automatic type and precision detection upon
  execution.

23
10g Features - JDBC

• Round-trips in 9i JDBC

SELECT u.user_name FROM fnd_user u where
u.user_id1 call count cpu
elapsed disk query current
rows ------- ------ -------- ----------
---------- ---------- ----------
---------- Parse 1 0.00 0.00
0 0 0 0 Execute
1000 0.26 0.25 0 0
0 0 Fetch 1000 0.17
0.18 0 3000 0
1000 ------- ------ -------- ----------
---------- ---------- ----------
---------- total 2001 0.43 0.44
0 3000 0 1000 Rows
Row Source Operation -------
--------------------------------------------------
- 1000 TABLE ACCESS BY INDEX ROWID
OBJ(60344) (cr3000 pr0 pw0 time63796 us)
1000 INDEX UNIQUE SCAN OBJ(60363) (cr2000
pr0 pw0 time36983 us)(object id
60363) Elapsed times include waiting on
following events Event waited on
Times Max. Wait Total Waited
---------------------------------------- Waited
---------- ------------ SQLNet message to
client 2003 0.00
0.00 SQLNet message from client
2003 0.14 2.34
24
10g Features - JDBC

• Round Trips in 10g - Bundled Calls

SELECT u.user_name FROM fnd_user u where
u.user_id1 call count cpu
elapsed disk query current
rows ------- ------ -------- ----------
---------- ---------- ----------
---------- Parse 1 0.00 0.00
0 0 0 0 Execute
1000 0.19 0.15 0 0
0 0 Fetch 1000 0.24
0.17 0 3000 0
1000 ------- ------ -------- ----------
---------- ---------- ----------
---------- total 2001 0.43 0.32
0 3000 0 1000 Rows
Row Source Operation -------
--------------------------------------------------
- 1000 TABLE ACCESS BY INDEX ROWID
OBJ(60344) (cr3000 pr0 pw0 time67134 us)
1000 INDEX UNIQUE SCAN OBJ(60363) (cr2000
pr0 pw0 time39483 us)(object id
60363) Elapsed times include waiting on
following events Event waited on
Times Max. Wait Total Waited
---------------------------------------- Waited
---------- ------------ SQLNet message to
client 1001 0.00
0.00 SQLNet message from client
1001 0.35 1.29
25
10g Features - Manageability

• AWR Reports
• Includes Time Model Breakdown
• Total DB time
• Total time in SQL
• Total time in PL/SQL
• Total time in Parsing
• Top SQL Section Reports
• Elapsed Time
• CPU Time
• Buffer Gets
• Physical Reads
• Executions
• Parse Calls
• Sharable Memory
• Version Count

26
10g Features - Manageability

• Improved detailed timing (of DB time)
• vsys_time_model

STAT_NAME

VALUE --------------------------------------------
-------------------- ---------- DB CPU

3.9431E11 DB time
6.0332E11 Java
execution elapsed time
0 PL/SQL compilation elapsed
time
1866649686 PL/SQL execution elapsed time
1.3932E10 background
cpu time
2.5332E10 background elapsed time
3.8919E12 connectio
n management call elapsed time
14244419 failed parse (out of shared
memory) elapsed time
0 failed parse elapsed time
7519740 hard parse (bind
mismatch) elapsed time
3131175 hard parse (sharing criteria) elapsed
time 2240364434 hard parse
elapsed time
1.0164E10 inbound PL/SQL rpc elapsed time
0 parse
time elapsed
2.8131E10 sequence load elapsed time
35252041 sql
execute elapsed time
5.8356E11
Parse
Conn Mgmt
Java Exec
PLSQL Exec
SQL Exec
27
10g Features - Manageability

• Improved wait information
• Wait Statistics rolled-up by class.
• New Wait Classes
• Administrative
• Application
• Cluster
• Commit
• Concurrency
• Configuration
• Idle
• Network
• Other
• Scheduler
• System I/O
• User I/O

28
10g Features - Manageability

• Improved wait information
• Latch and enqueue names are included in the wait.

EVENT
TOTAL_WAITS TIME_WAITED ------------
--------------------------------- -----------
----------- enq CF - contention
11 18 enq FU -
contention 258900
77846098 enq HW - contention
1 0 enq PS -
contention 7097
640 enq TM - contention
4 1 enq TX -
contention 3
2 enq US - contention
172 255 latch In memory
undo latch 4
1 latch cache buffers chains
94978 67229 latch cache buffers lru
chain 5989
1522 latch enqueue hash chains
24 3 latch library cache
1511
684 latch library cache lock
510 126 latch library cache pin
21
3 latch parallel query alloc buffer
30 2 latch row cache objects
53 39 latch
session allocation
244439 144724 latch shared pool
831 187
29
10g Features - Manageability

• SQL Identification
• New SQL_ID is used in place of SQL hash value to
  uniquely identify SQL statements
• Preserved across releases
• Allows SQL statement statistics to be easily
  compared across upgrades.

SQL_ID HASH_VALUE -------------
---------- SQL_TEXT ------------------------------
--------------------------------------------------
66tmsr3446uqn 3359861460 SELECT
WIAS.ACTIVITY_STATUS, WIAS.ACTIVITY_RESULT_CODE,
WIAS.ASSIGNED_USER, WIAS.NOTIFICATION_ID,
WIAS.BEGIN_DATE, WIAS.END_DATE, WIAS.DUE_DATE,
WIAS.ERROR_NAME, WIAS.ERROR_MESSAGE,
WIAS.ERROR_STACK FROM WF_ITEM_ACTIVITY_STATUSES
WIAS WHERE WIAS.ITEM_TYPE B3 AND
WIAS.ITEM_KEY B2 AND WIAS.PROCESS_ACTIVI
TY B1
30
10g Features - Manageability

• SQL Identification
• Where is this SQL coming from?
• SQLs originating from server side PL/SQL units
  can be identified using the new information in
  VSQL.
• VSQL (PROGRAM_ID, PROGRAM_LINE)

select o.owner,o.object_name,o.object_type,s.prog
ram_line from dba_objects o, vsql s
where o.object_id s.program_id and
s.sql_id '66tmsr3446uqn OWNER
OBJECT_NAME OBJECT_TYPE
PROGRAM_LINE ---------- -------------------------
----- --------------- ------------- APPS
WF_ITEM_ACTIVITY_STATUS PACKAGE BODY
76
31
D E M O N S T R A T I O N
AWR Report
32
10g Features

• DB Console
• A component of 10g Grid Control
• Complete Management Portal
• Alerts
• Maintenance
• Performance
• Administration
• Availability
• Space Management
• SQL Tuning Advisor

33
10g Features DB Console
34
10g Features DB Console
35
10g Features DB Console
36
10g Features DB ConsoleTop SQL
37
10g Features DB ConsoleTop SQL
38
11i/10g Batch Improvements

• Concurrent Program Performance

Concurrent Program Name 10g 9iR2 Improvement
High Volume Order Import 49.5K 37.5K 24
Pick Release 46.2K 31K 33
Ship Confirm 146K 107K 27
39
11i/10g Performance Improvements

• Territory Assignment (TAP)
• Assign Territory Accesses
• Type 1 1hr 56mins (9iR2)
• Type 1 1hr 49mins (10g)
• Type 2 2hrs 6mins (9iR2)
• Type 2 1hr 57mins (10g)
• Opportunities
• 3hrs 32mins (9iR2)
• 2hrs 40mins (10g)
• Leads
• 7hrs 4mins (9iR2)
• 5hrs 27mins (10g)

40
11i/10g Performance Improvements

• Payables
• Invoice Import (10K)
• 26 minutes (9iR2)
• 12 minutes (10g)
• Accounting Engine (10K)
• 8 minutes (9iR2)
• 7 minutes (10g)
• Approvals
• 46 minutes (9iR2)
• 39 minutes (10g)

41
11i/10g Performance Improvements

• HRMS
• Benefits
• 1 event life cycle in 3.11 seconds (9iR2)
• 1 event life cycle in 2.69 seconds (10g)
• Payroll
• 9.5 hours (9iR2)
• 8.5 hours (10g)

42
11i/10g Performance Improvements

• DBI
• Sales Forecast Summary
• 37 minutes (9iR2)
• 31 minutes (10g)
• Sales Opportunity Summary
• 7 minutes (9iR2)
• 6 minutes (10g)

43
11i/10g Applications Reports Improvements
Report Name 10g 9iR2 Improvement
Potential Revenue Summary 2 hrs 15 min 15 hrs 18 min 6.8X
Withholding Tax by Invoice Report 15 hrs 20 min 20 hrs 20 min 1.3X
Employee Assignments Report 12 min. 46 min. 4X
Purchase Summary By Category 37 min 1 hr 4 min 1.7X
Order/Invoice Detail Report 16 min 39 min 2.5X
Unbooked Orders Report 1 min 12 min 12X
44
MetaLink References
Note Description
282038.1 Oracle Applications Release 11i with Oracle Database 10g (Interoperability Note)
165195.1 Using AutoConfig to Manage System Configurations with Oracle Applications 11i
216205.1 Database Initialization Parameters and Configuration for Oracle Applications 11i
244040.1 Recommended Performance Patches for Oracle E-Business Suite
246105.1 Using J2SE Version 1.4 with Oracle E-Business Suite 11i, Release 11.5.9 or Higher
45
A