Re: create db & init.ora optimization

Jan

two thing to get

 TOAD (www.toadsoft.com for the free version) will help identify some of the more common things to tune in the init.ora

and the O'rielly book on Oracle Performance by Gurry&Corrigan (ISBN 1565922379) which will give you a nice plan of action on what to do and how to do it.

Bascially you need to at what the DB is doing so you can modify the init.ora params accordingly.

Martin

Jan-Erik Rosinowski wrote:
>
> hi,
>
> i'm trying to optimize an ora 7.3.4 database for about 10 concurrent
> users, which deals with blobs. nt-server w/ 128mb ram. typical
> userload consists of permanent recreation of stored procs (yup) and
> inserts/updates of hundreds of rows in one table.
>
> most query's plans look ok, so i know try to optimize the db-settings.
> any comments?
>
> ################################# init.ora
>
> audit_trail = false
> background_dump_dest = %dbpath%trc%oracle_sid%
> compatible = 7.3.0.0.0
> control_files =
> (%dbpath%ctl1%oracle_sid%.ora,%dbpath%ctl2%oracle_sid%.ora) # ...
> db_block_buffers = 5000
> db_block_size = 8192
> db_file_multiblock_read_count = 8
> db_files = 100
> db_name = %oracle_sid%
> dml_locks = 200
> log_archive_dest = %dbpath%lga%oracle_sid%
> log_archive_start = true
> log_buffer = 1048576
> log_checkpoint_interval = 20000
> max_dump_file_size = 10240
> open_cursors = 200
> optimizer_mode = choose
> processes = 100
> remote_login_passwordfile = shared
> rollback_segments =
> (rb0,rb1,rb2,rb3,rb4,rb5,rb6,rb7,rb8,rb9)
> row_cache_cursors = 100
> sequence_cache_entries = 10
> shared_pool_size = 15000000
> snapshot_refresh_processes = 1
> sort_area_size = 1000000
> text_enable = true
> timed_statistics = true
> user_dump_dest = %dbpath%trc%oracle_sid%
>
> ################################# build_db
>
> -----------------------------------------------------------------------------
> -----------------------------------------------------------------------------
> -----------------------------------------------------------------------------
>
> startup nomount pfile=%dbpath%init%oracle_sid%.ora
>
> -----------------------------------------------------------------------------
>
> create database %oracle_sid%
> controlfile reuse
> logfile
> group 1
> ('%dbpath%lg1a%oracle_sid%.dbf',
> '%dbpath%lg1b%oracle_sid%.dbf') size 10m reuse,
> group 2
> ('%dbpath%lg2a%oracle_sid%.dbf',
> '%dbpath%lg2b%oracle_sid%.dbf') size 10m reuse
> maxlogfiles 50
> maxdatafiles 50
> archivelog
> datafile
> '%dbpath%sys0%oracle_sid%.dbf'
> size 10m
> reuse
> autoextend on next 10m maxsize unlimited
> character set
> we8iso8859p1;
>
> -----------------------------------------------------------------------------
> -----------------------------------------------------------------------------
> -----------------------------------------------------------------------------
>
> -- tablespaces
>
> create tablespace user_data
> datafile '%dbpath%usr0%oracle_sid%.dbf'
> size 20m
> reuse
> autoextend on next 10m maxsize unlimited;
>
> create tablespace rollback_data
> datafile '%dbpath%rbs0%oracle_sid%.dbf'
> size 10m
> reuse
> autoextend on next 5m maxsize unlimited;
>
> create tablespace temporary_data
> datafile '%dbpath%tmp0%oracle_sid%.dbf'
> size 5m
> reuse
> autoextend on next 5m maxsize unlimited;
>
> -----------------------------------------------------------------------------
> -----------------------------------------------------------------------------
> -----------------------------------------------------------------------------
>
> -- create 10 rollback segments. allows 10 concurrent users with open
> -- transactions updating the database. this should be enough.
>
> create public rollback segment rbdummy storage(initial 1m next 1m)
> tablespace system;
> alter rollback segment rbdummy online;
>
> create public rollback segment rb0 storage(initial 1m next 1m)
> tablespace rollback_data;
> create public rollback segment rb1 storage(initial 1m next 1m)
> tablespace rollback_data;
> create public rollback segment rb2 storage(initial 1m next 1m)
> tablespace rollback_data;
> create public rollback segment rb3 storage(initial 1m next 1m)
> tablespace rollback_data;
> create public rollback segment rb4 storage(initial 1m next 1m)
> tablespace rollback_data;
> create public rollback segment rb5 storage(initial 1m next 1m)
> tablespace rollback_data;
> create public rollback segment rb6 storage(initial 1m next 1m)
> tablespace rollback_data;
> create public rollback segment rb7 storage(initial 1m next 1m)
> tablespace rollback_data;
> create public rollback segment rb8 storage(initial 1m next 1m)
> tablespace rollback_data;
> create public rollback segment rb9 storage(initial 1m next 1m)
> tablespace rollback_data;
>
> alter rollback segment rb0 online;
> alter rollback segment rb1 online;
> alter rollback segment rb2 online;
> alter rollback segment rb3 online;
> alter rollback segment rb4 online;
> alter rollback segment rb5 online;
> alter rollback segment rb6 online;
> alter rollback segment rb7 online;
> alter rollback segment rb8 online;
> alter rollback segment rb9 online;
>
> alter rollback segment rbdummy offline;
>
> drop public rollback segment rbdummy;
>
> -----------------------------------------------------------------------------
> -----------------------------------------------------------------------------
> -----------------------------------------------------------------------------
>
> alter user sys identified by "ledersessel";
> alter user system identified by "ledersessel";
> alter user system temporary tablespace temporary_data;
> alter user system default tablespace user_data;
>
> -----------------------------------------------------------------------------
>
> create user rsp identified by "tiger"
> default tablespace user_data
> temporary tablespace temporary_data
> profile default
> quota unlimited on user_data;
>
> -----------------------------------------------------------------------------
>
> grant connect,dba,exp_full_database,imp_full_database
> to rsp with admin option;
> alter user rsp default role all;
>
> -----------------------------------------------------------------------------
> -----------------------------------------------------------------------------
> -----------------------------------------------------------------------------
>
> ciao, jan
>
> http://www.rsp.de - Software zur effizienten Erstellung und Verwaltung von Gutachten, Analysen, Pruefprotokollen etc.
Received on Wed Dec 08 1999 - 04:47:25 CST