Scope:

Internal Meeting date:

Meeting with Aditya

S.No High Level

1 Partitioning Candidates (Table)
2 Partitioning Candidates (Index)
3 _BKP tables

4 Redo Log Switches

5 Debug/Trace enabled concurrent programs
6 Patches

7 Reorg Candidates (Tables, Indexes)

8 Hot Tables (Small)

9 Hot Indexes (Small)

10 Hot Tables (Big)

11 Hot Indexes (Big)

12 ADDM Findings

13 Advisors
14 AWR Warehouse
15 AWR bi-weekly Monitoring report
16 Oracle best practises document for perf tuning
17 Top memory consumers: SGA/PGA

18 Disco

19 Identify Unused Indexes (Seeded & Custom)

20 Spfile/Pfile parameters standard check

21 Defining Purging/Archiving standards
22 Custom Tables
23 Compression
All 4 production streams (PROD, FOGO, KING, PROD12)
Tue,19/May/2015 4:00 P.M IST (1 hour, Board room)
Thu, 21/May/2015 6:00 P.M IST (1 hour, Scottsdale)

Detailed Ownership
- -
- -
List of _BKP tables Chandra
Chandra to come up with the list of current Redo log switches details for all 4 production streams.
We will review in the meeting and take a call accordingly Chandra
Identify the list of all such programs Chandra
With each quarter patches we apply, check for dependent performance related patches Chandra

Get the list of Top:15 objects by Logical Reads

Get the list of Top:15 objects by Physical Reads
Get the list of Top:15 objects by Physical direct path Reads
*please note AWR only give Top:5 objects Durga
Identify small tables that are frequently referred/accessed from buffers. The plan is to review
them and see options to keep them in KEEP pool. Durga
Identify small Indexes that are frequently referred/accessed from buffers. The plan is to review
them and see options to keep them in KEEP pool. Durga
Identify list of most actively accessed big tables. We will take a call accordingly in the meeting to
reorg/purge/archive Durga
Identify list of most actively accessed big Indexes. We will take a call accordingly in the meeting to
reorg/purge/archive Durga
Collect all current ADDM suggestions given by OEM for all 4 production streams
(Gsi Prod, FOGO, KING, EBS12) Nikhil

We would like to schedule various advisors at table level, session level, object levels, etc
Please test the process of scheduling one advisor for each category in AZUL instance Nikhil
Building AWR warehouse for long term repository of AWR data Nikhil
Share top:5 events & high level analysis with Team for all 4 production streams Ravi
Find out what documents Oracle has for perf tuning best practises Ravi
Collect data through standard query Ravi

EUL schema: Ravi

After discussion with Aditya, the candidates for Index monitoring tables/schemas will be
provided. But, by our internal meeting date, please have the procedure to monitor unused
indexes thouroughly tested in non-production instance(s) Shekhar
Shekar to check for Meta link note and check those standards with our Ebiz databases.
Any deviations from standard parameters will be discussed in the meeting Shekhar
Nothing in place now. But Sridhar to keep this in the list of his ongoing activities. Sridhar
XX: Have inventory of these tables. Schema, Table name, rows, owner, purge schedules Sridhar
 Environment
We can't do partitioning at this point in 4 production streams.
We can't do partitioning at this point in 4 production streams.
For All 4 production streams

For All 4 production streams

For All 4 production streams
For All 4 production streams

For All 4 production streams

For All 4 production streams

For All 4 production streams

For All 4 production streams

For All 4 production streams

For All 4 production streams

We will schedule this for All production streams, but for our internal
meeting first test this on AZUL instance. Make sure you document
the process.
We intially start with non-prod instances, then move to prod
For All 4 production streams
For All 4 production streams
For All 4 production streams

GSI Prod

AZUL

For All 4 production streams

For All 4 production streams
It can be a little project of its own. Abhishek?
 Comments
We have partitioning licenses for only obiee, soa tables at this point
We have partitioning licenses for only obiee, soa tables at this point

Recommendation: 4 per hour

Check for Meta link document

We may not automate Rerog jobs, it's mostly manual stuff

What is a small table?
Any table that is less than 2% of total buffers

What is a big table?

Any table that is more than or equal to 2% of total buffers

jobs, custom tables, custom views. Inside discover, find out schedules that are not needed!
Again its kind of mini project. Since 2 years hardly any action. Ownership: Dave, Paulo, backup:Narayana

High priority on FOGO

 Identify small tables that are frequently referred/accessed from
buffers. The plan is to review them and see options to keep them in
Hot Tables (Small) KEEP pool.

Identify small Indexes that are frequently referred/accessed from

buffers. The plan is to review them and see options to keep them in
Hot Indexes (Small) KEEP pool.

Identify list of most actively accessed big tables. We will take a call
Hot Tables (Big) accordingly in the meeting to reorg/purge/archive

Identify list of most actively accessed big Indexes. We will take a call
Hot Indexes (Big) accordingly in the meeting to reorg/purge/archive

Collect all current ADDM suggestions given by OEM for all 4

production streams
ADDM Findings (Gsi Prod, FOGO, KING, EBS12)

We would like to schedule various advisors at table level, session

level, object levels, etc
Please test the process of scheduling one advisor for each category
Advisors in AZUL instance
AWR Warehouse Building AWR warehouse for long term repository of AWR data
S.No High Level
1 ADDM Findings
2 Advisors

3 Reorg Candidates (Tables)

4 Reorg Candidates (Indexes)

5 Hot Tables (Small)
6 Hot Indexes (Small)
7 Hot Tables (Big)
8 Hot Indexes (Big)
9 Partitioning Candidates (Table)
10 Partitioning Candidates (Index)
11 Identify Unused Indexes (Seeded & Custom)
12 Identify _BKP table and remove them
13 AWR bi-weekly Monitoring for Prod databases
14 Defining Purging/Archiving standards
15 Redo Log Switches
16 Spfile/Pfile parameters standard check
17 Debug/Trace enabled concurrent programs
18 Oracle best practises document for perf tuning
19 AWR Warehouse
20 Regular reorg jobs (Manual stuff)
21 Top memory consumers: SGA/PGA
22 Custom Tables
23 Disco
24 Patches

Followup: 21/May 6:00 P.M IST

Lead for each stream
 Detailed Ownership
Automatic Database Diagnostic Monitor inputs from OEM Nikhil
Monitoring activity through advisors. For example: SQL, Sessions, objects, etc Nikhil

Identify ALL tables with size > 1 GB

Query: select table_name,round((blocks*8),2)/1024/1024 "size (GB)" ,
round((num_rows*avg_row_len/1024),2)/1024/1024 "actual_data (GB)",
(round((blocks*8),2) - round((num_rows*avg_row_len/1024),2))/1024/1024
"wasted_space (GB)",
(((round((blocks*8),2) -
round((num_rows*avg_row_len/1024),2))/1024/1024)/(round((blocks*8),2)/1024/1024))*100
"Wasted_Per"
from dba_tables
where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
AND round((blocks*8),2)/1024/1024 >= 1
order by 5 desc Durga

Durga
Identify logical reads candidates & consider options to keep in KEEP pool Durga
Identify logical reads candidates & consider options to keep in KEEP pool Durga
Can't keep them in memory. Purging/Partitioning/Reorg Durga
Can't keep them in memory. Purging/Partitioning/Reorg Durga
All new candidates will be identified Shekhar
All new candidates will be identified Shekhar
For important application schemas & Tables with heavy write operations Shekhar
Chandra
Share top:5 events & analysis with team for all 4 production streams Ravi
Nothing in place now. Sridhar
Recommendation: 4 per hour Chandra
Need to look from both Oracle & Ebiz way! Shekhar
Profile options, concurrent programs, etc … Chandra
Find out what documents Oracle has for perf tuning best practises Ravi
Nikhil
Top:10 objects . Setup a meeting to review the collected data & Schedule in prod Shekhar

XX: Have inventory of these tables. Schema, Table name, rows, owner, purge schedules
EUL schema:
 Frequency Environment
Monthly All production streams
Monthly All production streams Top:25 heavily used objects

Top:10 Logical Reads

Top:10 Physical Reads
Top:10 Physical reads direct path
Monthly *AWR only gives Top:5 objects

Top:10 Logical Reads

