Skip to content

5.1. What you can ask a database

Table structure, diagrams, statistics, slow queries — asking in words without opening a client

The widget connects directly to the database and answers from it. It does not read metrics on your behalf; it reads table structures, counts statistics and fetches query plans.

You do not have to open a database client. Just ask in plain language.

First — say which database​

Several databases may be registered, so your question has to name the target.

"Show me the structure of <table> on <DB name>"

If you do not know the name, ask for it first.

"Which databases can I connect to?"

You get a list of the registered connections and their current state. Use those names as they are.

It supports MySQL, MariaDB, PostgreSQL, Oracle and SQL Server. What can be retrieved differs slightly by product.

1. Looking at structure​

What tables exist​

"What tables are in this database?" "Show me the schema list"

Tables and views come back together. If there are several schemas, ask about the schema first.

One table in detail​

"Tell me the structure of the USERS table"

The column list comes back with indexes and constraints as well. This is the question people use most often to check types and nullability.

The statement that created it​

"Show me the DDL for the USERS table"

You get the CREATE TABLE statement as it is. Use it to create the same table in another environment.

Finding tables by column name​

"Find every table that has a column called user_id"

Use this when you do not know the table name. It examines all the tables for that fragment and tells you where the column lives. It is particularly useful in schemas with hundreds of tables.

Relationship diagrams (ERDs)​

"Draw an ERD of the order-related tables"

You get a diagram of how the tables connect. How to read it, and the three things the diagram leaves out, are covered in Reading execution plans and ERDs.

2. Looking at state​

These ask what state the database is in right now. Use them while investigating an incident.

What you askWhat comes back
"How is the database doing?"Connection count, cache hit ratio, uptime, warnings
"Show me the sessions running now"Session ID, user, running query, wait event, blocked sessions
"Is anything locked?"The holder and the waiters, and the blocking chain
"What slow queries are there?"Query text, duration, rows read, timestamp

The order to look in when responses are slow​

The order to look in when database responses are slow

Stop when you see a blocking chain. The queries queued behind it are a consequence, not the cause.

3. Looking at size and indexes​

"Show me the statistics for the ORDERS table"

You get the row count, the space it occupies, the number of indexes and modification statistics. You have to know how large a table is before you can judge whether a full scan in an execution plan is a problem.

"Are the ORDERS indexes being used?"

You get how many times each index was used and how many rows it read. It also points out indexes that have never been used. An unused index does nothing for reads while slowing writes down.

4. Analysing a query​

When you only want the plan​

"Show me the execution plan for this query — SELECT ..."

You get a tree of the order in which the database processes the query.

When you also want to know why it is slow​

"Analyse why this query is slow — SELECT ..."

This does considerably more. Instead of just the execution plan, it gathers the structure, indexes and statistics of the tables involved as well.

  • It points out problems such as full scans and missing indexes
  • It suggests what to change
  • An ERD of the related tables comes with it

Make this your default question when dealing with a slow query. A plan on its own tells you "what it did" but rarely "why it did it that way".

If you use OPENMARU APM​

You do not have to retype the query. From the transaction detail you run the same analysis with one button (Diagnosing SQL straight from APM).

5. Querying directly​

"Count the orders cancelled yesterday on <DB name>"

Ask in words and it writes the query, runs it, and shows the result as a table. You can also give it SQL directly.

What is allowed is up to your administrator​

The scope differs per connection. On a connection configured as read-only, queries that modify data are rejected. Administrators can impose other limits as well.

  • Forbidding particular SQL keywords
  • Forbidding particular tables
  • Allowing queries only against permitted tables

When something is blocked, the reason appears in the answer. If a query you need is blocked, it is not something you can unblock from the screen, so contact your administrator.

You may be connected to a production database. Even for a counting question it is safer to narrow the scope. Reading a large table in full, with no period or condition, puts load on the database.

When it does not work​

It says it does not know which connection you mean​

You did not name the database, or the name differs from the registered one. Get the list first with "Which databases can I connect to?"

It says it cannot find the table​

  • The schema may differ — check with "Show me the schema list"
  • Case — Oracle often stores names in upper case
  • If you copied the name from a diagram, it may be wrong. Diagrams replace special characters with underscores (see Chapter 503)

Some fields are empty​

Different databases expose different statistics. Missing actual execution times or I/O figures mean that database does not provide them, not that something went wrong.

Next​