Pythian Blog: Technical Track

Oracle service secrets: group and filter

by Pythian Marketing on Aug 25, 2016 12:00:00 AM

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. [youtube https://www.youtube.com/watch?v=Ka_m7Tsa7sg&w=560&h=315]

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. 
ALTER SYSTEM SET SERVICE_NAMES='DEMO_OLTP,DEMO_BATCH';
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/oracle@192.168.78.101/DEMO_OLTP.PYTHIAN.COM
 ./sqlcl/bin/sql brost/oracle@192.168.78.101/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$SESSION 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 V$SERVICEMETRIC V$SERVICEMETRIC_HISTORY 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: V$ACTIVE_SESSION_HISTORY DBA_HIST_ACTIVE_SESS_HISTORY DBA_HIST_SERVICE_STAT DBA_HIST_SERVICE_WAIT_CLASS

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.

snapper

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 ( https://blog.tanelpoder.com ) - 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

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?

tl;dr

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!

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.