Top:10 Physical Reads
Top:10 Physical reads direct path
Monthly *AWR only gives Top:5 objects
Quarterly Have the data collected for Production streams
Quarterly Have the data collected for Production streams
Half-yearly Have the data collected for Production streams
Quarterly Have the data collected for Production streams
Quarterly obiee, soa
Quarterly
We can't do it at this point in 4 production streams.
Monthly
Monthly
Monthly Top consumers by Disk, CPU, I/O
Quarterly
Monthly
Once/Yearly Standard metalink note. Everytime we need to review.
Quarterly
Once/Yearly
Once
Quarterly

High priority on FOGO

jobs, custom tables, custom views. Inside discover, find out schedules that are not nee
CPU Path: AIX specific patches. Need metalink/ref doc from Aditya
 _small_table_threshold parameter is set to about 2% of the size of the buffer
cache.
The buffer cache size in GSI PROD is: SELECT name,block_size,buffers FROM
v$buffer_pool;
The output for above query is: 9974624. 2% of this number is: 199493 blocks.
SELECT name,block_size,buffers,
buffers*2/100,((buffers*2/100)*block_size)/1024/1024/1024 "Small
Threshold(GB)" FROM v$buffer_pool;
Small Threshold size in GSI prod is: 1.52GB Row movement??

Save it on Techhub. AWR tuning on weekends

Init.ora on linux is straight forward. little tricky AIX! 2 things in AIX at env level.

A little project led by Abhishek

hedules that are not needed! Again its kind of mini project. Since 2 years hardly any action. Ownership: Dave, Paulo, backup:Narayana
 DBMS Redefinition, with in OEM

ve, Paulo, backup:Narayana

Data Collected on: 27/May/2015 2:00 P.M

Instance Owner Segment_name Object_Type Category

FOGO JTF JTF_TASKS_B TABLE physical reads
FOGO XXDBD XXDBD_BR_CALL_ENTRY TABLE physical reads
FOGO CS CS_INCIDENTS_ALL_B TABLE physical reads
FOGO XXDBD XXDBD_BR_CS_GEO_EVENTS TABLE physical reads
FOGO JTF JTF_NOTES_TL TABLE physical reads
FOGO CSF CSF_DEBRIEF_LINES TABLE physical reads
FOGO JTF JTF_TASKS_TL TABLE physical reads
FOGO AR HZ_PARTIES TABLE physical reads
FOGO JTF JTF_TASK_ASSIGNMENTS TABLE physical reads
FOGO APPLSYS WF_NOTIFICATIONS TABLE physical reads

FOGO JTF JTF_TASKS_B TABLE Physical direct reads

FOGO XXDBD XXDBD_BR_CALL_ENTRY TABLE Physical direct reads
FOGO XXDBD XXDBD_BR_CS_GEO_EVENTS TABLE Physical direct reads
FOGO CS CS_INCIDENTS_ALL_B TABLE Physical direct reads
FOGO APPLSYS WF_ITEM_ATTRIBUTE_VALUES TABLE Physical direct reads
FOGO INV MTL_SYSTEM_ITEMS_B TABLE Physical direct reads
FOGO CS CS_INCIDENTS_AUDIT_B TABLE Physical direct reads
FOGO CSF CSF_DEBRIEF_HEADERS TABLE Physical Reads
FOGO APPLSYS WF_NOTIFICATIONS TABLE Physical direct reads
FOGO CSF CSF_DEBRIEF_LINES TABLE Physical direct reads

FOGO CS CS_INCIDENTS_ALL_B TABLE Logical Reads

FOGO CS CS_INCIDENTS_U1 INDEX Logical Reads
FOGO APPLSYS FND_LOOKUP_VALUES TABLE Logical Reads
FOGO JTF JTF_TASKS_B TABLE Logical Reads
FOGO APPLSYS FND_LOOKUP_VALUES_U1 INDEX Logical Reads
FOGO JTF JTF_TASKS_B_U1 INDEX Logical Reads
FOGO AR HZ_PARTIES_U2 INDEX Logical Reads
FOGO XXDBD XXDBD_BR_CALL_ENTRY TABLE Logical Reads
FOGO XXDBD XXDBD_BR_CS_BATCHCALL_HEADER TABLE Logical Reads
FOGO APPLSYS FND_PROFILE_OPTION_VALUES TABLE Logical Reads
 Object Size Actual Size Wasted Size
Total_reads (GB) (GB) (GB) %Reclaimable PCTFREE PCTUSED
2119660175 1.755 1.409 0.346 19.7 10
323791982 0.787 0.652 0.135 17.18 10
238052726 4.045 2.39 1.655 40.91 10
237140761 1.508 1.219 0.29 19.21 10
60405876 21.56 19.018 2.542 11.79 10
49929817 6.778 5.117 1.661 24.51 10
42959395 2.113 1.696 0.417 19.73 10
24224324 0.601 0.581 0.021 3.43 10
20856368 1.995 1.385 0.61 30.59 10
14634203 1.945 0.002 1.942 99.88 10

6378207870 1.76 1.41 20 10 NULL

1612943990 0.78 0.65 15.6 10 NULL
590010626 1.5 1.18 21 10 NULL
233746151 3.98 2.35 41 10 NULL
225423420 66.2 11.4 83 10 NULL
206041223 1.18 0.79 38.7 10 NULL
63420157 161 110 32 10 NULL
Total Reads 1.254 0.989 0.265 21.1 10
41290398 1.94 0.004 99.91 10 NULL
31677687 6.78 5.12 24 10 NULL

309349624688 3.98 2.35 41 10 NULL

80173480000 (del_lf_rows_len/lf_rows_len) * 100 = 0
55959742960 0.11 10 NULL
18872010704 1.76 1.41 20 10 NULL
16517846736 (del_lf_rows_len/lf_rows_len) * 100 = 0.00001
14360851776 (del_lf_rows_len/lf_rows_len) * 100 = 0
12234860240 (del_lf_rows_len/lf_rows_len) * 100 = 0
11473437504 0.78 0.65 15.6 10 NULL
11374478320 0.032 0.027 16 10 NULL
6052994496 0.005 0.003 29 20 NULl
 No.columns count(*) Last Analyzed
102 6200797 5-Jun-15
43 1288880 31-May-15
280 5402397 6-Jun-15
42 7309640 6-Jun-15
11 17131403 22-May-15
75 32900200 21-Mar-15
11 6195890 8-Jun-15
134 2180347 20-May-15
61 11991653 26-May-15
86 9453 8-Jun-15

102 6149121 26-May-15

43 1280191 27-May-15
42 7095877 26-May-15
280 5361957 26-May-15
8 176919317 27-May-15
337 1700850 19-Feb-15
432 127625543 27-May-15
33 7812233 29-Apr-15
86 12533 27-May-15
75 32900200 21-Mar-15

280 5361957 26-May-15

en) * 100 = 0
35 922311 21-Mar-15
102 6149121 26-May-15
* 100 = 0.00001
en) * 100 = 0
en) * 100 = 0
43 1280191 27-May-15
47 86713 27-May-15
12 64954 19-May-15
Data Collected on: 28/May/2015 2:00 P.M

Instance Owner Segment_name Type Category Total_reads

Tables By physical reads:
FOGO JTF JTF_TASKS_B TABLE physical reads 2119660175
FOGO XXDBD XXDBD_BR_CALL_ENTRY TABLE physical reads 323791982
FOGO CS CS_INCIDENTS_ALL_B TABLE physical reads 238052726
FOGO XXDBD XXDBD_BR_CS_GEO_EVENTS TABLE physical reads 237338466
FOGO JTF JTF_NOTES_TL TABLE physical reads 60405876
FOGO CSF CSF_DEBRIEF_LINES TABLE physical reads 49929817
FOGO JTF JTF_TASKS_TL TABLE physical reads 42959395
FOGO AR HZ_PARTIES TABLE physical reads 24224324
FOGO JTF JTF_TASK_ASSIGNMENTS TABLE physical reads 20856368
FOGO APPLSYS WF_NOTIFICATIONS TABLE physical reads 14634203
FOGO XXDBD XXDBD_DEBUG_TABLE TABLE physical reads 13502955
FOGO APPLSYS WF_ITEM_ATTRIBUTE_VALUES TABLE physical reads 8902027
FOGO INV MTL_SYSTEM_ITEMS_B TABLE physical reads 8729141
FOGO XXDBD XXDBD_CS_TASK_AGG TABLE physical reads 8522489
FOGO APPLSYS WF_ITEM_ACTIVITY_STATUSES TABLE physical reads 6296648

