Authors: Bipin Chandra, Joice Joy, Sreejith Nair
In this article, it is explained as how to monitor an OracleDB through Instana and review some of the main performance metrics. The OracleDB Instana sensor connects to the database and pulls the required metrics. It supports both local monitoring and remote monitoring.
Prerequisites
1. To make the sensor connect to Oracle and monitor the metrics and configuration, you need to have the read permission for the following tables:
| V_$SESSION, V_$BGPROCESS, V_$ACTIVE_SESSION_HISTORY, V_$SYSSTAT, V_$SGASTAT, V_$LIBRARYCACHE, V_$LATCH, V_$ROWCACHE, V_$RESOURCE_LIMIT, V_$STATNAME, V_$SESSTAT, V_$PROCESS, V_$SQLAREA, V_$PARAMETER, V_$SQL, V_$VERSION, V_$SYS_TIME_MODEL, V_$SYSTEM_WAIT_CLASS, V_$INSTANCE, V_$LOCK, DBA_HIST_SQLTEXT, DBA_HIST_SQLSTAT, DBA_HIST_SNAPSHOT, DBA_HIST_ACTIVE_SESS_HISTORY, DBA_USERS, DBA_OBJECTS, DBA_TABLESPACE_USAGE_METRICS, DBA_DATA_FILES |
2. To set the read permission, follow these steps:
a. Create a role
eg. create ROLE INSTANAROLE;
b. Grant privileges to this role for above-mentioned tables and views
eg. GRANT SELECT ON V_$SESSION to INSTANAROLE;
c. Assign this role to a user. The same user needs to be configured in the configuration yaml. For more information, see configuration section.
eg. GRANT INSTANAROLE to <user>;
d. Instana supports local and remote monitoring of OracleDb. In the agent configuration file ‘<agent_install_dir>/etc/instana/configuration.yaml’ you need to provide the necessary configuration details.
Local monitoring
Figure 1 - OralceDB Local Monitoring Architecture
Local monitoring is preferred when the OracleDB instance and the agent reside in the same host. The agent auto discovers the databaseSid from the locally running OracleDB process and you need to provide the db credentials (user and password), port, and poll_rate to complete the configuration.
Local monitoring configurations
Remote monitoring
Figure 2 - OralceDB Remote Monitoring Architecture
If the agent cannot be installed in the host where OracleDB is running it is preferring to use remote monitoring. You need to provide the host, port, credentials (user and password), databaseSid or databaseServiceName, availabilityZone, and poll_rate. Multiple databases are allowed to monitor by adding entries under remote tag.
Remote monitoring configurations
OracleDB dashboard screens:
Figure 3 - Process Utilization details
-
Running Process Count - The max utilization, current, Initial allocation, and Limit value of process.
Figure 4 - Total number of Process
-
Buffer Cache - The percentage of entries in the library cache that were parsed more than once (reloads) over the lifetime of the instance.
-
Execute/No Parse - The percentage of statement executions that do not require a corresponding parse.
-
Memory Sort - The percentage of sort efficiency (from ORDER BY clauses or index building) that are done to disk versus in-memory.
-
SQL Area Get Hit Rate - The library cache hit ratio describes the frequency with which a matching SQL statement is found in the shared pool when a SQL parse request is issued by a session.
-
Latch Hit Ratio - The ratio of the total number of latch misses to the number of latches gets for all latches. A low value for this ratio indicates a latching problem, whereas a high value is good.
Figure 5 - Library Cache Hit Ratio details as Chart
Figure 6 - System Global Area(SGA) Memory details as Chart
When the table is expanded, the memory that is used is represented as a graph.
Figure 7 - SGA Pools details as Chart inside Table
Figure 8 - SQL details as chart
-
Tablespaces details - A tablespace in an OracleDB consists of one or more physical data files. A data file can be associated with only one tablespace and only a database. Inside this one it shows the name, ssed space, max size, used percent and auto-extending details about the each tablespace.
Figure 9 - Tablespaces details as Chart inside Table
Figure 10 - Top 10 CPU Consuming Sessions as Table
We hope you found this article informative. If you would like to find out more about the benefits of using OracleDB Instana sensor, see the Instana documentation.
#EUM