About Oracle Applications,Database and related technologies, Concepts,issues and probable fixes,troubleshooting and resolutions.

Friday, February 27, 2015

Important schema tables/indexes and concurrent porgrams in R12 upgrade

---
Schema Size.-- is the list of Tables/Indexes in various
schemas/modules that carry huge data and will make a difference in
iteration timings.
We can keep a watch when these tables
or related scripts come into picture during upgrade driver.

-- Post upgrade job -- is the list of Concurrent programs that will be
submitted automatically during upgrade.
We anyhow, ensure they get
completed once we bring up services, but having a list consolidated
could be useful.

Schema Size:

Product

Object Names

Object Type

R11i Size (MB)

R12 Size (MB) - 3yrs

Delta (MB) - 3yrs

% Growth - 3yrs

R12 Size (MB) - 12yrs

Delta (MB) - 12yrs

% Growth - 12yrs

Financials

AMW

153

154

0

154

0

AP

242399

347606

105206

43.40%

353691

111292

45.90%

AP_INVOICE_DISTS_ARCH

TABLE

0

31197

31197

31197

31197

AP_INVOICE_LINES_ALL

TABLE

0

29824

29824

29760

29760

AP_INVOICE_DISTRIBUTIONS_ALL

TABLE

31197

48512

17315

55.50%

48512

17315

55.50%

AP_PAYMENT_HIST_DISTS

TABLE

0

4864

4864

7623

7623

AP_EXP_REPORT_DISTS_ALL

TABLE

772

4496

3724

482.40%

4496

3724

482.40%

AP_INVOICE_DISTS_ARCH_N14

INDEX

0

2901

2901

2901

2901

AP_INVOICE_LINES_U1

INDEX

0

2432

2432

2432

2432

AP_INVOICE_DISTS_ARCH_N18

INDEX

0

2264

2264

2264

2264

AR

414031

413972

-59

0.00%

413972

-59

0.00%

AX

48933

48665

-268

-0.50%

48665

-268

-0.50%

BIC

11

11

0

11

0

CE

2634

2831

197

7.50%

2831

197

7.50%

CE_STATEMENT_HEADERS_U2

INDEX

12

24

11

91.70%

24

11

91.70%

FA

18739

18774

36

0.20%

18832

93

0.50%

FA_MASS_ADDITIONS_N8

INDEX

32

58

26

81.20%

58

26

81.20%

FII

94215

100906

6691

7.10%

95295

1080

1.10%

FUN

0

1005

1005

1005

1004

FV

37

38

1

2.70%

38

1

2.70%

GL

510097

515728

5631

1.10%

527913

17816

3.50%

GL_IMPORT_REFERENCES_N4

INDEX

3248

7313

4065

125.20%

18534

15286

470.60%

HZ

378642

390322

11680

3.10%

390483

11841

3.10%

HZ_PARTY_USG_ASSIGNMENTS

TABLE

0

3028

3028

3028

3028

HZ_STAGED_PARTIES_N1

INDEX

0

2140

2140

2140

2140

HZ_GEO_NAME_REFERENCES_U1

INDEX

4

480

476

11900.00%

468

464

11600.00%

IA

1

1

0

1

0

IBY

541

4086

3544

655.10%

4100

3559

657.90%

IEX

50

32

-18

-36.00%

32

-18

-36.00%

IGI

49

49

0

49

0

IGW

14

15

1

7.10%

15

1

7.10%

IMC

8

8

0

8

0

ITA

0

8

8

8

8

JA

6

6

0

6

0

JAI

0

32

32

32

32

JE

190

191

1

0.50%

191

1

0.50%

JG

288

291

4

1.40%

291

3

1.00%

JL

5952

6009

57

1.00%

6010

58

1.00%

JL_ZZ_AP_INV_DIS_WH_ALL

TABLE

6

8

2

33.30%

8

2

33.30%

LNS

0

8

8

8

8

OIE

17

18

1

5.90%

