Learning is not a spectator sport

I’ve posted before on a few occasions about the importance of understanding the different services provided when you connect to the autonomous database. For example, the difference between TP and TP_URGENT is not just a difference in priority in terms of resource consumption – it also impacts the allowed usage of parallel facilities.

Even more important is understanding the difference between LOW and MEDIUM and HIGH services on autonomous database. Whilst the names might suggest simply an increase in “horsepower” available to your session, it may change the behaviour of scripts that you run. Here’s a quick cut/paste from that previous blog post to illustrate.

SQL> set echo on
SQL> connect xxx/xxx@myservice_low
Connected.
SQL> delete from t where rownum <= 1000;

1,000 rows deleted.

SQL> delete from t where rownum <= 1000;

1,000 rows deleted.

I might think to myself “Hmmm…I want to make sure that these deletes get a larger slice of the pie, so I’ll use the MEDIUM service instead”. Let’s see what happens when I do that.

SQL> connect xxx/xxx@myservice_medium
Connected.
SQL> delete from t where rownum <= 1000;

1,000 rows deleted.

SQL> delete from t where rownum <= 1000;

Error starting at line : 5 in command -
delete from t where rownum <= 1000
Error report -
ORA-12838: cannot read/modify an object after modifying it in parallel

As per the previous posts, all of these behaviours are well covered in the standard docs, so as always, I highly recommend you check them out.

But it does raise the question – How do you control which service someone chooses to connect to? After all, if a person has a TNS entry on their PC for the LOW service, and they have a rudimentary understanding of the autonomous services, they could easily create their own MEDIUM or HIGH entry and connect with that.

In the past, an option you could explore was to dynamically change their consumer group once they connect. I also covered that previously, but as I stressed in that post:

Important Note: Temporarily changing the consumer group is not equivalent to connecting to a different service.

because there is a lot more going on with each service than just the consumer group that your session is obtaining resources from. Similarly,

Alternatively you could possibly create a LOGON trigger for the user account to check which service was being used, and if it was not an appropriate one, then raise an error in the trigger. This prevents the wrong usage of the service, but does so at the expense of a friendly user experience.

However, there is now a friendlier way of doing this. Your autonomous database now has the CS_SESSION package which allows you to completely switch services.

SQL> desc CS_SESSION

PROCEDURE SWITCH_SERVICE
 Argument Name
 ---------------------------
 SERVICE_NAME

Now with a LOGON trigger, you can ensure that people access only the services they are meant to. More information on that package here

Shout out to Nagendra for bringing the package to my attention.