Tables By physical reads direct:

FOGO JTF JTF_TASKS_B TABLE physical reads direct 8492687117
FOGO XXDBD XXDBD_BR_CALL_ENTRY TABLE physical reads direct 1936637276
FOGO XXDBD XXDBD_BR_CS_GEO_EVENTS TABLE physical reads direct 1011319853
FOGO CS CS_INCIDENTS_ALL_B TABLE physical reads direct 477258658
FOGO APPLSYS WF_ITEM_ATTRIBUTE_VALUES TABLE physical reads direct 303454542
FOGO INV MTL_SYSTEM_ITEMS_B TABLE physical reads direct 293361293
FOGO CSF CSF_DEBRIEF_HEADERS TABLE physical reads direct 74767186
FOGO CS CS_INCIDENTS_AUDIT_B TABLE physical reads direct 63420157
FOGO APPLSYS WF_NOTIFICATIONS TABLE physical reads direct 57092896
FOGO AR HZ_PARTIES TABLE physical reads direct 48697832
FOGO CSF CSF_DEBRIEF_LINES TABLE physical reads direct 43716489
FOGO XXDBD XXDBD_DEBUG_TABLE TABLE physical reads direct 38208327
FOGO APPS XXDBD_ML_401K_DEBUG TABLE physical reads direct 33044472
FOGO CS CS_INCIDENTS_ALL_TL TABLE physical reads direct 30653977
FOGO JTF JTF_TASKS_TL TABLE physical reads direct 30392073

Tables By logical reads:

FOGO CS CS_INCIDENTS_ALL_B TABLE logical reads 429852557776
FOGO XXDBD XXDBD_CS_TASK_AGG TABLE logical reads 36940822736
FOGO JTF JTF_TASKS_B TABLE logical reads 26240750336
FOGO XXDBD XXDBD_CS_SR_AGG TABLE logical reads 23853693744
FOGO XXDBD XXDBD_BR_CALL_ENTRY TABLE logical reads 17540811968
FOGO XXDBD XXDBD_BR_CS_BATCHCALL_HEADER TABLE logical reads 16126641888
FOGO APPLSYS FND_PROFILE_OPTION_VALUES TABLE logical reads 8380108000
FOGO AR HZ_PARTIES TABLE logical reads 8256995072
FOGO JTF JTF_CAL_EXCEPTIONS_B TABLE logical reads 7924678496
FOGO XXDBD XXDBD_BR_CS_CAIXA_ATUAL_RET TABLE logical reads 6111442432
FOGO APPLSYS FND_USER TABLE logical reads 5686335488
FOGO JTF JTF_TASK_TYPES_TL TABLE logical reads 5507577104
FOGO JTF JTF_TASK_ASSIGNMENTS TABLE logical reads 3452747824
FOGO JTF JTF_RS_GROUPS_TL TABLE logical reads 2926143840

Tables By physical writes

FOGO JTF JTF_TASKS_TL TABLE physical writes 31421433
FOGO CS CS_INCIDENTS_ALL_B TABLE physical writes 3431662
FOGO XXDBD XXDBD_DEBUG_TABLE TABLE physical writes 3151025
FOGO APPLSYS WF_DEFERRED TABLE physical writes 1916717
FOGO XXDBD XXDBD_BR_CS_GEO_EVENTS TABLE physical writes 1645893
FOGO XXDBD XXDBD_CS_AUTOCLOSE_SR TABLE physical writes 917176
FOGO XXDBD XXDBD_BR_CALL_RESP TABLE physical writes 822638
FOGO XXDBD XXDBD_BR_TASK_OUT TABLE physical writes 801456
FOGO APPLSYS WF_ERROR TABLE physical writes 797021
FOGO XXDBD XXDBD_BR_CAPTURED_EVENTS TABLE physical writes 773317
FOGO XXDBD XXDBD_LOGON_EVENTS TABLE physical writes 724289
FOGO JTF JTF_TASKS_B TABLE physical writes 708730
FOGO XXDBD XXDBD_CS_TASK_AGG TABLE physical writes 674096
FOGO APPLSYS FND_CONCURRENT_REQUESTS TABLE physical writes 665952
FOGO APPLSYS WF_ITEM_ATTRIBUTE_VALUES TABLE physical writes 653165
Allocated Actual Wasted % No of Rows
(GB) (GB) (GB) Reclaimable PCTFREE PCTUSED columns count Last Analyzed

1.755 1.409 0.346 19.7 10 102 6200797 5-Jun-15

0.787 0.652 0.135 17.18 10 43 1288880 31-May-15
4.045 2.39 1.655 40.91 10 280 5402397 6-Jun-15
1.508 1.219 0.29 19.21 10 42 7309640 6-Jun-15
21.56 19.018 2.542 11.79 10 11 17131403 22-May-15
6.778 5.117 1.661 24.51 10 75 32900200 21-Mar-15
2.113 1.696 0.417 19.73 10 11 6195890 8-Jun-15
0.601 0.581 0.021 3.43 10 134 2180347 20-May-15
1.995 1.385 0.61 30.59 10 61 11991653 26-May-15
1.945 0.002 1.942 99.88 10 86 9453 8-Jun-15
102.111 85.975 16.135 15.8 10 6 831667897 6-Jun-15
66.169 11.216 54.952 83.05 10 8 174541767 8-Jun-15
1.18 0.795 0.385 32.61 10 337 1700426 21-Mar-15
2.772 2.4 0.371 13.4 10 29 6180580 4-Jun-15
4.743 0.638 4.105 86.55 10 18 8782720 7-Jun-15

1.755 1.409 0.346 19.7 10 102 6200797 5-Jun-15

0.787 0.652 0.135 17.18 10 43 1288880 31-May-15
1.508 1.219 0.29 19.21 10 42 7309640 6-Jun-15
4.045 2.39 1.655 40.91 10 280 5402397 6-Jun-15
66.169 11.216 54.952 83.05 10 8 174541767 8-Jun-15
1.18 0.795 0.385 32.61 10 337 1700426 21-Mar-15
1.254 1.017 0.237 18.93 10 33 7968207 4-Jun-15
161.327 109.114 52.213 32.36 10 432 127625543 27-May-15
1.945 0.002 1.942 99.88 10 86 9453 8-Jun-15
0.601 0.581 0.021 3.43 10 134 2180347 20-May-15
6.778 5.117 1.661 24.51 10 75 32900200 21-Mar-15
102.111 85.975 16.135 15.8 10 6 831667897 6-Jun-15
4.663 3.15 1.514 32.46 10 8 602727 2-Jun-15
0.663 0.514 0.149 22.42 10 15 5362483 28-May-15
2.113 1.696 0.417 19.73 10 11 6195890 8-Jun-15

4.045 2.39 1.655 40.91 10 280 5402397 6-Jun-15

2.772 2.4 0.371 13.4 10 29 6180580 4-Jun-15
1.755 1.409 0.346 19.7 10 102 6200797 5-Jun-15
1.516 1.318 0.198 13.08 10 32 5401433 4-Jun-15
0.787 0.652 0.135 17.18 10 43 1288880 31-May-15
0.033 0.027 0.006 17.17 10 47 86777 2-Jun-15
0.005 0.003 0.002 30.74 20 12 64840 6-Jun-15
 0.601 0.581 0.021 3.43 10 134 2180347 20-May-15
0.004 0.002 0.001 36.27 10 27 34003 16-May-15
0.054 0.045 0.009 15.88 10 26 250367 2-Jun-15
0.01 0.008 0.002 16.36 5 27 27753 6-Jun-15
0 0 0 76.99 10 11 464 21-Mar-15
1.995 1.385 0.61 30.59 10 61 11991653 26-May-15
0 0 0 10.57 10 11 985 21-Mar-15

2.113 1.696 0.417 19.73 10 11 6195890 8-Jun-15

