DBaaS is Fine in Most Cases.

TL;DR: For 99% of use-cases a DBaaS (autonomous...), an RDS- or an MPV-service is sufficient. But in case I want to investigate further, I am still very grateful to Oracle for exposing the V$ and  X$ views. They allow me to inspect, understand, and troubleshoot my system at the next level

This blog post is partly in reaction to...

https://twitter.com/samokhvalov/status/1678786986763755521

Firstly, I agree with Nikolay.
And when you need "backend access" or "root-level-control" on the system where you database is running: please build+manage your own servers. I understand the desire for control and access. It is the only way a real techie will "feel safe". But realise that when you have all that access: with great power comes great responsibility: You will have to secure, manage and maintain your platform yourself. Not all organisations are willing to do so, and even less are capable of attracting + retaining staff with the desired level of competence.
(and did I mention "Documentation" ??)


But the world is moving on....
And for the rest of us, there are the "managed services" like RDS and other MPVs. And we have to accept that those come with some restrictions.

For me, it took a while to get used to it, but now I'm ok with the RDS "level of service". For the following (nitpickity) reasons: 

1. My customers often dont want to be bothered with the details of DBA-work. And they definitely dont want to deal with Oracle-sales ever again. RDS is the best example I know that "takes away the worries" of mundane DBA-tasks and liberates us from the sales-vampires.

2. A good RDBMS (Oracle, and to some extend Postgres) will expose enough of its functioning via SQL-interface so that I can diagnose problems.

3. As an application-developer, software provider, I will also try to limit my software and its complexity to avoid getting into situations where I "need backend access".

4. A good provider (Oracle, RDS) will have + provided assistance if needed.  (this is not the place to write another long rant about the support-staff who are driven by the need to Close Tickets, rather than to solve problems, but you know who you are...)

To the RDBMS-vendors, if they care to listen, I would say : please expose your system, all of it, via the SQL-interface. The rules of Codd still apply, and Rule-4 is Especially applicable here. 

SQL is Everything, and Everything is SQL...

And please provide the data via SQL. That way your customers / developers / users can find their data, and combine it in any way they see fit. Running graphs, dashboards with metrics-over-time are very useful, but at some point some geeky cstmer will want to query for himself and combine data in ways your dashboard didnt think of yet... Please Give them that SQL..


To the providers of managed services (RDS, other MPVs), I would say: Expose! Provide as much dashboards and metrics as the RDBMS can expose. And provide the data via SQL: that way the consumer is free to select/slice/dice/present the data in any way they think they need.

The History of a Query

 TL;DR: The dba_hist_sqlstat view can tell you what has changed in the frequency or in the execution plan of a given query.

We have this dashboard-query that suddenly became very slow. The qry is a sum (something) to indicate "how much work was done". Normally it uses an index and is lighting fast. This morning it seem to have suddenly slowed down.

Let's dig in and use this as an example on "How to Investigate".

We know what the query looks like, and using the v$sqlarea, we can find the SQL_ID: 7292jtjypdyvt.

Next is the DBA_HIST_SQLSTAT : this can tell you how an SQL performed over the snapshot-intervals. 

If you are using "statspack", you will find similar information in STATS$SQL_SUMMARY, but you will need to subtract the preceding value to find the difference since the previous snapshot.

We start with this:

select * from dba_hist_sqlstat  sq

where sq.sql_id = '7292jtjypdyvt'

order by sq.snap_id ;

Since v12, the DBA_HIST...  views contain "delta" columns which save you the effort of subtracting.  

And we need to join to the snapshot view to find at what time each snapshot was taken. The query evolves into something like this:

select to_char ( sn.end_interval_time , 'DDMON HH24:MI') as Time

, sq.executions_delta     execs

, sq.buffer_gets_delta    buff_gets

, rows_processed_delta nr_rows

-- , sq.*

from dba_hist_sqlstat  sq

, dba_hist_snapshot sn

where sq.sql_id = '7292jtjypdyvt'

  and sn.snap_id = sq.snap_id

  and sn.dbid = sq.dbid

  and sn.instance_number = sq.instance_number

order by sn.dbid, sn.snap_id, sn.instance_number ;

To place it all in perspective, I also tend to look at the elapsed-time per execution, and the buffer_gets per execution:

elapsed_time_delta / executions_delta  as  sec_p_exe

But here I need to be careful, some decode is needed to avoid divide-by-zero.

And another interesting item is the plan_hash_value: if the plan has changed over time, it means the optimiser has chosen another plan, which may or may not be advantageous.

In my case, the inspection-query has turned into :

Or, in my case, I often run it from a script, that I have called 

SQL> @sql_freq <sql_id> <enter>

Let me discuss the relevant columns here:

TIME: Notice that my awr-snapshots are only 10min apart. I tend to set that interval narrow on critical systems or on systems under investigation.

EXECS: notice that this SQL is fired 10404 times between 07:30 and 07:40. That is quite a lot of executes in a 10min interval. Busy time-window?

BUFF_GETS and GET_PX (gets per execute) : At 07:50, the nr of buffer-gets suddenly goes up. And the nr of gets-per-execute goes from 3 (very efficient) to 7000 (not so efficient). 

SEC_PX: the nr of seconds per execute is still way below 0.5 sec. When rounded to whole-seconds, it ends up as zero-seconds. In some versions of the script, I display milliseconds, but when dealing with big/slow SQL, those numbers tend to become unpractically wide.

NR_ROWS: Because this is a sum-query, each execute only returns only 1 row.

G_PR: the nr of block-gets per row-returned. For some queries, I want to know the nr of blocks processed (gets) in relation to the nr of rows returned. In an OLTP system, you want the nr of blocks processed to be low. But if for some reasons the nr of rows is high, I can tolerate the processing of more blocks (e.g. bulk-shipments with a lot of items...)

PLN_HV: the Plan-Hash-Value. If this value changes, the execution-plan has changed over time. In this case, we we notice 3 intervals where the plan was indeed different. From the nr of Buffer-gets and the gets-per-row, we deduce that the "other plan" is probably a lot less efficient because it requires 7000 gets to return the result, whereas the "efficient plan" only needed 3 gets (a typical index-lookup) to return the result.

To further inspect the SQL, and display the execution-plans used, you can use something like:

select plan_table_output from table (dbms_xplan.display_awr('<sql_id>'));

And there is a lot more to discover, for example:

The DBA_HIST_SQL_PLAN contains details about the execution-plans, and DBA_HIST_SQLBIND can be used to hunt down the values of bind-variables from queries.

It is all there for you to explore ... 

In this particular case, the re-calculation of statistics for the relevant index was sufficient to "fix" this problem. At 09:00 the SQL had returned to its normal, efficient, PHV and 3 GET_PX.

Who uses this Index ?

 TL;DR: One more way to "monitor an index". Checking v$sql_plan can get you to the relevant SQL, tell you what component runs the query, and give you a lot of details. Read, experiment, and choose the way you want Your monitoring done.

There are multiple ways to find out if an index is useful or not, and they all have pro- and con- arguments.

Firstly, a very blunt way is to make the index invisible, or even drop it, and just see what happens. This method has a downside: it can impact users hard+fast. 

Secondly, the official documentation will provide you with DBA_INDEX_USAGE, and a very good article on this by Tim -oracle-base-dot-com Hall is found Here (Do Read It!). This 2nd method is less intrusive, more subtle, but it is still more or less a Yes/No answer to the question. It does not give you an indication of how-many users/session/queries/users are hitting this index, or how-often.

We are going to add yet another method: also not perfect, but maybe more suitable for some use-cases. Read on if you like to Dig In Deep....

Take a look at v$sql_plan and at the AWR and Statspack equivalents: DBA_HIST_SQL_PLAN and STAT$_SQL_PLAN. If you filter by object_owner, object_name and object_type, you can see which SQL-statements have used your index in their plan, both in sql-cache and in AWR/Statspack.

This view stores the "explained plan" of the SQL that resides in the shared_pool, and is also used by dbms_xplan.display_cursur and by (auto-)explain to display the explain-plans.

At this moment, I am interested in any SQL that uses my index. And I can obtain the list of SQL_IDs like this: 

select p.sql_id

from v$sql_plan p

where  p.object_owner = 'SCOTT'

and    p.object_type  = 'INDEX' ;

and    p.object_name  like 'EMP%'/* idx name(s) here */ 

;

From the nr of sql_ids we already get an idea of how many and how often this index is used. 

To make sure you dont miss occurrences, you should also check the DBA_HIST and/or the STAT$ equivalent because not all SQL-stmtns will remain in the sql-cache and be visible via the v$ views.