18

1

5.90%

PSA

9007

9008

1

0.00%

9008

1

0.00%

PSB

32

33

0

33

0

QRM

7

7

0

7

0

RG

1038

1372

334

32.20%

1371

334

32.20%

RG_REPORT_AXIS_CONTENTS

TABLE

452

821

369

81.60%

821

369

81.60%

RG_REPORT_CONTENT_OVERRIDES

TABLE

3

8

5

166.70%

8

5

166.70%

XLA

10

303219

303208

3032080.00%

560373

560363

5603630.00%

XLA_AE_LINES

TABLE PARTITION

0

99343

99343

153981

153981

XLA_DISTRIBUTION_LINKS

TABLE PARTITION

0

62699

62699

131320

131320

XLA_DISTRIBUTION_LINKS_N1

INDEX PARTITION

0

24249

24249

54407

54407

XLA_DISTRIBUTION_LINKS_U1

INDEX PARTITION

0

19665

19665

39736

39736

XLA_AE_LINES_N4

INDEX PARTITION

0

18145

18145

29834

29834

XLA_AE_LINES_U1

INDEX PARTITION

0

17953

17953

28897

28897

XLA_DISTRIBUTION_LINKS_N3

INDEX PARTITION

0

13433

13433

27898

27898

XLA_AE_HEADERS

TABLE PARTITION

0

11883

11883

20080

20080

XLA_EVENTS

TABLE PARTITION

0

6620

6620

11652

11652

XLE

0

4

4

4

4

XTR

883

918

35

4.00%

918

35

4.00%

ZX

0

69080

69080

152088

152088

ZX_LINES_DET_FACTORS

TABLE

0

28251

28251

71061

71061

ZX_LINES

TABLE

0

15066

15066

33435

33435

ZX_PARTY_TAX_PROFILE

TABLE

0

5568

5568

5568

5568

ZX_REC_NREC_DIST

TABLE

0

4800

4800

9378

9378

ZX_LINES_DET_FACTORS_U1

INDEX

0

3584

3584

9034

9034

ZX_LINES_U1

INDEX

0

2368

2368

5202

5202

Other

Object Names

Object Type

R11i Size (MB)

R12 Size (MB) - 3yrs

Delta (MB) - 3yrs

% Growth - 3yrs

R12 Size (MB) -12yrs

Delta (MB) - 12yrs

% Growth - 12yrs

A2K

36

36

0

36

0

ABM

11

11

0

11

0

AC

660

660

0

660

0

AD

5544

5914

370

6.70%

5879

334

6.00%

AHL

50

53

3

6.00%

53

3

6.00%

AK

1767

1831

63

3.60%

1830

63

3.60%

ALL

75

75

0

75

0

ALR

7540

7540

0

7540

0

AME

1344

1375

31

2.30%

1375

31

2.30%

AME_APPROVAL_GROUP_CONFIG_PK

INDEX

3

6

3

100.00%

6

3

100.00%

AME_APPROVAL_GROUP_CONFIG

TABLE

4

6

2

50.00%

6

2

50.00%

AMS

47958

48230

273

0.60%

48229

271

0.60%

AMS_IMP_LIST_HEADERS_ALL_TL_U1

INDEX

8

16

8

100.00%

16

8

100.00%

AMV

21

21

0

21

0

AS

107044

103371

-3674

-3.40%

103371

-3674

-3.40%

ASF

210

210

0

210

0

ASG

79

79

0

79

0

ASL

3

3

0

3

0

ASO

70892

70217

-675

-1.00%

70217

-675

-1.00%

ASO_SUP_INSTANCE_VALUE

TABLE

18

27

8

44.40%

27

8

44.40%

ASO_QUOTE_LINE_DETAILS

TABLE

47

54

8

17.00%

54

8

17.00%

ASP

0

0

0

0

0

AST

3

3

0

3

0

AU

1

1

0

1

0

AZ

8

10

2

25.00%

10