4.045 2.39 1.655 40.91 10 280 5402397 6-Jun-15
102.111 85.975 16.135 15.8 10 6 831667897 6-Jun-15
0.879 0 0.879 100 10 29 3 8-Jun-15
1.508 1.219 0.29 19.21 10 42 7309640 6-Jun-15
0.275 0.194 0.081 29.37 10 8 5220093 7-Jun-15
3.622 1.138 2.484 68.58 10 36 408867 4-Jun-15
0.828 0.164 0.665 80.26 10 40 457263 6-Jun-15
0.093 0 0.093 100 10 29 3 8-Jun-15
0.517 0.427 0.091 17.51 10 22 3579493 6-Jun-15
10.162 8.386 1.776 17.48 10 5 100047677 14-May-15
1.755 1.409 0.346 19.7 10 102 6200797 5-Jun-15
2.772 2.4 0.371 13.4 10 29 6180580 4-Jun-15
0.022 0.012 0.01 44.58 20 127 27637 8-Jun-15
66.169 11.216 54.952 83.05 10 8 174541767 8-Jun-15
Data Collected on: 28/May/2015 2:00 P.M

Instance Owner Segment_name Type Category Total_reads

Indexes By physical reads:
FOGO APPLSYS WF_ITEMS_N3 INDEX physical reads 8314817
FOGO APPLSYS WF_ITEM_ACTIVITY_STATUSES_N1 INDEX physical reads 7579525
FOGO APPLSYS WF_ITEM_ACTIVITY_STATUSES_N2 INDEX physical reads 4507035
FOGO APPLSYS WF_NOTIFICATIONS_HOT00 INDEX physical reads 4443809
FOGO APPLSYS WF_NOTIFICATIONS_N5 INDEX physical reads 4255264
FOGO JTF XXDBD_JTF_TASK_AUDITS_B_N1 INDEX physical reads 4223262
FOGO JTF XXDBD_JTF_TASK_ASSIGNMENTS_N1 INDEX physical reads 1901260
FOGO JTF JTF_NOTES_TL_U1 INDEX physical reads 1278525
FOGO APPLSYS WF_ITEM_ACTIVITY_STATUSES_N4 INDEX physical reads 1245115
FOGO APPLSYS WF_ITEM_ACTIVITY_STATUSES_N3 INDEX physical reads 1160943
FOGO APPLSYS WF_ITEMS_PK INDEX physical reads 1097294
FOGO APPLSYS WF_ITEMS_U1 INDEX physical reads 1048584
FOGO JTF XXJTF_TASKS_B_N1 INDEX physical reads 1025466
FOGO INV MTL_ITEM_CATEGORIES_U1 INDEX physical reads 1014647
FOGO JTF JTF_TASK_AUDITS_B_U1 INDEX physical reads 1012152

Indexes By physical reads direct:

FOGO APPLSYS WF_ITEM_ACTIVITY_STATUSES_N1 INDEX physical reads direct 26051055
FOGO APPLSYS WF_NOTIFICATIONS_ATTR_PK INDEX physical reads direct 8510450
FOGO APPLSYS WF_ITEM_ACTIVITY_STATUSES_N4 INDEX physical reads direct 4150373
FOGO APPLSYS WF_ITEM_ACTIVITY_STATUSES_N3 INDEX physical reads direct 3872363
FOGO APPLSYS WF_ITEMS_N2 INDEX physical reads direct 3576524
FOGO APPLSYS WF_ITEMS_N4 INDEX physical reads direct 3538922
FOGO APPLSYS WF_ITEMS_N3 INDEX physical reads direct 3442682
FOGO APPLSYS WF_ITEMS_PK INDEX physical reads direct 3416930
FOGO APPLSYS WF_ITEMS_U1 INDEX physical reads direct 3394132
FOGO JTF JTF_TASKS_TL_N1 INDEX physical reads direct 3061135
FOGO APPLSYS WF_ITEM_ATTRIBUTE_VALUES_PK INDEX physical reads direct 1866418
FOGO APPLSYS WF_ITEM_ACTIVITY_STATUSES_PK INDEX physical reads direct 1768470
FOGO JTF JTF_TASKS_B_N14 INDEX physical reads direct 1130893
FOGO JTF JTF_TASKS_B_N10 INDEX physical reads direct 785338
FOGO JTF JTF_TASK_ASSIGNMENTS_N1 INDEX physical reads direct 267023

Indexes By logical reads:

FOGO CS CS_INCIDENTS_U1 INDEX logical reads 107976096032
FOGO APPLSYS FND_LOOKUP_VALUES_U1 INDEX logical reads 28823096336
FOGO JTF JTF_TASKS_B_U1 INDEX logical reads 20448941648
FOGO AR HZ_PARTIES_U2 INDEX logical reads 17429155552
FOGO JTF JTF_TASKS_TL_U1 INDEX logical reads 9113682288
FOGO XXDBD XXDBD_CS_TASK_AGG_N1 INDEX logical reads 9033980544
FOGO APPLSYS FND_USER_U1 INDEX logical reads 7269141168
FOGO JTF JTF_TASK_STATUSES_TL_U1 INDEX logical reads 6067244192
FOGO JTF JTF_TASK_TYPES_B_U1 INDEX logical reads 5864312128
FOGO JTF JTF_TASK_STATUSES_B_U1 INDEX logical reads 5641006448
FOGO JTF JTF_RS_RESOURCE_EXTNS_U1 INDEX logical reads 5415029072
FOGO JTF JTF_TASK_TYPES_TL_U1 INDEX logical reads 5260136848
FOGO JTF JTF_OBJECTS_TL_U1 INDEX logical reads 5096704416
FOGO XXDBD IDX$$_25D10001 INDEX logical reads 4521624880
FOGO JTF JTF_RS_GROUPS_TL_U1 INDEX logical reads 4225050000

Indexes By physical writes:

FOGO APPLSYS WF_ITEM_ATTRIBUTE_VALUES_PK INDEX physical writes 13481779
FOGO AR DR$HZ_STAGE_PARTIES_T1$X INDEX physical writes 6532572
FOGO XXDBD XXDBD_BR_CS_GEO_EVENTS_N6 INDEX physical writes 2419721
FOGO APPLSYS WF_ITEM_ACTIVITY_STATUSES_N1 INDEX physical writes 2252673
FOGO APPLSYS WF_ITEM_ACTIVITY_STATUSES_PK INDEX physical writes 1954669
FOGO XXDBD XXDBD_BR_CS_GEO_EVENTS_N3 INDEX physical writes 1543506
FOGO APPLSYS WF_DEFERRED_N1 INDEX physical writes 1523191
FOGO XXDBD XXDBD_BR_CS_BTC_HEADER_OUT_U01 INDEX physical writes 1445361
FOGO XXDBD XXDBD_BR_CS_GEO_EVENTS_N4 INDEX physical writes 1422471
FOGO XXDBD XXDBD_BR_CS_BTC_HEADER_IN_U01 INDEX physical writes 1334675
FOGO APPLSYS WF_ITEMS_U1 INDEX physical writes 983257
FOGO APPLSYS WF_ITEMS_PK INDEX physical writes 966732
FOGO XXDBD XXDBD_BR_CS_GEO_EVENTS_N1 INDEX physical writes 894923
FOGO XXDBD XXDBD_JTF_TASK_AUDITS_B_N1 INDEX physical writes 845240
FOGO CS CS_INCIDENTS_AUDIT_TL_N1 INDEX physical writes 835428
Allocated
(MB)

1.00299
5.3493
0.44727
0.50293
0.42969
2.87207
0.41895
0.52057
3.03741
2.83154
1.38202
1.50201
0.22559
0.37762
1.32477

5.3493
2.12976
3.03741
2.83154
1.31299
1.19031
1.00299
1.38202
1.50201
0.62976
90.06476
4.92822
1.39142
0.74286
1.22894

0.14081
0.04675
0.17261
0.0705
0.14099
0.12036
0.0014
 0.00012
0.00012
0.00012
0.00299
0.00012
0.00024
0.00977
0.00012