Once you have the set of SQL_IDs, you can link them to v$sql or v$sqlarea to find out how often each of them is used, and by which users or programs. I tend to use something like this:

select a.sql_id

     , a.executions

     , a.parsing_schema_name

     , substr ( a.sql_text, 1, 25 ) as sqltxt

from v$sql_plan p

   , v$sqlarea a

where p.object_owner = 'SCOTT'

and   p.object_type  = 'INDEX'

and   p.object_name  like 'EMP%'  /* name(s) here...*/

and   a.sql_id = p.sql_id;

For even more detail, you can also check to see if the SQL_IDs occur in the v$active_session_history views: those will tell you which sessions, e.g. which programs/user/jobs have been running the queries that used your index.

By doing this little extra digging, you may obtain a much better view of who + when + what is using your index(es).

I would encourage you to use each of the methods described, and to "do your own digging", and then to make an informed decision on whether an index can be dropped or not.

As I've often done, I'll make the point again: All this information is available to you in Views and Tables, and can be viewed with just SQL, and you are free to use the tool of your choice: SQLDeveloper, SQLcl, SQL-Plus, or any other adhoc-query tool at your disposal. 

Everything is SQL, and SQL is Everything.

Go Experiment, Go Learn and Enjoy !

Use SQL to find problem-statements

 TL;DR: There is a lot of interesting information in v$sqlarea, but beware of jumping to conclusions. 

On a troubled system, one of the first things I look at is v$sqlarea.

Have a look: 

Select * from v$sqlarea s order by buffer_gets ;

This view has grown over time to include a lot of columns but I recommend you start by looking at the ones you (think you...) understand: executions, time, disk_reads.

Depending on what you (think you) know to be your bottleneck, you can order by buffer_gets, disk_reads, or simply by elapsed time. 

My starting point for investigating a system is often:

select sql_id

, executions

, elapsed_time as microsec

, elapsed_time / ( (executions +1) * 1000 ) msec_p_exec

, buffer_gets

, disk_reads

, substr ( sql_text, 1, 60) txt

from v$sqlarea s

where 1=1

-- and parsing_schema_name like 'APP_OWN%'

order by 4 desc /* heaviest on top */

, elapsed_time desc

, buffer_gets desc ;

When run in SQLDeveloper, this puts the "slowest-per-execute" on top of the list. But by editing the query, I can quickly look for the most-buffer-gets, or the most-disk-reads as well. 

In the screenshot above, it is clear that the first 3 lines, a PL/SQL block and two select-statements, are consuming the majority of the elapsed-time. Those are probably the pieces we should investigate.

Notice several things about this way of "finding heavies":

Depending on what I want to look for, I can add/remove columns to the select. Some lines are read-to-uncomment, in case I need them. In reality, the stmnt I have saved in a ready-to-use txtfile is much larger, but it would look really messy to put that up in a blog.

The +1 is my dirty-trick to avoid div-by-zero. Notice how several stmnts have Zero executes: those were still running while we did the sample.

When run in sqlcl or sql*plus, I remove the DESC from the order-by, to make sure my relevant stmnt appears at the bottom (e.g. I dont have to scroll back).

Some warnings are due: 

There are a lot of caveats to this quick-and-dirty method.

It will only find statements that are currently in the shared_pool, e.g. recent or often-used statements. 

The numbers are "averages", which may or may not be impacted by outliers. One slow stmnt, an accidental lock, or one user looking for "Everyone called Garcia" may impact the results. Hence in interpreting the results: also check against your AWR or statspack, and check against common sense.

For example, if you know or suspect your database has an IO bottleneck, you should also order by disk-reads rather than by buffer_gets or by elapsed.  And if the slowest SQL does not concur with the most disk-reads,  you should question whether you really have an IO bottleneck.

Once you have identified a few relevant or "heavy statements", also go and verify that those statements actually are involved in relevant activity. Sometimes, the "load" is caused by something you didnt expect. (I have a funny story about an uptime-measuring script that impacted a running system, causing a hiccup every 15 minutes).

And next, once you think you have the SQL_ID of a problem-stmnt, you can use that sql_id do do further inspection.

For example, you can dig into v$sql for child-cursors, or into v$sql_plan and v$sql_bind_capture. Lots of possibilities there, and there is always more to discover.

At this point, I strongly encourage you to experiment and do some further research by yourself. You are welcome to Copy/Paste my code, but you learn much more from discovering things by yourself.

Happy Searching !

Do you know Where you Are ?

TL;DR: Use the sqlprompt to identify the connected system.

Why? - My most frequent mistake is to run a command in the wrong window or against the wrong database. Hence I often double-check my schema, database, server, container, etc... 

How? - Using SQL to find my relevant information (how else...) and to set the prompt accordingly.

The simplest version looks like this:

rem following defines prompt as user @ database

set heading off

set feedback off

spool sqlstart

SELECT 'set sqlprompt "' || user || ' @ ' ||global_name||' '

       || ' > "'

FROM        global_name     gn

/

spool off

@sqlstart.lst

set heading on

set feedback on

This little script can be used in either SQL*Plus or SQLcl and it will modify the prompt to show me the schema and the global_name of the database I am connected to. This trick is 25 years old, and still useful.

So far so good. But in the world of pdbs, containers and often-cloned system, I needed something more advanced. In many places, the sandboxes, dev-clones and test-environments will have the same username, the same schemaname, and the same global_name or db_name, thereby preventing my cunning script to distinguish between those system.

Hence over the years my simple prompt-gadget turned into something like this:

rem following defines prompt as :

rem    user [schema] @ database @ host (env)

set heading off

set feedback off

spool sqlstart

SELECT 'set sqlprompt "' 

       || user

       || decode ( user

           , sys_context('userenv','current_schema') , ''

           , ' ['|| sys_context('userenv','current_schema') || ']'

          )

       || ' @ ' || global_name

       || ' @ '|| SYS_CONTEXT('USERENV','SERVER_HOST')       

       || decode  (SYS_CONTEXT('USERENV','SERVER_HOST') 

            , 'ip-nnn-nn-2-109', ' (PROD)'

            , 'ip-nnn-nn-0-226', ' (ACC)'

            , 'ip-nnn-nn-0-23',  ' (SE)'

            , '98b6d46dd637',    ' (XE)'

            , '98eac28a59c0',    ' (23c-demo)'

            , ' (-chk env-)')       

       || ' > "'

FROM    global_name

;

spool off

@sqlstart.lst

set heading on

set feedback on

This version has a number of improvements:

It will check for current_schema, in case you are connected via a proxy-user.

it will check for server_host, which you can use to verify what server or (docker, k8s) container you are running from.

it has a decode that you can edit to provide better information about the system you are connected to. 

In my case, if the default value of "-chk env-" appears, this means I am connected to a (new) database that is not yet "known to this script". If I want to clarify what this new system is about, I can go into the script and add a line to the decode to identify this database.

In this example, you can see that the last added line was to use the container (98ea....) to tell me that this is my latest 23c version.

(I have messy versions of the script with long lists of decode...)

In other versions, I have used v$database and v$instance to add more items to the prompt, and colleagues have used ANSI-escape codes to modify the colors of their terminal windows (red = danger... ).

Nothing will stop you from modifying this script to provide the information that You find relevant.

A few tips, as you dive into SQL

In previous posts, I proclaimed "Everything is SQL" (link)
And I re-called which tools you could use to explore the SQL from your database: SQL Developer and SQLcl (or whichever other SQL-client you prefer). (links...)
Now for some tips...

Tip1: Stay Current, and skilled.
Get familiar with your tools and Stay Current: Check the download-sites and keep up with the latest version. You are going to live in the SQL-environment, hence you may as well keep up with current versions. 
For SQL Developer you should probably explore the Web-Version as well.
For SQLcl, the most important item for me is to keep my $SQLPATH correct, or modify it for the task/client at hand. When I do demos or presentations, I often use a special, Clean, version of my sql-scripts.

Tip2: Collect and Save your scripts.
Collect and manage (github) your own set of tool-scripts and familiarise yourself with them. In my case, I have my own scripts for day-to-day examinations, and for troubleshooting. I can quickly clone my private set form github onto just about any machine I get to work on (if not, I have a zipfile, and at times I use old fashioned uuencode to circumvent filters ...)


Tip3: Explore scripts from others. 
There is a lot if useful stuff out there, and you should read some of it just for Inspiration. In the end, you will suffer the "not invented by me" syndrome, but it really helps to see what others did. I use the available tools from Oracle and others. 
Oracle provides AWR, ASH, (or statspack for Standard Edition).
Carlos Sierra maintains sqldb360. 
And for the high-tech folks, there is also the toolset from Tanel Poder. 
This topic merits a whole article by itself, but you can start by exploring the material from Tanel Poder and Carlos Sierra. Links...