2

25.00%

BAO

1

1

0

1

0

BEN

88109

87796

-313

-0.40%

87796

-313

-0.40%

BIL

64039

63603

-436

-0.70%

63603

-436

-0.70%

BIM

152186

140897

-11289

-7.40%

140897

-11289

-7.40%

BIS

977

982

6

0.60%

982

6

0.60%

BIS

BIS_RELATED_LINKS_TL

TABLE

3

4

1

33.30%

4

1

33.30%

BIV

7

6

0

6

0

BIX

20

20

0

20

0

BNE

149

181

32

21.50%

180

32

21.50%

BNE_INTERFACE_COLS_TL

TABLE

12

16

5

41.70%

16

4

33.30%

BNE_CONTENT_COLS_TL_UK1

INDEX

6

10

4

66.70%

10

4

66.70%

BNE_CONTENT_COLS_TL

TABLE

9

12

4

44.40%

12

4

44.40%

BNE_INTERFACE_COLS_TL_UK1

INDEX

6

10

4

66.70%

10

4

66.70%

BOM

3735

3741

6

0.20%

3741

6

0.20%

BSC

41

227

186

453.70%

226

185

451.20%

CCT

18

19

1

5.60%

19

1

5.60%

CLN

17

18

2

11.80%

18

2

11.80%

CN

170537

175770

5233

3.10%

175757

5220

3.10%

CRP

4

4

0

4

0

CS

16166

16144

-22

-0.10%

16144

-22

-0.10%

CSC

11

11

0

11

1

9.10%

CSD

11

15

4

36.40%

15

4

36.40%

CSF

13

17

4

30.80%

17

4

30.80%

CSI

62929

62980

51

0.10%

62970

41

0.10%

CSL

18

17

-1

-5.60%

17

-1

-5.60%

CSM

31

40

9

29.00%

40

9

29.00%

CSP

23

25

2

8.70%

25

2

8.70%

CSR

1

1

0

1

0

CST

7617

7613

-5

-0.10%

7613

-5

-0.10%

CTS

11

11

0

11

0

CUG

5

5

0

5

0

CUR

0

0

0

0

0

CZ

325

311

-14

-4.30%

311

-15

-4.60%

DDD

11

11

0

11

0

DL

0

0

0

0

0

DOM

1

5

4

400.00%

5

4

400.00%

DT

8

12

4

50.00%

12

4

50.00%

DT_COLUMN_PROMPTS_TL

TABLE

3

5

2

66.70%

5

2

66.70%

DT_COLUMN_PROMPTS_TL_PK

INDEX

2

3

2

100.00%

3

2

100.00%

EAM

7

11

4

57.10%

11

4

57.10%

ECX

462

468

6

1.30%

468

6

1.30%

ECX_OBJECT_ATTRIBUTES

TABLE

7

11

4

57.10%

11

4

57.10%

ECX_OBJECT_ATTRIBUTES_U1

INDEX

4

6

2

50.00%

6

2

50.00%

EDR

5

6

1

20.00%

6

1

20.00%

EGO

4348

4674

327

7.50%

4674

327

7.50%

EGO_MTL_SY_ITEMS_EXT_TL

TABLE PARTITION

1381

1933

553

40.00%

1933

553

40.00%

ENG

153

155

2

1.30%

155

2

1.30%

ENI

1459

1456

-3

-0.20%

1456

-3

-0.20%

ERS

16

16

0

16

0

FEM

0

280

280

280

280

FF

912

7764

6852

751.30%

7683

6771

742.40%

FF_DATABASE_ITEMS_TL

TABLE

0

3043

3043

2979

2979

FLM

6

6

1

16.70%

6

1

16.70%

FND

219616

221993

2377

1.10%

221514

1898

0.90%

FND_DOCUMENTS

TABLE

1005

1434

429

42.70%

1434

430

42.80%

FND_LOG_MESSAGES

TABLE

99

453

354

357.60%

264

165

