Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle performance tuning question

Re: Oracle performance tuning question

From: joel garry <joel-garry_at_home.com>
Date: Mon, 30 Jul 2007 16:18:31 -0700
Message-ID: <1185837511.451229.317070@e9g2000prf.googlegroups.com>


On Jul 29, 2:28 pm, sybra..._at_hccnet.nl wrote:
> On Sun, 29 Jul 2007 08:07:45 -0700, "jshen...._at_gmail.com"
>

> >db_name = "codb"
> >instance_name = codb
>
> >service_names = co65db
>
> >control_files = ("/oracle/oradata/codb/control/rlvcontrol01", "/oracle/
> >oradata/codb/control/rlvcontrol02", "/oracle/oradata/pi
> >n65db/control/rlvcontrol03")
>
> >open_cursors = 500
> >max_enabled_roles = 30
>
> >shared_pool_size = 800000000
>
> >large_pool_size = 30M
> >java_pool_size = 0
>
> >log_checkpoint_interval = 10000
> >log_checkpoint_timeout = 1800
>
> >#processes = 600
> >processes = 1200
>
> >log_buffer = 5242880
> >log_archive_start = true
> >log_archive_dest_1 = "location=/arch/codb"
> >log_archive_format = arch_%t_%s.arc
>
> >#DBCA uses the default database value (30) for max_rollback_segments
> >#100 rollback segments (or more) may be required in the future
> >#Uncomment the following entry when additional rollback segments are
> >created and made online
> >#max_rollback_segments = 101
> ># If using private rollback segments, place lines of the following
> ># form in each of your instance-specific init.ora files:
>
> >rollback_segments =
> >(COROLL00,COROLL01,COROLL02,COROLL03,COROLL04,COROLL05,COROLL06,COROLL07,C­OROLL08,COROLL09,COROLL10,P
> >INROLL11,COROLL12,COROLL13,COROLL14,COROLL15,COROLL16,COROLL17,COROLL18,CO­ROLL19,COROLL20,COROLL21,COROLL22,COROLL23,PIN
> >ROLL24,COROLL25,COROLL26,COROLL27,COROLL28,COROLL29,COROLL30,COROLL31,CORO­LL32,COROLL33,COROLL34,COROLL35,COROLL36,PINRO
> >LL37,COROLL38,COROLL39,COROLL40,COROLL41,COROLL42,COROLL43,COROLL44,COROLL­45,COROLL46,COROLL47,COROLL48,COROLL49,COROLL
> >50,COROLL51,COROLL52,COROLL53,COROLL54,COROLL55,COROLL56,COROLL57,COROLL58­,COROLL59,COROLL60,COROLLBIG,COROLL61,COROLL6
> >2,COROLL63,COROLL64,COROLL65,COROLL66,COROLL67,COROLL68,COROLL69,COROLL70,­COROLL71,COROLL72,COROLL73,COROLL74,COROLL75,
> >COROLL76,COROLL77,COROLL78,COROLL79,COROLL80,COROLL81,COROLL82,COROLL83,CO­ROLL84,COROLL85,COROLL86,COROLL87,COROLL88,PI
> >NROLL89,COROLL90,COROLL91,COROLL92,COROLL93,COROLL94,COROLL95,COROLL96,COR­OLL97,COROLL98,COROLL99)
>
> ># Global Naming -- enforce that a dblink has same name as the db it
> >connects to
> > global_names = true
>
> ># Uncomment the following line if you wish to enable the Oracle Trace
> >product
> ># to trace server activity. This enables scheduling of server
> >collections
> ># from the Oracle Enterprise Manager Console.
> ># Also, if the oracle_trace_collection_name parameter is non-null,
> ># every session will write to the named collection, as well as
> >enabling you
> ># to schedule future collections from the console.
> ># oracle_trace_enable = true
>
> ># define directories to store trace and alert files
> >background_dump_dest = /oracle/app/oracle/admin/codb/bdump
> >core_dump_dest = /oracle/app/oracle/admin/codb/cdump
>
> >user_dump_dest = /oracle/app/oracle/admin/codb/udump
>
> >db_block_size = 4096
>
> >remote_login_passwordfile = exclusive
>
> >os_authent_prefix = ""
>
> >compatible = "8.1.0"
> >sort_area_size = 65536
> >sort_area_retained_size = 65536
>
> >disk_asynch_io = TRUE
> >db_files=1024
>
> >#lock_sga=true
> >#
> >db_block_lru_latches=12
> >audit_trail = false
> >transaction_auditing = false
> >optimizer_mode = rule
> >db_file_multiblock_read_count = 16
> >db_writer_processes=4
>
> >parallel_execution_message_size=8192
> >sort_multiblock_read_count = 8
> >UTL_FILE_DIR=/oracle/migrate
>
> >#
> >HPUX_SCHED_NOAGE = 178
>
> Setting lock_sga to true would be yet another rash measure.

I don't think it's so rash if you set pre_page_sga, which preloads the sizing. I don't think I've used it myself on hp-ux, since any actual usage of swap space seems to send everything to hell anyways.

> You would need to closely verify your current parameters.
> To mention a few things with the worst first:
> your db_block_size is 4k, while as far as I remember HP always reads
> 8k blocks. So, even if you do a single block read and ask for 4k, HP
> will still read 8k, and simply transfer only 4k to Oracle.
> Changing db_block_size requires rebuilding the database, so beware.
>
> db_file_multiblock_read_count seems too small, as HP can read 1 Mb
> ahead.
>
> shared_pool_size is probably way too big. Did you ever query v$sgastat
> to find out how much shared pool is free?
>
> Large_pool is set to 30M and you don't use it, as you are not using
> MTS
> sort_area_size is 64k, should be 1 M.

If he's using RMAN, large_pool might be useful. Mine's set to twice that. Depends on how exactly RMAN is being used.

>
> But then the biggest problem of them all:
> you didn't set db_cache_size or db_block_buffers.
> The default is only 200 blocks.
> You should verify this parameter at once!
> You are not using any cache!
> I'm not surprised you are waiting for disk big time.

LOL if that's the case!

As I remember it (and I could be wrong - or perhaps it varied by exact server hardware, I seem to recall there was a webpage at hp that had the matrix) the OP's configuration must be 64 bit Oracle. However if I'm wrong and it is 32 bit Oracle, there are metalink notes about how to play games to get an SGA > 1.75G (Note:77310.1).

Note:68105.1 commonly misconfigured system parameters, is handy.

>> The oracle server is running with Async I/O.

>> Should I reconstruct DB files to larger size and fewer number ?

These two statements will lead to some problem, I think. Doesn't 11i require raw devices with Async?

jg

--
@home.com is bogus.
http://news.com.com/8301-10784_3-9747705-7.html
Received on Mon Jul 30 2007 - 18:18:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US