RE: DATABASE TUNING

From: Murugaian Adi <adi_at_MarketingAssociates.com>
Date: Mon, 18 Sep 2000 11:34:06 -0400
Message-Id: <10623.117263@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C02185.E1B436E8
Content-Type: text/plain;

charset="windows-1252"

Stephane,

Thanks for your detailed reply.

I have block size of 8K.
I have just created the database, created few table and there is no updation still.

Here is my SGA

SVRMGR> show sga;

Total System Global Area                         76029936 bytes
Fixed Size                                          69616 bytes
Variable Size                                    34820096 bytes
Database Buffers                                 40960000 bytes
Redo Buffers                                       180224 bytes

SVRMGR> Here is my sys.v_paramter table values:
should I adjust any paramter value?

NUM	NAME	VALUE
9	processes	100
10	sessions	115
11	timed_statistics	FALSE
12	timed_os_statistics	0
13	resource_limit	FALSE
14	license_max_sessions	0
15	license_sessions_warning	0
18	cpu_count	2
21	instance_groups	
22	event	
23	shared_pool_size	11534336
26	shared_pool_reserved_size	576716
28	large_pool_size	422880
30	java_pool_size	20000K
31	java_soft_sessionspace_limit	0
32	java_max_sessionspace_size	0
33	pre_page_sga	FALSE
34	shared_memory_address	0
35	hi_shared_memory_address	0
36	use_indirect_data_buffers	FALSE
38	lock_sga	FALSE
45	lock_name_space	
47	enqueue_resources	924
60	nls_language	AMERICAN
61	nls_territory	AMERICA
62	nls_sort	
63	nls_date_language	
64	nls_date_format	
65	nls_currency	
66	nls_numeric_characters	
67	nls_iso_currency	
68	nls_calendar	
69	nls_time_format	
70	nls_timestamp_format	
71	nls_time_tz_format	
72	nls_timestamp_tz_format	
73	nls_dual_currency	
74	nls_comp	
75	disk_asynch_io	TRUE
76	tape_asynch_io	TRUE
78	dbwr_io_slaves	0
82	backup_tape_io_slaves	FALSE
86	db_file_direct_io_count	64
91	resource_manager_plan	
100	lm_ress	6000
101	lm_locks	12000
102	active_instance_count	
120	control_files	/u01/oradata/ca01/control1_ca01.ctl,
/u02/oradata/ca01/control2_ca01.ctl, /u03/oradata/ca01/control3_ca01.ctl
122	db_file_name_convert	
123	log_file_name_convert	
125	db_block_buffers	5000
127	db_block_checksum	FALSE
128	db_block_size	8192
131	db_block_lru_latches	1
132	db_writer_processes	1
143	db_block_max_dirty_target	5000
145	buffer_pool_keep	
146	buffer_pool_recycle	
167	max_commit_propagation_delay	700
169	compatible	8.1.6
174	log_archive_start	FALSE
177	log_archive_dest	
178	log_archive_duplex_dest	
179	log_archive_dest_1	
180	log_archive_dest_2	
181	log_archive_dest_3	
182	log_archive_dest_4	
183	log_archive_dest_5	
184	log_archive_dest_state_1	enable
185	log_archive_dest_state_2	enable
186	log_archive_dest_state_3	enable
187	log_archive_dest_state_4	enable
188	log_archive_dest_state_5	enable
189	log_archive_max_processes	1
190	log_archive_min_succeed_dest	1
191	standby_archive_dest	?/dbs/arch
192	log_archive_trace	0
193	log_archive_format	%t_%s.dbf
195	log_buffer	163840
197	log_checkpoint_interval	10000
198	log_checkpoint_timeout	1800
205	db_files	400
206	db_file_multiblock_read_count	32
209	read_only_open_delayed	FALSE
210	parallel_server	FALSE
211	parallel_server_instances	1
215	gc_releasable_locks	0
216	gc_rollback_locks	0-128=32!8REACH
217	gc_files_to_locks	
218	gc_defer_time	10
230	thread	0
233	fast_start_io_target	0
238	log_checkpoints_to_alert	FALSE
239	recovery_parallelism	0
241	control_file_record_keep_time	7
243	dml_locks	504
244	row_locking	always
245	serializable	FALSE
246	replication_dependency_tracking	TRUE
247	instance_number	0
251	transactions	126
252	transactions_per_rollback_segment	5
253	max_rollback_segments	30
254	rollback_segments	r01, r02, r03, r04, r05
259	transaction_auditing	TRUE
264	fast_start_parallel_rollback	LOW
266	db_block_checking	FALSE
280	os_roles	FALSE
281	rdbms_server_dn	
282	max_enabled_roles	20
283	remote_os_authent	FALSE
284	remote_os_roles	FALSE
285	O7_DICTIONARY_ACCESSIBILITY	TRUE
287	remote_login_passwordfile	NONE
288	dblink_encrypt_login	FALSE
289	license_max_users	0
290	db_domain	
291	global_names	FALSE
293	distributed_transactions	31
296	commit_point_strength	1
297	instance_name	ca01
298	service_names	ca01
299	mts_dispatchers	
300	mts_servers	0
301	mts_max_servers	20
302	mts_max_dispatchers	5
303	mts_sessions	0
304	mts_circuits	0
305	local_listener	
309	mts_service	ca01
310	mts_listener_address	
311	mts_multiple_listeners	FALSE
312	open_links	4
313	open_links_per_instance	4
317	optimizer_features_enable	8.1.6
318	fixed_date	
319	audit_trail	NONE
320	sort_area_size	4194304
321	sort_area_retained_size	0
322	sort_multiblock_read_count	2
324	db_name	ca01
325	open_cursors	50
326	ifile	
327	sql_trace	FALSE
329	os_authent_prefix	ops$
331	optimizer_mode	CHOOSE
334	sql92_security	FALSE
336	blank_trimming	FALSE
337	always_anti_join	NESTED_LOOPS
338	partition_view_enabled	FALSE
341	star_transformation_enabled	FALSE
353	parallel_broadcast_enabled	FALSE
360	parallel_adaptive_multi_user	TRUE
361	parallel_threads_per_cpu	4
362	parallel_automatic_tuning	TRUE
376	always_semi_join	standard
381	optimizer_max_permutations	80000
382	optimizer_index_cost_adj	100
383	optimizer_index_caching	0
389	query_rewrite_enabled	TRUE
390	query_rewrite_integrity	enforced
409	sql_version	NATIVE
419	serial_reuse	DISABLE
420	cursor_space_for_time	FALSE
421	session_cached_cursors	0
422	text_enable	FALSE
423	remote_dependencies_mode	TIMESTAMP
424	utl_file_dir	
426	plsql_v2_compatibility	FALSE
428	job_queue_processes	0
429	job_queue_interval	60
430	optimizer_percent_parallel	0
432	parallel_min_percent	0
435	create_bitmap_area_size	8388608
436	bitmap_merge_area_size	1048576
438	cursor_sharing	EXACT
439	parallel_min_servers	0
440	parallel_max_servers	8
444	parallel_instance_group	
447	parallel_execution_message_size	4096
455	hash_join_enabled	TRUE
456	hash_area_size	26214400
457	hash_multiblock_io_count	0
460	shadow_core_dump	partial
461	background_core_dump	partial
462	background_dump_dest	?/rdbms/log
463	user_dump_dest	?/rdbms/log
464	max_dump_file_size	10240
465	core_dump_dest	?/dbs
469	audit_file_dest	?/rdbms/audit
471	oracle_trace_enable	FALSE
472	oracle_trace_facility_path	?/otrace/admin/fdf
473	oracle_trace_collection_path	?/otrace/admin/cdf
474	oracle_trace_facility_name	oracled
475	oracle_trace_collection_name	
476	oracle_trace_collection_size	5242880
479	object_cache_optimal_size	102400
480	object_cache_max_size_percent	10
482	session_max_open_files	10
485	aq_tm_processes	0
486	hs_autoregister	TRUE

