Dynamic Performance Views in Oracle

Dynamic Performance Views in Oracle Database

In this article, I am going to discuss Dynamic Performance Views in Oracle Database. Please read our previous article where we discussed Common Users vs Local Users in Oracle. Most of the time it is called v$ views because it starts with V$. We have many numbers of V$ views. We cannot distinguish each and every view separately. We will try to understand what is the meaning of v$views.

Dynamic Performance Views in Oracle:

Dynamic Performance views are the set of underlying views that are maintained by the server and accessible to the database administrator user SYS is called Dynamic performance views because they are updated continuously while the database is open and in use. It is the same data dictionary tables but the major difference is the view keeps on updating when required. Their contents relate mainly to the performance of the database. Dynamic performance views are identified by the prefix V_$. But the public synonyms for these views have the prefix V$. the dynamic performance views are managed by the enterprise manager and Oracle Trace, which is the primary interface for accessing information about system performance. The Enterprise manager is a very powerful tool to manage the database.

Note: Once the instance is started the V$ views that read from memory are accessible. Views that read data from the disk require that the database be mounted. This indicates we have two types of views. Some of them read from the memory and some of them read from the disk for the performance of the database.

Exercise on Oracle Dynamic Performance Views:

Step 1: Connect to the pluggable database.

Make sure the server and the database are up and running fine. Open the SQL Developer and connect to the database as an SYS user.

Check the database name.
Command: show con_name

Make sure to open and connect to the pluggable database because we have created the hr user and tables in the same pluggable database.

Now, the pluggable database is open and we will move to the pluggable database.

Step 2: Checking if there are any locked objects present in the database.

We have a query to check the locked tables and objects in the database. There is a view called v$locked_object view which shows if there are any tables or objects that are locked.

Query:

Select oracle_username,
os_user_name,
Locked_mode,
Object_name,
Object_type from v$locked_object a, dba_objects b where a.object_id=b.object_id;

We have joined two data dictionaries between v$locked_object and the dba_objects because the view v$locked_object does not contain the object name. Let us try to execute this query and see the output.

So, we did not find any locks present in the database because there is nothing running in the database. Let us make a change to the database like updating rows or deleting rows and try again.

Step 3: Updating any row in the table

We have created a sample schema named hr in the database. We will take a table from the user hr and select a row and make changes. We need a query to extract the rows from the table.

Query: select * from hr.employees;

We can see there are many columns and rows present inside the employees table of the hr schema. We will select any row or any employee and try to update any record of the selected employee.

We have selected john user and we will try to update his salary column. We will be using his employee_id column to select and write the update statement.

Query: Update hr.employees Set salary=salary+2000 Where employee_id=110;

Now it shows that 1 row is updated. It shows that row 110 has been updated. So, no other user can make any changes to this row because the current session is locked. This row is locked because we did not commit the transaction. If we commit the transaction then the row will be unlocked.

Step 4: Checking for the locked objects

After updating the employees, we will check whether we can see these locked tables in the dynamic performance view v$locked_object. Let’s run the same query again to check the status of the locked object.

Query: Select oracle_username, os_user_name, Locked_mode, Object_name, Object_type from v$locked_object a, dba_objects b where a.object_id=b.object_id;

We can see the output there is a result. The result shows that the OS_USERNAME as SYS has locked a table called EMPLOYEES and the object_type as TABLE. The LOCKED_MODE is 3 which means it is a partial lock. The table is not completely locked. We have specific rows that are locked. We understood that the data in v$views will be changed whenever there is a change in the table or any other object.

Step 5: Roll back and check the lock status.

We will roll back the entire change that we have done and let us see what will change to the V$locked_object view.

So, rollback is done. This indicates that all the changes that we have made are reverted back to original values. Let us have a look at the status of the view V$locked_object

We could see there are no results found. So, this indicates that there are no locked objects present in the database. It contains data because of changes in the database and some there will be no data when there is nothing happening in the database.

In the next article, I am going to discuss Data Dictionary Differences inside a Pluggable Database. Here, in this article, I try to explain Dynamic Performance Views in Oracle and I hope you enjoy this Dynamic Performance Views in Oracle Database article.