Help on optimizing a query

[gzz]

Hi,
I'm currently working on an application where i want to dynamically create a graph of the database's schema. So I looked into what 11g offers me, and came up with the following solution:

First, I want all root nodes, i.e. the tables that are not referred by a foreign key, and their cardinality:

SELECT TABLE_NAME, NUM_ROWS FROM USER_TABLES X WHERE TABLE_NAME NOT LIKE 'DR$%' MINUS (SELECT distinct d.TABLE_NAME, d.NUM_ROWS FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B, USER_CONS_COLUMNS C, user_tables d WHERE c.table_name=d.table_name and B.CONSTRAINT_TYPE='R' AND A.CONSTRAINT_NAME=B.CONSTRAINT_NAME AND B.R_CONSTRAINT_NAME = C.CONSTRAINT_NAME);

Secondly, I want all the other tables, i.e. the one that are referred by foreign keys, as well as the tables they're referred from:

SELECT DISTINCT C.TABLE_NAME, D.NUM_ROWS, A.TABLE_NAME, E.NUM_ROWS FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B, USER_CONS_COLUMNS C, USER_TABLES D, USER_TABLES E WHERE B.CONSTRAINT_TYPE='R' AND
A.CONSTRAINT_NAME=B.CONSTRAINT_NAME AND B.R_CONSTRAINT_NAME = C.CONSTRAINT_NAME and C.TABLE_NAME=d.table_name and a.TABLE_NAME=e.table_name
ORDER BY C.TABLE_NAME ASC;

This all works quite well, but - it takes ages. I have a comparably small schema with 23 tables (excluding the ones from oracle text, therefore the "not like DR$" in the first query. Still, the first query takes about 5s, the 2nd about 17s.

Is there an easier or faster way to accomplish what I want? And even if not why is it so slow given the tables are really small themselves?

Thanks!

[PAVB]

Did you trace it?

[gzz]

yes I did indeed - but I didn't understand the traces I have to admit.

This is the trace of the first statement: http://pastebin.com/ZBYHrCz9

And this is the 2nd one: http://pastebin.com/q7Vfdr0x

Thanks

[davey23uk]

5 seconds and 17 seconds is nothing

good for that kind of query

[gzz]

well, i guess "nothing" depends on your point of view.

I coded a small java prog that does a select * from the 3 involved tables and then does the set operations itself.

This all takes about 1 second to get the same results ... this is exactly why I wonder that oracle takes more than 20 times longer for this operation.

[ridges54]

Agreed, I ran this on a 60 GB test db I have and it took <1s for each statement. I traced that session, got this:
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.67 0.66 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 51 0.14 0.14 0 36071 0 713
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 55 0.81 0.81 0 36071 0 713

Misses in library cache during parse: 2

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 52 0.00 0.00
SQL*Net message from client 52 46.71 87.82

Use tkprof on any trace files you take (IMHO) it makes reading what is happening 100 fold easier to understand. Here is a great review on tkprof and translating them: http://www.oracleutilities.com/OSUtil/tkprof.html

Jim