------_=_NextPart_001_01C02185.E1B436E8
Content-Type: text/html;

charset="windows-1252"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dwindows-1252">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2448.0">
<TITLE>RE: DATABASE TUNING</TITLE>
</HEAD>
<BODY>

Stephane, 


Thanks for your detailed reply.


I have block size of 8K.
 I have just created the database, created few table =
and there is no updation still.


Here is my SGA


SVRMGR> show sga;
 Total System Global =
Area           &n= bsp;           &n= bsp; 76029936 bytes
 Fixed =

Size&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 69616 bytes</FONT>

 Variable =

Size&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =

34820096 bytes
 Database =

Buffers&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 40960000 =

bytes
 Redo =

Buffers&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp; 180224 bytes</FONT>

 SVRMGR>


Here is my sys.v_paramter table values:
 should I adjust any paramter value?


NUM     NAME    =
VALUE
 9       =
processes       100
 10      =
sessions        115
 11      =
timed_statistics        FALSE
 12      =
timed_os_statistics     0
 13      =
resource_limit  FALSE
 14      =
license_max_sessions    0
 15      =
license_sessions_warning        = 0
 18      =
cpu_count       2
 21      instance_groups =

 22      event   =

 23      =
shared_pool_size        = 11534336
 26      =
shared_pool_reserved_size       = 576716
 28      large_pool_size =