166.70%

FND_COLUMNS

TABLE

77

141

64

83.10%

141

64

83.10%

FND_COMPILED_DESCRIPTIVE_FLEXS

TABLE

132

192

60

45.50%

176

44

33.30%

FND_LOOKUP_VALUES

TABLE

297

341

44

14.80%

341

44

14.80%

FND_LOOKUP_VALUES_U2

INDEX

192

220

28

14.60%

220

28

14.60%

FPA

0

0

0

0

0

FRM

0

2

2

2

2

FTE

23

23

0

23

0

FTP

0

6

6

6

6

GCS

3

12

8

266.70%

12

8

266.70%

GHR

58

63

6

10.30%

63

6

10.30%

GMA

3

4

1

33.30%

4

1

33.30%

GMD

31

33

2

6.50%

33

2

6.50%

GME

7

9

2

28.60%

9

2

28.60%

GMF

4

6

2

50.00%

6

2

50.00%

GMI

5

5

0

5

0

GML

7

7

0

7

0

GMO

0

6

6

6

6

GMP

7

7

1

14.30%

7

1

14.30%

GMS

19

19

0

19

0

GR

46

47

1

2.20%

47

1

2.20%

GSR

1788

1788

0

1788

0

HR

7696

7696

0

7706

10

0.10%

HR_ITEMS_PROPERTIES_TL_FK3

INDEX

20

26

6

30.00%

26

6

30.00%

HRI

20275

19847

-428

-2.10%

19847

-429

-2.10%

HXC

41943

42107

165

0.40%

42107

165

0.40%

HXT

1478

1240

-238

-16.10%

1240

-238

-16.10%

IAM

11

11

0

11

0

IBC

132

133

0

133

1

0.80%

IBE

3194

3195

1

0.00%

583

-2611

-81.70%

IBT

1

1

0

1

0

IBU

2

2

0

2

0

IBW

0

5

5

5

5

IEB

7

7

0

7

0

IEC

38

38

0

38

0

IEM

16

20

4

25.00%

20

4

25.00%

IEO

12

13

0

13

0

IES

2773

2779

6

0.20%

2779

5

0.20%

IEU

866

867

2

0.20%

867

2

0.20%

IGC

22

22

0

22

0

IGF

86

89

3

3.50%

89

3

3.50%

IGS

556

567

11

2.00%

567

11

2.00%

INV

125

125

0

125

0

IPA

6

6

0

6

0

IRC

2230

2250

21

0.90%

2250

21

0.90%

IRC_NOTIFICATION_PREFERENCES

TABLE

11

17

6

54.50%

17

6

54.50%

IRC_NOTIFICATION_PREFS_N6

INDEX

5

7

2

40.00%

7

2

40.00%

IRC_NOTIFICATION_PREFS_PK

INDEX

3

5

2

66.70%

5

2

66.70%

IRC_NOTIFICATION_PREFS_N2

INDEX

3

5

2

66.70%

5

2

66.70%

IRC_NOTIFICATION_PREFS_N5

INDEX

3

4

2

66.70%

4

2

66.70%

IRC_NOTIFICATION_PREFS_N4

INDEX

3

4

2

66.70%

4

2

66.70%

IRC_NOTIFICATION_PREFS_N3

INDEX

3

4

1

33.30%

5

2

66.70%

ISC

38069

38090

21

0.10%

38089

19

0.00%

ITG

1

1

0

1

0

JMF

0

3

3

3

3

JTF

121475

110871

-10604

-8.70%

110974

-10501

-8.60%

JTM

48

48

0

48

0

JTS

4

4

0

4

0

LM

129

129

0

129

0

MRP

8074

8074

0

8074

0

MSC

1739

1766

27

1.60%

1766

27

1.60%

MSD

19

21

2

10.50%

22

2

10.50%

MSR

0

0

0

0

0

MST

14

14

0

14

0

MWA

0

0

0

0

0

OAM

0

1

1

