Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 20-11-2009
Member
 
Join Date: Jun 2009
Posts: 56
Consolidate MLD in sybase

I am facing a big database in Sybase, and I have to redo the database model by removing the tables and stored procedures not used knowing that there is a daily treatment on the base. I heard about a tool that can allow me to see the use of statistical tables and procedures of the database or something like a house or other tools that could be launched.

Is there really a tool of this kind? Do you have another alternative to offer me?
Reply With Quote
  #2  
Old 20-11-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
Re: Consolidate MLD in sybase

For tables, and provided that either 12.5.0.3 or later, you can use the table master .. monOpenObjectActivity (that is part of tables "MDA") to estimate the utilization rate. I stress the word "estimate" because the data in this table are were abandoning to zero when rebooting the server data, and can also be erased if the config variable "number of open objects" and "number of open indexes" are too small.

Its much more complicated. A tool like sqlbrowser can make a static analysis of all the code on the SQL server, which provides information on the dependencies between objects. Then it is possible using the master table .. monSysStatement (also a MDA table) to determine which statements are executed. Again - this use provides an estimate, and is not completely trivial to implement.

You can also use the auditing and audit all executions of stored proc.
Reply With Quote
  #3  
Old 20-11-2009
Member
 
Join Date: Jun 2009
Posts: 56
Re: Consolidate MLD in sybase

Thank you very much for your response, I'll check everything.

If I understand correctly, as the database is not restarted stats in master .. monOpenObjectActivity are correct?

I can locate "number of open objects" and "number of open indexes" to verify that their size is correct?

Regarding the auditing of stored proc, you talk of change all the procs in faisaint ensure they do an insert on a table that is used to log each proc that is executed?
Reply With Quote
  #4  
Old 20-11-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
Re: Consolidate MLD in sybase

To check the value of "number of open ..." - Sp_monitorconfig 'all' and see the column "max used" and "num reuse.

For auditing - no - we can set up Sybase auditing (via the base sybsecurity) and then turn on the auditing of the exec stored procs (via sp_audit 'exec_procedure', 'all', 'all ',' on ').

But actually, if you have a relatively small number of procs that are suspected not to be used then we can also add logging to the same proc ...
Reply With Quote
  #5  
Old 20-11-2009
Member
 
Join Date: Jun 2009
Posts: 56
Re: Consolidate MLD in sybase

Thanks for the info. I've tested a few of your scripts in the tutorials. However I had the question: is there a query to find out when last started dataserver? Indeed I do not control what level of use of databases. Column OptSelectCount of monOpenObjectActivity gives me the number of times the index was read to the subject? So I can deduce that each use of "select" on the table must not pass the reading of the index associated?
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Consolidate MLD in sybase"
Thread Thread Starter Forum Replies Last Post
How to append one array to Another, and Consolidate them in vba Tarani MS Office Support 10 24-02-2012 04:10 PM
sybase installer jsalazar Software Development 1 16-08-2011 03:46 PM
Need Sybase Database Backup Program Muwafaq Software Development 5 03-03-2010 12:52 AM
How to know the balance of each month in Sybase Banjiji Software Development 3 20-11-2009 07:45 PM
Sybase tutorial djbbenn Software Development 2 01-08-2009 07:43 PM


All times are GMT +5.5. The time now is 11:02 PM.