90.06476
11.79395
0.58179
5.3493
4.92822
0.22925
0.11719
0.146
0.17419
0.37012
1.50201
1.38202
0.19788
0.90216
6.39075
Data Collected on: 28/May/2015 2:00 P.M
Instance Owner Segment_name Type Logical Reads Physical Reads
FOGO XXDBD XXDBD_BR_CS_BATCHCALL_HEADER TABLE 16138727584 0
FOGO APPLSYS FND_PROFILE_OPTION_VALUES TABLE 8383272240 0
FOGO JTF JTF_CAL_EXCEPTIONS_B TABLE 7925013600 0
FOGO APPLSYS FND_USER TABLE 5686370384 0
FOGO JTF JTF_TASK_TYPES_TL TABLE 5513990384 0
FOGO JTF JTF_RS_GROUPS_TL TABLE 2926167024 0
FOGO JTF JTF_RS_GROUPS_B TABLE 1963308176 0
FOGO XXDBD XXDBD_CS_MFS_MSG TABLE 1816356720 0
FOGO XXDBD XXDBD_BR_GET_EVENTS TABLE 1573817856 31
FOGO JTF JTF_RS_RESOURCE_EXTNS_TL TABLE 997621136 63
FOGO XXDBD XXDBD_SERVICE_BOM TABLE 854593808 0
FOGO CSI CSI_SYSTEMS_TL TABLE 798988032 0
FOGO JTF JTF_RS_ROLE_RELATIONS TABLE 794858352 2
FOGO APPLSYS FND_FLEX_VALUES TABLE 751366624 0
FOGO JTF JTF_OBJECTS_B TABLE 735055392 17
FOGO APPLSYS FND_RESPONSIBILITY_TL TABLE 607656688 0
FOGO APPLSYS FND_CONCURRENT_REQUESTS TABLE 569637216 148715
FOGO JTF JTF_TASK_STATUSES_TL TABLE 546388416 0
FOGO JTF JTF_RS_GROUP_MEMBERS TABLE 387151168 136
FOGO APPLSYS FND_PROFILE_OPTIONS TABLE 361515808 0
FOGO HR HR_ORGANIZATION_INFORMATION TABLE 266016768 0
FOGO APPLSYS FND_TERRITORIES TABLE 213714240 0
FOGO APPLSYS FND_FLEX_VALUES_TL TABLE 151225360 0
FOGO AR HZ_STYLE_FORMATS_B TABLE 133872368 0
FOGO APPLSYS FND_APPLICATION TABLE 130333120 0
FOGO XXDBD XXDBD_CS_MFS_INT_LBR_TIME_TAB TABLE 105537680 1
FOGO OKC OKC_K_HEADERS_B TABLE 86848672 0
FOGO HR PER_ADDRESSES TABLE 84513808 1472
FOGO JTF JTF_TASK_STATUSES_B TABLE 81494112 0
FOGO HR HR_ALL_ORGANIZATION_UNITS TABLE 78508976 0
FOGO JTF JTF_HOOKS_DATA TABLE 75707136 0
FOGO APPLSYS FND_CONCURRENT_QUEUES TABLE 73666288 0
FOGO APPLSYS FND_CONCURRENT_PROCESSES TABLE 72836432 228
FOGO APPLSYS FND_RESPONSIBILITY TABLE 67372544 1
FOGO HR HR_ALL_ORGANIZATION_UNITS_TL TABLE 55332112 0
FOGO OKS OKS_COVERAGE_TIMEZONES TABLE 50919632 0
FOGO APPLSYS WF_PROCESS_ACTIVITIES TABLE 44387840 0
FOGO APPLSYS FND_FILE_TEMP TABLE 44041728 3
FOGO APPLSYS FND_FLEX_VALUE_SETS TABLE 40307632 0
FOGO INV MTL_CATEGORIES_B TABLE 34832576 0
FOGO INV MTL_PARAMETERS TABLE 34375200 0
FOGO APPLSYS FND_PRODUCT_GROUPS TABLE 30540576 0
FOGO OKS OKS_COVERAGE_TIMES TABLE 30165680 0
FOGO OKC OKC_K_LINES_B TABLE 28568160 0
FOGO APPLSYS FND_LANGUAGES TABLE 26820784 0
FOGO XXDBD XXDBD_MACHINE_CLASSES TABLE 25706544 0
FOGO XXDBD XXDBD_BR_REFUSAL_REQUEST TABLE 25611808 0
FOGO HR HR_LOCATIONS_ALL TABLE 23745312 1
FOGO XXDBD XXDBD_BR_DECAL_CTIS_OUT TABLE 23241632 1
FOGO HR PER_PHONES TABLE 22803984 3
Total Reads Object Size (MB)
16138727584 33.671875
8383272240 5
7925013600 3.96875
5686370384 10
5513990384 0.125
2926167024 0.125
1963308176 0.25
1816356720 18.453125
1573817887 48.171875
997621199 0.609375
854593808 33.40625
798988032 0.8515625
794858354 2.984375
751366624 8
735055409 0.1796875
607656688 2
569785931 22.875
546388416 0.0546875
387151304 1.96875
361515808 3
266016768 0.6875
213714240 0.0390625
151225360 16
133872368 0.03125
130333120 0.0390625
105537681 13.34375
86848672 0.2421875
84515280 25.8125
81494112 0.0390625
78508976 0.4375
75707136 0.0625
73666288 0.0390625
72836660 1.9375
67372545 0.8125
55332112 0.4375
50919632 0.3046875
44387840 4.984375
44041731 2.9453125
40307632 3
34832576 8.5
34375200 0.0859375
30540576 0.03125
30165680 0.7421875
28568160 2.984375
26820784 0.03125
25706544 0.03125
25611808 1.90625
23745313 3.984375
23241633 28.484375
22803987 8.984375
Data Collected on: 28/May/2015 2:00 P.M
Instance Owner Segment_name Type Number of Rows Object Size (MB)
FOGO APPLSYS FND_CONCURRENT_REQUESTS TABLE 29897 22.875
FOGO APPLSYS FND_CONCURRENT_PROGRAMS TABLE 10029 2
FOGO APPLSYS FND_APPLICATION_TL TABLE 2186 0.1875
FOGO APPLSYS FND_CONCURRENT_PROGRAMS_TL TABLE 64649 9
FOGO APPLSYS FND_APPLICATION TABLE 286 0.0390625
FOGO CS CS_TRANSACTION_TYPES_TL TABLE 401 0.0625
FOGO CS CS_INCIDENT_TYPES_B TABLE 27 0.03125
FOGO CS CS_TXN_BILLING_TYPES TABLE 217 0.0390625
FOGO CS CS_TRANSACTION_TYPES_B TABLE 101 0.0390625
FOGO CS CS_BUSINESS_PROCESSES TABLE 17 0.03125
FOGO CSI CSI_IB_TXN_TYPES TABLE 52 0.03125
FOGO CSI CSI_SOURCE_IB_TYPES TABLE 142 0.03125
FOGO JTF JTF_TASK_STATUSES_TL TABLE 323 0.0546875
FOGO JTF JTF_TASK_STATUSES_B TABLE 75 0.0390625
FOGO JTF JTF_OBJECTS_TL TABLE 3794 0.5
FOGO JTF JTF_OBJECTS_B TABLE 471 0.1796875
FOGO XXDBD XXDBD_CS_USER_BRANCH_AGG TABLE 17249 1.90625
FOGO APPLSYS FND_USER TABLE 27753 10
FOGO JTF JTF_TASK_TYPES_TL TABLE 464 0.125
FOGO JTF JTF_TASK_PRIORITIES_TL TABLE 86 0.03125
FOGO OKC OKC_K_LINES_B TABLE 11596 2.984375
FOGO JTF JTF_RS_GROUPS_B TABLE 984 0.25
FOGO XXDBD XXDBD_BR_MASS_OPERATIONS TABLE 147983 25.53125
FOGO OKC OKC_K_HEADERS_B TABLE 571 0.2421875
FOGO JTF JTF_RS_GROUPS_TL TABLE 985 0.125
FOGO OKC OKC_K_ITEMS TABLE 9327 1.9765625
FOGO HR HR_ALL_ORGANIZATION_UNITS TABLE 2533 0.4375
FOGO JTF JTF_RS_RESOURCE_EXTNS TABLE 5172 2.984375
FOGO HR HR_ORGANIZATION_INFORMATION TABLE 4756 0.6875
FOGO AR HZ_CUST_ACCOUNTS TABLE 14328 4
FOGO HR PER_PHONES TABLE 56212 8.984375
FOGO XXDBD XXDBD_BR_CS_BATCHCALL_HEADER TABLE 86777 33.671875
FOGO HR HR_ALL_ORGANIZATION_UNITS_TL TABLE 4967 0.4375
FOGO DISCEUL_UEUL5_BR_RUNS TABLE 26614 1.71875
FOGO DISCEUL_UEUL5_BATCH_REPORTS TABLE 513 0.171875
FOGO JTF JTF_RS_RESOURCE_EXTNS_TL TABLE 5182 0.609375
FOGO CSI CSI_SYSTEMS_TL TABLE 5395 0.8515625
FOGO APPLSYS FND_FLEX_VALUE_SETS TABLE 15122 3
FOGO CS CS_INCIDENT_STATUSES_TL TABLE 113 0.03125
FOGO APPLSYS FND_FLEX_VALUES TABLE 87186 8
FOGO XXDBD XXDBD_OKS_PRICE_LIST TABLE 5376 0.9765625
FOGO CS CS_INCIDENT_TYPES_TL TABLE 69 0.03125
FOGO APPLSYS FND_FLEX_VALUES_TL TABLE 198618 16
FOGO JTF JTF_RS_ROLE_RELATIONS TABLE 26899 2.984375
FOGO JTF JTF_RS_GROUP_MEMBERS TABLE 17376 1.96875
FOGO DISCEUL_UEUL5_EUL_USERS TABLE 14729 1.5390625
FOGO APPLSYS FND_TERRITORIES_TL TABLE 2672 0.3203125
FOGO JTF JTF_TASK_TYPES_B TABLE 145 0.0625
FOGO JTF JTF_RS_ROLES_B TABLE 172 0.046875
FOGO XXDBD XXDBD_BR_CS_EXTERNAL_LOCATION TABLE 3579 0.6171875
Plan Count Total Physical Reads
100055 148714
100027 0
99871 0
99867 0
99369 0
46488 0
44236 0
44168 0
44161 0
44156 0
21748 0
21741 0
10668 0
9971 0
8571 0
7669 17
7651 0
6747 0
5540 0
4675 0
3163 0
2414 0
2175 0
1663 0
1662 0
1571 0
1138 0
1120 0
1106 0
981 0
887 3
880 0
835 0
819 0
813 0
744 60
556 0
548 0
535 0
509 0
503 0
334 0
329 0
313 2
296 136
292 0
279 0
267 0
249 0
249 0
Data Collected on: 28/May/2015 2:00 P.M

Instance Owner Segment_name Type Logical Reads Physical Reads

Total Reads Object Size (MB)
Data Collected on: 28/May/2015 2:00 P.M >= 0.5 GB
Table Size Actual Data Wasted Space
Instance Owner Table Name (GB) (GB) (GB)
FOGO APPLSYS WF_DEFERRED 0.878616 0.000001 0.878615
FOGO APPLSYS WF_COMMENTS 0.58873 0.000694 0.588036
FOGO APPLSYS WF_NOTIFICATIONS 1.944664 0.002603 1.942061
FOGO APPLSYS WF_NOTIFICATION_ATTRIBUTES 5.024658 0.007174 5.017484
FOGO DISCEUL_UEUL5_DOCUMENTS 0.5411 0.001209 0.539891
FOGO APPLSYS WF_ITEMS 0.890648 0.09966 0.790988
FOGO APPLSYS WF_ITEM_ACTIVITY_STATUSES 4.743202 0.622515 4.120687
FOGO XXDBD XXDBD_BR_DEBRIEF_MATERIAL_OUT 0.717339 0.114829 0.602509
FOGO APPLSYS WF_ITEM_ATTRIBUTE_VALUES 66.168617 11.120103 55.048514
FOGO XXDBD XXDBD_BR_TASK_OUT 0.828316 0.166549 0.661767
FOGO XXDBD XXDBD_BR_DEBRIEF_LABOR_OUT 0.603714 0.128282 0.475432
FOGO XXDBD XXDBD_BR_CALL_RESP 3.622017 1.138173 2.483844
FOGO AR HZ_STAGED_PARTIES 0.600418 0.232548 0.367871
FOGO JTF JTF_IH_INTERACTIONS 1.694023 0.929231 0.764792
FOGO AR HZ_RELATIONSHIPS 0.585777 0.343147 0.24263
FOGO CS CS_INCIDENTS_ALL_B 4.044701 2.389903 1.654798
FOGO XXDBD XXDBD_BR_CS_BTC_SYMPTOMS_OUT 0.510727 0.302015 0.208712
FOGO XXDBD XXDBD_BR_CS_BTC_DE_SY_LINE_OUT 0.837624 0.497193 0.340431
FOGO XXDBD XXDBD_BR_CS_BTC_DE_SY_LINES_IN 0.873032 0.524538 0.348494
FOGO ASO ASO_QUOTE_LINE_DETAILS 0.693336 0.418917 0.274419
FOGO BEN BEN_ELIG_PER_OPT_F 3.143776 1.959965 1.183811
FOGO BEN BEN_ELIG_PER_F 2.097916 1.323028 0.774888
FOGO BEN BEN_ENRT_RT 0.889687 0.562297 0.327389
FOGO MRP MRP_FLOW_DEMANDS 0.573059 0.362793 0.210266
FOGO KBACE XKB_BALANCE_DETAILS 8.248787 5.462819 2.785968
FOGO CSI CSI_ITEM_INSTANCES_H 0.638077 0.428366 0.209711
FOGO INV MTL_SYSTEM_ITEMS_B 1.179688 0.79499 0.384698
FOGO APPS XXDBD_ML_401K_DEBUG 4.6632 3.149641 1.513559
FOGO CS CS_INCIDENTS_AUDIT_B 161.32717 109.113984 52.213187
FOGO JTF JTF_TASK_ASSIGNMENTS 2.057503 1.399487 0.658015
FOGO BEN BEN_ELIG_PER_ELCTBL_CHC 1.344566 0.915375 0.429192
FOGO HR PAY_RUN_RESULT_VALUES 13.825531 9.66187 4.163661
FOGO KBACE KBDX_COSTING_RECON 0.863625 0.613618 0.250007
FOGO JTF JTF_IH_ACTIVITIES 2.082962 1.520129 0.562833
FOGO CZ CZ_LCE_TEXTS 0.762184 0.558835 0.203349
FOGO JTF JTF_TASK_REFERENCES_TL 0.64254 0.472175 0.170365
FOGO CZ CZ_EXPRESSION_NODES 1.05043 0.773045 0.277385
FOGO XXDBD XXDBD_BR_CS_GEO_TASKREP 1.942482 1.429853 0.512629
FOGO INV MTL_TXN_REQUEST_LINES 0.738884 0.554544 0.18434
FOGO CSF CSF_DEBRIEF_LINES 6.778053 5.116997 1.661057
FOGO KBACE XKB_TAX_BALANCE_DETAILS 1.264839 0.958112 0.306727
FOGO CS CS_INCIDENTS_AUDIT_TL 7.16909 5.466506 1.702584
FOGO XXDBD XXDBD_BR_CS_BTC_LINES_OUT 1.12281 0.86424 0.258571
FOGO XXDBD XXDBD_BR_CS_GEO_EVENTS 1.508369 1.161024 0.347346
FOGO CZ CZ_PS_PROP_VALS 1.879364 1.452549 0.426815
FOGO CSL CSL_CONTR_BUSS_TXN_TYPES 0.581604 0.450225 0.131379
FOGO INV MTL_ITEM_CATS_SN 0.543037 0.421177 0.121861
FOGO CS CS_INCIDENTS_ALL_TL 0.663033 0.514403 0.14863
FOGO JTF JTF_TASK_AUDITS_B 17.018608 13.273945 3.744663
FOGO CSL CSL_CONTR_BUSS_TXN_TYPES_ACC 0.673149 0.525094 0.148055
FOGO JTF JTF_NOTE_CONTEXTS 1.044594 0.814922 0.229671
FOGO XXDBD XXDBD_BR_CS_BTC_HEADER_IN 1.653847 1.295801 0.358046
FOGO JTF JTF_NOTES_B 1.500511 1.179807 0.320705
FOGO APPS XXDBD_CS_DEB_MV 0.649231 0.511032 0.138199
FOGO DISCEUL_UEUL5_QPP_STATS 1.791428 1.41535 0.376078
FOGO QA QA_ACTION_LOG 2.971985 2.35029 0.621695
FOGO XXDBD XXDBD_JTF_TASK_ASSIGN_AUD 2.141479 1.69497 0.446509
FOGO APPLSYS AD_PATCH_RUN_BUG_ACTIONS 0.767509 0.60818 0.159329
FOGO JTF JTF_TASK_AUDITS_TL 32.946808 26.145726 6.801081
FOGO XXDBD XXDBD_BR_CS_BTC_HEADER_OUT 1.50676 1.196739 0.310021
FOGO IEU IEU_UWQM_ITEMS 1.93985 1.554348 0.385501
FOGO JTF JTF_TASKS_TL 2.11335 1.696489 0.41686
FOGO JTF JTF_TASKS_B 1.754852 1.409086 0.345766
FOGO XXDBD XXDBD_BR_CS_BATCHCALL_H_AUDIT 0.541168 0.436647 0.104521
FOGO CSF CSF_DEBRIEF_HEADERS 1.254074 1.016673 0.237401
FOGO BOM CST_ITEM_COST_DETAILS 5.446419 4.417624 1.028795
FOGO HR FF_ARCHIVE_ITEMS 0.637787 0.517659 0.120128
FOGO KBACE KBDX_CTS_PAYCHECK_DETAILS 5.142822 4.226324 0.916498
FOGO INV MTL_ITEM_CATEGORIES 0.779297 0.640641 0.138656
FOGO JTF JTF_TASK_REFERENCES_B 1.433403 1.17964 0.253763
FOGO XXDBD XXDBD_JTF_TASK_AUDITS_B 3.986717 3.283813 0.702904
FOGO XXDBD XXDBD_BR_CAPTURED_EVENTS 0.517311 0.426709 0.090602
FOGO XXDBD XXDBD_LOGON_EVENTS 10.162056 8.385899 1.776157
FOGO XXDBD XXDBD_BR_CALL_ENTRY 0.794769 0.661647 0.133122
FOGO MOBILEADC$SYNC_HIS_PUB_ITEMS 7.992218 6.663256 1.328962
FOGO XXDBD XXDBD_BR_CS_GEO_POSIC 0.744049 0.625001 0.119048
FOGO XXDBD XXDBD_DEBUG_TABLE 102.11057 85.975171 16.135395
FOGO APPS EUL5_B120702094049Q1R1 0.731995 0.618856 0.113138
FOGO WIP WIP_TRANSACTION_ACCOUNTS 0.880447 0.744951 0.135496
FOGO BOM CST_ITEM_COSTS 0.84375 0.719016 0.124734
FOGO APPS EUL5_B140106132848Q1R1 0.970894 0.828121 0.142773
FOGO XNP XNP_MSGS 1.447968 1.248098 0.199869
FOGO XXDBD XXDBD_CS_TASK_AGG 2.771713 2.400299 0.371414
FOGO APPLSYS FND_LOGINS 0.755585 0.655792 0.099793
FOGO XXDBD XXDBD_CSI_ITEM_STG 1.584045 1.374915 0.20913
FOGO XXDBD XXDBD_CS_SR_AGG 1.516235 1.317985 0.198251
FOGO CZ CZ_RULES 0.729057 0.641746 0.087312
FOGO JTF JTF_NOTES_TL 21.560089 19.018196 2.541893
FOGO HR PAY_ASSIGNMENT_ACTIONS 0.66597 0.592903 0.073067
FOGO AR HZ_PARTIES 0.601379 0.580753 0.020626
Wasted Per PCT_FREE PCT_USED NUM_ROWS LAST_ANALYZED PARTITIONED BUFFER_POOL
99.9999 10 3 9-Jun-15 NO DEFAULT
99.882151 10 9933 9-Jun-15 NO DEFAULT
99.866142 10 10127 9-Jun-15 NO DEFAULT
99.857221 10 96290 9-Jun-15 NO DEFAULT
99.776579 10 6832 14-Jul-12 NO DEFAULT
88.81042 10 1597147 9-Jun-15 NO DEFAULT
86.875631 10 8569497 9-Jun-15 NO DEFAULT
83.992344 10 489273 9-Jun-15 NO DEFAULT
83.194294 10 173045217 9-Jun-15 NO DEFAULT
79.893084 10 466920 9-Jun-15 NO DEFAULT
78.751177 10 1513647 9-Jun-15 NO DEFAULT
68.576277 10 408867 4-Jun-15 NO DEFAULT
61.26906 10 1124757 21-May-15 NO DEFAULT
45.146504 10 9502420 26-May-15 NO DEFAULT
41.420244 10 2046951 21-Mar-15 NO DEFAULT
40.91274 10 5402397 6-Jun-15 NO DEFAULT
40.865583 10 9007960 6-Jun-15 NO DEFAULT
40.642441 10 14428563 31-May-15 NO DEFAULT
39.917682 10 14441487 31-May-15 NO DEFAULT
39.579537 10 3059924 21-Mar-15 NO DEFAULT
37.655696 10 14513770 21-Mar-15 NO DEFAULT
36.936095 10 8557771 21-Mar-15 NO DEFAULT
36.798293 10 3821278 21-Mar-15 NO DEFAULT
36.691922 10 17706622 21-Mar-15 NO DEFAULT
33.774276 10 325869838 14-Jul-12 NO DEFAULT
32.866058 10 2323003 3-Jun-15 NO DEFAULT
32.610132 10 1700426 21-Mar-15 NO DEFAULT
32.457525 10 602727 2-Jun-15 NO DEFAULT
32.364782 10 127625543 27-May-15 NO DEFAULT
31.981261 10 12118453 9-Jun-15 NO DEFAULT
31.920453 10 5781624 21-Mar-15 NO DEFAULT
30.115743 10 691623569 21-Mar-15 NO DEFAULT
28.948533 10 21962241 14-Jul-12 NO DEFAULT
27.020786 10 16002220 15-May-15 NO DEFAULT
26.679807 10 410708 21-Mar-15 NO DEFAULT
26.51436 10 12071277 27-May-15 NO DEFAULT
26.406793 10 8557228 21-Mar-15 NO DEFAULT
26.3904 10 3165553 9-Jun-15 NO DEFAULT
24.948413 10 3698368 21-Mar-15 NO DEFAULT
24.506396 10 32900200 21-Mar-15 NO KEEP
24.250263 10 14909642 14-Jul-12 NO DEFAULT
23.748953 10 127600360 27-May-15 NO DEFAULT
23.028898 10 5836290 6-Jun-15 NO DEFAULT
23.027903 10 6964467 9-Jun-15 NO DEFAULT
22.710614 10 36271221 21-Mar-15 NO DEFAULT
22.589054 10 8193654 21-Mar-15 NO DEFAULT
22.440537 10 10278074 21-Mar-15 NO DEFAULT
22.416665 10 5362483 28-May-15 NO DEFAULT
22.003343 10 46730460 23-May-15 NO DEFAULT
21.994454 10 11276300 21-Mar-15 NO DEFAULT
21.986665 10 17157183 22-May-15 NO DEFAULT
21.649258 10 5633020 4-Jun-15 NO DEFAULT
21.37302 10 17119023 22-May-15 NO DEFAULT
21.286494 10 328573 4-May-15 NO DEFAULT
20.993205 10 1650076 14-Jul-12 NO DEFAULT
20.918506 10 22941861 21-Mar-15 NO DEFAULT
20.850491 10 31378633 23-May-15 NO DEFAULT
20.759267 10 11661222 21-Mar-15 NO DEFAULT
20.642611 10 46711747 23-May-15 NO DEFAULT
20.575328 10 5788237 9-Jun-15 NO DEFAULT
19.872745 10 8784047 9-Jun-15 NO DEFAULT
19.725105 10 6195890 8-Jun-15 NO DEFAULT
19.703447 10 6200797 5-Jun-15 NO KEEP
19.313908 10 819662 21-Mar-15 NO DEFAULT
18.930386 10 7968207 4-Jun-15 NO DEFAULT
18.889388 10 41976878 21-Mar-15 NO DEFAULT
18.8351 10 9751442 21-Mar-15 NO DEFAULT
17.820915 10 39806852 14-Jul-12 NO DEFAULT
17.792456 10 10582813 21-Mar-15 NO DEFAULT
17.703525 10 12063133 27-May-15 NO DEFAULT
17.631154 10 46394307 23-May-15 NO DEFAULT
17.51408 10 3579493 6-Jun-15 NO DEFAULT
17.478319 10 100047677 14-May-15 NO DEFAULT
16.749759 10 1305953 9-Jun-15 NO DEFAULT
16.628202 10 80388949 14-Jul-12 NO DEFAULT
15.999992 10 4329613 9-Jun-15 NO DEFAULT
15.801885 10 831667897 6-Jun-15 NO DEFAULT
15.456166 10 1954388 21-Mar-15 NO DEFAULT
15.389484 5 5969292 21-Mar-15 NO DEFAULT
14.783329 10 5079192 21-Mar-15 NO DEFAULT
14.70533 10 3406850 9-Jun-15 NO DEFAULT
13.80345 10 1141512 21-Mar-15 NO DEFAULT
13.400154 10 6180580 4-Jun-15 NO DEFAULT
13.207382 5 14981937 18-May-15 NO DEFAULT
13.202284 10 7381520 7-May-15 NO DEFAULT
13.075181 10 5401433 4-Jun-15 NO DEFAULT
11.975963 10 1058478 21-Mar-15 NO DEFAULT
11.789808 10 17131403 22-May-15 NO DEFAULT
10.971555 10 12992333 21-Mar-15 NO DEFAULT
3.429777 10 2180347 20-May-15 NO DEFAULT
Top:15 Tables by Reads

