From the course: Learning Oracle Database 19c

Dynamic performance views (V$) - Oracle Database Tutorial

From the course: Learning Oracle Database 19c

Start my 1-month free trial

Dynamic performance views (V$)

“

- [Instructor] Oracle Dynamic Performance Views give you a view into database instance metrics at a moment in time. They're always up to date. They are used just like tables, so I'll show you some examples of querying some of these views. What exactly are dynamic performance views? They're tables, but are populated and maintained in memory. They are owned by the SYS user and begin with either V$ or GV$ depending on how many instances are running against your database. They are continuously updated when something in memory changes. New sessions, more CPU use, permanent database tables were queries or updated. These views are truly temporary. They reside in memory, begin to populate when the database instance starts, and are gone when the database shuts down. Not until the database is started again do the views get repopulated. Here are the kinds of things that you'll see in a dynamic performance view. Session status, database file state, running job progress, memory usage by session, current system and individual session parameter values and many other database-wide metrics. Here is one of the V$ views you'll work with a lot, V$ instance. It has metrics like database state, startup time, whether the instance is in archive log mode and many more. The query retrieve instance name, server host name, database version and instant status from V$ instance. I can't emphasize enough how dynamic these views are. The V$ view, v$mystat keeps a running total of hundreds of instance metrics in the current session. In the example, I first find out how much DB time my session has used. Next, I get a count of the rows in the data dictionary view, DBA tab columns. Then check the DB time again. Sure enough, I used another chunk of database resources to retrieve that row count. You don't have to memorize every V$ View name, but you'll get to know a subset of them very well since they provide an instantaneous view of what's going on in the database and that's key to keeping your database running at top efficiency.

Contents