Skip to content

Tape server querying DB at very high rate

Summary

The tape servers are querying the DB at a very high rate. E-mail from Nilo:

Just checkin CTA perf and ran some reports from the DB. It seems that the SQL themselve run ok, but the number of execution during 1 hour perios is very high SQL statement with SQL_ID "g7wcmmndapzuf" was executed 497330 times and had an average elapsed time of 0.028 seconds. => 138 executions per second SQL statement with SQL_ID "f238xwnpyys4v" was executed 75527396 times and had an average elapsed time of 0.000045 seconds. => 20979 executions per second SQL statement with SQL_ID "6fqspa5awq687" was executed 21009795 times and had an average elapsed time of 0.000048 seconds. => 5836 the CPU load is very high. Could this be reviewed ? Here, not even a memory-based DBMS would help, because we are basically cpu bound...

Details

Output of one of the perf reports:

ADDM Task ADDM:3246123132_1_28021
ADDM Report for Task 'ADDM:3246123132_1_28021' ---------------------------------------------- Analysis Period --------------- AWR snapshot range from 28020 to 28021. Time period starts at 19-SEP-22 13.00.28 Time period ends at 19-SEP-22 14.00.24 Analysis Target --------------- Database 'CASTNSP' with DB ID 3246123132. Database version 19.0.0.0.0. ADDM performed an analysis of instance CASTNSP1, numbered 1 and hosted at itrac5409.cern.ch. Activity During the Analysis Period ----------------------------------- Total database time was 39785 seconds. The average number of active sessions was 11.06. Summary of Findings ------------------- Description Active Sessions Recommendations Percent of Activity --------------------- ------------------- --------------- 1 Top SQL Statements 9.84 | 88.95 4 2 Commits and Rollbacks .23 | 2.04 2 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Findings and Recommendations ---------------------------- Finding 1: Top SQL Statements Impact is 9.84 active sessions, 88.95% of total activity. --------------------------------------------------------- SQL statements consuming significant database time were found. These statements offer a good opportunity for performance improvement. Recommendation 1: SQL Tuning Estimated benefit is 6.98 active sessions, 63.13% of total activity. -------------------------------------------------------------------- Action Run SQL Tuning Advisor on the SELECT statement with SQL_ID "g7wcmmndapzuf". Related Object SQL statement with SQL_ID g7wcmmndapzuf. SELECT TAPE.VID AS VID,MEDIA_TYPE.MEDIA_TYPE_NAME AS MEDIA_TYPE,TAPE.VENDOR AS VENDOR,LOGICAL_LIBRARY.LOGICAL_LIBRARY_NAME AS LOGICAL_LIBRARY_NAME,TAPE_POOL.TAPE_POOL_NAME AS TAPE_POOL_NAME,VIRTUAL_ORGANIZATION.VIRTUAL_ORGANIZATION_NAME AS VO,TAPE.ENCRYPTION_KEY_NAME AS ENCRYPTION_KEY_NAME,MEDIA_TYPE.CAPACITY_IN_BYTES AS CAPACITY_IN_BYTES,TAPE.DATA_IN_BYTES AS DATA_IN_BYTES,TAPE.NB_MASTER_FILES AS NB_MASTER_FILES,TAPE.MASTER_DATA_IN_BYTES AS MASTER_DATA_IN_BYTES,TAPE.LAST_FSEQ AS LAST_FSEQ,TAPE.IS_FULL AS IS_FULL,TAPE.DIRTY AS DIRTY,TAPE.IS_FROM_CASTOR AS IS_FROM_CASTOR,TAPE.LABEL_FORMAT AS LABEL_FORMAT,TAPE.LABEL_DRIVE AS LABEL_DRIVE,TAPE.LABEL_TIME AS LABEL_TIME,TAPE.LAST_READ_DRIVE AS LAST_READ_DRIVE,TAPE.LAST_READ_TIME AS LAST_READ_TIME,TAPE.LAST_WRITE_DRIVE AS LAST_WRITE_DRIVE,TAPE.LAST_WRITE_TIME AS LAST_WRITE_TIME,TAPE.READ_MOUNT_COUNT AS READ_MOUNT_COUNT,TAPE.WRITE_MOUNT_COUNT AS WRITE_MOUNT_COUNT,TAPE.VERIFICATION_STATUS AS VERIFICATION_STATUS,TAPE.USER_COMMENT AS USER_COMMENT,TAPE.TAPE_STATE AS TAPE_STATE,TAPE.STATE_REASON AS STATE_REASON,TAPE.STATE_UPDATE_TIME AS STATE_UPDATE_TIME,TAPE.STATE_MODIFIED_BY AS STATE_MODIFIED_BY,TAPE.CREATION_LOG_USER_NAME AS CREATION_LOG_USER_NAME,TAPE.CREATION_LOG_HOST_NAME AS CREATION_LOG_HOST_NAME,TAPE.CREATION_LOG_TIME AS CREATION_LOG_TIME,TAPE.LAST_UPDATE_USER_NAME AS LAST_UPDATE_USER_NAME,TAPE.LAST_UPDATE_HOST_NAME AS LAST_UPDATE_HOST_NAME,TAPE.LAST_UPDATE_TIME AS LAST_UPDATE_TIME FROM TAPE INNER JOIN TAPE_POOL ON TAPE.TAPE_POOL_ID = TAPE_POOL.TAPE_POOL_ID INNER JOIN LOGICAL_LIBRARY ON TAPE.LOGICAL_LIBRARY_ID = LOGICAL_LIBRARY.LOGICAL_LIBRARY_ID INNER JOIN MEDIA_TYPE ON TAPE.MEDIA_TYPE_ID = MEDIA_TYPE.MEDIA_TYPE_ID INNER JOIN VIRTUAL_ORGANIZATION ON TAPE_POOL.VIRTUAL_ORGANIZATION_ID = VIRTUAL_ORGANIZATION.VIRTUAL_ORGANIZATION_ID WHERE LOGICAL_LIBRARY.LOGICAL_LIBRARY_NAME = :LOGICAL_LIBRARY_NAME ORDER BY TAPE.VID Action Use bigger fetch arrays while fetching results from the SELECT statement with SQL_ID "g7wcmmndapzuf". Related Object SQL statement with SQL_ID g7wcmmndapzuf. SELECT TAPE.VID AS VID,MEDIA_TYPE.MEDIA_TYPE_NAME AS MEDIA_TYPE,TAPE.VENDOR AS VENDOR,LOGICAL_LIBRARY.LOGICAL_LIBRARY_NAME AS LOGICAL_LIBRARY_NAME,TAPE_POOL.TAPE_POOL_NAME AS TAPE_POOL_NAME,VIRTUAL_ORGANIZATION.VIRTUAL_ORGANIZATION_NAME AS VO,TAPE.ENCRYPTION_KEY_NAME AS ENCRYPTION_KEY_NAME,MEDIA_TYPE.CAPACITY_IN_BYTES AS CAPACITY_IN_BYTES,TAPE.DATA_IN_BYTES AS DATA_IN_BYTES,TAPE.NB_MASTER_FILES AS NB_MASTER_FILES,TAPE.MASTER_DATA_IN_BYTES AS MASTER_DATA_IN_BYTES,TAPE.LAST_FSEQ AS LAST_FSEQ,TAPE.IS_FULL AS IS_FULL,TAPE.DIRTY AS DIRTY,TAPE.IS_FROM_CASTOR AS IS_FROM_CASTOR,TAPE.LABEL_FORMAT AS LABEL_FORMAT,TAPE.LABEL_DRIVE AS LABEL_DRIVE,TAPE.LABEL_TIME AS LABEL_TIME,TAPE.LAST_READ_DRIVE AS LAST_READ_DRIVE,TAPE.LAST_READ_TIME AS LAST_READ_TIME,TAPE.LAST_WRITE_DRIVE AS LAST_WRITE_DRIVE,TAPE.LAST_WRITE_TIME AS LAST_WRITE_TIME,TAPE.READ_MOUNT_COUNT AS READ_MOUNT_COUNT,TAPE.WRITE_MOUNT_COUNT AS WRITE_MOUNT_COUNT,TAPE.VERIFICATION_STATUS AS VERIFICATION_STATUS,TAPE.USER_COMMENT AS USER_COMMENT,TAPE.TAPE_STATE AS TAPE_STATE,TAPE.STATE_REASON AS STATE_REASON,TAPE.STATE_UPDATE_TIME AS STATE_UPDATE_TIME,TAPE.STATE_MODIFIED_BY AS STATE_MODIFIED_BY,TAPE.CREATION_LOG_USER_NAME AS CREATION_LOG_USER_NAME,TAPE.CREATION_LOG_HOST_NAME AS CREATION_LOG_HOST_NAME,TAPE.CREATION_LOG_TIME AS CREATION_LOG_TIME,TAPE.LAST_UPDATE_USER_NAME AS LAST_UPDATE_USER_NAME,TAPE.LAST_UPDATE_HOST_NAME AS LAST_UPDATE_HOST_NAME,TAPE.LAST_UPDATE_TIME AS LAST_UPDATE_TIME FROM TAPE INNER JOIN TAPE_POOL ON TAPE.TAPE_POOL_ID = TAPE_POOL.TAPE_POOL_ID INNER JOIN LOGICAL_LIBRARY ON TAPE.LOGICAL_LIBRARY_ID = LOGICAL_LIBRARY.LOGICAL_LIBRARY_ID INNER JOIN MEDIA_TYPE ON TAPE.MEDIA_TYPE_ID = MEDIA_TYPE.MEDIA_TYPE_ID INNER JOIN VIRTUAL_ORGANIZATION ON TAPE_POOL.VIRTUAL_ORGANIZATION_ID = VIRTUAL_ORGANIZATION.VIRTUAL_ORGANIZATION_ID WHERE LOGICAL_LIBRARY.LOGICAL_LIBRARY_NAME = :LOGICAL_LIBRARY_NAME ORDER BY TAPE.VID Rationale The SQL spent 99% of its database time on CPU, I/O and Cluster waits. This part of database time may be improved by the SQL Tuning Advisor. Rationale Database time for this SQL was divided as follows: 100% for SQL execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java execution. Rationale SQL statement with SQL_ID "g7wcmmndapzuf" was executed 497330 times and had an average elapsed time of 0.028 seconds. Rationale At least 2 distinct execution plans were utilized for this SQL statement during the analysis period. Recommendation 2: SQL Tuning Estimated benefit is 1.93 active sessions, 17.44% of total activity. -------------------------------------------------------------------- Action Run SQL Tuning Advisor on the SELECT statement with SQL_ID "f238xwnpyys4v". Related Object SQL statement with SQL_ID f238xwnpyys4v. SELECT DRIVE_STATE.DRIVE_NAME AS DRIVE_NAME,DRIVE_STATE.HOST AS HOST,DRIVE_STATE.LOGICAL_LIBRARY AS LOGICAL_LIBRARY,DRIVE_STATE.SESSION_ID AS SESSION_ID,DRIVE_STATE.BYTES_TRANSFERED_IN_SESSION AS BYTES_TRANSFERED_IN_SESSION,DRIVE_STATE.FILES_TRANSFERED_IN_SESSION AS FILES_TRANSFERED_IN_SESSION,DRIVE_STATE.SESSION_START_TIME AS SESSION_START_TIME,DRIVE_STATE.SESSION_ELAPSED_TIME AS SESSION_ELAPSED_TIME,DRIVE_STATE.MOUNT_START_TIME AS MOUNT_START_TIME,DRIVE_STATE.TRANSFER_START_TIME AS TRANSFER_START_TIME,DRIVE_STATE.UNLOAD_START_TIME AS UNLOAD_START_TIME,DRIVE_STATE.UNMOUNT_START_TIME AS UNMOUNT_START_TIME,DRIVE_STATE.DRAINING_START_TIME AS DRAINING_START_TIME,DRIVE_STATE.DOWN_OR_UP_START_TIME AS DOWN_OR_UP_START_TIME,DRIVE_STATE.PROBE_START_TIME AS PROBE_START_TIME,DRIVE_STATE.CLEANUP_START_TIME AS CLEANUP_START_TIME,DRIVE_STATE.START_START_TIME AS START_START_TIME,DRIVE_STATE.SHUTDOWN_TIME AS SHUTDOWN_TIME,DRIVE_STATE.MOUNT_TYPE AS MOUNT_TYPE,DRIVE_STATE.DRIVE_STATUS AS DRIVE_STATUS,DRIVE_STATE.DESIRED_UP AS DESIRED_UP,DRIVE_STATE.DESIRED_FORCE_DOWN AS DESIRED_FORCE_DOWN,DRIVE_STATE.REASON_UP_DOWN AS REASON_UP_DOWN,DRIVE_STATE.CURRENT_VID AS CURRENT_VID,DRIVE_STATE.CTA_VERSION AS CTA_VERSION,DRIVE_STATE.CURRENT_PRIORITY AS CURRENT_PRIORITY,DRIVE_STATE.CURRENT_ACTIVITY AS CURRENT_ACTIVITY,DRIVE_STATE.CURRENT_TAPE_POOL AS CURRENT_TAPE_POOL,DRIVE_STATE.NEXT_MOUNT_TYPE AS NEXT_MOUNT_TYPE,DRIVE_STATE.NEXT_VID AS NEXT_VID,DRIVE_STATE.NEXT_TAPE_POOL AS NEXT_TAPE_POOL,DRIVE_STATE.NEXT_PRIORITY AS NEXT_PRIORITY,DRIVE_STATE.NEXT_ACTIVITY AS NEXT_ACTIVITY,DRIVE_STATE.DEV_FILE_NAME AS DEV_FILE_NAME,DRIVE_STATE.RAW_LIBRARY_SLOT AS RAW_LIBRARY_SLOT,DRIVE_STATE.CURRENT_VO AS CURRENT_VO,DRIVE_STATE.NEXT_VO AS NEXT_VO,DRIVE_STATE.USER_COMMENT AS USER_COMMENT,DRIVE_STATE.CREATION_LOG_USER_NAME AS CREATION_LOG_USER_NAME,DRIVE_STATE.CREATION_LOG_HOST_NAME AS CREATION_LOG_HOST_NAME,DRIVE_STATE.CREATION_LOG_TIME AS CREATION_LOG_TIME,DRIVE_STATE.LAST_UPDATE_USER_NAME AS LAST_UPDATE_USER_NAME,DRIVE_STATE.LAST_UPDATE_HOST_NAME AS LAST_UPDATE_HOST_NAME,DRIVE_STATE.LAST_UPDATE_TIME AS LAST_UPDATE_TIME,DRIVE_STATE.DISK_SYSTEM_NAME AS DISK_SYSTEM_NAME,DRIVE_STATE.RESERVED_BYTES AS RESERVED_BYTES,DRIVE_STATE.RESERVATION_SESSION_ID as RESERVATION_SESSION_ID,LOGICAL_LIBRARY.IS_DISABLED AS IS_DISABLED FROM DRIVE_STATE LEFT JOIN LOGICAL_LIBRARY ON LOGICAL_LIBRARY.LOGICAL_LIBRARY_NAME = DRIVE_STATE.LOGICAL_LIBRARY WHERE DRIVE_NAME = :DRIVE_NAME Rationale The SQL spent 98% of its database time on CPU, I/O and Cluster waits. This part of database time may be improved by the SQL Tuning Advisor. Rationale Database time for this SQL was divided as follows: 100% for SQL execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java execution. Rationale SQL statement with SQL_ID "f238xwnpyys4v" was executed 75527396 times and had an average elapsed time of 0.000045 seconds. Rationale At least 2 distinct execution plans were utilized for this SQL statement during the analysis period. Recommendation 3: SQL Tuning Estimated benefit is .58 active sessions, 5.26% of total activity. ------------------------------------------------------------------ Action Run SQL Tuning Advisor on the SELECT statement with SQL_ID "6fqspa5awq687". Related Object SQL statement with SQL_ID 6fqspa5awq687. SELECT TAPE.VID AS VID,MEDIA_TYPE.MEDIA_TYPE_NAME AS MEDIA_TYPE,TAPE.VENDOR AS VENDOR,LOGICAL_LIBRARY.LOGICAL_LIBRARY_NAME AS LOGICAL_LIBRARY_NAME,TAPE_POOL.TAPE_POOL_NAME AS TAPE_POOL_NAME,VIRTUAL_ORGANIZATION.VIRTUAL_ORGANIZATION_NAME AS VO,TAPE.ENCRYPTION_KEY_NAME AS ENCRYPTION_KEY_NAME,MEDIA_TYPE.CAPACITY_IN_BYTES AS CAPACITY_IN_BYTES,TAPE.DATA_IN_BYTES AS DATA_IN_BYTES,TAPE.LAST_FSEQ AS LAST_FSEQ,TAPE.IS_FULL AS IS_FULL,TAPE.IS_FROM_CASTOR AS IS_FROM_CASTOR,TAPE.LABEL_FORMAT AS LABEL_FORMAT,TAPE.LABEL_DRIVE AS LABEL_DRIVE,TAPE.LABEL_TIME AS LABEL_TIME,TAPE.LAST_READ_DRIVE AS LAST_READ_DRIVE,TAPE.LAST_READ_TIME AS LAST_READ_TIME,TAPE.LAST_WRITE_DRIVE AS LAST_WRITE_DRIVE,TAPE.LAST_WRITE_TIME AS LAST_WRITE_TIME,TAPE.READ_MOUNT_COUNT AS READ_MOUNT_COUNT,TAPE.WRITE_MOUNT_COUNT AS WRITE_MOUNT_COUNT,TAPE.USER_COMMENT AS USER_COMMENT,TAPE.TAPE_STATE AS TAPE_STATE,TAPE.STATE_REASON AS STATE_REASON,TAPE.STATE_UPDATE_TIME AS STATE_UPDATE_TIME,TAPE.STATE_MODIFIED_BY AS STATE_MODIFIED_BY,TAPE.CREATION_LOG_USER_NAME AS CREATION_LOG_USER_NAME,TAPE.CREATION_LOG_HOST_NAME AS CREATION_LOG_HOST_NAME,TAPE.CREATION_LOG_TIME AS CREATION_LOG_TIME,TAPE.LAST_UPDATE_USER_NAME AS LAST_UPDATE_USER_NAME,TAPE.LAST_UPDATE_HOST_NAME AS LAST_UPDATE_HOST_NAME,TAPE.LAST_UPDATE_TIME AS LAST_UPDATE_TIME FROM TAPE INNER JOIN TAPE_POOL ON TAPE.TAPE_POOL_ID = TAPE_POOL.TAPE_POOL_ID INNER JOIN LOGICAL_LIBRARY ON TAPE.LOGICAL_LIBRARY_ID = LOGICAL_LIBRARY.LOGICAL_LIBRARY_ID INNER JOIN MEDIA_TYPE ON TAPE.MEDIA_TYPE_ID = MEDIA_TYPE.MEDIA_TYPE_ID INNER JOIN VIRTUAL_ORGANIZATION ON TAPE_POOL.VIRTUAL_ORGANIZATION_ID = VIRTUAL_ORGANIZATION.VIRTUAL_ORGANIZATION_ID WHERE VID = :VID Rationale The SQL spent 100% of its database time on CPU, I/O and Cluster waits. This part of database time may be improved by the SQL Tuning Advisor. Rationale Database time for this SQL was divided as follows: 100% for SQL execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java execution. Rationale SQL statement with SQL_ID "6fqspa5awq687" was executed 21009795 times and had an average elapsed time of 0.000048 seconds. Recommendation 4: SQL Tuning Estimated benefit is .27 active sessions, 2.48% of total activity. ------------------------------------------------------------------ Action Run SQL Tuning Advisor on the SELECT statement with SQL_ID "cd5kkfza769fr". Related Object SQL statement with SQL_ID cd5kkfza769fr. SELECT TAPE.VID AS VID,MEDIA_TYPE.MEDIA_TYPE_NAME AS MEDIA_TYPE,TAPE.VENDOR AS VENDOR,LOGICAL_LIBRARY.LOGICAL_LIBRARY_NAME AS LOGICAL_LIBRARY_NAME,TAPE_POOL.TAPE_POOL_NAME AS TAPE_POOL_NAME,VIRTUAL_ORGANIZATION.VIRTUAL_ORGANIZATION_NAME AS VO,TAPE.ENCRYPTION_KEY_NAME AS ENCRYPTION_KEY_NAME,MEDIA_TYPE.CAPACITY_IN_BYTES AS CAPACITY_IN_BYTES,TAPE.DATA_IN_BYTES AS DATA_IN_BYTES,TAPE.LAST_FSEQ AS LAST_FSEQ,TAPE.IS_FULL AS IS_FULL,TAPE.IS_FROM_CASTOR AS IS_FROM_CASTOR,TAPE.LABEL_FORMAT AS LABEL_FORMAT,TAPE.LABEL_DRIVE AS LABEL_DRIVE,TAPE.LABEL_TIME AS LABEL_TIME,TAPE.LAST_READ_DRIVE AS LAST_READ_DRIVE,TAPE.LAST_READ_TIME AS LAST_READ_TIME,TAPE.LAST_WRITE_DRIVE AS LAST_WRITE_DRIVE,TAPE.LAST_WRITE_TIME AS LAST_WRITE_TIME,TAPE.READ_MOUNT_COUNT AS READ_MOUNT_COUNT,TAPE.WRITE_MOUNT_COUNT AS WRITE_MOUNT_COUNT,TAPE.USER_COMMENT AS USER_COMMENT,TAPE.TAPE_STATE AS TAPE_STATE,TAPE.STATE_REASON AS STATE_REASON,TAPE.STATE_UPDATE_TIME AS STATE_UPDATE_TIME,TAPE.STATE_MODIFIED_BY AS STATE_MODIFIED_BY,TAPE.CREATION_LOG_USER_NAME AS CREATION_LOG_USER_NAME,TAPE.CREATION_LOG_HOST_NAME AS CREATION_LOG_HOST_NAME,TAPE.CREATION_LOG_TIME AS CREATION_LOG_TIME,TAPE.LAST_UPDATE_USER_NAME AS LAST_UPDATE_USER_NAME,TAPE.LAST_UPDATE_HOST_NAME AS LAST_UPDATE_HOST_NAME,TAPE.LAST_UPDATE_TIME AS LAST_UPDATE_TIME FROM TAPE INNER JOIN TAPE_POOL ON TAPE.TAPE_POOL_ID = TAPE_POOL.TAPE_POOL_ID INNER JOIN LOGICAL_LIBRARY ON TAPE.LOGICAL_LIBRARY_ID = LOGICAL_LIBRARY.LOGICAL_LIBRARY_ID INNER JOIN MEDIA_TYPE ON TAPE.MEDIA_TYPE_ID = MEDIA_TYPE.MEDIA_TYPE_ID INNER JOIN VIRTUAL_ORGANIZATION ON TAPE_POOL.VIRTUAL_ORGANIZATION_ID = VIRTUAL_ORGANIZATION.VIRTUAL_ORGANIZATION_ID WHERE VID IN (:V1,:V2,:V3,:V4,:V5,:V6,:V7,:V8,:V9,:V10,:V11,:V12,:V13,:V14,:V15,:V 16,:V17,:V18,:V19,:V20,:V21,:V22,:V23,:V24,:V25,:V26,:V27,:V28,:V29,: V30,:V31,:V32,:V33,:V34,:V35,:V36,:V37,:V38,:V39,:V40,:V41,:V42,:V43, :V44,:V45,:V46,:V47,:V48,:V49,:V50,:V51,:V52,:V53,:V54,:V55,:V56,:V57 ,:V58,:V59,:V60,:V61,:V62,:V63,:V64,:V65,:V66,:V67,:V68,:V69,:V70,:V7 1,:V72,:V73,:V74,:V75,:V76,:V77,:V78,:V79,:V80,:V81,:V82,:V83,:V84,:V 85,:V86,:V87,:V88,:V89,:V90,:V91,:V92,:V93,:V94,:V95,:V96,:V97,:V98,: V99,:V100) Action Use bigger fetch arrays while fetching results from the SELECT statement with SQL_ID "cd5kkfza769fr". Related Object SQL statement with SQL_ID cd5kkfza769fr. SELECT TAPE.VID AS VID,MEDIA_TYPE.MEDIA_TYPE_NAME AS MEDIA_TYPE,TAPE.VENDOR AS VENDOR,LOGICAL_LIBRARY.LOGICAL_LIBRARY_NAME AS LOGICAL_LIBRARY_NAME,TAPE_POOL.TAPE_POOL_NAME AS TAPE_POOL_NAME,VIRTUAL_ORGANIZATION.VIRTUAL_ORGANIZATION_NAME AS VO,TAPE.ENCRYPTION_KEY_NAME AS ENCRYPTION_KEY_NAME,MEDIA_TYPE.CAPACITY_IN_BYTES AS CAPACITY_IN_BYTES,TAPE.DATA_IN_BYTES AS DATA_IN_BYTES,TAPE.LAST_FSEQ AS LAST_FSEQ,TAPE.IS_FULL AS IS_FULL,TAPE.IS_FROM_CASTOR AS IS_FROM_CASTOR,TAPE.LABEL_FORMAT AS LABEL_FORMAT,TAPE.LABEL_DRIVE AS LABEL_DRIVE,TAPE.LABEL_TIME AS LABEL_TIME,TAPE.LAST_READ_DRIVE AS LAST_READ_DRIVE,TAPE.LAST_READ_TIME AS LAST_READ_TIME,TAPE.LAST_WRITE_DRIVE AS LAST_WRITE_DRIVE,TAPE.LAST_WRITE_TIME AS LAST_WRITE_TIME,TAPE.READ_MOUNT_COUNT AS READ_MOUNT_COUNT,TAPE.WRITE_MOUNT_COUNT AS WRITE_MOUNT_COUNT,TAPE.USER_COMMENT AS USER_COMMENT,TAPE.TAPE_STATE AS TAPE_STATE,TAPE.STATE_REASON AS STATE_REASON,TAPE.STATE_UPDATE_TIME AS STATE_UPDATE_TIME,TAPE.STATE_MODIFIED_BY AS STATE_MODIFIED_BY,TAPE.CREATION_LOG_USER_NAME AS CREATION_LOG_USER_NAME,TAPE.CREATION_LOG_HOST_NAME AS CREATION_LOG_HOST_NAME,TAPE.CREATION_LOG_TIME AS CREATION_LOG_TIME,TAPE.LAST_UPDATE_USER_NAME AS LAST_UPDATE_USER_NAME,TAPE.LAST_UPDATE_HOST_NAME AS LAST_UPDATE_HOST_NAME,TAPE.LAST_UPDATE_TIME AS LAST_UPDATE_TIME FROM TAPE INNER JOIN TAPE_POOL ON TAPE.TAPE_POOL_ID = TAPE_POOL.TAPE_POOL_ID INNER JOIN LOGICAL_LIBRARY ON TAPE.LOGICAL_LIBRARY_ID = LOGICAL_LIBRARY.LOGICAL_LIBRARY_ID INNER JOIN MEDIA_TYPE ON TAPE.MEDIA_TYPE_ID = MEDIA_TYPE.MEDIA_TYPE_ID INNER JOIN VIRTUAL_ORGANIZATION ON TAPE_POOL.VIRTUAL_ORGANIZATION_ID = VIRTUAL_ORGANIZATION.VIRTUAL_ORGANIZATION_ID WHERE VID IN (:V1,:V2,:V3,:V4,:V5,:V6,:V7,:V8,:V9,:V10,:V11,:V12,:V13,:V14,:V15,:V 16,:V17,:V18,:V19,:V20,:V21,:V22,:V23,:V24,:V25,:V26,:V27,:V28,:V29,: V30,:V31,:V32,:V33,:V34,:V35,:V36,:V37,:V38,:V39,:V40,:V41,:V42,:V43, :V44,:V45,:V46,:V47,:V48,:V49,:V50,:V51,:V52,:V53,:V54,:V55,:V56,:V57 ,:V58,:V59,:V60,:V61,:V62,:V63,:V64,:V65,:V66,:V67,:V68,:V69,:V70,:V7 1,:V72,:V73,:V74,:V75,:V76,:V77,:V78,:V79,:V80,:V81,:V82,:V83,:V84,:V 85,:V86,:V87,:V88,:V89,:V90,:V91,:V92,:V93,:V94,:V95,:V96,:V97,:V98,: V99,:V100) Rationale The SQL spent 97% of its database time on CPU, I/O and Cluster waits. This part of database time may be improved by the SQL Tuning Advisor. Rationale Database time for this SQL was divided as follows: 99% for SQL execution, 1% for parsing, 0% for PL/SQL execution and 0% for Java execution. Rationale SQL statement with SQL_ID "cd5kkfza769fr" was executed 205990 times and had an average elapsed time of 0.0036 seconds. Finding 2: Commits and Rollbacks Impact is .23 active sessions, 2.04% of total activity. ------------------------------------------------------- Waits on event "log file sync" while performing COMMIT and ROLLBACK operations were consuming significant database time. Recommendation 1: Application Analysis Estimated benefit is .23 active sessions, 2.04% of total activity. ------------------------------------------------------------------ Action Investigate application logic for possible reduction in the number of COMMIT operations by increasing the size of transactions. Rationale The application was performing 8432 transactions per minute with an average redo size of 618 bytes per transaction. Recommendation 2: Host Configuration Estimated benefit is .23 active sessions, 2.04% of total activity. ------------------------------------------------------------------ Action Investigate the possibility of improving the performance of I/O to the online redo log files. Rationale The average size of writes to the online redo log files was 0 K and the average time per write was 0 milliseconds. Rationale The total I/O throughput on redo log files was 148 K per second for reads and 290 K per second for writes. Rationale The redo log I/O throughput was divided as follows: 0% by RMAN and recovery, 66% by Log Writer, 0% by Archiver, 0% by Streams AQ and 33% by all other activity. Symptoms That Led to the Finding: --------------------------------- Wait class "Commit" was consuming significant database time. Impact is .23 active sessions, 2.04% of total activity. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Additional Information ---------------------- Miscellaneous Information ------------------------- Wait class "Application" was not consuming significant database time. Wait class "Cluster" was not consuming significant database time. Wait class "Concurrency" was not consuming significant database time. Wait class "Configuration" was not consuming significant database time. CPU was not a bottleneck for the instance. Wait class "Network" was not consuming significant database time. Wait class "User I/O" was not consuming significant database time. The network latency of the cluster interconnect was within acceptable limits of 1 milliseconds. Session connect and disconnect calls were not consuming significant database time. Hard parsing of SQL statements was not consuming significant database time.