Distinct statistics

Top:15 Indexes by Reads

Small Table Reads(Logical + Physical)

Index Reads (Logical + Physical)

Small Table Reads (by Plan, disk I/O)

Table Fragmentation (Reorg)

select UPPER(t3.instance_name), t1.owner, t1.table_name, t4.object_type, t5.statistic_name, t5.total_reads,
round((t1.blocks*8)/1024/1024,3) "size (GB)" ,
round((t1.num_rows*t1.avg_row_len/1024)/1024/1024,3) "actual_data (GB)",
round((t1.blocks*8 - (t1.num_rows*t1.avg_row_len/1024))/1024/1024,3) "wasted_space (GB)",
round ((((round((t1.blocks*8),3) - round((t1.num_rows*t1.avg_row_len/1024),3))/1024/1024)/(round((t1.blocks*8),3)/1024/1
"Wasted_Per",
t1.pct_free, t1.pct_used,count(t2.table_name), t1.NUM_ROWS, t1.LAST_ANALYZED
FROM
dba_tables t1, dba_tab_columns t2, v$instance t3, dba_objects t4,
(select owner,segment_name,object_type,total_reads, statistic_name
from ( select owner,object_name as segment_name,object_type,
value as total_reads, statistic_name
from v$segment_statistics
where statistic_name in ('&Enter_type_of_reads')
and object_type = 'TABLE'
order by total_reads desc)
where rownum <16) t5
where (round((t1.blocks*8),2) > round((t1.num_rows*t1.avg_row_len/1024),2))
--AND round((t1.blocks*8),2)/1024/1024 > 1
and t1.table_name = t2.table_name
AND t1.table_name = t4.object_name
AND t4.object_type in ('TABLE')
AND t1.table_name = t5.segment_name AND t1.owner = t5.owner
GROUP BY
UPPER(t3.instance_name), t1.owner, t1.table_name, t4.object_type, t5.statistic_name, t5.total_reads,
round((t1.blocks*8)/1024/1024,3),
round((t1.num_rows*t1.avg_row_len/1024)/1024/1024,3),
round((t1.blocks*8 - (t1.num_rows*t1.avg_row_len/1024))/1024/1024,3),
round ((((round((t1.blocks*8),3) - round((t1.num_rows*t1.avg_row_len/1024),3))/1024/1024)/(round((t1.blocks*8),3)/1024/1
t1.pct_free, t1.pct_used,t1.NUM_ROWS, t1.LAST_ANALYZED
order by t5.total_reads desc;
select distinct statistic_name from v$segment_statistics;
Ex: physical reads, physical reads direct, logical reads, physical writes

select UPPER(T2.instance_name), T3.owner, t3.segment_name, t3.object_type, t3.statistic_name, t3.total_reads, round(SUM(

FROM dba_segments T1, v$instance T2,
(select owner,segment_name,object_type,statistic_name, total_reads
from ( select owner,object_name as segment_name,object_type,
value as total_reads, statistic_name
from v$segment_statistics
where statistic_name in ('&Enter_Type_of_reads')
and object_type = 'INDEX'
order by total_reads desc)
where rownum <16) T3
where T1.segment_name = t3.segment_name AND t1.owner = t3.owner
GROUP BY
T2.instance_name, T3.owner, t3.segment_name, t3.object_type, t3.statistic_name, t3.total_reads
ORDER BY t3.total_reads desc
;
#For tables less than 50 MB
SELECT UPPER(t2.instance_name), t.owner,t.table_name, 'TABLE', lr.value "Logical Reads", pr.value "Physical Reads", lr.value +
(blocks*8)/1024 MB
FROM
(SELECT owner,object_name,value FROM v$segment_statistics WHERE statistic_name='logical reads') lr,
(SELECT owner,object_name,value FROM v$segment_statistics WHERE statistic_name='physical reads') pr,
dba_tables t, v$instance T2
WHERE lr.owner=pr.owner AND lr.object_name=pr.object_name
AND lr.owner=t.owner AND lr.object_name=t.table_name
and LR.owner NOT IN ('SYS', 'SYSTEM')
and (blocks*8)/1024 <= 50
ORDER BY 5 desc
;

SELECT UPPER(t2.instance_name), t.owner, t.index_name, 'INDEX', lr.value "Logical Reads", pr.value "Physical Reads", lr.value
total_reads,
sum(bytes)/1024/1024 MB
FROM
(SELECT owner,object_name,value FROM v$segment_statistics WHERE statistic_name='logical reads') lr,
(SELECT owner,object_name,value FROM v$segment_statistics WHERE statistic_name='physical reads') pr,
dba_indexes t, v$instance t2, dba_segments t3
where lr.owner=pr.owner AND lr.object_name=pr.object_name
AND lr.owner=t.owner AND lr.object_name=t.table_name
and LR.owner not LIKE '%SYS%'
GROUP BY t2.instance_name, t.owner, t.index_name, 'INDEX', lr.value, pr.value, lr.value+pr.value
ORDER BY 5 DESC;
SELECT UPPER(T4.instance_name), T2.OWNER, T2.TABLE_NAME, 'TABLE', T2.NUM_ROWS, T2.BLOCKS*8/1024 TABLE_SIZE_M
SQL_PLAN_COUNT,
T3.value total_physical_reads
FROM
(select rownum SNO, object_name, count_object
FROM
(
select object_name, count(object_name) count_object from v$sql_plan
where object_name IS NOT NULL AND object_type = 'TABLE'
-- operation = 'TABLE ACCESS'
and object_owner not in ('SYS', 'SYSTEM')
group by object_name
order by 2 desc
) )T1, dba_tables T2 , v$segment_statistics T3, v$instance T4
WHERE
T1.object_name = T2.table_name AND
T1.object_name = T3.object_name AND
T3.statistic_name in ('physical reads')
-- AND T2.OWNER = 'APPLSYS'
AND T2.BLOCKS*8/1024 <= 50
-- ORDER BY 7 desc
-- ORDER BY T1.SNO
ORDER BY 7 desc
;

select UPPER(t2.instance_name), T1.owner, T1.table_name,round(round((T1.blocks*8),6)/1024/1024,6) "size (GB)" ,

round(round((T1.num_rows*T1.avg_row_len/1024),6)/1024/1024,6) "actual_data (GB)",
round((round((T1.blocks*8),6) - round((T1.num_rows*T1.avg_row_len/1024),6))/1024/1024,6) "wasted_space
round((((round((T1.blocks*8),6) - round((T1.num_rows*T1.avg_row_len/1024),6))/1024/1024)/(round((T1.blo
1024/1024))*100,6) "Wasted_Per",
T1.PCT_FREE, T1.PCT_USED, T1.NUM_ROWS, T1.LAST_ANALYZED, T1.PARTITIONED, T1.BUFFER_POOL
from dba_tables T1, v$instance T2
where
(round((T1.blocks*8),6) > round((T1.num_rows*T1.avg_row_len/1024),6)) AND
round((T1.blocks*8),4)/1024/1024 >= 0.5 AND PARTITIONED = 'NO'
AND T1.OWNER NOT IN ('SYS', 'SYSTEM')
order by 7 desc
;