Paramètres internes de l’instance

La table x$kvit donne une liste de paramètres dynamiques utilisés au moment du select.

La version 10g remonte la liste suivante :

select KVITTAG
, KVITDSC
, KVITVAL
from x$kvit
/

KVITTAG KVITDSC KVITVAL
-------------------- ---------------------------------------------------------------- ----------
ksbcpu number of logical CPUs in the system used by Oracle 2
ksbcpucore number of physical CPU cores in the system used by Oracle 0
ksbcpusocket number of physical CPU sockets in the system used by Oracle 0
ksbcpu_hwm high water mark of number of CPUs used by Oracle 2
ksbcpucore_hwm high water mark of number of CPU cores on system 0
ksbcpusocket_hwm high water mark of number of CPU sockets on system 0
ksbcpu_actual number of available CPUs in the system 2
ksbcpu_dr CPU dynamic reconfiguration supported 1
kcbnbh number of buffers 136551
kcbldq large dirty queue if kcbclw reaches this 25
kcbfsp Max percentage of LRU list foreground can scan for free 40
kcbcln Initial percentage of LRU list to keep clean 2
kcbnbf number buffer objects 750
kcbwst Flag that indicates recovery or db suspension 0
kcteln Error Log Number for thread open 0
kcvgcw SGA: opcode for checkpoint cross-instance call 0
kcvgcw SGA:opcode for pq checkpoint cross-instance call 0

La version 9i remonte la liste suivante :


KVITTAG KVITDSC KVITVAL
-------------------- ---------------------------------------------------------------- ----------
kcbnbh number of buffers 381120
kcbldq large dirty queue if kcbclw reaches this 25
kcbdsp Max percentage of LRU list dbwriter can scan for dirty 25
kcbfsp Max percentage of LRU list foreground can scan for free 40
kcbnbf number buffer objects 5000
kcbwst Flag that indicates recovery or db suspension 0
kcteln Error Log Number for thread open 0
kcvgcw SGA: opcode for checkpoint cross-instance call 0

Incidence du statistics level

La variable statistics level permet des stocker des statistiques dans certaines vues.

La requête suivante permet de voir quelle vue est impactée, à quel niveau de statistique et si le calcul de la statistique peut être modifié au niveau session

select decode(ACTIVATION_LEVEL,1,'Typical','All') "A Level"
, Name
, decode(SESSION_CHANGEABLE,1,'Ok',' -') "Modif"
, decode(SESSION_STATUS,1,'On',' -') "Sess"
, decode(SYSTEM_STATUS,1,'On',' -') "Sys"
, VIEW_NAME
from x$prmsltyx
order by ACTIVATION_LEVEL
/

En 11gR2 on obtient

A Level NAME                                                             Mo Se Sy VIEW_NAME
------- ---------------------------------------------------------------- -- -- -- -------------------------
Typical Buffer Cache Advice - On On V$DB_CACHE_ADVICE
Typical V$IOSTAT_* statistics - On On
Typical Timed Statistics Ok On On
Typical MTTR Advice - On On V$MTTR_TARGET_ADVICE
Typical Adaptive Thresholds Enabled - On On
Typical Segment Level Statistics - On On V$SEGSTAT
Typical PGA Advice - On On V$PGA_TARGET_ADVICE
Typical Shared Pool Advice - On On V$SHARED_POOL_ADVICE
Typical Modification Monitoring - On On
Typical Longops Statistics - On On V$SESSION_LONGOPS
Typical Bind Data Capture - On On V$SQL_BIND_CAPTURE
Typical Ultrafast Latch Statistics - On On
Typical Threshold-based Alerts - On On
Typical Global Cache Statistics - On On
Typical Active Session History - On On V$ACTIVE_SESSION_HISTORY
Typical Undo Advisor, Alerts and Fast Ramp up - On On V$UNDOSTAT
Typical Streams Pool Advice - On On V$STREAMS_POOL_ADVICE
Typical Time Model Events Ok On On V$SESS_TIME_MODEL
Typical Plan Execution Sampling Ok On On V$ACTIVE_SESSION_HISTORY
Typical Automated Maintenance Tasks - On On
Typical SQL Monitoring Ok On On V$SQL_MONITORING
All Plan Execution Statistics Ok - - V$SQL_PLAN_STATISTICS
All Timed OS Statistics Ok - -

En 10g on obtient

A Level NAME                                     Mo Se Sy VIEW_NAME
------- ---------------------------------------- -- -- -- ------------------------------
Typical Buffer Cache Advice - On On V$DB_CACHE_ADVICE
Typical Streams Pool Advice - On On V$STREAMS_POOL_ADVICE
Typical Timed Statistics Ok On On
Typical MTTR Advice - On On V$MTTR_TARGET_ADVICE
Typical Undo Advisor, Alerts and Fast Ramp up - On On V$UNDOSTAT
Typical Segment Level Statistics - On On V$SEGSTAT
Typical PGA Advice - On On V$PGA_TARGET_ADVICE
Typical Shared Pool Advice - On On V$SHARED_POOL_ADVICE
Typical Modification Monitoring - On On
Typical Longops Statistics - On On V$SESSION_LONGOPS
Typical Bind Data Capture - On On V$SQL_BIND_CAPTURE
Typical Ultrafast Latch Statistics - On On
Typical Threshold-based Alerts - On On
Typical Global Cache Statistics - On On
Typical Active Session History - On On V$ACTIVE_SESSION_HISTORY
All Plan Execution Statistics Ok - - V$SQL_PLAN_STATISTICS
All Timed OS Statistics Ok - -