1

1

OE

99748

99768

21

0.00%

99768

21

0.00%

OFA

21

21

0

21

0

OFS

26051

26051

0

26051

0

OKC

307861

309621

1760

0.60%

309629

1768

0.60%

OKC_K_REL_OBJS

TABLE

715

874

159

22.20%

874

159

22.20%

OKC_CONTRACT_REL_OBJS_U1

INDEX

288

376

88

30.60%

380

92

31.90%

OKE

30

31

0

31

0

OKI

23080

23110

29

0.10%

23110

29

0.10%

OKL

86

96

10

11.60%

96

10

11.60%

OKS

101140

101382

242

0.20%

101390

250

0.20%

ONT

394

394

0

394

0

OPI

1640

960

-680

-41.50%

960

-680

-41.50%

OTA

3297

3333

36

1.10%

3333

36

1.10%

OU

2

2

0

2

0

OZF

230

250

20

8.70%

250

20

8.70%

PA

421270

416577

-4693

-1.10%

416589

-4681

-1.10%

PAY

66546

66944

398

0.60%

66799

254

0.40%

PER

19868

19835

-33

-0.20%

19835

-33

-0.20%

PER_PEOPLE_F_N58

INDEX

8

49

41

512.50%

47

39

487.50%

PER_PERSON_LIST_CHG_ARCH

TABLE

21

24

3

14.30%

24

3

14.30%

PFT

0

2

2

2

2

PJI

10869

10874

5

0.00%

10874

5

0.00%

PJM

5

5

0

5

0

PMI

2

2

0

2

0

PN

892

899

7

0.80%

900

8

0.90%

PQH

420

433

13

3.10%

432

13

3.10%

PQP

8

9

1

12.50%

9

1

12.50%

PRP

7

8

1

14.30%

8

1

14.30%

PSP

35

36

1

2.90%

36

1

2.90%

PV

32233

32239

6

0.00%

32239

6

0.00%

PV_SEARCH_ATTR_VALUES_U3

INDEX

15

16

2

13.30%

16

2

13.30%

PV_SEARCH_ATTR_VALUES_U2

INDEX

15

16

2

13.30%

16

2

13.30%

QA

23

24

1

4.30%

24

1

4.30%

QB

10

10

0

10

0

QP

37525

37521

-3

0.00%

37521

-4

0.00%

RCM

0

0

0

0

0

RLM

5

5

0

5

0

RMS

62

62

0

62

0

SOE

2904

2904

0

2904

0

SR

1543

1543

0

1543

0

SRT

3402

3402

0

3402

0

SSP

7

7

0

7

0

TP

1

1

0

1

0

TRA

33

33

0

33

0

TS

45

45

0

45

0

VEA

4

4

0

4

0

WIP

421

423

2

0.50%

423

2

0.50%

WMS

1525

1584

59

3.90%

1583

59

3.90%

WMS_DISPATCHED_TASKS_HISTOR_N5

INDEX

3

9

6

200.00%

9

6

200.00%

WMS_PARAMETERS_TL

TABLE

4

8

4

100.00%

8

4

100.00%

WPS

0

0

0

0

0

WSH

35176

37414

2238

6.40%

37416

2240

6.40%

WSH_LOCATIONS

TABLE

910

1910

1000

109.90%

1910

1000

109.90%

WSH_LOCATIONS_N4

INDEX

370

909

538

145.40%

910

540

145.90%

WSH_LOCATIONS_N3

INDEX

184

395

212

115.20%

395

212

115.20%

WSH_LOCATIONS_N2

INDEX

161

351

190

118.00%

350

189

117.40%

WSH_LOCATIONS_U1

INDEX

151

326

175

115.90%

326

176

116.60%

WSH_LOCATIONS_N1

INDEX

155

288

133

85.80%

289

133

85.80%

WSM

10

12

2

20.00%

12

2

20.00%

WWI

4

4

0

4

0

XDO

7

22

15

