This discussion is archived
1 2 3 4 Previous Next 54 Replies Latest reply: Jan 15, 2013 4:29 PM by jgarry Go to original post RSS
  • 15. Re: Performance Issue
    yxes2013 Newbie
    Currently Being Moderated
    One question worth asking is, are there other things on the machine outside oracle which suddenly steal the filesystem cache and leave a large number of your "physical reads" actually going to disc rather than coming from memory ?
    The action plan yesterday when everything has nothing to do because the server was hanging....is to reboot the stop the database and reboot the server hoping the the server itself will release
    everything it locks resources. My hunch is It might hit something (data) or part of the hardware that is creating harware locks? Or maybe block, memory, disk, network corruption?

    But even if we restarted the server and run the autoinvoice rma module it was still very slow. So it might me on the data? Because yesterday they run the same program and it is good whole day.


    I asked the users, operators, sysadmin what is the difference being done yesterday and today? and they same none. They said this has been behaving for the last 6 months. but it getting worst lately that they were cancelling transactions worth P5Million a day.
  • 16. Re: Performance Issue
    jgarry Guru
    Currently Being Moderated
    Not a joke. Did you read the link? It was an illustration of the bind peeking issue.

    For your buffer cache, show us the init parameters from the alert log, they should be displayed after startup messages. Also, check the docs for the V$DB_CACHE_ADVICE view
  • 17. Re: Performance Issue
    yxes2013 Newbie
    Currently Being Moderated
    Thanks jq,

    I check the alert log at again i see
    Tue Jan  8 14:58:52 2013
    Errors in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_8774.trc:
    ORA-12570: TNS:packet reader failure
    ORA-06512: at "JSCUS.FUNC_GET_MTL_SRC", line 23
    Tue Jan  8 15:03:27 2013
    Beginning log switch checkpoint up to RBA [0x21576.2.10], SCN: 0x056e.7527328b
    Thread 1 advanced to log sequence 136566
    This has occured since I increase the redo log and undo_retention just the other day.
    Can you help how to resolve this please.....
    Starting ORACLE instance (normal)
    LICENSE_MAX_SESSION = 0
    LICENSE_SESSIONS_WARNING = 0
    SCN scheme 2
    Using log_archive_dest parameter default value
    LICENSE_MAX_USERS = 0
    SYS auditing is disabled
    Starting up ORACLE RDBMS Version: 9.2.0.6.0.
    System parameters with non-default values:
      _trace_files_public      = TRUE
      processes                = 400
      sessions                 = 800
      timed_statistics         = TRUE
      shared_pool_size         = 301989888
      sga_max_size             = 1688804248
      shared_pool_reserved_size= 30000000
      _shared_pool_reserved_min_alloc= 4100
      java_pool_size           = 67108864
      _kghdsidx_count          = 1
      enqueue_resources        = 32000
      nls_language             = american
      nls_territory            = america
      nls_sort                 = binary
      nls_date_format          = DD-MON-RR
      nls_numeric_characters   = .,
      nls_comp                 = binary
      nls_length_semantics     = BYTE
      control_files            = /u02/oracle/oaproddata/cntrl01.dbf, /u02/oracle/oaproddata/cntrl02.dbf, /u02/oracle/oaproddata/cntrl03.dbf
      db_block_checksum        = TRUE
      db_block_size            = 8192
      db_cache_size            = 301989888
      compatible               = 9.2.0
      log_buffer               = 10485760
      log_checkpoint_interval  = 100000
      log_checkpoint_timeout   = 1200
      db_files                 = 512
      db_file_multiblock_read_count= 8
      log_checkpoints_to_alert = TRUE
      dml_locks                = 10000
      row_locking              = always
      undo_management          = AUTO
      undo_tablespace          = APPS_UNDOTS1
      undo_suppress_errors     = FALSE
      undo_retention           = 3600
      db_block_checking        = FALSE
      max_enabled_roles        = 100
      O7_DICTIONARY_ACCESSIBILITY= FALSE
      session_cached_cursors   = 200
      utl_file_dir             = /usr/tmp, /usr/tmp, /u02/oracle/oaproddb/9.2.0/appsutil/outbound/OAPROD_oel5, /usr/tmp
      job_queue_processes      = 2
      _system_trig_enabled     = TRUE
      cursor_sharing           = EXACT
      parallel_min_servers     = 0
      parallel_max_servers     = 8
      background_dump_dest     = /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/bdump
      user_dump_dest           = /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump
      max_dump_file_size       = 20480
      core_dump_dest           = /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/cdump
      optimizer_features_enable= 9.2.0
      db_name                  = OAPROD
      open_cursors             = 1000
      ifile                    = /u02/oracle/oaproddb/9.2.0/dbs/OAPROD_oel5_ifile.ora
      sql_trace                = FALSE
      _sort_elimination_cost_ratio= 5
      _b_tree_bitmap_plans     = FALSE
      _fast_full_scan_enabled  = FALSE
      optimizer_max_permutations= 2000
      query_rewrite_enabled    = true
      _index_join_enabled      = FALSE
      _sqlexec_progression_cost= 2147483647
      _like_with_bind_as_equality= TRUE
      pga_aggregate_target     = 4294967296
      workarea_size_policy     = AUTO
      aq_tm_processes          = 1
      olap_page_pool_size      = 4194304
    PMON started with pid=2
    DBW0 started with pid=3
    DBW1 started with pid=4
    LGWR started with pid=5
    CKPT started with pid=6
    SMON started with pid=7
    RECO started with pid=8
    CJQ0 started with pid=9
    QMN0 started with pid=10
    Tue Jan  8 11:59:18 2013
    ALTER DATABASE   MOUNT
    Tue Jan  8 11:59:22 2013
    Successful mount of redo thread 1, with mount id 4254113430
    Tue Jan  8 11:59:22 2013
    Database mounted in Exclusive Mode.
    Completed: ALTER DATABASE   MOUNT
    Tue Jan  8 11:59:22 2013
    ALTER DATABASE OPEN
    Tue Jan  8 11:59:23 2013
    Thread 1 opened at log sequence 136562
      Current log# 3 seq# 136562 mem# 0: /u02/oracle/oaproddata/log03a.dbf
      Current log# 3 seq# 136562 mem# 1: /u02/oracle/oaproddata/log03b.dbf
    Successful open of redo thread 1
    Tue Jan  8 11:59:23 2013
    MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
    Tue Jan  8 11:59:23 2013
    SMON: enabling cache recovery
    Tue Jan  8 11:59:23 2013
    Successfully onlined Undo Tablespace 368.
    Tue Jan  8 11:59:23 2013
    SMON: enabling tx recovery
    Tue Jan  8 11:59:23 2013
    Database Characterset is UTF8
    replication_dependency_tracking turned off (no async multimaster replication found)
    Completed: ALTER DATABASE OPEN
    Tue Jan  8 12:02:18 2013
    Beginning log switch checkpoint up to RBA [0x21573.2.10], SCN: 0x056e.7521cc0e
    Thread 1 advanced to log sequence 136563
      Current log# 4 seq# 136563 mem# 0: /u02/oracle/oaproddata/log04a.dbf
      Current log# 4 seq# 136563 mem# 1: /u02/oracle/oaproddata/log04b.dbf
    Tue Jan  8 12:22:33 2013
    Completed checkpoint up to RBA [0x21573.2.10], SCN: 0x056e.7521cc0e
    Tue Jan  8 12:50:04 2013
    Beginning log switch checkpoint up to RBA [0x21574.2.10], SCN: 0x056e.752326a7
    Thread 1 advanced to log sequence 136564
      Current log# 3 seq# 136564 mem# 0: /u02/oracle/oaproddata/log03a.dbf
      Current log# 3 seq# 136564 mem# 1: /u02/oracle/oaproddata/log03b.dbf
    Tue Jan  8 13:10:19 2013
    Completed checkpoint up to RBA [0x21574.2.10], SCN: 0x056e.752326a7
    Tue Jan  8 13:49:16 2013
    Beginning log switch checkpoint up to RBA [0x21575.2.10], SCN: 0x056e.7524a192
    Thread 1 advanced to log sequence 136565
      Current log# 4 seq# 136565 mem# 0: /u02/oracle/oaproddata/log04a.dbf
      Current log# 4 seq# 136565 mem# 1: /u02/oracle/oaproddata/log04b.dbf
    Tue Jan  8 14:10:16 2013
    Completed checkpoint up to RBA [0x21575.2.10], SCN: 0x056e.7524a192
  • 18. Re: Performance Issue
    jgarry Guru
    Currently Being Moderated
    Please post the lines from your alert log that specify your parameters, after the line that says "System parameters with non-default values:"
  • 19. Re: Performance Issue
    yxes2013 Newbie
    Currently Being Moderated
    Hi... posted. thanks.
  • 20. Re: Performance Issue
    jgarry Guru
    Currently Being Moderated
    Do you know how you came up with the values for pga_aggregate_target and db_cache_size? They seem possibly out of whack (too large and too small, respectively), but that might just be me being used to my systems. But I can't help but wonder when those excel programs run, you might just be doing the classic "use so much memory you start swapping" trick.

    What does linux have to say about memory usage at the slow times? How much physical memory do you have?
  • 21. Re: Performance Issue
    yxes2013 Newbie
    Currently Being Moderated
    Physical memory is 12Gb...But my database is 32 bit so it takes only 2Gb max. I am correct? So lots of idle RAM?

    Can you give me what size do I set. But again what has this to do with the "good" performance at some days?
  • 22. Re: Performance Issue
    sb92075 Guru
    Currently Being Moderated
    yxes2013 wrote:

    3. Can you increase your SGA to handle more appropriately the volume required by the queries?
    Is it already 1.7 which is the limit of 32 bit Oracle. which we have.
    not entirely true. 1.7MB is default maximum size as shipped by Oracle.
    The Oracle kernel can be relinked after changing a parameter file that increases the SGA to around 2.6MB
    I did this more than a decade ago when still running V9.2.0.7
  • 23. Re: Performance Issue
    yxes2013 Newbie
    Currently Being Moderated
    Can you give me steps to do it please.... Thanks
  • 24. Re: Performance Issue
    sb92075 Guru
    Currently Being Moderated
    yxes2013 wrote:
    Can you give me steps to do it please.... Thanks
    I have long forgotten the details.
    I did it based upon a NOTE available on Metalink; aka My Oracle Support
  • 25. Re: Performance Issue
    jgarry Guru
    Currently Being Moderated
    yxes2013 wrote:
    Physical memory is 12Gb...But my database is 32 bit so it takes only 2Gb max. I am correct? So lots of idle RAM?
    Well, you might go back to the concepts manual and review about SGA and PGA. You stated earlier you set SGA to 1.7G, please do a "show SGA" and show us what it thinks. You are using sga_max_size, and not sga_target, so you may not have the buffers set optimally, and it may be smaller than you think. Tuning is an iterative task.

    The pga_aggregate_target is just that - a target. Oracle can use more. Much has been written about it, in fact, when I was running 9.2.0.6, Jonathan Lewis was writing a lot about it. Every time I thought I understood it something confused me more, and I eventually gave up and used what worked for me (and decided the docs were a bit off). So google his name and pga_aggregate target and 9i and maybe you'll understand it.

    >
    Can you give me what size do I set. But again what has this to do with the "good" performance at some days?
    This may go along with what Jonathan said about something outside Oracle making it slow. I think what that something is, and I'm speculating here, is some of your programs use lots and lots of PGA, to the point where your system chokes. So the solution would be to make your pga_aggregate_target much smaller. But I wouldn't make any adjustments without gathering evidence, one data point is what linux thinks is being used (top and swapinfo are gross measures I use, I'm not on linux). There are also SGA and PGA advisors, as well as db cache advice. (I may be confused as to what is in what version, see your performance tuning guide. Also, some of the docs are misleading about this stuff, google what Jonathan has had to say. And there is lots of misinformation on the net specifically on this subject.)

    Since Jonathan has been in this thread, he might have more to say on the subject.

    You might also check the plans of the programs that are slow. It's possible the excel programs are simply asking for way too much information. I've seen it where such clients blow up (or the user X's out of it, and it keeps running on the server), and they keep submitting more and more.
  • 26. Re: Performance Issue
    yxes2013 Newbie
    Currently Being Moderated
    SQL*Plus: Release 9.2.0.6.0 - Production on Wed Jan 9 09:31:27 2013
    
    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
    
    
    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.6.0 - Production
    
    SQL> show sga
    
    Total System Global Area 1688804248 bytes
    Fixed Size                   453528 bytes
    Variable Size            1375731712 bytes
    Database Buffers          301989888 bytes
    Redo Buffers               10629120 bytes
  • 27. Re: Performance Issue
    sb92075 Guru
    Currently Being Moderated
    at OS level you should run top & vmstat when system is totally bogged down under apparent heavy load.

    The combination of the two can help to identify which system resource (CPU, RAM, I/O) is the bottleneck.

    COPY & PASTE the results & be sure to enclose in
     tags                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 28. Re: Performance Issue
    jgarry Guru
    Currently Being Moderated
    http://asktom.oracle.com/pls/asktom/f?p=100:11:16924899315320::::P11_QUESTION_ID:365088445659
  • 29. Re: Performance Issue
    sb92075 Guru
    Currently Being Moderated
    SQL to show where time is being spent

    http://www.orafaq.com/forum/mv/msg/183304/563118/173420/#msg_563118

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points