En 9i on obtient

A Level NAME                           Mo Se Sy VIEW_NAME
------- ------------------------------ -- -- -- ------------------------------
Typical Buffer Cache Advice - On On V$DB_CACHE_ADVICE
Typical MTTR Advice - On On V$MTTR_TARGET_ADVICE
Typical Timed Statistics Ok On On
Typical PGA Advice - On On V$PGA_TARGET_ADVICE
Typical Shared Pool Advice - On On V$SHARED_POOL_ADVICE
Typical Segment Level Statistics - On On V$SEGSTAT
All Timed OS Statistics Ok - -
All Plan Execution Statistics Ok - - V$SQL_PLAN_STATISTICS

Afficher la liste des « fix control » passés sur une base

Les fix control sont apportés par les patchs / patchsets / patchsets updates et influent sur le comportement de l’optimiseur. La table x$qksbgsys les liste et donne leur description succincte

La requête suivante liste les bugs, ce qu’ils font et la version à partir de laquelle ils ont étés introduits

select BUGNO_QKSBGSYROW
  , DESC_QKSBGSYROW
  , OFE_QKSBGSYROW
  , EVENT_QKSBGSYROW
from x$qksbgsys
/
BUGNO_QKSBGSYROW DESC_QKSBGSYROW                                                  OFE_QKSBGSYROW            EVENT_QKSBGSYROW
---------------- ---------------------------------------------------------------- ------------------------- ----------------
        4611850 do not clobber predicate during first-k row estimate phase       10.2.0.2                                 0
        4663804 use smallest table as first table in join card. initial ordering 10.2.0.2                                 0
        4663698 for cached NL table set tab_cost_io to zero                      10.2.0.2                                 0
[...]

La requete suivante sur la table x$qksbgses est équivalente

select distinct BUGNO_QKSBGSEROW
   , DESC_QKSBGSEROW
   , OFE_QKSBGSEROW
   , EVENT_QKSBGSEROW
from x$qksbgses
/

Il est possible de désactiver un des correctifs par hint :

select /*+ OPT_PARAM('_fix_control' '9195582:0') */ * from dual ;

Depuis quelques version les vues V$SYS_FIX_CONTROL et V$SESSION_FIX_CONTROL permettent de voir ces données plus facilement

desc V$SESSION_FIX_CONTROL
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 SESSION_ID                                NUMBER
 BUGNO                                     NUMBER
 VALUE                                     NUMBER
 SQL_FEATURE                               VARCHAR2(64)
 DESCRIPTION                               VARCHAR2(64)
 OPTIMIZER_FEATURE_ENABLE                  VARCHAR2(25)
 EVENT                                     NUMBER
 IS_DEFAULT                                NUMBER

desc V$SYSTEM_FIX_CONTROL
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 BUGNO                                     NUMBER
 VALUE                                     NUMBER
 SQL_FEATURE                               VARCHAR2(64)
 DESCRIPTION                               VARCHAR2(64)
 OPTIMIZER_FEATURE_ENABLE                  VARCHAR2(25)
 EVENT                                     NUMBER
 IS_DEFAULT                                NUMBER

Lister les tables X$ d’une version Oracle

Ce petit script PERL permet de lister les tables « X$ » du moteur … Ce qui ne dit pas ce qu’elles font

#!/usr/bin/perl -w

use strict;

#open O, ($ORACLE_HOME."/bin/oracle");
open O, ("/logiciels/oracle/ora_10.2.0/bin/oracle");
open F, (">x");

my $l;
my $p = ' ' x 40;
my %x;

while (read (O,$l,10000)) {
$l = $p.$l;

foreach ($l =~ /(x$w{3,})/g) {
$x{$_}++;
}

$p = substr ($l,-40);
}

foreach (sort keys %x) {
print F "$_n";
}

Ce script provient du site http://www.adp-gmbh.ch

On peut trouver certaines explications sur le contenu des tables « X$ » sur freeshell, sur le site BC Oracle Training ou en bien d’autres endroits 😉

La version 10 d’oracle en liste quelques 350 par exemple la table x$kwddef liste l’ensemble des mots clés du SQL, la requête suivante permet donc de les lister :

select KEYWORD from x$kwddef
order by KEYWORD
/

vipca -silent

Créer les adresses vip en utilisant vipca en mode silencieux :

En étant connecté root :

vipca -silent -nodelist rac01,rac02 -nodevips 'rac01/rac01-vip/255.255.255.0/ce1,rac02/rac02-vip/255.255.255.0/ce1'

Gérer le failover des VIP :

crs_stat -p ora.rac01.vip > /var/tmp/ora.rac01.vip.cap
crs_stat -p ora.rac02.vip > /var/tmp/ora.rac02.vip.cap

changer les lignes

HOSTING_MEMBERS=rac01

en

HOSTING_MEMBERS=rac01 rac02

crs_register ora.rac01.vip -dir /var/tmp -u
crs_register ora.rac02.vip -dir /var/tmp -u