| Metric Group | SQL Query | Affected Metrics |
|---|---|---|
cdb_datafiles_offline |
SELECT sum(CASE WHEN ONLINE_STATUS IN ('ONLINE', 'SYSTEM','RECOVER') THEN 0 ELSE 1 END) AS "CDB_DATAFILES_OFFLINE" , TABLESPACE_NAME FROM dba_data_files WHERE TABLESPACE_NAME IN ('%') GROUP BY TABLESPACE_NAME |
tablespace.offlineCDBDatafiles |
db_id_instance_metric |
SELECT t1.INST_ID, t2.DBID FROM (SELECT INST_ID FROM gv$instance) t1, (SELECT DBID FROM v$database) t2 |
dbID |
db_id_tablespace_metric |
SELECT t1.TABLESPACE_NAME, t2.DBID FROM (SELECT TABLESPACE_NAME FROM DBA_TABLESPACES) t1, (SELECT DBID FROM v$database) t2 |
dbID |
global_name_instance_metric |
SELECT t1.INST_ID, t2.GLOBAL_NAME FROM (SELECT INST_ID FROM gv$instance) t1, (SELECT GLOBAL_NAME FROM global_name) t2 |
globalName |
global_name_tablespace_metric |
SELECT t1.TABLESPACE_NAME, t2.GLOBAL_NAME FROM (SELECT TABLESPACE_NAME FROM DBA_TABLESPACES) t1, (SELECT GLOBAL_NAME FROM global_name) t2 |
globalName |
locked_accounts |
SELECT INST_ID, LOCKED_ACCOUNTS FROM (SELECT count(1) AS "LOCKED_ACCOUNTS" FROM cdb_users a, cdb_pdbs b WHERE a.con_id = b.con_id AND username IN ('SYS', 'SYSTEM', 'DBSNMP') AND a.account_status != 'OPEN') l, gv$instance i |
lockedAccounts |
oracleLongRunningQueries |
SELECT inst_id, sum(num) AS total FROM (( SELECT i.inst_id, 1 AS num FROM gv$session s, gv$instance i WHERE i.inst_id=s.inst_id AND s.status='ACTIVE' AND s.type <>'BACKGROUND' AND s.last_call_et > 60 GROUP BY i.inst_id ) UNION ( SELECT i.inst_id, 0 AS num FROM gv$session s, gv$instance i WHERE i.inst_id=s.inst_id)) GROUP BY inst_id |
longRunningQueries |
pdb_datafiles_offline |
SELECT sum(CASE WHEN ONLINE_STATUS IN ('ONLINE', 'SYSTEM','RECOVER') THEN 0 ELSE 1 END) AS "PDB_DATAFILES_OFFLINE" , a.TABLESPACE_NAME FROM cdb_data_files a, cdb_pdbs b WHERE a.con_id = b.con_id AND TABLESPACE_NAME IN ('%') GROUP BY TABLESPACE_NAME |
tablespace.offlinePDBDatafiles |
pdb_non_write |
SELECT TABLESPACE_NAME, sum(CASE WHEN ONLINE_STATUS IN ('ONLINE', 'SYSTEM','RECOVER') THEN 0 ELSE 1 END) AS "PDB_NON_WRITE_MODE" FROM cdb_data_files a, cdb_pdbs b WHERE a.con_id = b.con_id AND TABLESPACE_NAME IN ('%') GROUP BY TABLESPACE_NAME |
tablespace.pdbDatafilesNonWrite |
pga_metrics |
SELECT INST_ID, NAME, VALUE FROM gv$pgastat | memory.pgaInUseInBytes memory.pgaAllocatedInBytes memory.pgaFreeableInBytes memory.pgaMaxSizeInBytes |
read_write_metrics |
SELECT INST_ID, SUM(PHYRDS) AS "PhysicalReads", SUM(PHYWRTS) AS "PhysicalWrites", SUM(PHYBLKRD) AS "PhysicalBlockReads", SUM(PHYBLKWRT) AS "PhysicalBlockWrites", SUM(READTIM) * 10 AS "ReadTime", SUM(WRITETIM) * 10 AS "WriteTime" FROM gv$filestat GROUP BY INST_ID |
disk.reads disk.writes disk.blocksRead disk.blocksWritten disk.readTimeInMilliseconds disk.writeTimeInMilliseconds |
redo_log_waits |
SELECT sysevent.total_waits, inst.inst_id, sysevent.event FROM GV$SYSTEM_EVENT sysevent, GV$INSTANCE inst WHERE sysevent.inst_id=inst.inst_id |
redoLog.waits redoLog.logFileSwitch redoLog.logFileSwitchCheckpointIncomplete redoLog.logFileSwitchArchivingNeeded sga.bufferBusyWaits sga.freeBufferWaits sga.freeBufferInspected |
rollback_segments |
SELECT SUM(stat.gets) AS gets, sum(stat.waits) AS waits, sum(stat.waits)/sum(stat.gets) AS ratio, inst.inst_id FROM GV$ROLLSTAT stat, GV$INSTANCE inst WHERE stat.inst_id=inst.inst_id GROUP BY inst.inst_id |
rollbackSegments.gets rollbackSegments.waits rollbackSegments.ratioWait |
sga |
SELECT sga.name, sga.value,inst.inst_id FROM GV$SGA sga, GV$INSTANCE inst WHERE sga.inst_id=inst.inst_id |
sga.fixedSizeInBytes sga.redoBuffersInBytes |
sga_hit_ratio |
SELECT inst.inst_id,(1 - (phy.value - lob.value - dir.value)/ses.value) as ratio FROM GV$SYSSTAT ses, GV$SYSSTAT lob, GV$SYSSTAT dir, GV$SYSSTAT phy, GV$INSTANCE inst WHERE ses.name='session logical reads' AND dir.name='physical reads direct' AND lob.name='physical reads direct (lob)' AND phy.name='physical reads' AND ses.inst_id=inst.inst_id AND lob.inst_id=inst.inst_id AND dir.inst_id=inst.inst_id AND phy.inst_id=inst.inst_id |
sga.hitRatio |
sga_log_alloc_retries |
SELECT (rbar.value/re.value) as ratio, inst.inst_id FROM GV$SYSSTAT rbar, GV$SYSSTAT re, GV$INSTANCE inst WHERE rbar.name like 'redo buffer allocation retries' AND re.name like 'redo entries' AND re.inst_id=inst.inst_id AND rbar.inst_id=inst.inst_id |
sga.logBufferAllocationRetriesRatio |
sga_log_buffer_space_waits |
SELECT count(wait.inst_id) as count,inst.inst_id FROM GV$SESSION_WAIT wait, GV$INSTANCE inst WHERE wait.event like 'log buffer space%' AND inst.inst_id=wait.inst_id GROUP BY inst.inst_id |
sga.logBufferSpaceWaits |
sga_shared_pool_dict_cache_ratio |
SELECT (SUM(rcache.getmisses)/SUM(rcache.gets)) as ratio,inst.inst_id FROM GV$rowcache rcache, GV$INSTANCE inst WHERE inst.inst_id=rcache.inst_id GROUP BY inst.inst_id |
sga.sharedPoolDictCacheMissRatio |
sga_shared_pool_library_cache_hit_ratio |
SELECT libcache.gethitratio as ratio,inst.inst_id FROM GV$librarycache libcache, GV$INSTANCE inst WHERE namespace='SQL AREA' AND inst.inst_id=libcache.inst_id |
sga.sharedPoolLibraryCacheHitRatio |
sga_shared_pool_library_cache_reload_ratio |
SELECT (sum(libcache.reloads)/sum(libcache.pins)) AS ratio,inst.inst_id FROM GV$librarycache libcache, GV$INSTANCE inst WHERE inst.inst_id=libcache.inst_id GROUP BY inst.inst_id |
sga.sharedPoolLibraryCacheReloadRatio |
sga_shared_pool_library_cache_sharable_statement |
SELECT SUM(sqlarea.sharable_mem) AS sum,inst.inst_id FROM GV$sqlarea sqlarea, GV$INSTANCE inst WHERE sqlarea.executions > 5 AND inst.inst_id=sqlarea.inst_id GROUP BY inst.inst_id |
sga.sharedPoolLibraryCacheShareableMemoryPerStatementInBytes |
sga_shared_pool_library_cache_shareable_user |
SELECT SUM(250 * sqlarea.users_opening) AS sum,inst.inst_id FROM GV$sqlarea sqlarea, GV$INSTANCE inst WHERE inst.inst_id=sqlarea.inst_id GROUP BY inst.inst_id |
sga.sharedPoolLibraryCacheShareableMemoryPerUserInBytes |
sgauga_total_memory |
SELECT SUM(value) AS sum,inst.inst_id FROM GV$sesstat, GV$statname, GV$INSTANCE inst WHERE name = 'session uga memory max' AND GV$sesstat.statistic#=GV$statname.statistic# AND GV$sesstat.inst_id=inst.inst_id AND GV$statname.inst_id=inst.inst_id GROUP BY inst.inst_id |
sga.ugaTotalMemoryInBytes |
sys_metrics |
SELECT INST_ID, METRIC_NAME, VALUE FROM gv$sysmetric |
memory.bufferCacheHitRatio memory.sortsRatio memory.redoAllocationHitRatio query.transactionsPerSecond query.physicalReadsPerTransaction query.physicalWritesPerTransaction disk.physicalReadsPerSecond query.physicalReadsPerTransaction disk.physicalWritesPerSecond query.physicalWritesPerTransaction disk.physicalLobsReadsPerSecond query.physicalLobsReadsPerTransaction disk.physicalLobsWritesPerSecond query.physicalLobsWritesPerTransaction memory.redoGeneratedBytesPerSecond memory.redoGeneratedBytesPerTransaction db.logonsPerTransaction db.openCursorsPerSecond db.openCursorsPerTransaction db.userCommitsPerSecond db.userCommitsPercentage db.userRollbacksPerSecond db.userRollbacksPercentage db.userCallsPerSecond db.userCallsPerTransaction db.recursiveCallsPerSecond db.recursiveCallsPerTransaction db.logicalReadsPerSecond db.logicalReadsPerTransaction db.dbwrCheckpointsPerSecond db.backgroundCheckpointsPerSecond db.redoWritesPerSecond db.redoWritesPerTransaction db.longTableScansPerSecond db.longTableScansPerTransaction db.totalTableScansPerSecond db.totalTableScansPerTransaction db.fullIndexScansPerSecond db.fullIndexScansPerTransaction db.totalIndexScansPerSecond db.totalIndexScansPerTransaction db.totalParseCountPerSecond db.totalParseCountPerTransaction db.hardParseCountPerSecond db.hardParseCountPerTransaction db.parseFailureCountPerSecond db.parseFailureCountPerTransaction db.cursorCacheHitsPerAttempts disk.sortPerSecond disk.sortPerTransaction db.rowsPerSort db.softParseRatio db.userCallsRatio db.hostCpuUtilization network.trafficBytePerSecond db.enqueueTimeoutsPerSecond db.enqueueTimeoutsPerTransaction db.enqueueWaitsPerSecond db.enqueueWaitsPerTransaction db.enqueueDeadlocksPerSecond db.enqueueDeadlocksPerTransaction db.enqueueRequestsPerSecond db.enqueueRequestsPerTransaction db.blockGetsPerSecond db.blockGetsPerTransaction db.consistentReadGetsPerSecond db.blockChangesPerSecond db.consistentReadGetsPerTransaction db.blockChangesPerTransaction db.consistentReadChangesPerSecond db.consistentReadChangesPerTransaction db.cpuUsagePerSecond db.cpuUsagePerTransaction db.crBlocksCreatedPerSecond db.crBlocksCreatedPerTransaction db.crUndoRecordsAppliedPerSecond db.crUndoRecordsAppliedPerTransaction db.userRollbackUndoRecordsAppliedPerSecond db.userRollbackUndoRecordsAppliedPerTransaction db.leafNodeSplitsPerSecond db.leafNodeSplitsPerTransaction db.branchNodeSplitsPerSecond db.branchNodeSplitsPerTransaction disk.physicalReadIoRequestsPerSecond disk.physicalReadBytesPerSecond db.GcCrBlockRecievedPerSecond db.GcCrBlockRecievedPerTransaction db.GcCurrentBlockReceivedPerSecond db.GcCurrentBlockReceivedPerTransaction db.globalCacheAverageCrGetTime db.globalCacheAverageCurrentGetTime disk.physicalWriteTotalIoRequestsPerSecond memory.globalCacheBlocksCorrupted memory.globalCacheBlocksLost db.currentLogons db.currentOpenCursors db.userLimitPercentage db.sqlServiceResponseTime db.waitTimeRatio db.cpuTimeRatio db.responseTimePerTransaction db.rowCacheHitRatio db.rowCacheMissRatio db.libraryCacheHitRatio db.libraryCacheMissRatio db.sharedPoolFreePercentage db.pgaCacheHitPercentage db.processLimitPercentage db.sessionLimitPercentage db.executionsPerTransaction db.executionsPerSecond db.TransactionsPerLogon db.databaseCpuTimePerSecond disk.physicalWriteBytesPerSecond disk.physicalWriteIoRequestsPerSecond db.blockChangesPerUserCall db.blockGetsPerUserCall db.executionsPerUserCall disk.logicalReadsPerUserCall db.sortsPerUserCall db.tableScansPerUserCall db.osLoad db.streamsPoolUsagePercentage network.ioMegabytesPerSecond network.ioRequestsPerSecond db.averageActiveSessions db.activeSerialSessions db.activeParallelSessions db.backgroundCpuUsagePerSecond db.backgroundTimePerSecond db.hostCpuUsagePerSecond disk.tempSpaceUsedInBytes db.sessionCount |
sysstat |
SELECT (rbar.value/re.value) as ratio, inst.inst_id FROM GV$SYSSTAT rbar, GV$SYSSTAT re, GV$INSTANCE inst WHERE rbar.name like 'redo buffer allocation retries' AND re.name like 'redo entries' AND re.inst_id=inst.inst_id AND rbar.inst_id=inst.inst_id |
sga.logBufferAllocationRetriesRatio |
tablespace_metrics |
SELECT a.TABLESPACE_NAME, a.USED_PERCENT, a.USED_SPACE * b.BLOCK_SIZE AS "USED", a.TABLESPACE_SIZE * b.BLOCK_SIZE AS "SIZE", b.TABLESPACE_OFFLINE AS "OFFLINE" FROM DBA_TABLESPACE_USAGE_METRICS a JOIN ( SELECT TABLESPACE_NAME, BLOCK_SIZE, MAX( CASE WHEN status = 'OFFLINE' THEN 1 ELSE 0 END) AS "TABLESPACE_OFFLINE" FROM DBA_TABLESPACES GROUP BY TABLESPACE_NAME, BLOCK_SIZE ) b ON a.TABLESPACE_NAME = b.TABLESPACE_NAME WHERE a.TABLESPACE_NAME IN ('%s') |
tablespace.spaceConsumedInBytes tablespace.spaceReservedInBytes tablespace.spaceUsedPercentage tablespace.isOffline |