11 rows selected.
3.4 Redo Log File Wait (100/Day is good)
***************************************************************
- Log File Switch Statistics
*******************************************
old 7: WHERE thread#=&thread
new 7: WHERE thread#= 1
old 8: AND TO_CHAR(first_time,'yyyymm')='&yyyymm'
new 8: AND TO_CHAR(first_time,'yyyymm')='201409'
| Min/Day | Avg/Day | Max/Day |
|---|---|---|
| 1 | 1 | 1 |
- Log File Switch Count Per Day
*******************************************
old 28: WHERE thread#=&thread
new 28: WHERE thread#= 1
old 29: AND TO_CHAR(first_time,'yyyymm')='&yyyymm'
new 29: AND TO_CHAR(first_time,'yyyymm')='201409'
| DAY | 00 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | Day Sum |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2014-09-15 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
| 2014-09-12 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
| 2014-09-09 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 |
| 2014-09-07 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
| 2014-09-04 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
| 2014-09-01 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
6 rows selected.
- Redo Log Space Requests
*******************************************
| Name |
Value
(Near 0 is good, or Increase LOG_BUFFER) |
|---|---|
| Redo Log Space Requests | 313 |
*******************************************
* 4. System and Datafile *
* 4.1 System Free Space *
* 4.2 User Free Space *
* 4.3 Extents *
*******************************************
4.1 System Free Space (> 20% is good)
***************************************************************
| Tablespace |
Free(%)
(If<20%, increase SYSTEM tablespace) |
Used(%) |
Total
Size(MB) |
Used
Size(MB) |
Free
Size(MB) |
|---|---|---|---|---|---|
| SYSTEM | 2.68% | 97.31% | 700 | 681 | 19 |
4.2 User Free Space (> 20% is good)
***************************************************************
| Tablespace |
Free(%)
(If<20%, increase user-defined tablespace) |
Used(%) |
Total
Size(MB) |
Used
Size(MB) |
Free
Size(MB) |
|---|---|---|---|---|---|
| PARCMMITT_IDX_01 | 99.9% | .09% | 1,024 | 1 | 1,023 |
| SECUREDATA | 99.81% | .18% | 3,000 | 6 | 2,994 |
| SECUREINDEX | 99.81% | .18% | 1,024 | 2 | 1,022 |
| RESKDI_DATA | 99.2% | .79% | 1,024 | 8 | 1,016 |
| UNDOTBS1 | 99.16% | .83% | 1,024 | 9 | 1,015 |
| KIDS_IDX2 | 98.13% | 1.86% | 4,000 | 75 | 3,925 |
| SYSAUX | 9.64% | 90.35% | 600 | 542 | 58 |
| KIDS_DATA2 | 85.33% | 14.66% | 1,024 | 150 | 874 |
| USERS | 80% | 20% | 5 | 1 | 4 |
| TBS_PIMAC | 41.87% | 58.12% | 2,048 | 1,190 | 858 |
| PARCMMITT_DAT_01 | 4.78% | 95.21% | 1,980 | 1,885 | 95 |
| IDX_PIMAC | 21.77% | 78.22% | 1,024 | 801 | 223 |
12 rows selected.
4.3 Extents (More than 100 Extents or MAX_EXTENTS<10)
***************************************************************
no rows selected
SQL>
SQL> cls
SP2-0042: unknown command "cls" - rest of line ignored.
SQL> clrear
SP2-0042: unknown command "clrear" - rest of line ignored.
SQL> clear
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> @/tmp/dbpm.sql
| THREAD | INSTANCE | DBNAME | LOG_MODE | HOST_NAME | PLATFORM_NAME | VERSION | PAR | LOG_MODE |
|---|---|---|---|---|---|---|---|---|
| 1 | PIMAC | PIMAC | ARCHIVELOG | pimac-DB | HP-UX IA (64-bit) | 11.2.0.3.0 | NO | ARCHIVELOG |
| Total System Global Area | 5144453120 | bytes |
| Fixed Size | 2191440 | bytes |
| Variable Size | 1711280048 | bytes |
| Database Buffers | 3422552064 | bytes |
| Redo Buffers | 8429568 | bytes |
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /ARCH
Oldest online log sequence 143
Next log sequence to archive 145
Current log sequence 145
*******************************************
* 1. SGA Configuration *
* 1.1 Shared Pool Size *
* 1.2 DB Cache Size *
* 1.3 Redo Buffer Size *
*******************************************
1.1 Shared Pool Size (bytes)
***************************************************************
| Pool Name | Total Size |
|---|---|
| shared pool | 1,140,850,688 |
| Pool Name | Used Size | Used(%) |
|---|---|---|
| shared pool | 1,066,178,584 | 93.45% |
| Pool Name | Name | Free Size | Free(%) |
|---|---|---|---|
| shared pool | free memory | 74,672,104 | 6.55% |
1.2 Database Cache Size (bytes)
***************************************************************
| Name | Total Size | Used Size | Free Size | Used(%) | Free(%) |
|---|---|---|---|---|---|
| buffer_cache | 3,422,552,064 | 2,762,686,464 | 659,865,600 | 80.72% | 19.28% |
1.3 Redo Buffer Size (bytes)
***************************************************************
| Buffer Name | Size |
|---|---|
| log_buffer | 8,429,568 |
*******************************************
* 2. Memory Allocation *
* 2.1 Library Cache Miss Ratio *
* 2.2 Data Dictionary Miss Ratio *
* 2.3 Buffer Cache Hit Ratio *
*******************************************
2.1 Library Cache Miss Ratio
***************************************************************
|
Library Cache Miss Ratio
(If>1%, increase SHARED_POOL_SIZE) |
|---|
| .1% |
2.2 Data Dictionary Cache Hit Ratio and Miss Ratio
***************************************************************
|
Data Dictionary Cache Miss Ratio
(If>9.8%, increase SHARED_POOL_SIZE) |
Data Dictionary Cache Hit Ratio
(If<90%, increase SHARED_POOL_SIZE) |
|---|---|
| .59% | 99.4% |
2.3 Buffer Cache Hit Ratio
***************************************************************
Buffer Cache Hit Ratio: 99.95% (If<70%, increase DB_CACHE_SIZE)
PL/SQL procedure successfully completed.
*******************************************
* 3. Contention *
* 3.1 Disk Physical I/O *
* 3.2 Internal and External Sort *
* 3.3 Rollback Segments Wait Ratio *
* 3.4 Redo Log File Wait *
*******************************************
3.1 Disk Physical I/O
***************************************************************
View created.
| Tablespace | Datafile | Reads% | Writes% |
|---|---|---|---|
| IDX_PIMAC | /oradata/PIMAC/IDX_PIMAC.DBF | .06 | .00 |
| KIDS_DATA2 | /oradata/PIMAC/KIDS_DATA2.DBF | 2.80 | .94 |
| KIDS_IDX2 | /oradata/PIMAC/KIDS_IDX2.DBF | 1.34 | .43 |
| PARCMMITT_DAT_01 | /oradata/PIMAC/PARCMMITT_DAT_01.DBF | 37.71 | .65 |
| PARCMMITT_IDX_01 | /oradata/PIMAC/PARCMMITT_IDX_01.DBF | .00 | .00 |
| RESKDI_DATA | /oradata/PIMAC/RESKDI_DATA.DBF | .00 | .00 |
| SECUREDATA | /oradata/PIMAC/SECUREDATA01.DBF | .33 | .02 |
| SECUREINDEX | /oradata/PIMAC/SECUREINDEX01.DBF | .06 | .00 |
| SYSAUX | /oradata/PIMAC/sysaux01.dbf | 36.82 | 54.51 |
| SYSTEM | /oradata/PIMAC/system01.dbf | 15.52 | 10.56 |
| TBS_PIMAC | /oradata/PIMAC/TBS_PIMAC.DBF | 5.32 | .00 |
| UNDOTBS1 | /oradata/PIMAC/undotbs01.dbf | .04 | 32.90 |
| USERS | /oradata/PIMAC/users01.dbf | .00 | .00 |
13 rows selected.
View dropped.
3.2 Internal and External Sort
***************************************************************
|
Disk Sort Ratio
(If higher, increase SORT_AREA_SIZE) |
Disk Sorts | Memory Sorts | Rows Sorted |
|---|---|---|---|
| 0% | 0 | 10,260,816 | 286,361,571 |
3.3 Rollback Segments Wait Ratio
***************************************************************
| Rollback Segment |
Wait(Miss) Ratio
(If>1~2%, increase undo segment) |
|---|---|
| _SYSSMU5_1657013790$ | .01% |
| _SYSSMU9_710568957$ | .01% |
| _SYSSMU8_2605333497$ | .01% |
| _SYSSMU10_27235165$ | .01% |
| _SYSSMU1_442678724$ | .01% |
| _SYSSMU3_3909353866$ | .01% |
| _SYSSMU4_4025277305$ | .01% |
| _SYSSMU7_2285735219$ | 0% |
| _SYSSMU2_2843903545$ | 0% |
| _SYSSMU6_1322568757$ | 0% |
| SYSTEM | 0% |
11 rows selected.
3.4 Redo Log File Wait (100/Day is good)
***************************************************************
- Log File Switch Statistics
*******************************************
old 7: WHERE thread#=&thread
new 7: WHERE thread#= 1
old 8: AND TO_CHAR(first_time,'yyyymm')='&yyyymm'
new 8: AND TO_CHAR(first_time,'yyyymm')='201409'
| Min/Day | Avg/Day | Max/Day |
|---|---|---|
| 1 | 1 | 1 |
- Log File Switch Count Per Day
*******************************************
old 28: WHERE thread#=&thread
new 28: WHERE thread#= 1
old 29: AND TO_CHAR(first_time,'yyyymm')='&yyyymm'
new 29: AND TO_CHAR(first_time,'yyyymm')='201409'
| DAY | 00 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | Day Sum |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2014-09-15 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
| 2014-09-12 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
| 2014-09-09 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 |
| 2014-09-07 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
| 2014-09-04 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
| 2014-09-01 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
6 rows selected.
- Redo Log Space Requests
*******************************************
| Name |
Value
(Near 0 is good, or Increase LOG_BUFFER) |
|---|---|
| Redo Log Space Requests | 313 |
*******************************************
* 4. System and Datafile *
* 4.1 System Free Space *
* 4.2 User Free Space *
* 4.3 Extents *
*******************************************
4.1 System Free Space (> 20% is good)
***************************************************************
| Tablespace |
Free(%)
(If<20%, increase SYSTEM tablespace) |
Used(%) |
Total
Size(MB) |
Used
Size(MB) |
Free
Size(MB) |
|---|---|---|---|---|---|
| SYSTEM | 2.68% | 97.31% | 700 | 681 | 19 |
4.2 User Free Space (> 20% is good)
***************************************************************
| Tablespace |
Free(%)
(If<20%, increase user-defined tablespace) |
Used(%) |
Total
Size(MB) |
Used
Size(MB) |
Free
Size(MB) |
|---|---|---|---|---|---|
| PARCMMITT_IDX_01 | 99.9% | .09% | 1,024 | 1 | 1,023 |
| SECUREDATA | 99.81% | .18% | 3,000 | 6 | 2,994 |
| SECUREINDEX | 99.81% | .18% | 1,024 | 2 | 1,022 |
| RESKDI_DATA | 99.2% | .79% | 1,024 | 8 | 1,016 |
| UNDOTBS1 | 99.16% | .83% | 1,024 | 9 | 1,015 |
| KIDS_IDX2 | 98.13% | 1.86% | 4,000 | 75 | 3,925 |
| SYSAUX | 9.64% | 90.35% | 600 | 542 | 58 |
| KIDS_DATA2 | 85.33% | 14.66% | 1,024 | 150 | 874 |
| USERS | 80% | 20% | 5 | 1 | 4 |
| TBS_PIMAC | 41.87% | 58.12% | 2,048 | 1,190 | 858 |
| PARCMMITT_DAT_01 | 4.78% | 95.21% | 1,980 | 1,885 | 95 |
| IDX_PIMAC | 21.77% | 78.22% | 1,024 | 801 | 223 |
12 rows selected.
4.3 Extents (More than 100 Extents or MAX_EXTENTS<10)
***************************************************************
no rows selected
SQL>