Every Database Administrator is having an idea from where to get the details about the instance, i.e., v$instance. But today let us see from where these details comes from and what are the other details and important information with respect to our instance. let’s see how much they are useful with our understanding of ORACLE Architecture and troubleshooting aspects.
Let’s query the view and check the output of the query.
1: SQL> select * from v$instance;
2: 3: INSTANCE_NUMBER INSTANCE_NAME4: --------------- ----------------
5: HOST_NAME6: ----------------------------------------------------------------
7: VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
8: ----------------- --------- ------------ --- ---------- ------- ---------------
9: LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO10: ---------- --- ----------------- ------------------ --------- ---
11: 1 orcl 12: PAVAN-PC13: 11.2.0.1.0 03-OCT-10 OPEN NO 1 STOPPED
14: ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
We can able to view the details about the instance, like instance_number, instance_name etc., Just think from where we are getting these details. The following is the query which works under the v$instance..
1: select ks.inst_id,
2: ksuxsins, 3: ksuxssid, 4: ksuxshst, 5: ksuxsver, 6: ksuxstim, 7: decode(ksuxssts,8: 0,'STARTED',
9: 1,'MOUNTED',
10: 2,'OPEN',
11: 3,'OPEN MIGRATE',
12: 'UNKNOWN'),
13: decode(ksuxsshr,14: 0,'NO',
15: 1,'YES',
16: 2,NULL),
17: ksuxsthr, 18: decode(ksuxsarc,19: 0,'STOPPED',
20: 1,'STARTED',
21: 'FAILED'),
22: decode(ksuxslsw,23: 0,NULL,
24: 2,'ARCHIVE LOG',
25: 3,'CLEAR LOG',
26: 4,'CHECKPOINT',
27: 5,'REDO GENERATION'),
28: decode(ksuxsdba,29: 0,'ALLOWED',
30: 'RESTRICTED'),
31: decode(ksuxsshp,32: 0,'NO','YES'),
33: decode(kvitval,34: 0,'ACTIVE',
35: 2147483647,'SUSPENDED',
36: 'INSTANCE RECOVERY'),
37: decode(ksuxsrol,38: 1,'PRIMARY_INSTANCE',
39: 2,'SECONDARY_INSTANCE',
40: 'UNKNOWN'),
41: decode(qui_state,42: 0,'NORMAL',
43: 1,'QUIESCING',
44: 2,'QUIESCED',
45: 'UNKNOWN'),
46: decode(bitand(ksuxsdst, 1), 47: 0, 'NO',
48: 1, 'YES', 'NO')
49: from
50: x$ksuxsinst ks, 51: x$kvit kv, 52: x$quiesce qu 53: where kvittag = 'kcbwst'
X$KSUXSINST - [K]ernel [U]ser Management [X]Instance [Inst]ance State - since instance is part of the user management which comes under the role of DBA.
X$KVIT - [K]ernel Performance Layer [V] [T]ransitory Instance parameters - parameters which change or gets modified values based on instance.
X$QUIESCE – Holds the details about the quiesce state of Database.
let’s further understand about these tables and their default information residing with in it.
1: SQL> desc x$kvit
2: Name Null? Type
3: ----------------------------------------- -------- ----------------
4: ADDR RAW(4) 5: INDX NUMBER 6: INST_ID NUMBER 7: KVITVAL NUMBER 8: KVITTAG VARCHAR2(64) 9: KVITDSC VARCHAR2(64) 10: 11: SQL> select count(*) from x$kvit;
12: 13: COUNT(*)
14: ---------- 15: 17Let’s see the 17 records – which comes by default installation and what this information depicts.
1: SQL> select * from x$kvit;
2: 3: ADDR INDX INST_ID KVITVAL KVITTAG KVITDSC 4: -------- ---------- ---------- ---------- ---------- ----------
5: 6: 06299A20 0 1 2 ksbcpu number of logical CPUs in the system used by Oracle
7: 06299A28 1 1 2 ksbcpucore number of physical CPU cores in the system used by Oracle
8: 06299A30 2 1 1 ksbcpusocket number of physical CPU sockets in the system used by Oracle
9: 06299A38 3 1 2 ksbcpu_hwm high water mark of number of CPUs used by Oracle
10: 06299A40 4 1 2 ksbcpucore_hwm high water mark of number of CPU cores on system
11: 06299A48 5 1 1 ksbcpusocket_hwm high water mark of number of CPU sockets on system
12: 06299A50 6 1 2 ksbcpu_actual number of available CPUs in the system
13: 06299A58 7 1 1 ksbcpu_dr CPU dynamic reconfiguration supported
14: 06299A60 8 1 45770 kcbnbh number of buffers
15: 06299A68 9 1 25 kcbldq large dirty queue if kcbclw reaches this
16: 06299A70 10 1 40 kcbfsp Max percentage of LRU list foreground can scan for free
17: 06299A78 11 1 2 kcbcln Initial percentage of LRU list to keep clean
18: 06299A80 12 1 750 kcbnbf number buffer objects 19: 06299A88 13 1 0 kcbwst Flag that indicates recovery or db suspension
20: 06299A90 14 1 0 kcteln Error Log Number for thread open
21: 06299A98 15 1 0 kcvgcw SGA: opcode for checkpoint cross-instance call
22: 06299AA0 16 1 0 kcvgcw SGA:opcode for pq checkpoint cross-instance call
23: 24: 17 rows selected.
25: 26: SQL> spool off;
You can find plenty of details for your reference, you can see based on the SGA configuration, the number of buffer objects available are calculated and maintained. Some of the high water marks are maintained – a static reference is carried out and stored initially by oracle based on the hardware we opt for the installation. The query joins with the x$kvit in order to get the details about the “kcbwst”.
Take your time and go through the details – in future posts we will come back to these values and parameters. Let’s check other views.
1: SQL> select * from x$ksuxsinst;
2: 3: ADDR INDX INST_ID KSUXSINS KSUXSSID 4: -------- ---------- ---------- ---------- ----------------
5: KSUXSHST 6: ----------------------------------------------------------------
7: KSUXSVER KSUXSTIM KSUXSSTS KSUXSSHR KSUXSTHR KSUXSARC 8: ----------------- --------- ---------- ---------- ---------- ----------
9: KSUXSLSW KSUXSDBA KSUXSSHP KSUXSSCN KSUXSROL KSUXSDST 10: ---------- ---------- ---------- ---------------- ---------- ----------
11: 1049E784 0 1 1 orcl 12: PAVAN-PC 13: 11.2.0.1.0 03-OCT-10 2 0 1 0 14: 0 0 0 3784462 1 0 15: 16: 17: SQL> spool off
Rest of the details about the instance are maintained in the “x$ksuxsinst”.
1 comments: on "Internals of v$instance view…."
Hi Pavan,
Excited to check out these internals.Great !!!!!
Where to get the full names of the internal tables like "X$KSUXSINST"??
Regards,
Anand
Post a Comment