Oracle service secrets: group and filter

Posted in: Oracle, Technical Track

At the KSCOPE16 conference in Chicago I recorded a two minute tech-tip with Bob Rhubart of OTN about adding services to your Oracle database to increase manageability and performance. You can watch the video here now and I will follow this up with a series of small blog posts about all of the great things that services enable you to do but that I rarely see being used in the real world.


What are services?

In their simplest form, services are just a logical entity that is passed along with the connection string that clients use to connect to a database. Think of them like a tag that you can attach to connections. Services have to be created or registered with a database either through the srvctl (for cluster databases) or DBMS_SERVICES or the instance parameter SERVICE_NAMES on single instances. In this simple example I am using an 11g single instance database and create two new services called DEMO_BATCH and DEMO_OLTP on this.


And now I can use one of those services (and the db_name suffix) to connect to this database. In this example I use sqlcl to connect to the same database using two different service names.

./sqlcl/bin/sql brost/[email protected]/DEMO_OLTP.PYTHIAN.COM
./sqlcl/bin/sql brost/[email protected]/DEMO_BATCH.PYTHIAN.COM

You could now use this to create services like this for OLTP, Batch, reporting, backend, frontend and so on and reconfigure clients and application servers to use a specific service in the connection string. But you might be asking what’s in it for you and what the benefits of this are.

Group and Filter to Command and Conquer

The first thing to note is that by using a service name to connect to a database you have passed some information to the instance and this will show up in various places including dynamic performance views where you can use the service name to filter or group by.

Interested in how many connections there are per service? easy:

select service_name, count(*) from gv$session group by service_name;

You can find a service_name column in these dynamic performance views:

V$SERVICE_EVENT – top events by service
V$SERVICE_WAIT_CLASS – wait classes by service
V$SERVICE_STATS – like v$sesstat but grouped by service

And of course also in the corresponding ASH and AWR tables where you might have to join SERVICE_HASH against V$ACTIVE_SERVICES or DBA_HIST_SERVICE_NAME:


tools support for services

It doesn’t stop with some dictionary views and sql scripts. Look at your favourite GUI tuning tool. It probably has a grouping or filtering by service as well, here are some examples:

AWR reports

awr_service_statsOf course AWR reports include sections about services, allowing you to dig into database time and wait events by service. The picture shows time spent by service, and another report (not shown here) breaks this down further by wait event and times waited.

Enterprise Manager

EM has many references to services. Starting with the database overview picture where the default shows wait classes but can be changed to services, same as on the “performance home” page. The Top Activity and Top Consumers. You will have a hard time navigating the performance pages of EM12c or EM13c and not seeing services all over these pages.


SQL> @snapper ash=service_name+wait_class 5 1 all
Sampling SID all with interval 5 seconds, taking 1 snapshots...

-- Session Snapper v4.15 - by Tanel Poder ( ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)

Active% | SERVICE_NAME              | WAIT_CLASS
   100% | DEMO_BATCH                | Application
    47% | DEMO_OLTP                 | ON CPU
    40% | DEMO_OLTP                 | User I/O
    30% | SYS$BACKGROUND            | System I/O
    14% | DEMO_OLTP                 | Commit
     5% | SYS$BACKGROUND            | ON CPU
     2% | DEMO_OLTP                 | Concurrency

--  End of ASH snap 1, end=2016-08-17 13:28:07, seconds=5, samples_taken=42


tunas360 services pie

You haven’t heard of tunas360 yet? Think of it like the lovechild of snapper and edb360. It samples v$session for 10 minutes (by default) and creates an easy to digest html output. And since it only queries v$session, there is no need to license diag&tuning and you can even run tunas360 on standard edition databases. Do I even have to mention that edb360 also includes ASH aggregates by service?


In troubleshooting and reporting it can be very useful to group or filter by parts of the application, client or other parameters. You can easily assign such tags to connections by using services.

Want to hear more? Follow this blog for other parts in this series or find me talking about services at the following conferences:

  • Oracle OpenWorld: EOUC Database ACES Share Their Favorite Database Things
  • DOAG Konferenz: November 15, Services nutzen!


Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Björn Rost is an Oracle ACE Director, and one of Pythian’s top Oracle experts. A popular presenter, he travels the world attending technology conferences, sharing insights, and learning with his wide network of peers. Björn also serves as president of IOUG’s RAC special interest group. He is always challenging himself, personally through physical activities including triathlons, and professionally through his ongoing quest to increase his knowledge of Oracle and other leading technologies.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *