-
Help on optimizing a query
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!
-
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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
-
5 seconds and 17 seconds is nothing
good for that kind of query
-
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.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|