This discussion is archived
3 Replies Latest reply: May 28, 2013 7:36 AM by marksmithusa RSS

Increasing Performance & Speed of Database

summee Newbie
Currently Being Moderated
Hi
In our office on 10-12 PC's we are using tool/software made from Oracle Forms & Reports 6i .The main task of this software/tool is to insert & update records into DB which maintained on UNIX server separately. There are total 25 users on Oracle Database .
Some of the user have large no of records to insert into DB & some have less. We are inserting records into DB on monthly basis. During each month end task of insert & update is at peek and what is happening is that some users which have large records to insert & update are taking enormous time than users which have less records & these large record users are almost hanging there for hours affecting whole DB's performance & slowing other office tasks.
Separate tablespace are allocated for each user and the size of tablespaces for users with large records is almost double of those with less record .Apart from month end during normal days the speed and performance of large record user is also less than the less record user but it is not affecting other office tasks. Now my question is how to increase the speed and performance of user with large records and also how to avoid effect of this on other office work do I need to change my DB's parameter for this?
Please guide!!
  • 1. Re: Increasing Performance & Speed of Database
    sybrand_b Guru
    Currently Being Moderated
    Your assumption changing database parameters will increase performance is incorrect.
    Almost always changing database parameters has little impact on performance, as it is almost always the application which is causing problems.
    Oracle recommends tuning the application first.

    Right now you have a 1-line question:
    My car is broken, please fix my car.

    Without further analysis nothing can be done, and you are effectively asking for free consultancy, without specifying any information, which will allow this forum to see what is going on.
    You would first need to run AWR reports and/or Statspack reports, to answer the main and only question
    'What is it waiting for'

    Without that the only advice which can be given is
    alter system set _fast=true scope=spfile
    and bounce the database.

    --------------
    Sybrand Bakker
    Senior Oracle DBA
  • 2. Re: Increasing Performance & Speed of Database
    rahulras Explorer
    Currently Being Moderated
    When your software is running slow, almost certainly, some SQL query (or queries) is/are running slow or your application have locking issues.
    First you need to find those queries. AWR report can be a good tool for that.
    Another very simple way is, when "your software is running slow", check in v$session table. That will show you which queries are running.

    Do you have DBAs? If they are not able to help you in this case, you need to hire new DBAs.
  • 3. Re: Increasing Performance & Speed of Database
    marksmithusa Journeyer
    Currently Being Moderated
    summee wrote:
    Hi
    In our office on 10-12 PC's we are using tool/software made from Oracle Forms & Reports 6i .The main task of this software/tool is to insert & update records into DB which maintained on UNIX server separately. There are total 25 users on Oracle Database .
    Which version of the database? Which version of the operating system?
    Some of the user have large no of records to insert into DB & some have less. We are inserting records into DB on monthly basis. During each month end task of insert & update is at peek and what is happening is that some users which have large records to insert & update are taking enormous time than users which have less records & these large record users are almost hanging there for hours affecting whole DB's performance & slowing other office tasks.
    Having to process more data will, usually, mean an increase in processing time.
    Separate tablespace are allocated for each user and the size of tablespaces for users with large records is almost double of those with less record .Apart from month end during normal days the speed and performance of large record user is also less than the less record user but it is not affecting other office tasks. Now my question is how to increase the speed and performance of user with large records and also how to avoid effect of this on other office work do I need to change my DB's parameter for this?
    I do not know.

    As above, you're basically asking 'my car is stalling, how do I make it go faster?'

    Your application logic might be completely screwed.
    Your statistics might be out of date.
    Most likely, you are suffering from a resource contention of some description - but that would be a blind guess based on the lack of information provided.
    Your server might be swapping.
    Backups might be running.
    It might be a Friday. Fridays are bad luck for DBAs.
    The db_go_fast parameter is probably not set to TRUE.

    In order for you to be able to determine what your problem is, you need to look at the monitoring that you have available.

    Assuming that you have the relevant licensing, check the AWR snapshot during these 'busy' times and that will tell you your top 5 wait events.

    If you run ADDM for the same period, it'll even tell you what's happening in managerspeak....'Lots of I/O is happening', 'Row lock contention', 'your cheese sandwich is mine', etc, etc.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points