Skip to main content

Home/ eDBA Services - Oracle & MySQL/ Group items tagged v$

Rss Feed Group items tagged

Filter: All | Bookmarks | Topics Simple Middle
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

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.
Dariusz Owczarek

Adaptive Cursor Sharing - 0 views

database.com.mk/?p=333

dba edba oracle

shared by Dariusz Owczarek on 06 May 10 - Cached
  • Adaptive Cursor Sharing is a new feature starting from Oracle version 11g release 1. The idea behind is to improve the execution plans for statements with bind variables. CBO has been enhanced to allow multiple execution plans to be used for a single statement with bind variables, without hard parsing the SQL.
  • There is no special way to configure ACS. It is on by default and of course there is a hidden initialization parameter to turn it off if needed. Key role in the process of decision whether ACS will be used for a particular statement are: Two new columns in V$SQL view (IS_BIND_SENSITIVE and IS_BIND_AWARE) Three new views (V$SQL_CS_HISTOGRAM, V$SQL_CS_SELECTIVITY, V$SQL_CS_STATISTICS)
1 - 3 of 3
Showing 20▼ items per page