-
Hello, is the a way that I can find out which columns are being used in a table. I have a very huge table and I know that there are some of the columns that are not being used, but I don't know the exact ones. Is there any way that I can find out. If there are, can anybody tell me the exact way to find out?
thanks,
dorothy
-
hm what do u mean if it´s being used, a null column?
-
Okay let me try and explain it further. I have a huge table with a lot of columns. There are some front end applications that query some of the columns and fields in the database. Not all the columns are being querried. i.e. some of these columns are just there and application are not using them.
So I am trying to find out which of these columns are being used (i.e. selected from ) and which of them are not being used?
Are there any SQL queries that can let me do this? OR are there any tools that can help me find which of the columns are being used or not?
thanks,
dorothy
[Edited by dorothy on 12-16-2000 at 12:47 PM]
-
You can try to look in v$sqlarea an get all the statements
that refer to your table.
Gert
-
Dorthy
You could do something like the following
select column_name
from dba_tab_columns
where table_name =
and num_distinct = 0
The tables will have had to be analyzed first in order for the statistics to be there though.
Joe
-
Hi joe,
So is this query going to tell me exactly what columns that the applications query from? I want to be able to determine which of the columns that are used by the applications for query purposes and which ones are not being used.
i.e. which columns get used and the ones that do not get used
thanks,
dorothy
-
Dorthy,
No, I misunderstood your question. That will tell you which columns in a particular table are not populated. If you need to find out which columns are being accessed by your applications, I would suggest doing something like turning Auditing on for a couple of days and going through the trace files or something of that nature. The problem you are going to run into probably though is that some of the columns might only be accessed during batch processing, monthly loads, etc.. etc.. Some might almost never be used at all except for special circumstances.
Is there some larger issue that you are looking at. Maybe if we understood exactly what the issue is we'd be able to offer some better advice.
Thanks,
Joe
-
Thanks Joe,
Let me tell you the whole scenario. The whole scenario is that, we are going to build a new database(called Database1) from the scratch. There is an old Database (called Database2) that has very huge tables. There are some applications that query some of the columns from Database2(but not all the columns). We do not know the names of these columns that these applications query from. So we are trying to find out those columns.
The purpose of finding these columns is to put the exact columns that are used by the applications into the new tables of the database that we are going to create(i.e. database1). By knowing the exact columns, we will not put columns that are not needed into the new tables of the new database.
How exactly do you do tuning auditing? Is there any specific documentation that I can read? But before that can you tell me in a nutshell how it's done and how it works?
thanks for your time,
dorothy
-
Dorthy,
I'll ponder the issue more and try to come up with a more efficient method but right now the only things I can think of in the absence of going through the source code is to turn auditing on for the whole database by setting the parameter SQL_TRACE = TRUE inside of your initialization file. This will create a separate trace file for each session in the directory defined by USER_DUMP_DIR. This is going to create a huge number of traces though if you have any real activity going on throughout the day and it's also going to slow down your database.
After you have the trace files you'd have to run them through something like tkprof and examine the sql that was executed and note which columns were referenced. Once again, you're going to run into the issue of tables/columns that are very infrequently accessed.
Can anyone else think of a better method than turning auditing on or going through the v$sqlarea statement by statement?
Joe
-
The better way to do it to look into the code of application and user queries. You should also consider the dependency of the front end applciation which is inserting/updating data into that table. After looking into the applications come out with the list of column not referenced by application. There may be some columns referenced by application but of no relavence or obsolete. In that case you should modify the application first and then modify the database.
What do the expertssay?
Sanjay
-
How about putting a trigger on the table? I know there is no trigger for SELECT, but then what are the odds that the app does a SELECT but never an insert, update or delete?
make your trigger do this
if :new.column_of_interest <> :old.column_of_interest then
insert a row into some table for monitoring;
end if;
-
If you are going for the all the user tables in the database then you are in for Trouble.
But for a specfic table means you can try by
1.sql_trace on
2.sql_text ananlysis
3.Try using the New Monitoring option for tables.
Identify the columns that you want to eleiminate.create a view on all the columns that you need .Test your application .
Then drop all the columns .This is going to be a pain.
