3
I have a program to search dependencies of records to proceed with data deletion here in the company. We support some databases but on Oracle the query is processed very slowly (about 10 to 15 seconds for each table in remote production banks).
Queries for consultation
Parent tables
SELECT DISTINCT c_pk.table_name table_name, CAST(a.column_name AS
VARCHAR(100)) as MainColumnName, a.constraint_name, a.position,
a.table_name as r_table_name, c_pk.constraint_name r_pk,
CAST((SELECT ax.column_name FROM all_cons_columns ax
WHERE ax.owner = c_pk.owner
AND ax.constraint_name = c_pk.constraint_name
AND ax.position = a.position) as VARCHAR(100)) as column_name
FROM all_cons_columns a
INNER JOIN all_constraints c ON a.owner = c.owner
AND a.constraint_name = c.constraint_name
INNER JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
AND c.r_constraint_name = c_pk.constraint_name
WHERE c.constraint_type = ''R''
AND a.table_name = ''%s''
ORDER BY
table_name, constraint_name, position
Daughter tables
SELECT a.table_name, CAST(a.column_name AS VARCHAR(100)) as
column_name, a.constraint_name, a.position,
c_pk.table_name r_table_name, c_pk.constraint_name r_pk,
CAST((SELECT ax.column_name FROM all_cons_columns ax
WHERE ax.owner = c_pk.owner
AND ax.constraint_name = c_pk.constraint_name
AND ax.position = a.position) as VARCHAR(100)) as MainColumnName
FROM all_cons_columns a
JOIN all_constraints c ON a.owner = c.owner
AND a.constraint_name = c.constraint_name
JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
AND c.r_constraint_name = c_pk.constraint_name
WHERE c.constraint_type = ''R''
AND c_pk.table_name = ''%s''
ORDER BY
table_name, constraint_name, position
Is there any faster way to query this data?
The queries are correct, there is nothing to optimize, I believe. The problem may be in the environment, SGA etc. Production queries in this BC are slow ?
– Motta
How are the indexes? The time of 10/15 seconds is for return or execution of the query (Could be network problem)?
– Felipe Fonseca
Because of this I thought and did not find , how to collect statistics in the Metadata tables ?
– Motta
@Motta the system in production works normally, even local the query is not very fast, but the program we have that uses it maps all references of a record, and when we have to delete a process for example it makes them to about 50 ~ 100 tables
– Caputo
@Felipefonseca as are tables and views of Oracle itself, I do not know if I can change the indexes
– Caputo
Analyzing saw that this part is contributing to the delay:
CAST((SELECT ax.column_name FROM all_cons_columns ax 
 WHERE ax.owner = c_pk.owner 
 AND ax.constraint_name = c_pk.constraint_name 
 AND ax.position = a.position) as VARCHAR(100)) as MainColumnName– Caputo
Caputo, since the information retrieved relates to the static structure of the tables and not to the data contained in them, a cache of the values returned by these queries?
– utluiz
@utluiz Today I cache by execution and do not keep the cache between executions. Each client has its own internal DBA team and most clients are in different versions of the system. For each table, I only go once per run, but sometimes the record has so many dependencies that the process takes over half an hour. We did not use the tool frequently, but I felt the need to increase its performance.
– Caputo
http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_stats.htm#i1055451 try to apply statistic collection to metadata dictionary and see if it improves.
– Motta
Revisiting an old post I remembered this, see if collecting statistics from the dictionary improves something http://docs.oracle.com/database/121/ARPLS/d_stats.htm#ARPLS68571
– Motta
Thanks @Motta, I’m allocated to another project at the moment. I’ll try to take the test later this week!
– Caputo