Visualizing Almost Fifty Thousand DBA_OBJECTS In A Graph

What do almost fifty thousand Oracle objects look like?

Continuing my exploration of graph databases, I loaded every object from DBA_OBJECTS into a NEO4J graph and visualized it with Gephi.

That included all the objects from Oracle 11.2 DBA_OBJECTS, with the exception of the Java objects: Java Source, Java Class, Java Data. It also included some other schemas I’ve loaded into the database, such as Perfstat, SH, SCOTT, BI, etc. Altogether, 48,690 objects, and 61,710 relationships were inserted into NEO4J and then imported into Gephi.

Good Data Source:

As I did this, I thought that DBA_OBJECTS makes a rather good dataset to experiment with. It’s freely available to any DBA. The data is not sensitive. There is lots of data:  tens of thousands of rows. The relationships between the objects are listed in DBA_DEPENDENCIES. Most all the data points represent a connected tree structure.  This is exactly what NEO4J and Gephi work well with.

OpenOrd Layout:

Gephi has a number of layouts to work with. I used the OpenOrd layout in Gephi to visualize all the data. OpenOrd completed the layout quickly, in a few minutes. By contast, the Fruchterman Reingold layout did not complete even after I let it run all night. Perhaps Fruchterman Reingold is only good for small data sets.

I partitioned according to the Object_Type (Synonym, View, Index, Table), using the default colors Gephi provided. Then set the edge color to red.  See what the results looked like.

The Big Picture:

OpenOrd showed stelliums of objects clustered together.

DBA_OBJECTS Visualized In OpenOrd Layout, wide

—

They remind me of constellations in the night sky.

DBA_OBJECTS Tree – Modelled As A Graph in NEO4J, Visualized With Gephi

The structure for Oracle objects is a tree structure. Not a table structure in rows and columns.

As I wrote in my posts on the parents and children of database objects, a tree structure of Oracle object might be:

TYPE
   TABLE
      VIEW
         FUNCTION
            PROCEDURE

In another post, there is a query I wrote to show object dependencies. DBA_DEPENDENCIES will show the object’s parents. Or the children. But only one level up, or down.

Trying to see the tree structure using SQL queries is problematic. The reason being, the output from SQL is not a tree at all. And, as I wrote before, the very same object can be seen multiple times in the output.

NEO4J And Gephi:

Recently, I’ve been using the graph database, NEO4J. It is perfectly suited to create tree structures and store them in a database.

In Oracle 11.2, I snagged the data from DBA_OBJECTS and DBA_DEPENDENCIES, created Cypher commands to insert the nodes into NEO4J, and created the relationships/vertexes between the nodes. Then I used Gephi to visualize the graph with different layouts. See some different output.

—

All the parents and children of the View, SYS.DBA_OBJECTS, and the Synonym, PUBLIC.DBA_OBJECTS.

Yifan Hu Layout of DBA_OBJECTS

Using the Yifan Hu layout radiates all the nodes. 

Installing Gephi On 64 bit Machines

The instructions for installing Gephi can be found at:

https://gephi.org/users/install/

They seem simple enough. But I found out that on a 64 bit server, it’s really important that you have a full 64/64/64 bit combination. That represents the operating system, Java, and Gephi. Here are some hints to get it right.

Java:

Be sure to download the right java file.  

Installing NVidia Drivers on Redhat 6.4 and Removing Nouveau

Recently I’ve been working with the graph database, NEO4J. Much of the usefulness of graph software is in visualizing it. So, I decided to install Gephi on Redhat 6.4. Gephi installed on Redhat easily enough. But it did not display the most important thing, the graph window!  😦

Gephi With No Graph Section

Debugging Gephi:

The Gephi website says:  “Gephi uses an OpenGL 3D engine to speed up graph visualization. However a compatible graphic card is required.”

Here’s how you can add some debugging to Gephi.