422880
 30      =
java_pool_size  20000K
 31      =
java_soft_sessionspace_limit    0
 32      =
java_max_sessionspace_size      0
 33      =
pre_page_sga    FALSE
 34      =
shared_memory_address   0
 35      =
hi_shared_memory_address        = 0
 36      =
use_indirect_data_buffers       = FALSE
 38      =
lock_sga        FALSE
 45      lock_name_space =

 47      =
enqueue_resources       924
 60      =
nls_language    AMERICAN
 61      =
nls_territory   AMERICA
 62      =
nls_sort        
 63      =
nls_date_language       
 64      nls_date_format =

 65      =
nls_currency    
 66      =
nls_numeric_characters  
 67      =
nls_iso_currency        
 68      =
nls_calendar    
 69      nls_time_format =

 70      =
nls_timestamp_format    
 71      =
nls_time_tz_format      
 72      =
nls_timestamp_tz_format 
 73      =
nls_dual_currency       
 74      =
nls_comp        
 75      =
disk_asynch_io  TRUE
 76      =
tape_asynch_io  TRUE
 78      =
dbwr_io_slaves  0
 82      =
backup_tape_io_slaves   FALSE
 86      =
db_file_direct_io_count 64
 91      =
resource_manager_plan   
 100     lm_ress 6000
 101     =
lm_locks        12000
 102     =
active_instance_count   
 120     =
control_files   /u01/oradata/ca01/control1_ca01.ctl, = /u02/oradata/ca01/control2_ca01.ctl, =
/u03/oradata/ca01/control3_ca01.ctl

122     =
db_file_name_convert    
 123     =
log_file_name_convert   
 125     =
db_block_buffers        5000
 127     =
db_block_checksum       FALSE
 128     =
db_block_size   8192
 131     =
db_block_lru_latches    1
 132     =
db_writer_processes     1
 143     =
db_block_max_dirty_target       = 5000
 145     =
buffer_pool_keep        
 146     =
buffer_pool_recycle     
 167     =

max_commit_propagation_delay&nbsp;&nbsp;&nbsp; 700</FONT>


<BR><FONT SIZE=3D2>169&nbsp;&nbsp;&nbsp;&nbsp; =

compatible&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.1.6</FONT>

 174     =
log_archive_start       FALSE
 177     =
log_archive_dest        
 178     log_archive_duplex_dest =

 179     =
log_archive_dest_1      
 180     =
log_archive_dest_2      
 181     =
log_archive_dest_3      
 182     =
log_archive_dest_4      
 183     =
log_archive_dest_5      
 184     =
log_archive_dest_state_1        = enable
 185     =
log_archive_dest_state_2        = enable
 186     =
log_archive_dest_state_3        = enable
 187     =
log_archive_dest_state_4        = enable
 188     =
log_archive_dest_state_5        = enable
 189     =
log_archive_max_processes       1
 190     =
log_archive_min_succeed_dest    1
 191     =
standby_archive_dest    ?/dbs/arch
 192     =
log_archive_trace       0
 193     =
log_archive_format      %t_%s.dbf
 195     =
log_buffer      163840
 197     log_checkpoint_interval =
10000
 198     =
log_checkpoint_timeout  1800
 205     =
db_files        400
 206     =
db_file_multiblock_read_count   32
 209     =
read_only_open_delayed  FALSE
 210     parallel_server =
FALSE
 211     =
parallel_server_instances       1
 215     =
gc_releasable_locks     0
 216     =
gc_rollback_locks       = 0-128=3D32!8REACH
 217     =
gc_files_to_locks       
 218     =
gc_defer_time   10
 230     thread  0
 233     =
fast_start_io_target    0
 238     =
log_checkpoints_to_alert        = FALSE
 239     =
recovery_parallelism    0
 241     =
control_file_record_keep_time   7
 243     =
dml_locks       504
 244     =
row_locking     always
 245     =
serializable    FALSE
 246     =
replication_dependency_tracking TRUE
 247     instance_number 0
 251     =
