| Oracle Tuning - Explain plan [message #360274] |
Thu, 20 November 2008 04:32  |
psoftneto
Messages: 44
Registered: July 2008
|
Member |
|
|
Hi all,
I have two databases on different servers (but same OS and Oracle version) with the same data but init.ora parameters are different:
Here is one explain plan for a request:
15 INSERT STATEMENT
13 SORT [GROUP BY]
12 FILTER
11 TABLE_E TABLE ACCESS [BY INDEX ROWID]
10 NESTED LOOPS
8 NESTED LOOPS
5 HASH JOIN
3 HASH JOIN
1 TABLE_A TABLE ACCESS [FULL]
2 INDEX B1 (TABLE_B) INDEX [FAST FULL SCAN]
4 TABLE_C TABLE ACCESS [FULL]
7 TABLE_D TABLE ACCESS [BY INDEX ROWID]
6 INDEX D1 (TABLE_D) INDEX [UNIQUE SCAN]
9 INDEX E1 (TABLE_E) INDEX [RANGE SCAN]
14 INDEX F1 (TABLE_F) INDEX [UNIQUE SCAN]
The request last 2hours on one environment an more than 4hours on the other. The sql plan is the same for the two environments.
Can some one tell me for that plan which parameters are important to modify?
Thanks
|
|
|
|
| Re: Oracle Tuning - Explain plan [message #360372 is a reply to message #360274] |
Thu, 20 November 2008 13:19  |
psoftneto
Messages: 44
Registered: July 2008
|
Member |
|
|
After running statspack - I noticed the following:
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 99.97
Buffer Hit %: 86.76 In-memory Sort %: 100.00
Library Hit %: 98.54 Soft Parse %: 97.88
Execute to Parse %: 27.13 Latch Hit %: 99.98
Parse CPU to Parse Elapsd %: 58.33 % Non-Parse CPU: 98.49
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 90.51 87.31
% SQL with executions>1: 58.04 53.59
% Memory for SQL w/exec>1: 61.20 56.22
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read 259,834 4,763 89.92
db file scattered read 92,552 216 4.07
CPU time 157 2.97
PX Deq Credit: send blkd 787 43 .82
log file sync 1,752 40 .76
-------------------------------------------------------------
Can someone advice on those parameters ?
Thanks
|
|
|
|
| Re: Oracle Tuning - Explain plan [message #360563 is a reply to message #360274] |
Fri, 21 November 2008 06:51 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The most likely cause is that the two boxes haf different hardware, or that there are other processes running on the slower box.
If you wanted to be helpful, you could show us the differences between the two init.ora files (after all, if it's important enough to mention, surely it's important enough to provide details of).
How did you copy the data over - If it was an Export/Import, then you've got the same logical data, but with a significantly different physical structure, which could make a significant difference in performance.
The only thing your statspack snapshot tells me is that you're doing too much parsing - either you're using dynamic sql without bind variables, or your application software isn't using bind variables.
|
|
|
|
|
|
| Re: Oracle Tuning - Explain plan [message #360590 is a reply to message #360572] |
Fri, 21 November 2008 08:54 |
psoftneto
Messages: 44
Registered: July 2008
|
Member |
|
|
The first init.ora is
compatible =9.2.0.6.0
cursor_sharing=SIMILAR
db_16K_cache_size =640M
db_block_size=8192
db_cache_size =1920M
db_file_multiblock_read_count = 32
db_files=250
dml_locks =1000
enqueue_resources =1020
fast_start_mttr_target =1800
java_pool_size=16M
lock_sga =TRUE
log_archive_max_processes=2
log_archive_start =TRUE
log_buffer =524288
log_checkpoint_interval = 820000
max_dump_file_size = unlimited
max_enabled_roles=20
open_cursors = 255
open_links =0
open_links_per_instance =0
parallel_automatic_tuning = true
parallel_max_servers = 5
pga_aggregate_target=3123M
pre_page_sga =TRUE
processes =100
remote_login_passwordfile=EXCLUSIVE
session_cached_cursors =100
sga_max_size =3000M
shared_pool_size =128M
timed_statistics = true
undo_management=AUTO
undo_retention=7200
undo_suppress_errors=TRUE
The second init.ora is
compatible=9.2.0.0.0
cpu_count = 4
db_16k_cache_size=16M
db_block_size=8192
db_cache_size=400M
db_domain=WORLD
db_file_multiblock_read_count=128
db_files=250
fast_start_mttr_target=300
java_pool_size=0
job_queue_processes=2
large_pool_size=8388608
log_archive_start=false
log_buffer=65536
open_cursors=300
pga_aggregate_target=870M
processes=150
remote_login_passwordfile=EXCLUSIVE
sga_max_size=1200M
shared_pool_size=100M
sort_area_size=65536
timed_statistics=TRUE
undo_management=AUTO
undo_retention=10800
After reading statspack results, I have already decided to modify init.ora file 2 to
db_cache_size=640M
db_16k_cache_size=320M
shared_pool_size=128M
sga_max_size=2000M
log_buffer=10485760
Thanks for your time
|
|
|
|
| Re: Oracle Tuning - Explain plan [message #360599 is a reply to message #360590] |
Fri, 21 November 2008 10:01 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Do you have many tables in tablespaces with a 16k block size? If not, youre wasting a lot of memory on them.
where did you arrive at that value for log_buffers from.
Oracle's documentation recommend a value of 65536 or higher for a busy system - you seem to hav chosen a value 2 orders of magnitude higher than that, and twice the value that you had in the database that was performing adequately.
what makes you think that it's our redo logs that are causing the problems, and why do you think that changing this parameter (as opposed to moving the redo log files onto a less contested disk array) is the solution.
I think we're seeing @Anacedents favourite 'Ready, fire,Aim' approach to performance tuning.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
