DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Siebel 77 and Oracle 9.2.0.6

  1. #1
    Join Date
    Nov 2002
    Posts
    170

    Siebel 77 and Oracle 9.2.0.6

    Just wondering if anyone has implemented siebel 7.7 with Oracle 9.2.0.6.

    We are currently upgrading our siebel 7.0.5 on Oracle 8.1.7 (Rule based Opt) to Siebel 7.7 with Oracle 9.2.0.6(Cost Based Opt).
    We have made several tweaking of the init param basd on Siebel/Oracle's suggestions and overall for the Cost opt. The Application overall is performing good except for some exceptions where the screen hangs and or take much longer than the 7.0 environment. The UAT(User Acceptance Test) is over and some users have concern with this environment when they compare to the old 7.0 environmment. It is very hard to convince the management that this would be a ongoing tuning process which might take a little while to get every thing comparable. I have seen in few forums that people have been using rule based OPT still with the 7.7 Optimizer or atleast using the optimizer_features_enabled=8.1.7 since the 9.2.0 is having some issues. Please advise any issues you had to address during the implementation.

    ANy advise will be helpfull.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    have you traced the specific problems, see where time time being taken?

  3. #3
    Join Date
    Sep 2001
    Posts
    200
    I know of a case where the DB is at 9i but the optimizer is still a 8i...because of similar issues.
    Life is what is happening today while you were planning tomorrow.

  4. #4
    Join Date
    Nov 2002
    Posts
    170
    Init.ora
    ======
    db_name = "sblprod"
    instance_name = sblprod
    service_names = sblprod

    control_files = ("/oracle/ora92/oradata/sblprod/control01.ctl",
    "/oracle/ora92/oradata/sblprod/control02.ctl",
    "/oracle/ora92/oradata/sblprod/control03.ctl")

    open_cursors = 3000
    max_enabled_roles = 60

    db_block_size = 8192
    db_block_buffers = 983040 # Changed 10/02 ver 4.0
    #db_cache_size=805306380 # db_cache_size= db_block_buffers * db_block_size
    #db_cache_size=905306380
    #db_cache_size=1273741824
    #db_cache_size=2073741824 # Changed 09/01 ver 1.0
    db_writer_processes=8
    #db_file_multiblock_read_count=64
    db_file_multiblock_read_count=16 # chyanged 10/02 ver 4.0
    Parallel_automatic_Tuning = TRUE

    buffer_pool_keep = (buffers:158000,lru_latches:12) # changed 10/02 ver 3.0
    buffer_pool_recycle = (buffers:294448, lru_latches:10) # changed 10/02 ver 3.0


    NLS_SORT = BINARY
    shared_pool_reserved_size = 944371840
    #shared_pool_reserved_size = 188743680 # Changed 09/08 ver 2.0

    cursor_sharing = EXACT
    session_cached_cursors = 200

    #shared_pool_size = 1643718400 # Changed 09/01 ver 1.0
    #shared_pool_size = 1943718400 # Changed 09/08 ver 2.0
    #shared_pool_size = 2443718400 # Changed 09/08 ver 4.0
    shared_pool_size = 3443718400 # Changed 09/08 ver 4.0
    #large_pool_size = 504857600
    large_pool_size = 804857600 # Changed 09/08 ver 2.0
    java_pool_size = 1600000

    log_checkpoint_interval = 51360000
    processes = 3200
    log_buffer = 4048000

    log_archive_start = true
    #log_archive_dest_1 = "location=/interfaces/sblprod/"
    log_archive_dest_1 = "location=/arch"
    log_archive_format = arch_%t_%s.arc
    #log_archive_max_processes = 2 #check

    undo_management=auto # Changed 09/01
    undo_tablespace=undo_tbs # Changed 09/01
    undo_retention=7200 # Changed 09/01

    background_dump_dest = /oracle/ora92/admin/sblprod/bdump
    core_dump_dest = /oracle/ora92/admin/sblprod/cdump
    user_dump_dest = /oracle/ora92/admin/sblprod/udump

    remote_login_passwordfile = exclusive # Changed for upgrade
    #remote_login_passwordfile = none

    #os_authent_prefix = ""
    #mts_servers=0

    #compatible = "8.1.7"
    compatible = 9.2.0
    #sort_area_size = 259715200 # changed 09/08 ver 2.0
    #sort_area_retained_size = 26214400 # changed 09/08 ver 2.0
    workarea_size_policy=AUTO # Changed 09/01 ver 1.0
    pga_aggregate_target=2147483648 # Changed 09/01 ver 1.0


    #sort_multiblock_read_count=32 # obsolete
    utl_file_dir=/interfaces

    #optimizer_mode=Rule # Changed for upgarde Changed 09/11 ver 3.0
    #optimizer_mode=CHOOSE #all parameter has to change if opt_mode=choose

    # Oracle recomeded parameter to work with optimizer mode

    optimizer_mode=CHOOSE # Changed for oracle upgrade
    #hash_area_size= 419430400 # 2*sort_area_size # changed 09/08 ver 2.0
    hash_join_enabled=true
    optimizer_dynamic_sampling=1
    optimizer_features_enable=9.2.0
    #optimizer_index_caching=20
    optimizer_index_caching=0 # changed 10/02 ver 4.0
    partition_view_enabled=false
    query_rewrite_enabled=false
    query_rewrite_integrity=enforced
    star_transformation_enabled=false

    # Siebel recomended parameter for optimizer mode

    #optimizer_max_permutations=100
    optimizer_max_permutations=2000 # changed 10/02 ver 4.0
    optimizer_index_cost_adj=1

    timed_statistics=True

    log_checkpoints_to_alert=TRUE
    #db_block_lru_latches=70 # obsolete

    #_system_trig_enabled=false # explicitly set fpr upgrade

    db_files=350

    job_queue_processes=2 # Changed for upgrade
    #job_queue_processes=0

    nls_date_format='DD-MM-YYYY'

    #fast_start_io_target = 327680
    lock_sga = TRUE #check may be obsolete
    pre_page_sga = TRUE

    resource_limit = TRUE
    #EVENT = "10619 trace name context forever, level 1"
    utl_file_dir='/interfaces','c:\temp'

  5. #5
    Join Date
    Nov 2002
    Posts
    170
    Quote Originally Posted by davey23uk
    have you traced the specific problems, see where time time being taken?
    (Physical read/Consistent Gets)db file sequential read is where it is spending most times. We have increased the db_buffer_cache almost 1 & 1/2 and still planning to increase, since memory is not an issue on this Box. Please suggest if you see anything odd/obvious in the init.ora.

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    normally you trace the process and fix the SQL and not adding memory blindly

    looking your init.ora there are some funny values:

    none of your control file is multiplexed? that is basics you know, looking this, have you multiplexed your redo logs?

    how big is your database? isnt you SGA so exaggerated? 7GB db cache, 900mb shared pool reserved size (what on earth would need 900mb shared pool chunk...?!), 3gb shared pool, you have TONs of PL/SQL codes and not sharing SQL? 800mb large pool, are you using MTS?

    optimizer_index_cost_adj=1.... who told you to set that?

    your utl_file_dir is duplicated, you should be using directory anyways

  7. #7
    Join Date
    Nov 2002
    Posts
    170
    Quote Originally Posted by pando
    normally you trace the process and fix the SQL and not adding memory blindly

    looking your init.ora there are some funny values:

    none of your control file is multiplexed? that is basics you know, looking this, have you multiplexed your redo logs?

    how big is your database? isnt you SGA so exaggerated? 7GB db cache, 900mb shared pool reserved size (what on earth would need 900mb shared pool chunk...?!), 3gb shared pool, you have TONs of PL/SQL codes and not sharing SQL? 800mb large pool, are you using MTS?

    optimizer_index_cost_adj=1.... who told you to set that?

    your utl_file_dir is duplicated, you should be using directory anyways
    We are on SAN so multiplexing redo may not be required but controlfile yes we have to do that.

    As far as SGA goes...if you see the gradual incremental values which was to support this application. Yes it needs that much of db_buffer_cache since we have tables with 180 million rows and more and have multi tables joins for most queries. It is doing a lot of outter joins and loops.

    Optimizer_index_cost is strongly recomended by Siebel no exception. We have changed it to 100 and it degraded performance.

    No we are not using MTS. You think Large pool is less ??
    I agree 3 GB shared_pool is unusual but spreport kept reporting high reloads until we got it to 3 GB. I am not sure decresing the shared_pool_reserved size will do any good...unless you see it is required...also I have noticed everytime we flush the shared_pool these quries runs good.....Oracle recomended to have a cron job or dbms job to periodically flush shared_pool. ANy ideas why/how flushing shared_pool can make some querry run faster ??

  8. #8
    Join Date
    Nov 2002
    Posts
    170
    Our Database is close to 1 terabyte

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    We are on SAN so multiplexing redo may not be required but controlfile yes we have to do that.

    Multiplexing doesnt only save you from hardware failure



    As far as SGA goes...if you see the gradual incremental values which was to support this application. Yes it needs that much of db_buffer_cache since we have tables with 180 million rows and more and have multi tables joins for most queries. It is doing a lot of outter joins and loops.

    When you tuned your db cache did you see many many many buffer requests?



    Optimizer_index_cost is strongly recomended by Siebel no exception. We have changed it to 100 and it degraded performance.

    It is recommended for ALL OLTP systes but normally you set it to 20 - 30 but 1.... is like saying index all my queries. Is there any Siebel doc which says this should be 1 and not any other value...?



    No we are not using MTS. You think Large pool is less ??

    If you dont have MTS why are you setting large pool for? If you use RMAN or Parallel Query (dont think so anyways) you dont need more than 16MB or so

    I agree 3 GB shared_pool is unusual but spreport kept reporting high reloads until we got it to 3 GB.

    You have a high parse/execute ratio? i.e few parses and high number of executions.
    Are the SQL statements shared? 3GB fits like million of SQLs!

    I am not sure decresing the shared_pool_reserved size will do any good...unless you see it is required...

    Do you know what is reserved size for? It is used to reserve high chunk of memory for a large memory allocation. There is nothing which can be 900MB. How did you determine this is a valid value?



    Also I have noticed everytime we flush the shared_pool these quries runs good.....Oracle recomended to have a cron job or dbms job to periodically flush shared_pool.

    That is plainly bull**** ok. Oracle recommends the opposite


    ANy ideas why/how flushing shared_pool can make some querry run faster ?

    Because your SQL may run fine for same predicate but different values, for example

    select * from X where status = 'A' is probably very fast but
    select * from X where status = 'B' can be slow because 99% of the table has that sort of data

    This shares same SQL PLAN but if you use the first plan for second query then your plan is bad. Assumming your SQL are being shared

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    1. SP size
    Shared Pool 3 GB is too big. There is no need for this much size in Siebel because Siebel always uses bind variables. You rarely find "literal SQL" in Siebel. More over, Siebel does not use PKG, Procedure and Function. If you set SP too big, then the freelists for the shared SQL statements with in the SP will become longer and longer. You will notice performance degradation over a period of time. This is the main reason, Siebel and Oracle recommends to flush the SP every day night.

    2. Optimizer_max_permutations =2000
    I know Siebel query involves joining more than 40 tables in 7.5 release. In RBO, that may not be a problem. Because there are only 15 access paths, RBO will consider. But, Siebel 7.7 uses CBO and setting max value of 2000, Oracle may not produce optimum (best) execution plans for a query involved 40 tables joining. By lowering the value, you just descrease the parse time.
    Have you tested your system with higher value for this parameter?


    3. log_buffer and log_parallelism
    I would prefer to set 1 mb for log_buffer and 8 for log_parallelism. Both the values gave me good performance in Siebel 7.5.

    4. How many user connections do you anticipate in your system?
    Watch out PGA usage.

    If possible, post here statspack report with interval of 5 min.

    Tamil
    Last edited by tamilselvan; 10-21-2005 at 03:30 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width