Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Posts
    93

    Unanswered: Tuning an Oracle DB

    I am using TOAD & OEM (9i) for all my databases. However, oracle is driving is very very slow and I need to improve the performance.

    I have increased tablespaces and checked all the usual disk drive, but its hasn't improved. Can someone recommend SQL scripts I could run to increase the speed etc....anything that I should do...

    Cheers
    Emer
    Cheers
    Etravels

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You need to find out where the contention or slowness is ... Is it hard drive speed or contention, archiving, etc ...

    What is the OS, Oracle version, drive layout, etc ... ?
    When was the last time statistics were updated on the objects (if you are using cost based optimization) ?
    What does the initxxx.ora file look like ?

    Are queries written inefficiently ??? Are there indexes on the larger tables ???

    Attached is a script to find the 25 TOP sql queries (by buffer gets) ... You
    can take the SQL that is running, run it in SQLPlus setting autotrace on
    to get an explain plan to look at ...

    Need a lot more information on your environment in order to help out

    HTH
    Gregg
    Attached Files Attached Files
    • File Type: txt 1.txt (700 Bytes, 85 views)

  3. #3
    Join Date
    Feb 2004
    Posts
    93

    thanks for this

    Here is a copy of the ini.ora file, any ideas?

    I have ran that pLSQL cursor : result:
    ---------- ----------------------
    33020126 begin Upload(:V00001,:V00002,:V00003,:V00004,:V00005); end;
    29220304 SELECT COUNT(*) FROM CHARTER_PRIMARY_KEY WHERE TABLE_NAME = UPP
    ' PER(:b1) AND COLUMN_NAME = UPPER(:b2)
    27968337 begin Maintain_X_Table(:V00001,:V00002); end;
    13984246 SELECT COUNT(*) FROM ANY_CHARTER_COLUMN WHERE OWNER = :b1 AND
    ' TABLE_NAME = UPPER(:b2) AND COLUMN_NAME = UPPER(:b3)
    13983602 SELECT DATA_TYPE,DATA_PRECISION,DATA_SCALE FROM ANY_CHARTER_COLU
    ' UMN WHERE OWNER = :b1 AND TABLE_NAME = UPPER(:b2) AND COLUMN_NA
    ' ME = UPPER(:b3)
    11575472 begin Upload(:V0001,:V0002,:V0003,:V0004,:V0005); end;
    4759412 SELECT COUNT(*) FROM DBA_ROLE_PRIVS WHERE GRANTEE = UPPER(:b1)
    ' AND GRANTED_ROLE = UPPER(:b2)
    4089579 SELECT COLUMN_NAME FROM CHARTER_PRIMARY_KEY WHERE TABLE_NAME =
    ' UPPER(:b1) ORDER BY POSITION
    3384623 /* OracleOEM */ SELECT DISTINCT u.name FROM sys.obj$ o, sys.user$
    ' u WHERE o.owner# = u.user#
    3097570 select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where
    ' e t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0
    2841581 begin Maintain_X_Indexes(:V00001,:V00002,:V00003); end;
    2263724 select o.owner#, u.name, o.name, o.namespace,
    ' o.obj#, d.d_timestamp, nvl(d.property,0) from dependency
    ' $ d, obj$ o, user$ u where d.p_obj#=:1 and (d.p_timestamp=:2 o
    ' r d.property=2) and d.d_obj#=o.obj# and o.owner#=u.user# an
    ' d decode(:3,0,0,o.type#)=:3
    1946036 SELECT TABLESPACE_NAME,BYTES,INITIAL_EXTENT,NVL(NEXT_EXTE NT,INITIA
    ' AL_EXTENT),MAX_EXTENTS,NVL(PCT_INCREASE,0) FROM USER_SEGMENTS W
    ' HERE SEGMENT_NAME = :b1
    1710680 SELECT COUNT(*) FROM CHARTER_CATALOG CAT WHERE CAT.TABLE_NAME =
    ' = UPPER(:b1) AND CAT.TABLE_TYPE LIKE 'REFERENCE%' AND NOT EXIST
    ' S (SELECT * FROM CHARTER_COLUMN COL WHERE COL.TABLE_NAME = CAT
    ' .TABLE_NAME AND COL.COLUMN_NAME = 'DORMANT' )
    1635910 begin Table_Operation(:V00001,:V00002,:V00003,:V00004,:V 00005,:V00
    ' 0006,:V00007,:V00008,:V00009,:V00010,:V00011,:V000 12,:V00013,:V000
    ' 14,:V00015,:V00016,:V00017,:V00018,:V00019); end;
    1004123 select owner C1, table_name C2, pct_free C3, pct_used C4,
    ' avg_row_len C5, num_rows C6, chain_cnt C7, chain_cnt/num_rows
    ' C8 From dba_tables where owner not in ('sys', 'system') and tabl
    ' e_name in (select table_name from dba_tab_columns where data_type
    ' in ('row', 'long raw')) and chain_cnt >0 order by chain_cnt desc
    969444 update Twin_HIERARCHY_COMBINATION T set HIERARCHY_ID = ( select S.
    ' .HIERARCHY_ID from HIERARCHY_COMBINATION S where S.HIERARCHY_COMBI
    ' NATION_ID = T.HIERARCHY_COMBINATION_ID ), HIERARCHY_01 = ( select
    ' S.HIERARCHY_01 from HIERARCHY_COMBINATION S where S.HIERARCHY_COMB
    ' INATION_ID = T.HIERARCHY_COMBINATION_ID ), HIERARCHY_02 = ( select
    ' S.HIERARCHY_02 from HIERARCHY_COMBINATION S where S.HIERARCHY_COM
    ' BINATION_ID = T.HIERARCHY_COMBINATION_ID ), HIERARCHY_03 = ( selec
    ' t S.HIERARCHY_03 from HIERARCHY_COMBINATION S where S.HIERARCHY_CO
    ' MBINATION_ID = T.HIERARCHY_COMBINATION_ID ), HIERARCHY_04 = ( sele
    ' ct S.HIERARCHY_04 from HIERARCHY_COMBINATION S where S.HIERARCHY_C
    ' OMBINATION_ID = T.HIERARCHY_COMBINATION_ID ), HIERARCHY_05 = ( sel
    ' ect S.HIERARCHY_05 from HIERARCHY_COMBINATION S where S.HIERARCHY_
    ' COMBINATION_ID = T.HIERARCHY_COMBINATION_ID ), HIERARCHY_06 = ( se
    ' lect S.HIERARCHY_06 from HIERARCHY_COMBINATION S where S.HIERARCHY
    ' _COMBINATION_ID = T.HIERARCHY_COMBINATION_ID ), HIERARCHY_07 = ( s
    ' elect S.HI
    959406 SELECT COUNT(*) FROM CHARTER_COLUMN WHERE TABLE_NAME = UPPER(:b
    ' b1) AND COLUMN_NAME = 'PRIMARY_FLAG'
    825034 SELECT MIN(START_TIME),MAX(FINISH_TIME),SUM(HOURS) FROM WORKING_
    ' _HOURS WHERE DAY = RTRIM(TO_CHAR(:b1,'DAY'))
    794479 SELECT COLUMN_NAME FROM CHARTER_COLUMN WHERE TABLE_NAME = UPPER
    ' R(:b1) ORDER BY POSITION
    708981 SELECT DISTINCT (CONSTRAINT_NAME) FROM CHARTER_PRIMARY_KEY WHER
    ' RE TABLE_NAME = UPPER(:b1)
    666789 select username from sys.all_users order by username
    593508 begin EMER.QUEST_SL_USER_MANAGER.grant_user(1); end;
    572822 /* OracleOEM */ SELECT d.status "Status", d.tablespace_name "Name"
    ' ", d.contents "Type", d.extent_management "Extent Management", TO_
    ' CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", T
    ' O_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99999999.999')
    ' ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0), '99999999.999') "Used (
    ' M)", TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),
    ' '990.00') "Used %" FROM sys.dba_tablespaces d, (select tablespace_
    ' name, sum(bytes) bytes from dba_data_files group by tablespace_nam
    ' e) a, (select tablespace_name, sum(bytes) bytes from dba_free_spac
    ' e group by tablespace_name) f WHERE d.tablespace_name = a.tablespa
    ' ce_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT (d
    ' .extent_management like 'LOCAL' AND d.contents like 'TEMPORARY') U
    ' NION ALL SELECT d.status "Status", d.tablespace_name "Name", d.con
    ' tents "Type", d.extent_management "Extent Management", TO_CHAR(NVL
    ' (a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(N
    ' VL(t.bytes
    525462 /* OracleOEM */ SELECT /*+ ordered no_merge(v) */ v.status "Status
    ' s", d.file_name "Name", d.tablespace_name "Tablespace", TO_CHAR(NV
    ' L(d.bytes / 1024 / 1024, 0), '99999990.000') "Size (M)", TO_CHAR(N
    ' VL((d.bytes - NVL(s.bytes, 0))/1024/1024, 0),'99999999.999') || '/
    ' ' || TO_CHAR(NVL(d.bytes/1024/1024, 0),'99999999.999') || '/' || N
    ' VL(d.autoextensible, 'NO') "Used (M)", TO_CHAR(NVL((d.bytes - NVL(
    ' s.bytes, 0)) / d.bytes * 100, 0), '990.00') "Used %" FROM sys.dba_
    ' data_files d, v$datafile v, (SELECT file_id, SUM(bytes) bytes FRO
    ' M sys.dba_free_space GROUP BY file_id) s WHERE (s.file_id (+)= d.
    ' file_id) AND (d.file_name = v.name) UNION ALL SELECT /*+ ordered n
    ' o_merge(v) */ v.status "Status", d.file_name "Name", d.tablespace_
    ' name "Tablespace", TO_CHAR(NVL(d.bytes / 1024 / 1024, 0), '9999999
    ' 0.000') "Size (M)", TO_CHAR(NVL(t.bytes_cached/1024/1024, 0),'9999
    ' 9999.999') || '/' || TO_CHAR(NVL(d.bytes/1024/1024, 0),'99999999.9
    ' 99') || '/' || NVL(d.autoextensible, 'NO') "Used (M)", TO_CHAR(NVL
    ' (t.bytes_c
    cheers
    Etravels
    Attached Files Attached Files
    Cheers
    Etravels

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You seem to be running Oracle 8.1.6 ... Function based indexing will not help here ...

    Most of the problem sql seems to revolve around tables
    CHARTER_PRIMARY_KEY and ANY_CHARTER_COLUMN

    By using UPPER in the where clause, you are taking any indexing out of the
    picture ... How many rows are do you have in those tables ??? Are they
    indexed ??? Can you take away the UPPER function ??? When were the tables last analyzed ???

    What does the procedure UPLOAD and Maintain_X_Table do ??? Are there
    queries or updates in there that are causing problems ???
    How many users are you running ??? You may be having some problems with rollback segments ... Is there any contention there ? You should have roughly 4 users per rollback segment ...

    Based on the number of "buffer gets" I believe that the majority of the problems are with the 2 above tables and procedures ... Start pulling out the queries from the procedures and run them thru tracing .

    HTH
    Gregg

  5. #5
    Join Date
    Nov 2002
    Posts
    833
    run statpacks reports and take snapshots

    switch on tracing and analyze the output with tkprof
    or even set event 10046

  6. #6
    Join Date
    Feb 2004
    Posts
    93

    Red face gbrabham

    Thanks for your message:
    CHARTER_PRIMARY_KEY and ANY_CHARTER_COLUMN are all views..have very few data in the tables.
    We are a software vendor and hold lots of schemas for all our customers. its not like we have lots of data in the table cos we don't hold that information as such.
    I don't get involved with the development of the scripts as all our software is done in VB (front end ) either sql server or Oracle db (where I look after)..

    Upload' is used to upload reference data from one database to another . for example all clients have 2 database, on being the main database and the other admin database which holds all the reference data for the main database. ie if we make a change to the main db we must make changes within the admin databases to populate to the main db.

    Maintain_x procedure ,adds or enlarges columns to our reference tables..all the x tables are within the admin database

    Rollbacks; I am not really sure, must check this out:contention? what is this?

    re; trace files how do I do this? sorry I am really a newbie to the whole DBA

    Cheers
    Emer
    Cheers
    Etravels

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    as Osy45 stated, 95% of the time the DATABASE is slow because of
    the sql the application is throwing at it. You need to tune the sql
    that is being run.
    This could entail re-writing the sql/procs, indexing, etc.

    The best way would be to do as suggested and run statspack and also
    trace the sessions from the application and then TKPROF those trace files.
    I would then try to match the statspack queries to the TKPROF output.
    Start with the statspack queries that use the most logical and physical
    I/O and move down from there. Look at your TKPROF where those queries match and check the explain plan in there and how much logical
    I/O is being performed. If you can lower the logical, the physical I/O will
    normally also be lowered at the same time.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    With free advice you get what you paid for it.
    Here is an alternative methodology which really does work.
    Modify initSID.ora file to contain the following line and bounce the DB
    event="10046 trace name context forever, level 12"
    Now EVERY session which runs will create a trace file in udump so have plenty of disk space under it.
    Next run the application especially those operations which are the slowest.
    Next run all the trace file thru TKPROF and make *tkp files with EXPLAIN=user/pass
    Next grep -i total *tkp
    Look for lines where there is a "large" difference between CPU time & Elaplsed time; 'cuz these are the problem operations.
    Use these values to find which file contains them to the the problem SQL & EXPLAIN_PLANS
    Optimize the problem SQL to get Elapsed close to CPU time.
    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.

  9. #9
    Join Date
    Feb 2004
    Posts
    93

    great advice from all!! however,statspack gettin this..

    I have managed to locate how to create a statspack but I am receiving the following:
    SPCPKG.LIS FILE:
    Creating Package STATSPACK...
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0641: "SHOW ERRORS" requires connection to server
    Creating Package Body STATSPACK...
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0641: "SHOW ERRORS" requires connection to server

    NOTE:
    SPCPKG complete. Please check spcpkg.lis for any errors.

    I am connecting as sys @sysdba...

    any ideas?
    Cheers
    Etravels

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >any ideas?
    Hire somebody who can do more than just spell S-Q-L.
    You should NOT be selling a product that runs on top of Oracle when you don't know how to even log into it.
    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.

  11. #11
    Join Date
    Feb 2004
    Posts
    93

    I've connected as sysdba

    ie sys/io@io as sysdba

    then following a script as given and how to install it., with prompts for tablesapaces which didn't happen....

    Well I have just finished my DBA course and starting on a new job..You have to learn somehow and I guess its all about experience on hand!....This forum is the best around!!

    E
    Cheers
    Etravels

Posting Permissions

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