Tip4: Always establish “Where You Are”.
Make sure that you are “looking at the problem” and not at some random database(-clone) running in a container on some developer-box. Many ppl seem to logon to the wrong DB (e.g. pre-prod instead of prod). Make sure you are looking at the correct database or instance.
Sounds trivial, but it is one of the most occurring "mistakes" I come across. You have to check and Double Check that you are Actually looking at the right database, and that the supposed problem you see really is the problem the cstmr is having issues with.

Similar when you get mailed a Statspack or AWR report: double + triple check that is was from the correct database, and that the problem-you-want did occur at the time-interval of the report.

The other classic is to run some disastrous stmnt in Production while you were assuming you were just connected to some Dev-copy.
Sorry to hammer on about this, but it has happened too often...
For this reason, my most used, and most valuable scripts are the ones called pr.sql (to set the prompt) and a number of varieties on it. I tend to customise it for the environment I look at. Possibly merits a separate blogpost (future link)

So far the first set of Tips..
next post probably about pr.sql or some of the ready-to-use scripts out there.

Your Tools: SQLDeveloper and SQLcl

In the previous post (link), I insisted: Everything is SQL, and SQL is Everything.

So if all the information you need is presented in the form of tables and views, and everything is SELECT-able, you should get familiar with SQL and with the tools to run SQL.

Your tools of choice should be SQLcl or SQL-Developer, or the SQL-Developer web variety, if you want to be totally hip  (downloads and information here).

You may also know that SQL*Plus is still available on Every Platform where the RDBMS itself is deployed.

And there are others... if you are used to other SQL-tools, you will find that most of them can connect to an Oracle database, and work just fine. For example TOAD or DBeaver will also do the job just fine (some documentation here).

Running a query from SQLDeveloper mostly looks like this:

In there  you see I am using SQL to join two views, and I am looking for statement executed by SCOTT, with the heaviest IO-stmnt listed on top. This information, and many other valuable data is available to query using just the tools and SQL that you already know.

But as a DBA, I have a strong preference for using a CLI (Command Line Interface) next to my GUIs or other tools. The big advantage of a CLI is the capability to "script" your commands. Scripts will "store your knowledge" and make it repeatable, re-usable.

In my case, I would use SQLDeveloper to do ad-hoc inspections, queries. But once a query or a report needs to be re-run more then once, when I think it is useful in future, I will create a script. From that point on, the query can be run from using its filename.sql from the CLI. 

Example is the script "pr.sql" a very small script that I use everywhere and all the time: it sets the SQL-Prompt to tell me I which user, which database and which server I am connected to, just to make sure I am always typing at the correct prompt... 

SQL> 

SQL> connect scott/tiger@orclpdb1 

Connected.

SQL> 

SQL> @pr

set sqlprompt "SCOTT @ ORCLCDB @ oracle-21c-vagrant > "

SCOTT @ ORCLCDB @ oracle-21c-vagrant > 

Because "Everything is SQL", that script actually goes out and uses SQL to find the user, the database (or PDB) and the host this database runs on, and then sets the prompt accordingly. 

If you make a habit of creating and using scrips, then, over time, you will collect a nice set of scripts. 

The first good reason for using the CLI tools, SQL*Plus or SQLcl, is that those tools can run scripts quickly using the start command or the @ operator. If you stick with the GUIs, you will at some point get tired of copy-pasting your code-snippets of SQL-commands into the GUI. Running a script from the command-prompt takes less of a mouse-and-type effort.

And once you are using the CLI, you should investigate the setting of the environment-variable $SQLPATH. (windows: %SQLPATH ). This variable can contain one or more directories where you can store your scripts and _always_ have them at your fingertips. Not to mention the useful scripts you might copy from others who have already gone there and done that.

In Summary:

 - Download and get to know the tools.

 - Explore the data, find your information.

 - Consider creating your own set of scripts.

 - Use environment-variables to always have the scripts available.

Next blogs...: 

Tips to avoid the mistakes I made, and still see others making

And links to where you can find good re-usable scripts already built by others. 

(insert future links here..)

And... I told you: "SQL is Everything and SQL is Everything"