EBusiness Suite and Oracle Database 10g

1
E-Business Suite and Oracle Database 10g

• Ahmed Alomari,
• Applications Performance Group
• Oracle Corporation

2
Agenda

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

3
10g Certification with 11i

• 10gR1 Database (10.1.0.4) Certified with 11i
• 11.5.9 CU2 with Interoperability patch 3129264.
• 11.5.10 with Interoperability patch 4332078.
• MetaLink Note 282038.1 (10gR1 Interoperability
  note for Oracle Applications 11i)
• 10gR2 Database (10.2.0.2) Certified with 11i
• 11.5.9 CU2 with 10gR2 Interop patch 4653217.
• 11.5.10 CU2 with 10gR2 Interop patch 4653225.
• MetaLink Note 362203.1 (10gR2 Interoperability
  note for Oracle Applications 11i)

4
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

5
10g Features - Manageability

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

6
10g Features Optimizer Enhancements

• RBO Rest in Peace!!
• CPU Costing
• Cost Based Transformations
• Sub-query un-nesting
• View merging
• Pushing of join predicates
• Cartesian Joins no longer considered when a
  connection exists.
• Hash based Aggregation
• HASH GROUP BY row source instead of SORT GROUP BY
• Runtime execution plans available via vsql_plan.
• Bind variable values are captured via
  vsql_bind_capture

7
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.
• Query Block Name (qblock_name) can be used in
  conjunction with query block hint.
• dbms_xplan.display_cursor() can be used to show
  runtime plan of a cursor.

8
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")
9
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')
10
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.

11
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.

12
10g Features PL/SQL Enhancements

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

13
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.

14
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.

15
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.

16
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)
17
10g Features

• Library Cache Optimizations
• New efficient mutex is used to pin/unpin cursors
• Reduces library cache pin latch contention
  considerably
• Performance Improvement ranges from 10-90
  depending on the amount of time spent in library
  cache latch contention (due to pins).
• Init.ora parameter _kks_use_mutex_pinTRUE
  enables this optimization
• Enabled by default in 10.2.0.2

18
10g Features

• Library Cache Optimizations
• Optimize special cursors
• COMMIT
• ROLLBACK
• SAVEPOINT
• ALTER SESSION
• Improves Applications performance 5-10

19
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')
20
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.

21
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
22
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
23
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
• Diff Report of two periods
• Top SQL Section Reports
• Elapsed Time
• CPU Time
• Buffer Gets
• Physical Reads
• Executions
• Parse Calls
• Sharable Memory
• Version Count

24
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
25
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

26
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
27
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
28
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
29
D E M O N S T R A T I O N
AWR Report
30
10g Features

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

31
(No Transcript)
32
(No Transcript)
33
(No Transcript)
34
(No Transcript)
35
(No Transcript)
36
D E M O N S T R A T I O N
DB Control
37
11i/10g Batch Improvements
38
11i/10g Performance Improvements
39
11i/10g Performance Improvements
40
11i/10g Performance Improvements
41
11i/10g Applications Reports Improvements
42
References

• Oracles Business runs on 10gR2
• 7.5 TB DB
• 60 Modules
• 11.5.10
• 10,000 concurrent users
• 4 node RAC Cluster
• Dell
• Order Management
• 11.5.10
• Peak volume 3.5 million order lines per day
• Web store
• Linux RAC Cluster

43
References

• BAE Systems
• 350 GB DB
• 11.5.10
• 6,000 users
• 4 node RAC Cluster
• Timex
• Order Management
• 11.5.10
• 44 million order lines
• EDW Supply Chain Intelligence
• Linux

44
MetaLink References
45

• Q A