No question at this time
The DBA-Village forum
|Ever registered users||48484|
|Total active users||1453|
|Act. users last 24h||2|
|Act. users last hour||0|
|Registered user hits last week||97|
|Registered user hits last month||634||
PLATO - Free PL/Sql Package for Auditing and Tuning Oracle
Welcome to the homepage of the PLATO package.
PLATO is a free tool that allows you to analyze your database in a nice report.
It indicates problems in your database regarding storage, security, organization and much more.
A tuning module gives you a snapshot of your database at a given period and
the systemstate shows you what is going on in your database at that moment.
The output of the tool is a report in TXT or HTML format, but the package can
also be called online using Oracle's mod_plsql.
Please try it out and enjoy. Just make sure you are registered on DBA-Village (free)
in order to be able to download the tool.
Info: On July 6th, 2008 version 50 has been run in already more than 1200
databases without any negative feedback, so stability and safety should be ok.
All versions together have already been run in more than 3400 databases.
- Q: When setting up the tool do we need to use the sys account?
- A: SYS is required, because there are some queries on x$ views (to see underscore parameters of init.ora). It is not possible to select from these views as a non-SYS user directly. So SYS has to create a view and grant select on that view to the plato user. I may consider building a version that skips these sections and only requires select any dictionary. Mmm… I’ll think about it. Personally I prefer to create a separate user (e.g. plato) for installing the package, but technically there is no problem installing it as SYS or SYSTEM.
- Q: When the package is running I understand that it is only reading tables and creating a temp table.
- A: It does also a lot of PL/SQL, but for the rest this is correct. The temp table is only used for the Tuning module. It will store a lot of statistics in this table, then wait a certain time, then store the same statistics again and then make the difference (calculate the increment) of the statistics. The Audit and Inventory modules only do select’s on a lot of data-dictionary and v$ tables.
- Q: Is there a chance that we could harm/corrupt the database when this is running?
- A: In theory not. I have no knowledge of issues in 9.0 or higher. The only issue I once had was in 8.1.7 due to a bug. If you create a wrapped package and the status is invalid (e.g. because of missing grants) then you get into trouble when installing the same package again without dropping the invalid one first. But as said, in 9.0 or higher I have never had any issue and I have installed it already in hundreds of databases. But of course, a new bug in Oracle is always possible…
Reference: Bug# 2422726 OERI possible when RECOMPILING a WRAPPED PACKAGE - See Metalink Note:2422726.8
I have no idea, and never run it in exceptional databases, like database with 50Gb shared pool or +2000 datafiles. It will not crash anything, but it might take a long time to complete or use a lot of space in the temp table when running the tuning module.
- Q: What are the majority of tables you are reading. Is it the V$tables?
A: Indeed, v$ and dba_ views.
- Q: Is there much overhead to the database and system overhead? Will the users see a drop in performance?
- A: First of all, you should not run it constantly. Only now and then if you want to make a report of your database. On the other hand, while it is running, it is fully active. My experience is that the most difficult part is the tablespace overview. It is know that in many Oracle versions, queries on dba_segments can be slow, especially when there are a lot of segments in the database (like apps or SAP databases). In the worst case you can always kill the session. You can easily follow the progress of the package using select action from v$session where module = ‘PLATO’.
- Q: What’s the average time for it to complete?
- A: Between 10 seconds and 1 or 2 minutes. As said above, having many objects in your database will slow down the execution.
- Q: Would you have something that runs against MySQL?
- A: No.
- Q: When you are getting tuning results how far is it looking back?
- A: Most of the statistics indicate the increment during the interval specified (plato.tuning('format',[interval])). Things that are since instance startup are: "Database statistics" and "Top 7 SQL".