How-to configure Oracle Database monitoring
Posted: August 21st, 2008 | Author: TnT Admin | Filed under: How-Tos | Tags: LoadRunner, Monitoring, Oracle | 8 Comments »
Most of the time, with every application, there comes with the Database setup and most of the time, it’s Oracle (being the biggest player in the DB industry). Fundamentally, (1) it requires a Oracle client to be installed on the machine as a native client. (2) A valid account and privileges to the Oracle V$ tables that holds statistics. (3) Ensure that you can properly query from the Controller using the SQL tools and extract data from the V$ table and you should be fine with the setup. In this article, we will be providing the a typical connection to the Oracle database for monitoring (i.e. we will not be focusing on counters directly related for analyzing database performance).
Let’s go through an overview for implementing the Oracle DB. Basically, we are doing in this sequence:
- Get an account in Oracle and access to the V$ tables that contains the monitoring data.
- Install 32-bit Oracle client on the Controller machine.
- Ensuring that proper configuration is done to connect to the DB using TNSNAMES.ora and defining the Oracle path.
- Login to the DB using SQL*PLUS and run a query to see if you can collect the statistics from the V$ tables.
- Launch LoadRunner Controller, configure the monitor and start your load test!
Ok, let’s dive into the details!
1. Request for an account and password to be created in the database instance (unless you are the DBA). Example, loadtester, and grant them access to read the following table. These tables consist of the statistic of the database instance.
- V$SESSTAT
- V$SYSSTAT
- V$STATNAME
- V$INSTANCE
- V$SESSION
2. Ensure that Oracle client libraries are installed on the Controller. Remembered that in order to monitor, you need local installation of the client in order for LoadRunner to query the monitoring data. If you do not have the client libraries, download it from Oracle Downloads. Download only the 32-bit Oracle client.
3. If you have the client installed, ensure the 32-bit Oracle client is installed on the Controller, not the 16-bit client.
4. Verify that %OracleHome%\bin is included in the path environment. This can be done by going to START > My Computer > Properties > “Advanced Tab” > Enviromental Settings.
5. Configure the TNSNAMES.ora file on the Controller. TNSNAMES.ora file is a SQL*Net configuration file that defines databases addresses for establishing connections to them. This file normally resides in the ORACLE_HOME\NETWORK\ADMIN directory. (Source: OraFaq)
Attached is an example of the TNSNAMES.ora file. Click here to download.
6. Ensure login successful with the created account (e.g. loadtester) with SQL*Plus from the Controller machine.
7. Ensure the privileges are given properly by typing the following command. If they don’t return results or return access rights issues, most probably the steps for prior to this one has not been configured properly. Therefore, approach the Database Administrator for assistance or go back to the previous steps to ensure they are properly configured.
- SELECT * FROM V$SESSTAT
- SELECT * FROM V$SYSSTAT
- SELECT * FROM V$STATNAME
- SELECT * FROM V$INSTANCE
- SELECT * FROM V$SESSION
8. Launch LoadRunner Controller as per norm and add the Oracle Database Monitor. When prompt, enter the account name (e.g. loadtester), it’s corresponding password and the destination server name. Statistics should be drawn from the V$ table and displayed in Controller.
With the above, you should be able to successfully configure the monitoring environment and the Controller with minimal difficulty.
Note:
You can change the sampling interval for the monitor using the vmon.cfg file located in {loadrunner-installed-dir}\monitors\vmon.cfg
how to configure and analyse MS SQL Server component? is this help us to find issues with database component? will u plz give me one example?
Will this work with an Oracle RAC setup? More specifically, will this allow you to view stats per node or does it give a total of all nodes?
Hi Chris,
Personally, Ive not tested with Oracle RAC and do not know the capabilities if monitoring is possible on the nodes. You might want to research how the nodes are being monitored from a conventional point of view. If they stored their monitoring data in V$ dynamic views, then most probably it will work. Anyone care to share their experiences on Oracle RAC monitoring?
Hi Sridhar,
MS SQL is part of Windows Permon utility as they are all from the same brand. Make sure you have rights to monitor the Windows Server and TCP139 opened. Refer to “How-to configure Windows System Resource monitoring”. On an architecture (general) level, take note of processor utilization of application servers and database server. If application server has lower processing time and database server having processing time, it may be database server having problems such as expensive SQL. Fundamentally, MS SQL also have memory area, and % hit ratios to meet. Do read up a few of Oracle resources as they will be able to help you further the understanding (search our site for Oracle materials). I sincerely apologize that I do not have MS SQL analyzing materials.
How to monitor custom query on controller graph? Everything is configured properly and I have access to V$ data tables. Where can I add custom query for monitoring. Please give an example if possible.Thanks in advanse.
Hi Roman, You want to refer to the Monitor Reference, under chapter 36, Oracle Monitoring, where there is a small section on configuring the custom query for Oracle. On an overview, you will (a) need to have the privileges to the V$ tables, (b) amend vmon.cfg to include custom queries that made specified queries to the database.
Hi, The system has a web server and a database server. The appliaction send requestd to web server which further goes to database server. Now if the appliaction server is down or we can responding slowly, in that case web server would continue to send request to database server then which graph is used to check the performance of database server.
Hi Naveen, There isn’t any sliver bullet to what graphs to analyze. For a start you may like to look at the Database graphs and pay attention to the Library Cache and Database Buffer Cache. You may want to dwell deeper with the following resources on how to analyze databases (in particularly Oracle).
* Analyzing Shared Pool (Library Cache)
* Analyzing Oracle SGA Database Buffer Cache
* Analyzing Oracle SGA Large Pool
* Analyzing Oracle SGA Java Pool