| How to monitor view usage ? [message #148060] |
Tue, 22 November 2005 08:42  |
pscjhe
Messages: 38
Registered: April 2005
|
Member |
|
|
Views are used frequently by developers to develop crystal reports. It gets out of hand after several hundreds of views are generated in production (before I manage this). I can't tell now which one is used by real report and which report is used by users in production. Worse, I can't turn on audit_trail in production. How can I tell which view is used in production, even if used only once? Sort of like monitoring table or indexes, I thought about making view invalid and hope Oracle will revalidate it when SQL uses the view. Unfortunately it doesn't work like this.
Anyone has a good idea ?
|
|
|
|
|
|
| Re: How to monitor view usage ? [message #148705 is a reply to message #148060] |
Sat, 26 November 2005 16:50  |
dbaxchangedba
Messages: 26
Registered: November 2005
|
Junior Member |
|
|
A few things you can possibly do:
(1) Create a seperate login id for running reports. Make sure that the crystal reports uses this reports specific id for running the report
(2) Create a "after logon" trigger on the database and within that trigger, set sq_trace = true. For example:
create or replace trigger logon_trigger
after logon on database
begin
if (user = 'REPORT_USER') then
execute immediate 'alter session set sql_trace=true';
end if;
end;
/
(3) Setting up tracing through the trigger will enable you to capture all the SQL statements run by the reports which will tell you the view being used by the reports.
(4) An alternative solution would be to use stored outlines. Through a logon trigger, you can enable stored outlines for ids that are being used to run the reports and then you can query the dba_outlines view to see the sql statements that were run by the reports which will tell you as to what views were used.
Hope this helps!
http://www.dbaxchange.com
(One stop site for practical solutions to oracle issues, oracle findings, Book recommendations, International job search site recommendations and much more....)
|
|
|
|
| Re: How to monitor view usage ? [message #148826 is a reply to message #148705] |
Mon, 28 November 2005 07:43 |
pscjhe
Messages: 38
Registered: April 2005
|
Member |
|
|
Thanks for your inputs. I have thought of these alternatives before I sent the email. None of them seems to be practical enough to put into production. I probably didn't make it clear. This is third party application. Hence vendor's table/views/procedures are mostly documented but NOT in-house developed views. I only try to find out poorly managed views. Nothing more nothing less.
First of all, for future views, I can put in a policy of change control them into different schema_id or special view prefix, suffix etc. The issue is with existing views. I can't change production so much for simply tracking the view usage. If vendor doesn't let you put in audit_trail, do you think they would let you put in logon trigger, sql trace, stored outliner? Even if they do, it is too complex and dangerous to put in production without some serious testing against VENDOR's application. Do you realize changing a view's owner or schema will require recreating all the crystal reports that use the view ? If developer has poor habit of creating views in production, do you think he would have a good habit of change control them ? No. Of course not!
BTW. I did find a solution for this question using my original idea of "invalidating" view. This is after reading "concept guide" carefully.
|
|
|
|
| Re: How to monitor view usage ? [message #148880 is a reply to message #148826] |
Mon, 28 November 2005 17:49 |
dbaxchangedba
Messages: 26
Registered: November 2005
|
Junior Member |
|
|
"Do you realize changing a view's owner or schema will require recreating all the crystal reports that use the view ? "
Please read my post carefully. I'm not suggesting for you to change the ownership for the views. Let the schema owner for the views continue to be what it is now but rather give "select " privileges on all of your views to a new user through a role and create a logon trigger for that new user and have reports run through that user.
"If vendor doesn't let you put in audit_trail, do you think they would let you put in logon trigger, sql trace, stored outliner?"
I'm surprised they allow you to make views go invalid in production! Have you analyzed the impact of making views go invalid? Library cache pins and load locks may occur during view recompilation leading to serious system performance drops due to waiting for 'library cache pin' and 'library cache load lock' events.
Also, as you might already know, after object invalidations, Oracle tries to recompile the object at the time of the first access to it. It may be a problem in case when other sessions have pinned the object to the library cache or if objects that the view depends on have locks on them in which case waiting for object recompilation may even take hours blocking all the sessions trying to access it.
Impact analysis is an important factor when coming up with solutions to problems otherwise quick solution can become long term problems!
http://www.dbaxchange.com
|
|
|
|
| Re: How to monitor view usage ? [message #148986 is a reply to message #148880] |
Tue, 29 November 2005 07:11 |
pscjhe
Messages: 38
Registered: April 2005
|
Member |
|
|
Using which login account for running crystal report is pre-defined by vendor. Hence I can't change it. Of course trace can still be done through this login account. To making it worse, database is MTS, or shared server, configuration. You won't get as clean trace you would like in dedicated server configuration.
Recompiling all invalid objects during the busiest time of day of course will have performance impact. But who will do that? It has to be some low usage time at night, weekends or downtime. Also these customized views are used by only these reports, which are usually run sequentially. Hence the race condition is rarely the problem.
| Quote: |
It may be a problem in case when other sessions have pinned the object to the library cache or if objects that the view depends on have locks on them in which case waiting for object recompilation may even take hours blocking all the sessions trying to access it
|
I don't understand what lock will hold view compilation for hours. Do you have some simple examples?
|
|
|
|
| Re: How to monitor view usage ? [message #148994 is a reply to message #148986] |
Tue, 29 November 2005 07:48 |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>> How can I tell which view is used in production, even if used only once?
How about this?
Ofcourse, this shows only views used SINCE the last restart.
If this is irrelevant for what you are looking into, please excuse and ignore.
scott@9i > !dbuptime mutation
HOST_NAME INSTANCE_NAME Database Uptime
--------------- ---------------- ------------------------------
mutation mutation 0 days 0 hours 0 minutes
scott@9i > select name,type,loads,executions from v$db_object_cache where owner='SCOTT' and type='VIEW';
no rows selected
scott@9i > select * from myview where 1=2;
no rows selected
scott@9i > column name format a10
scott@9i > select name,type,loads,executions from v$db_object_cache where owner='SCOTT' and type='VIEW';
NAME TYPE LOADS EXECUTIONS
---------- ---------------------------- ---------- ----------
MYVIEW VIEW 1 0
|
|
|
|
| Re: How to monitor view usage ? [message #148999 is a reply to message #148994] |
Tue, 29 November 2005 08:29 |
pscjhe
Messages: 38
Registered: April 2005
|
Member |
|
|
This is rather interesting idea. If I have to do this between restart instance, all I need to do is to flush shared pool. Since v$ views are dynamic, loaded views can be aged out of shared pool. So polling interval is critical to this process. But this method requires no change on production database. It is certainly superior.
It would be better if there is a way to unload individual view out of shared pool. I tried to use DBMS_SHARED_POOL.UNKEEP to unload the view out of shared pool. Seems that table and view are exceptions. Any better idea there ?
|
|
|
|
|
|
| Re: How to monitor view usage ? [message #149001 is a reply to message #149000] |
Tue, 29 November 2005 09:04 |
pscjhe
Messages: 38
Registered: April 2005
|
Member |
|
|
I normally don't create these directly in production. I would use database link to "collect" data out of production and use MERGE to get the list. Since read-only normally does not require change control.
The issue is still to "unload" individual view. The other issue is to "COUNT" how many times view is used. Is there a way ??
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
