Re: Fine-Tuning and Monitoring: Can oracle 7.3.3. Enterprise Manager for NT help?

Patrick Tan wrote:
>
> Hello,
> I am currently experiencing problems with my Production Oracle Database.
> It is extremely slow in inserting records into certain tables lately.
>
> We realize we made several mistakes such as:
> * Using too many views to simplify SQL statements (reduces table joins).
> * Too many constraints which caused lots of checking prior to deletion.
> * Defragmented Tablespaces.
>
> Does anyone know how I might be able to
> - use Enterprise Manager to monitor performance problems,
> defragmentation etc?
> - Any SQL queries which takes more than 10 secs to execute?
>
> Any recommendations on tools (commercial or otherwise) which could help us
> improve
> a Production Database's performance?
>
> Thanks,
> -Patrick-

Hello Patrick,

are there to much full table scans? May be our tool HORA (see wwww.keeptool.swn.de for a trial version) will help you. Look at the performance page of HORA. Here are some information what HORA will show You.

The number of SQL executions and the quota of full table scans are displayed. A distinction will be made between small and large tables. For tables which have only a few rows a full table scan will not cause any problem if you do not expect an extreme increase of the data volume, for example in the development phase of database systems. The quota of full table scans for large tables, however, should be small.

When the monitoring function is active the quota of full table scans may be observed for a longer time. Problematical application program segments could be localized. Use Oracle's tkprof tool for any further examination. In Hora's user page switch on the SQL trace for the corresponding session for the critical time period. Problematical SQL statements could be analyzed in Hora's SQL page. The graphical EXPLAIN PLAN shows the SQL execution plan generated by the Oracle optimizer. The Oracle optimizer could be influenced by a reformulation or by hints. The effects can be observed directly in Hora.

In January there will be a new version of HORA with extended functionality.

regards Jan Dieckmann, KeepTool GbR

Hora (c) KeepTool Software GbR Received on Mon Nov 24 1997 - 00:00:00 CST