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

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

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

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

run statpacks reports and take snapshots

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

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

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.

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.

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?

>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.

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