Skip to Main Content
  • Questions
  • Maximum number of concurrent sessions in multi instance database

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Syed.

Asked: May 12, 2022 - 11:04 pm UTC

Last updated: May 18, 2022 - 2:46 am UTC

Version: Oracle 12C

Viewed 1000+ times

You Asked

Hi,

We have Oracle 12C on 2 instances. I know GV$license can give maximum number of concurrent sessions since start of instances. But is there a way to get maximum we had accessing the database from both together ?

Syed

and Connor said...

gv$resource_limit will give you this information, plus other things.

Also, there is plenty of good information in gv$sysmetric which breaks this data down into time intervals, along with its historical AWR equivlents:

DBA_HIST_CON_SYSMETRIC_HIST
DBA_HIST_CON_SYSMETRIC_SUMM
DBA_HIST_SYSMETRIC_HISTORY
DBA_HIST_SYSMETRIC_SUMMARY



Rating

  (1 rating)

Comments

will gv$resource_limit give result at same point ?

Syed, May 18, 2022 - 1:06 am UTC

Thanks for the reply. But the view gv$resource_limit, will that not give MAX_UTILIZATION for both instances at different points in time ? or they will both show the number at same point in time ? I am after a query which gives me maximum number of concurrent sessions at any point in the whole database and not in each session independently ?
Connor McDonald
May 18, 2022 - 2:46 am UTC

SQL> select distinct METRIC_NAME from gv$sysmetric
  2  order by 1;

METRIC_NAME
--------------------------------------------------------------
Active Parallel Sessions
Active Serial Sessions
Average Active Sessions
Average Synchronous Single-Block Read Latency
Background CPU Usage Per Sec
Background Checkpoints Per Sec
Background Time Per Sec
Branch Node Splits Per Sec
Branch Node Splits Per Txn
Buffer Cache Hit Ratio
CPU Usage Per Sec
CPU Usage Per Txn
CR Blocks Created Per Sec
CR Blocks Created Per Txn
CR Undo Records Applied Per Sec
CR Undo Records Applied Per Txn
Captured user calls
Cell Physical IO Interconnect Bytes
Consistent Read Changes Per Sec
Consistent Read Changes Per Txn
Consistent Read Gets Per Sec
Consistent Read Gets Per Txn
Current Logons Count
Current OS Load
Current Open Cursors Count
Cursor Cache Hit Ratio
DB Block Changes Per Sec
DB Block Changes Per Txn
DB Block Changes Per User Call
DB Block Gets Per Sec
DB Block Gets Per Txn
DB Block Gets Per User Call
DBWR Checkpoints Per Sec
DDL statements parallelized Per Sec
DML statements parallelized Per Sec
Database CPU Time Ratio
Database Time Per Sec
Database Wait Time Ratio
Disk Sort Per Sec
Disk Sort Per Txn
Enqueue Deadlocks Per Sec
Enqueue Deadlocks Per Txn
Enqueue Requests Per Sec
Enqueue Requests Per Txn
Enqueue Timeouts Per Sec
Enqueue Timeouts Per Txn
Enqueue Waits Per Sec
Enqueue Waits Per Txn
Execute Without Parse Ratio
Executions Per Sec
Executions Per Txn
Executions Per User Call
Full Index Scans Per Sec
Full Index Scans Per Txn
GC CR Block Received Per Second
GC CR Block Received Per Txn
GC Current Block Received Per Second
GC Current Block Received Per Txn
Global Cache Average CR Get Time
Global Cache Average Current Get Time
Global Cache Blocks Corrupted
Global Cache Blocks Lost
Hard Parse Count Per Sec
Hard Parse Count Per Txn
Host CPU Usage Per Sec
Host CPU Utilization (%)
I/O Megabytes per Second
I/O Requests per Second
Leaf Node Splits Per Sec
Leaf Node Splits Per Txn
Library Cache Hit Ratio
Library Cache Miss Ratio
Logical Reads Per Sec
Logical Reads Per Txn
Logical Reads Per User Call
Logons Per Sec
Logons Per Txn
Long Table Scans Per Sec
Long Table Scans Per Txn
Memory Sorts Ratio
Network Traffic Volume Per Sec
Open Cursors Per Sec
Open Cursors Per Txn
PGA Cache Hit %
PQ QC Session Count
PQ Slave Session Count
PX downgraded 1 to 25% Per Sec
PX downgraded 25 to 50% Per Sec
PX downgraded 50 to 75% Per Sec
PX downgraded 75 to 99% Per Sec
PX downgraded to serial Per Sec
PX operations not downgraded Per Sec
Parse Failure Count Per Sec
Parse Failure Count Per Txn
Physical Read Bytes Per Sec
Physical Read IO Requests Per Sec
Physical Read Total Bytes Per Sec
Physical Read Total IO Requests Per Sec
Physical Reads Direct Lobs Per Sec
Physical Reads Direct Lobs Per Txn
Physical Reads Direct Per Sec
Physical Reads Direct Per Txn
Physical Reads Per Sec
Physical Reads Per Txn
Physical Write Bytes Per Sec
Physical Write IO Requests Per Sec
Physical Write Total Bytes Per Sec
Physical Write Total IO Requests Per Sec
Physical Writes Direct Lobs  Per Txn
Physical Writes Direct Lobs Per Sec
Physical Writes Direct Per Sec
Physical Writes Direct Per Txn
Physical Writes Per Sec
Physical Writes Per Txn
Process Limit %
Queries parallelized Per Sec
Recursive Calls Per Sec
Recursive Calls Per Txn
Redo Allocation Hit Ratio
Redo Generated Per Sec
Redo Generated Per Txn
Redo Writes Per Sec
Redo Writes Per Txn
Replayed user calls
Response Time Per Txn
Row Cache Hit Ratio
Row Cache Miss Ratio
Rows Per Sort
Run Queue Per Sec
SQL Service Response Time
Session Count
Session Limit %
Shared Pool Free %
Soft Parse Ratio
Streams Pool Usage Percentage
Temp Space Used
Total Index Scans Per Sec
Total Index Scans Per Txn
Total PGA Allocated
Total PGA Used by SQL Workareas
Total Parse Count Per Sec
Total Parse Count Per Txn
Total Sorts Per User Call
Total Table Scans Per Sec
Total Table Scans Per Txn
Total Table Scans Per User Call
Txns Per Logon
User Calls Per Sec
User Calls Per Txn
User Calls Ratio
User Commits Per Sec
User Commits Percentage
User Limit %
User Rollback Undo Records Applied Per Txn
User Rollback UndoRec Applied Per Sec
User Rollbacks Per Sec
User Rollbacks Percentage
User Transaction Per Sec
VM in bytes Per Sec
VM out bytes Per Sec
Workload Capture and Replay status

161 rows selected.



More to Explore

Performance

Get all the information about database performance in the Database Performance guide.