Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2011
    Posts
    5

    Unanswered: Database Performance

    Hi,

    We don't have a dba. Some of our processes are taking a long time. I am wondering if anybody can advice me what i need to do on the database level to help tune the performance. For starters,

    tablespace size(mb) free(mb) %free %used
    UNDOTBS1 200 180.6875 90 10
    SYSAUX 350 20.375 6 94
    USERS 12183.75 353.125 3 97
    SYSTEM 520 10.3125 2 98
    TEMP 674 0 0 100

    from the looks of it, are my tablespaces running out of space? Will this affect performance? We are using oracle 10g.

    Thank you.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Your best bet is to find a DBA - there are a lot of freelancers who can quickly help you. If you insist on doing it yourself, start with reading the Concepts manual.

  3. #3
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    TEMP tablespace has AUTOEXTEND ON?
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  4. #4
    Join Date
    Aug 2003
    Location
    Guwahati, India
    Posts
    33
    Can you elaborate a little further.
    Like the OS and the version of Oracle you are using and the free disk space.
    G J Shankar Nath
    IT Consultant, Vedswasti Services Pvt. Ltd
    Guwahati-781007
    India

  5. #5
    Join Date
    Aug 2008
    Posts
    147
    Is this a sudden drop in performance? Is it a specific query? If so , then it may be able to analyse the plan on the query .
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  6. #6
    Join Date
    Jul 2011
    Posts
    5
    i'll get back on the exact oracle and OS version. But to answer the rest first, i would say the performance got slower as the company grew in headcount. They kinda grew 2x. The target table that our application will write to has over 3 million records.

    Autoextend is on.

    Tblspc_name bytes autoext maxbytes increment by
    USERS 12775587840 YES 34359721984 160
    SYSAUX 367001600 YES 34359721984 1280
    UNDOTBS1 209715200 YES 34359721984 640
    SYSTEM 545259520 YES 34359721984 1280

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    run statspack/AWR report during busy time covering 15 minute period
    It can contain clues to problematic SQL statements
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    also, do you gather statistics on a regular basis (daily?). Its makes a very large difference in performance.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    do as below so we can know complete Oracle version & OS name.

    Post via COPY & PASTE complete results of
    SELECT * from v$version;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Jul 2011
    Posts
    5
    Hi all,

    I managed to get the versions as requested:

    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
    PL/SQL Release 10.2.0.1.0 - Production
    CORE 10.2.0.1.0 Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production
    OS- MS Windows Server 2003 Standard Edition Service Pack 2
    Diskspace free - D: 203 GB (Where oracle is located)
    C: 32.3 GB (OS)

    I don't gather statistics on a regular basis, however i did try to compute statistics and rebuild index but didn't help at all on the performance.

    I will need to read up on statspack, don't know how to use it.

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •