Skip to main content

Home/ eDBA Services - Oracle & MySQL/ Group items tagged tuning

Rss Feed Group items tagged

Filter: All | Bookmarks | Topics Simple Middle
Dariusz Owczarek

Tuning and Optimizing RHEL for Oracle 9i and 10g Databases (Red Hat Enterprise Linux - ... - 0 views

www.puschitz.com/TuningLinuxForOracle.shtml

oracle dba linux tuning edba

shared by Dariusz Owczarek on 02 Feb 10 - Cached
  • Dariusz Owczarek on 02 Feb 10
     
    This article is a step by step guide for tuning and optimizing Red Hat Enterprise Linux on x86 and x86-64 platforms running Oracle 9i (32bit/64bit) and Oracle 10g (32bit/64bit) standalone and RAC databases.
Dariusz Owczarek

Oracle SQL Tuning tricks - 0 views

www.dba-oracle.com/t_sql_tuning_tricks.htm

dba oracle tuning

shared by Dariusz Owczarek on 28 Oct 09 - Cached
  • Dariusz Owczarek on 28 Oct 09
     
    Oracle SQL tuning experts use an endless number of techniques, but there are some common tricks that can be used to optimize all SQL.
Dariusz Owczarek

Oracle's V$ Views - Dynamic Performance Views - 1 views

www.adp-gmbh.ch/...dynamic_performance_views.html

dba oracle v$ dictionary

shared by Dariusz Owczarek on 27 Jun 09 - Cached
  • v$database This view lets you access database information.
  • v$datafile This view contains an entry for each datafile of the database.
  • v$dataguard_status Shows error messages in a data guard environment.
  • ...11 more annotations...
  • v$flash_recovery_area_usage See also v$recovery_file_dest
  • v$lock This view stores all information relating to locks in the database.
  • v$mystat This view records statistical data about the session that accesses it.
  • v$parameter Lists the name-value pairs of the init.ora file (or their default, if not in the init.ora).
  • v$session
  • v$session_longops Use v$session_longops if you have a long running pl/sql procedure and want to give feedback on how far the procedure proceeded.
  • v$session_wait This views shows what wait event each session is waiting for, or what the last event was that it waited for.
  • v$session_wait_history This view is new in Oracle 10g and allows improved timing and statistics.
  • v$sqlarea Join v$sqlarea's address with v$session's sql_address.
  • v$sql_bind_capture New with Oracle 10g This view captures bind variables for all sessions and is faster than setting 10046 on level 4.
  • v$version Use this view to find out what version you actually work on: select * from v$version;
  • Dariusz Owczarek on 27 Jun 09
     
    Oracle's data dictionary provides information that Oracle needs to perform its tasks. The data dictionary is stored in table structures owned by SYS. Their content is exposed through dictionary views. Dynamic performance views (so called v$ views) are part of Oracle's data dictionary.
Dariusz Owczarek

Oracle OS statistics - 0 views

www.dba-oracle.com/t_os_statistics.htm

dba oracle

shared by Dariusz Owczarek on 16 Oct 09 - Cached
  • Dariusz Owczarek on 16 Oct 09
     
    The speed of disk access, the backlog of CPU enqueues, network latency and RAM swapping can all be the root cause of an Oracle performance problem, and no amount of tuning can fix a problem that is at the OS layer, external to Oracle. Oracle has two main ways to collect OS information...
Dariusz Owczarek

Meaning of Oracle Key Statistics - 0 views

skillport.books24x7.com/...viewer.asp

dba edba oracle performance

shared by Dariusz Owczarek on 08 Jun 10 - Cached
  • Statistics are somewhat fallible in that they are seldom 100 percent accurate, but in most cases they do sufficiently indicate what was intended. Be sure you understand what each statistic represents and the units used (there is a big difference between microseconds and centiseconds).
  • Time-breakdown statistics (Time Model) make it significantly easier to determine the type of operations that are consuming resources in the database.
  • DB time: Time spent by all user processes in the database (that is,. non-idle wait time + CPU time).
  • ...11 more annotations...
  • DB CPU: Time spent by all user processes on the CPU, in Oracle code. On most systems, the majority of time will be spent in DB CPU, SQL execute elapsed time, or PL/SQL execution elapsed time (and possibly Java). Time spent in parse and connection management should be low, so if the levels indicate a high percentage of DB time, a problem exists in the relevant area. You can use this data to correlate with Top 5 Timed Events and Load Profile.
  • Database time (DB time) is an important time-based statistic: it measures the total time spent in the database by active sessions (that is, foreground user processes either actively working or actively waiting in a database call). DB time includes CPU time, I/O time, and other non-idle wait time.
  • Because DB time represents the sum of the time that all sessions spend in database calls, it can easily exceed the elapsed wall-clock time.
  • The objective of tuning an Oracle system could be stated as reducing the time that users spend in performing actions in the database, or simply reducing DB time.
  • Wait time is artificially inflated when the host is CPU bound because the wait time includes the actual time spent waiting (for example, waiting for a disk I/O), as well as the time spent by the process in the OS run-queue waiting to be rescheduled.
  • Therefore, when the host is CPU bound, it is important to reduce CPU utilization before addressing wait-related problems, because otherwise you may be addressing the wrong problem.
  • You can use ASH data to estimate DB time when the actual DB time is not available—for example, if a session has exited. Because ASH samples active sessions every second, you can estimate DB time (in seconds) to be the number of ASH samples counted.
  • V$OSSTAT is OS-related resource utilization data that the Oracle server collects. The statistics available vary by platform. You can use V$OSSTAT to determine CPU utilization (BUSY_TICKS and IDLE_TICKS), and also compare this to the host's CPU utilization statistics. Also look for high OS_CPU_WAIT_TIME, which may indicate the host is CPU bound.
  • V$OSSTAT statistics can be compared with the Time Model statistics, for example to determine how much of the total CPU used on the host is attributable to this instance: DB CPU / BUSY_TICKS.
  • note that the units for these two statistics differ.
  • In 10g, each wait event (V$SYSTEM_EVENT) is classified into one of nine wait classes: Application, Commit, Concurrency, Configuration, Network, Other, System I/O, User I/O, and Idle. The class names are reasonably self-explanatory except Other, which is a catchall bucket for wait events that should not ever contribute any significant wait time.
1 - 5 of 5
Showing 20▼ items per page