Home » RDBMS Server » Performance Tuning » Oracle Tuning - Explain plan (Oracle 9i, AIX)
Oracle Tuning - Explain plan [message #360274] Thu, 20 November 2008 04:32 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #360572 is a reply to message #360563] Fri, 21 November 2008 07:21 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I have no idea why the OP would opt to open a new thread without even bothering to respond to the previous thread with the same concern.
As JRowbottom indicated, physical structure is different and the OP i copying the Statistics which makes CBO to hallucinate.
Re: Oracle Tuning - Explain plan [message #360590 is a reply to message #360572] Fri, 21 November 2008 08:54 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Synonyms and dblinks
Next Topic: Understand how optimizer works
Goto Forum:
  


Current Time: Mon Dec 05 12:39:40 CST 2016

Total time taken to generate the page: 0.10101 seconds