Skip to Main Content
  • Questions

  • How many context switches my session does?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Zilvinas.

Asked: January 19, 2018 - 6:49 am UTC

Last updated: January 22, 2018 - 3:42 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hello,

My questios is already in subject.
What performance views has information about about PL/SQL and SQL context switches?

SELECT n.Name FROM v$statname n WHERE n.Name LIKE '%context%'

Returns only "OS Voluntary context switches" and "OS Involuntary context switches"

Google does tell nothing either.

Žilvinas

and Connor said...

We don't instrument at the level, so to my knowledge there is not a statistic you can mine for it.

If you have a particular routine in mind that wish to examine, you can use the DBMS_HPROF package to profile it.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adfns/hierarchical-profiler.html#GUID-B2E3A739-08C6-4648-A65F-1D093A0DADDE

If you *really* want to dig into the low level details, Frits Hoogland did a couple of excellent deep diving posts into the topic:

https://fritshoogland.wordpress.com/2016/01/23/plsql-context-switch/

https://fritshoogland.wordpress.com/2016/01/25/plsql-context-switch-part-2/

Rating

  (1 rating)

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

Comments

Just wanted to test

Zilvinas Vidmantas, January 23, 2018 - 10:36 am UTC

Thanks for the answer,

I just wanted to test.
We have 12c Oracle but compatibility set to 11.
And I wanted to test if udf pragma works in our case(functions in with clause do not work).
Test showed that time is better.
I noticed biggest impact on "recursive cpu usage" statistics.

But I noticed other strange thing.
Function works slower if it is placed in big package.
At first I thought that udf pragma works only in stand alone functions. But in new small package it worked as fast as stand alone function. Never thought that pacakge size can infulence execution speed of function. Package is realy not so very huge. Say 15 procedures and about thousand rows in size.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library