Oracle performance metricset

edit

Oracle performance metricset

edit

performance Metricset includes performance related events that might be correlated between them. It contains mainly cursor and cache based data and can generate 3 types of events.

Required database access

edit

To ensure that the module has access to the appropriate metrics, the module requires that you configure a user with access to the following tables:

  • V$BUFFER_POOL_STATISTICS
  • v$sesstat
  • v$statname
  • v$session
  • v$sysstat
  • V$LIBRARYCACHE

Description of fields

edit
  • machine: Operating system machine name
  • buffer_pool: Name of the buffer pool in the instance
  • username: Oracle username
  • io_reloads: Reloads / Pins ratio. A Reload is any PIN of an object that is not the first PIN performed since the object handle was created, and which requires loading the object from disk. Pins are the number of times a PIN was requested for objects of this namespace.
  • lock_requests: Average of the ratio between gethits and gets being Gethits the number of times an object’s handle was found in memory and gets the number of times a lock was requested for objects of this namespace.
  • pin_requests: Average of all pinhits/pins ratios being PinHits the number of times all of the metadata pieces of the library object were found in memory and pins the number of times a PIN was requested for objects of this namespace.
  • cache.buffer.hit.pct: The cache hit ratio of the specified buffer pool.
  • cache.physical_reads: Physical reads
  • cache.get.consistent: Consistent gets statistic
  • cache.get.db_blocks: Database blocks gotten
  • cursors.avg: Average cursors opened by username and machine
  • cursors.max: Max cursors opened by username and machine
  • cursors.total: Total opened cursors by username and machine
  • cursors.opened.current: Total number of current open cursors
  • cursors.opened.total: Total number of cursors opened since the instance started
  • cursors.parse.real: Real number of parses that occurred: session cursor cache hits - parse count (total)
  • cursors.parse.total: Total number of parse calls (hard and soft). A soft parse is a check on an object already in the shared pool, to verify that the permissions on the underlying object have not changed.
  • cursors.session.cache_hits: Number of hits in the session cursor cache. A hit means that the SQL statement did not have to be reparsed.
  • cursors.cache_hit.pct: Ratio of session cursor cache hits from total number of cursors

Example events

edit

Instance based cursors data:

{
    "@timestamp": "2017-10-12T08:05:34.853Z",
    "event": {
        "dataset": "oracle.performance",
        "duration": 115000,
        "module": "oracle"
    },
    "metricset": {
        "name": "performance"
    },
    "oracle": {
        "performance": {
            "cursors": {
                "opened": {
                    "current": 7,
                    "total": 6225
                },
                "parse": {
                    "real": 1336,
                    "total": 3684
                },
                "session": {
                    "cache_hits": 5020
                },
                "total": {
                    "cache_hit": {
                        "pct": 0.8064257028112449
                    }
                }
            },
            "io_reloads": 0.0013963503027202182,
            "lock_requests": 0.5725039956419224,
            "pin_requests": 0.7780581056654354
        }
    },
    "service": {
        "address": "oracle://sys:passwordlocalhost/ORCLPDB1.localdomain",
        "type": "oracle"
    }
}

Cursor data aggregated by username and machine:

{
    "@timestamp": "2017-10-12T08:05:34.853Z",
    "event": {
        "dataset": "oracle.performance",
        "duration": 115000,
        "module": "oracle"
    },
    "metricset": {
        "name": "performance"
    },
    "oracle": {
        "performance": {
            "cursors": {
                "avg": 0.625,
                "max": 17,
                "total": 25
            },
            "machine": "2ed9ac3a4c3d",
            "username": "Unknown"
        }
    },
    "service": {
        "address": "oracle://sys:passwordlocalhost/ORCLPDB1.localdomain",
        "type": "oracle"
    }
}

Cache data:

{
    "@timestamp": "2017-10-12T08:05:34.853Z",
    "event": {
        "dataset": "oracle.performance",
        "duration": 115000,
        "module": "oracle"
    },
    "metricset": {
        "name": "performance"
    },
    "oracle": {
        "performance": {
            "buffer_pool": "DEFAULT",
            "cache": {
                "buffer": {
                    "hit": {
                        "pct": 0.9510712759136568
                    }
                },
                "get": {
                    "consistent": 358125,
                    "db_blocks": 16195
                },
                "physical_reads": 18315
            }
        }
    },
    "service": {
        "address": "oracle://sys:passwordlocalhost/ORCLPDB1.localdomain",
        "type": "oracle"
    }
}

Fields

edit

For a description of each field in the metricset, see the exported fields section.

Here is an example document generated by this metricset:

{
    "@timestamp": "2017-10-12T08:05:34.853Z",
    "event": {
        "dataset": "oracle.performance",
        "duration": 115000,
        "module": "oracle"
    },
    "metricset": {
        "name": "performance"
    },
    "oracle": {
        "performance": {
            "cursors": {
                "cache_hit": {
                    "pct": 0.8311065930874237
                },
                "opened": {
                    "current": 5,
                    "total": 6886
                },
                "parse": {
                    "real": 1524,
                    "total": 4199
                },
                "session": {
                    "cache_hits": 5723
                }
            },
            "io_reloads": 0.0015732643391367942,
            "lock_requests": 0.5958019475347308,
            "pin_requests": 0.8058170730696832
        }
    },
    "service": {
        "address": "oracle://sys:Oradoc_db1@localhost/ORCLPDB1.localdomain?connectionClass=\u0026poolIncrement=0\u0026poolMaxSessions=0\u0026poolMinSessions=0\u0026sysdba=1\u0026sysoper=0\u0026sysasm=0\u0026standaloneConnection=0\u0026enableEvents=0\u0026heterogeneousPool=0\u0026prelim=0",
        "type": "oracle"
    }
}