Hi folks,

since the latest upgrade, we are having an issue with application performance - even within sections which were untouched. Application loads and works very slow - unusable in future live environment.

We would like to identify if it's something related to formspider or some other (maybe incorrect) setting in database.

Do we have any possibilities to identify behavior of formspider in database and some tools/queries which would identify loading times in application sections?

It might not be related to formspider at all, but we need to be sure. Database was restarted, no objects are locked, etc ...

Many thanks, Tomas

asked 11 Jan '13, 02:27

Tomeo's gravatar image

Tomeo
162139
accept rate: 3%


Hi Tomas,

You may use DBMS_PROFILER to trace your performance issues.

How to install DBMS_PROFILER:

  • open $ORACLE_HOME/rdbms/admin folder. Find profload.sql and proftab.sql scripts in this folder.
  • run profload.sql in SYS schema and run proftab.sql in schema where you will use the profiler.

Using DBMS_PROFILER:

  • start and stop the profiler (Add dbms_profiler.start_profiler and dbms_profiler.stop_profiler commands to your procedure where you want to start tracing performance and stop it.):

    procedure your_procedure_name is begin dbms_profiler.start_profiler('your_procedure_name'); -- your existing methods or Formspider APIs dbms_profiler.stop_profiler; end;

  • collect the data on tables:

Get the "runid" parameter from followinq query:

select runid, run_owner, run_date, run_comment
from plsql_profiler_runs;

Use "runid" parameter in followind query to see objects used:

select runid, unit_number, unit_type, unit_owner, unit_name, unit_timestamp
from plsql_profiler_units
where  runid = &runid;

Use "runid" and "unit_number" parameters to see details on specific object:

select pd.unit_number, pu.unit_name, pd.line#, pd.total_occur passes, round(pd.total_time / 1000000000,5) total_time, us.text text
from plsql_profiler_data pd, plsql_profiler_units pu, user_source us
where pd.runid = &runid
and pd.unit_number = &unit_number
and pd.runid = pu.runid
and pd.unit_number = pu.unit_number
and us.name = pu.unit_name
and us.line = pd.line#
and us.type in ('PACKAGE BODY','PROCEDURE','FUNCTION')
and pd.total_time > 0;

Regards

link

answered 11 Jan '13, 05:03

Samet%20Basaran's gravatar image

Samet Basaran
1372
accept rate: 24%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×1

Asked: 11 Jan '13, 02:27

Seen: 568 times

Last updated: 11 Jan '13, 05:03

Related questions


© Copyright Gerger 2017