transactions    126
 252     =
transactions_per_rollback_segment       = 5
 253     =
max_rollback_segments   30
 254     =
rollback_segments       r01, r02, r03, = r04, r05
 259     =
transaction_auditing    TRUE
 264     =
fast_start_parallel_rollback    LOW
 266     =
db_block_checking       FALSE
 280     =
os_roles        FALSE
 281     rdbms_server_dn 
 282     =
max_enabled_roles       20
 283     =
remote_os_authent       FALSE
 284     remote_os_roles =
FALSE
 285     =
O7_DICTIONARY_ACCESSIBILITY     TRUE
 287     =
remote_login_passwordfile       = NONE
 288     =
dblink_encrypt_login    FALSE
 289     =
license_max_users       0
 290     =
db_domain       
 291     =
global_names    FALSE
 293     =
distributed_transactions        = 31
 296     =
commit_point_strength   1
 297     =
instance_name   ca01
 298     =
service_names   ca01
 299     mts_dispatchers 
 300     =
mts_servers     0
 301     mts_max_servers =
20
 302     =
mts_max_dispatchers     5
 303     =
mts_sessions    0
 304     =
mts_circuits    0
 305     local_listener  =

 309     =
mts_service     ca01
 310     =
mts_listener_address    
 311     =

mts_multiple_listeners&nbsp; FALSE</FONT>


<BR><FONT SIZE=3D2>312&nbsp;&nbsp;&nbsp;&nbsp; =

open_links&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4</FONT>

 313     open_links_per_instance =
4
 317     =
optimizer_features_enable       = 8.1.6
 318     =
fixed_date      
 319     =
audit_trail     NONE
 320     sort_area_size  =
4194304
 321     sort_area_retained_size =
0
 322     =
sort_multiblock_read_count      2
 324     db_name ca01
 325     =
open_cursors    50
 326     ifile   =

 327     =
sql_trace       FALSE
 329     =
os_authent_prefix       ops$
 331     optimizer_mode  =
CHOOSE
 334     sql92_security  =
FALSE
 336     blank_trimming  =
FALSE
 337     =
always_anti_join        = NESTED_LOOPS
 338     =
partition_view_enabled  FALSE
 341     =
star_transformation_enabled     FALSE
 353     =
parallel_broadcast_enabled      FALSE
 360     =
parallel_adaptive_multi_user    TRUE
 361     =
parallel_threads_per_cpu        = 4
 362     =
parallel_automatic_tuning       = TRUE
 376     =
always_semi_join        = standard
 381     =
optimizer_max_permutations      80000
 382     =
optimizer_index_cost_adj        = 100
 383     optimizer_index_caching =
0
 389     =
query_rewrite_enabled   TRUE
 390     query_rewrite_integrity =
enforced
 409     =
sql_version     NATIVE
 419     =
serial_reuse    DISABLE
 420     =
cursor_space_for_time   FALSE
 421     =
session_cached_cursors  0
 422     =
text_enable     FALSE
 423     =
remote_dependencies_mode        = TIMESTAMP
 424     =
utl_file_dir    
 426     =
plsql_v2_compatibility  FALSE
 428     =
job_queue_processes     0
 429     =
job_queue_interval      60
 430     =
optimizer_percent_parallel      0
 432     =
parallel_min_percent    0
 435     create_bitmap_area_size =
8388608
 436     =
bitmap_merge_area_size  1048576
 438     cursor_sharing  =
EXACT
 439     =
parallel_min_servers    0
 440     =
parallel_max_servers    8
 444     parallel_instance_group =

 447     parallel_execution_messag=
e_size 4096
 455     =
hash_join_enabled       TRUE
 456     hash_area_size  =
26214400
 457     =
hash_multiblock_io_count        = 0
 460     =
shadow_core_dump        = partial
 461     =
background_core_dump    partial
 462     =
background_dump_dest    ?/rdbms/log
 463     user_dump_dest  =
?/rdbms/log
 464     =
max_dump_file_size      10240
 465     core_dump_dest  =
?/dbs
 469     audit_file_dest =
?/rdbms/audit
 471     =
oracle_trace_enable     FALSE
 472     =
oracle_trace_facility_path      = ?/otrace/admin/fdf
 473     =
oracle_trace_collection_path    = ?/otrace/admin/cdf
 474     =
oracle_trace_facility_name      oracled
 475     =
Received on Mon Sep 18 2000 - 10:34:06 CDT