So: A few weeks ago I was requested by my team lead to dive into a schema that had been set up to record florists’ psychic activity* in a CRM. That is, various user activities and other, automated activities were to be recorded into this schema, with tables eventually to reach sizes of millions and tens of millions of rows. We in Operations were supposed to monitor activity, check that things were running smoothly, et cetera. The three main tools given us were a commercial administration tool, the regular log files written to the Linux file system, and this multi-table schema, which is what this post is about.
The guys in charge of developing the schema are on a different team in a different department, and inaccessible through normal channels to a guy of my pay grade. This means that to succeed in my task I have to figure things out by myself.
These are the stages I go through when given a task like this:
First – make sure you can’t screw things up royally for yourself
Nothing makes me lose what little hairs l have left on my shaved pate like thinking I might have just dropped some table, deleted some rows or stuck some production process by standing on a table and locking it and stopping an entire billing process. Therefore I will, whenever humanly possible, do all my development on a backup/standby, development or test schema. In addition, I will ask the DBA to create a user with read-only privileges.
How to tell that your user is read only (taken from http://stackoverflow.com/questions/15066408/how-to-find-the-privileges-and-roles-granted-to-a-user-in-oracle):
Granted Roles:
SELECT *
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = 'USER';
Privileges Granted Directly To User:
SELECT *
FROM DBA_TAB_PRIVS
WHERE GRANTEE = 'USER';
Privileges Granted to Role Granted to User:
SELECT *
FROM DBA_TAB_PRIVS
WHERE GRANTEE IN (SELECT granted_role
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = 'USER');
Granted System Privileges:
SELECT *
FROM DBA_SYS_PRIVS
WHERE GRANTEE = 'USER';Now that you have a nice safe user with read-only privileges and preferably are in a backup environment that can be [easily/ periodically] restored, one can start investigating the schema.
The only tool we have (in this firewalled corporate environment) for examining databases is Toad for Oracle (for Postgresql I would use pgAdmin). Tip: whatever you do, don’t open two Toad instances simultaneously (don’t have two toad.exe processes running in the Task Manager – this will soon crash the application). You can open as many editors, schema or session browsers as you like within the single Toad instance.
Investigating the Schema
The first thing to do is see what the current situation is in the schema. You can do this in Toad just by opening a new Schema Browser. This shows basic stuff, like how many rows there are in the table (at the last time the table was analyzed).
But what about the content of the tables? Some schemas have tables with constant values in them, so that you can trivially discover all the possible values that can be in a column. For instance, a table called ROSES that contains all 6679 names of rose flowers, or a table of PRIMARY_COLORS that contains the names of six colors: white, black, red, yellow, green, and blue. This is a table of constants and should be in the database, referable by foreign key from other tables. Unfortunately, in the schema I received there were absolutely none. The values in the tables were inserted from outside processes, with no explanation given. Our process will therefore be to isolate these values and build ourselves lists of constants and to assign them some kind of meaning that will help us to just do our effing jobs. If the values are all different, with no repeatability, we are effed. Otherwise – maybe we can get on top of things!
At this stage of writing, I have for the last two weeks avoided even accessing the DB, as access to it has been forbidden to all but those chosen by the Manager of Information Systems. This is a known problem in IT/IS departments, where your primary aim is avoiding threats to your employment. Therefore, if anybody senior to you says to leave the DB alone – do that! Go do something else, and wait for the all-clear. That is why l try to upfront all my work when I can, and get as much done before my window for doing my job is arbitrarily closed.
Select DISTINCT on each column
When I looked at the “processes” table, I started performing SELECT DISTINCT on each column. This immediately led to some interesting results. The table had about four million rows. l looked at the process_definition_name column, which had what appeared to be very high variance, with very long strings such as:
ReadingAllPsionicServices/SubServices/SubProcesses/GetOtherPsychicFalloverJob/Processes/GetOtherPsychicFalloverJob.processFour million unique process names? My heart sank. But when running:
SELECT COUNT(DISTINCT process_definition_name) FROM processes;The result came out 340. So those four million rows actually turned out to have 340 distinct values, which recurred over and over again. My interpretation is that these are likely to be the names of the processes running on the system and that there are 340 of them.
The idea is to do this repeatedly until one has built up an idea of the values than can be in the tables. This way, I could better understand the parent system and deal with problems as they arose.
Deciphering BLOB/CLOB fields
Lazy programmers abound, and I am probably one of them myself sometimes. But because I hate doing donkey-work propping up technical debt of my own, I usually “up-front” some work to make my code work reliably so that I don’t have to work on it again and again later on, usually at the worst possible moment, like a crisis in a production system. In this case, however, since I didn’t design or code the system, I had no choice: The table creators, whether because they hadn’t had privileges or because they were just lazy, stuck all the most important data as XML structures into HUGECLOBS. In Toad, I can double-click on the
HUGECLOB field and I can see what’s hidden inside. But that kind of manual viewing, while useful, doesn’t help in SQL queries. This meant that in order to understand what was going on, I had to put CLOB and XML deciphering into my SQL code. The following code gets the content of two fields out of the XML data, the mental credit payment identifier and the name of the telepath who made the payment using his psionic powers (I have obviously obfuscated the true names of the fields).
Sample of the XML data:
…data containing telepathPayId and telepathName…
Sample code to extract the data:
select
extractValue(
XMLTYPE(AUDIT.bulkdata),
'/ns0:retrieveMentalCreditPay/ns0:telepathPayId/text()',
'xmlns:ns0="http://www mind-read.com/services/schemas/retrieveMentalCreditPay'") as
TLPTH_PAY_ID,
extractValue(
XMLTYPE(AUDIT.bulkdata),
'/ns0: retrieveMentalCreditPay/ns0:telepathName/text()', 'xmlns: ns0="http://www.mind-read.com/services/schemas/retrieveMentaICreditPay"') as
TLPTH _NAME
from AUDIT
where
AUDIT.MESSAGEID= 'ID:psionic_id.1AFC33CDF767D7F03:49830'To recap up till here:
I showed how I used DISTINCT to investigate the constant Values that I needed in order to get the big picture of the system I was supposed to monitor. I also showed how I used EXTRACTVALUE and XMLTYPE in order to retrieve information that was hidden in XML fields.
*In case you’re wondering: Psychic activity in florists is a very real thing! No kidding! (wink).