Skip to Main Content
  • Questions
  • Database Resource Manager, v$active_session_history p1text=location p2text=consumer group id

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Sandeep.

Asked: November 29, 2016 - 4:09 pm UTC

Last updated: December 07, 2016 - 2:54 pm UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Hi Tom,
Wish you all the best.

1] I have a 12c database
2] Not created as a CDB
SQL> select cdb from v$database;
CDB
---
NO

3] No resource manager is configured
SQL> SHOW PARAMETER RESOURCE_MANAGER_PLAN
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan string
4] but when I am investigating the time taken for a select query, in the v$active_session_history
1st I see no event, session state is on CPU, 4 digit wait_time and for those values most of the time it is
the p1text=location
p2text = consumer group id

and when I checked the "consumer group id" belongs to OTHER_GROUPS which is mandatory.

Could you please help me understanding, or figuring out how these things are related with each other.

Thanks and Regards,
Sandeep

and Connor said...

Just because *you* dont set a resource plan, doesnt mean that plans will not be used. For example, here's a database on my laptop

SQL> show parameter resource_manager_plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
resource_manager_plan                string


But if look in the alert log, here are some entries from last night

Tue Nov 29 22:00:00 2016
Setting Resource Manager plan SCHEDULER[0x32DA]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
...
...
Wed Nov 30 02:00:00 2016
Closing scheduler window
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter


Our automated tasks and maintenance infrastructure will activate resource manager plans at scheduled times etc.

Hope this helps.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sandeep, November 30, 2016 - 5:13 am UTC

Hi Connor,

Thank you.

Well, for oracle database internal maintenance purpose, use of resource manager is understood.

But why a user query should also be coming under resource manager?

And here it is simply increasing the query execution time, as it is regulating the available CPU. preempting the process from CPU, causing waits to get CPU back in a round-robin fashion. And why should not I use the full capacity of available CPU time when there are no load in the db.

How can I disable this?

Thanks and Regards,
Sandeep
Connor McDonald
November 30, 2016 - 6:07 am UTC

If resource manager is throttling you back, you would normally see related waits for those events:

SQL> select name from v$event_name
  2  where lower(name) like 'res%';

NAME
----------------------------------------------
resmgr:cpu quantum
resmgr:large I/O queued
resmgr:small I/O queued
resmgr:internal state change
resmgr:sessions to exit
resmgr:become active
resmgr:pq queued
Resolution of in-doubt txns
resmgr:internal state cleanup

9 rows selected.



Just because you are in a consumer group does not mean you are necessarily being throttled.

Sandeep, November 30, 2016 - 8:59 am UTC

Hi Connor
Thank you.

So what is the meaning of this scenario

V$active_session_history
p1text=location
p2text=consumer group id
and event is nil
wait_time=1234

Thanks and Regards,
Sandeep

Connor McDonald
December 01, 2016 - 12:50 am UTC

WAIT_TIME in ASH is equivalent to WAIT_TIME from V$SESSION_WAIT, ie (from the docs)

"If the session is currently waiting, then the value is 0.

If the session is not in a wait, then the value is as follows:
> 0 - Value is the duration of the last wait in hundredths of a second
-1 - Duration of the last wait was less than a hundredth of a second
-2 - Parameter TIMED_STATISTICS was set to false"

So if your state is ON CPU, then it has been 1234 hundreths *since* the last wait was encountered.

Sandeep, December 01, 2016 - 3:27 am UTC

Hi Connor,

Thank You.

Question-1
---------------
When the session is ON CPU, what does P1 , P2 and P3 mean?

Question-2
---------------
When the session is ON CPU, wait time is the time for which the session waited before getting CPU to run. If this interpretation is correct, then where the wait time and event are recorded, for which the session waited before it got cpu to run?
OR
It is the wait for the CPU itself? And after the wait time it got the CPU to run ?

Question-3
---------------
If P1 is location
&
P2 is consumer group id
Then what does it mean ?

Could you please help answering the above questions?

Thanks and Regards,
Sandeep
Connor McDonald
December 07, 2016 - 2:54 pm UTC

They reflect the previous wait. eg I did this

SQL> declare
  2    v number;
  3  begin
  4    select x into v from t for update;
  5    for i in 1 .. 10000000 loop
  6     v := sqrt(i);
  7    end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.


but in another session I had locked the row in table t, so it will be stuck on row 4 for a while first. Then when I committed, the plsql will burn a lot of cpu. Here's what ASH looked like:

select event, session_state, p1, p2
from v$active_session_history
where session_id = 367
order by sample_time;


EVENT                                                            SESSION         P1         P2
---------------------------------------------------------------- ------- ---------- ----------
enq: TX - row lock contention                                    WAITING 1415053318     655382
enq: TX - row lock contention                                    WAITING 1415053318     655382
enq: TX - row lock contention                                    WAITING 1415053318     655382
enq: TX - row lock contention                                    WAITING 1415053318     655382
enq: TX - row lock contention                                    WAITING 1415053318     655382
enq: TX - row lock contention                                    WAITING 1415053318     655382
enq: TX - row lock contention                                    WAITING 1415053318     655382
enq: TX - row lock contention                                    WAITING 1415053318     655382
                                                                 ON CPU  1415053318     655382
                                                                 ON CPU  1415053318     655382
                                                                 ON CPU  1415053318     655382





You can see that it went onto CPU but kept the p1/p2 info

More to Explore

Performance

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