214.30%

23

16

228.60%

XDO_TEMPLATES_TL_U2

INDEX

1

2

1

100.00%

2

1

100.00%

XDP

31

35

4

12.90%

35

4

12.90%

XNB

0

0

0

0

0

XNP

3650

3656

7

0.20%

3656

7

0.20%

XX

1

1

0

1

0

ZPB

0

15

15

15

15

SYS

78949

142090

63141

79.90%

149558

70906

89.80%

SYSTEM

566

566

0

566

0

Procurement

Object Names

Object Type

R11i Size (MB)

R12 Size (MB) - 3yrs

Delta (MB) - 3yrs

% Growth - 3yrs

R12 Size (MB) - 12yrs

Delta (MB) - 12yrs

% Growth - 12yrs

CHV

6

6

0

6

0

ICX

10621

10684

64

0.60%

10683

62

0.60%

PO

18133

19045

911

5.00%

19021

887

4.90%

PO_LINE_LOCATIONS_ARCHIVE_ALL

TABLE

872

1012

140

16.10%

1012

140

16.10%

PO_LINE_LOCATIONS_ALL

TABLE

944

1056

112

11.90%

1052

108

11.40%

PO_HEADERS_ARCHIVE_ALL

TABLE

556

616

60

10.80%

616

60

10.80%

PON

35

39

4

11.40%

39

4

11.40%

POS

5

56

52

1040.00%

58

54

1080.00%

Total

4417565

4889912

472346

10.70%

5247207

829642

18.70%

Required Post Upgrade Jobs:

Below is a list of of the concurrent programs that form part of the
required post upgrade steps. These concurrent programs are automatcally
submitted during the regular downtime upgrade, so no manual submission
is required. For our ORAPROD 6.5 TB database, upgrading 12 years of
historical financial data and using 30 target processes in the
concurrent manager, these jobs took 13 hours.

Application Name

Concurrent Program Name

User Concurrent Program Name

Elapsed Time (in minutes)

Total Number Of Requests

Advanced Supply Chain Planning

MSCREFMV

Refresh Materialized
Views

.23

2

Application Object Library

FDFCMPA

Compile All
Flexfields

344.25

1

Application Object Library

FDFCMPN

Compile Non-Compiled
Flexfields

232.55

12

Application Object Library

FDFVGN

Flexfield View
Generator

1.18

774

Application Object Library

FNDFIXRN

Fix FND role
names

1.68

1

Application Object Library

FNDSCMPI

Compile
Security

.08

1

Applications DBA

ADDRPOBS

Drop obsolete products
schema

2.3

66

Asia/Pacific Localizations

JAINDFOBS

India - Remove India Localization
contexts from DFFs

.03

1

Collections

IEX_COLLECTORS_TO_RESOURCE

IEX: AR Collectors To Resource
Collectors

.37

1

Contracts Core

OKCARTCRTCTX

Create Clause Text
Index

28.47

1

Contracts Core

OKCCHRCRCTX

Create Contract Header Text
Index

275.5

1

Contracts Core

OKC_XPRT_PUBLISH_RULES

Contract Expert: Activate
Rules

1.63

5

Financial Intelligence

FII_BUDGET_BASE_UPG_C

Update FII_BUDGET_BASE
program

16.22

1

Human Resources

IRC_CREATE_NOTIFICATION

iRecruitment Create
Notification Preferences

1.17

30

Human Resources

PERNMFT

Update Person
Names

731.7

1

Human Resources

PERNMFT_INT

Update Person Names
(Internal)

730.65

1

Human Resources

PER_FND_SESSIONS_CLEANUP

Remove obsolete sessions from
fnd_sessions

.23

1

Incentive Compensation

CNUPGPMTCLWRKER

Upgrade payment commission lines
- Worker

95.6

900

Incentive Compensation

CNUPGPMTCOMMTRXMGR

Upgrade commissions and
transactions for AutoUpgrade or AutoPatch - Manager