ORACLE Interview Questions and Answers
Part:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
(Continued from previous part...)
603. What is in all those X$ tables? (for DBA
The following list attempts to describe some x$ tables. The list may not be complete or accurate, but represents an attempt to figure out what information they contain. One should generally not write queries against these tables as they are internal to Oracle, and Oracle may change them without any prior notification.
X$K2GTE2
|
Kernel 2 Phase Commit Global Transaction Entry Fixed Table
|
X$K2GTE
|
Kernel 2 Phase Commit Global Transaction Entry Fixed Table
|
X$BH
|
Buffer headers contain information describing the current contents of a piece of the buffer cache
|
X$KCBCBH
|
Cache Buffer Current Buffer Header Fixed Table. It can predict the potential loss of decreasing the number of database buffers. The db_block_lru_statistics parameter has to be set to true to gather information in this table.
|
X$KCVFH
|
File Header Fixed Table
|
X$KDNCE
|
SGA Cache Entry Fixed Table
|
X$KDNST
|
Sequence Cache Statistics Fixed Table
|
X$KDXHS
|
Histogram structure Fixed Table
|
X$KDXST
|
Statistics collection Fixed Table
|
X$KGHLU
|
One-row summary of LRU statistics for the shared pool
|
X$KGLBODY
|
Derived from X$KGLOB (col kglhdnsp = 2)
|
X$KGLCLUSTER
|
Derived from X$KGLOB (col kglhdnsp = 5)
|
X$KGLINDEX
|
Derived from X$KGLOB (col kglhdnsp = 4)
|
X$KGLLC
|
Latch Clean-up state for library cache objects Fixed Table
|
X$KGLPN
|
Library cache pin Fixed Table
|
X$KGLTABLE
|
Derived from X$KGLOB (col kglhdnsp = 1)
|
X$KGLTR
|
Library Cache Translation Table entry Fixed Table
|
X$KGLTRIGGER
|
Derived from X$KGLOB (col kglhdnsp = 3)
|
X$KGLXS
|
Library Cache Access Table
|
X$KKMMD
|
Fixed table to look at what databases are mounted and their status
|
X$KKSBV
|
Cursor Cache Bind Variables
|
X$KSMSP
|
Each row represents a piece of memory in the shared pool
|
X$KSQDN
|
Global database name
|
X$KSQST
|
Enqueue statistics by type
|
X$KSUCF
|
Cost function for each Kernel Profile (join to X$KSUPL)
|
X$KSUPL
|
Resource Limit for each Kernel Profile
|
X$KSURU
|
Resource Usage for each Kernel Profile (join with X$KSUPL)
|
X$KSQST
|
Gets and waits for different types of enqueues
|
X$KTTVS
|
Indicate tablespace that has valid save undo segments
|
X$KVII
|
Internal instance parameters set at instance initialization
|
X$KVIS
|
Oracle Data Block (size_t type) variables
|
X$KVIT
|
Instance internal flags, variables and parameters that can change during the life of an instance
|
X$KXFPCDS
|
Client Dequeue Statistics
|
X$KXFPCMS
|
Client Messages Statistics
|
X$KZDOS
|
Represent an os role as defined by the operating system
|
X$KZSRO
|
Security state Role: List of enabled roles
|
X$LE
|
Lock Element: each PCM lock that is used by the buffer cache (gc_db_locks)
|
X$MESSAGES
|
Displays all the different messages that can be sent to the Background processes
|
X$NLS_PARAMETERS
|
NLS database parameters
|
Handy X$table queries
Some handy queries based on the X$ memory tables:
. Largest # blocks you can write at any given time:
select kviival write_batch_size
from x$kvii where kviitag = 'kcbswc';
. See the gets and waits for different types of enqueues:
select * from x$ksqst
where ksqstget > 0;
Oracle Kernel Subsystems
Listed below are some of the important subsystems in the Oracle kernel. This table might help you to read those dreaded trace files and internal messages. For example, if you see messages like this, you will at least know where they come from:
OPIRIP: Uncaught error 447. Error stack:
KCF: write/open error block=0x3e800 online=1
OPI
|
Oracle Program Interface
|
KK
|
Compilation Layer - Parse SQL, compile PL/SQL
|
KX
|
Execution Layer - Bind and execute SQL and PL/SQL
|
K2
|
Distributed Execution Layer - 2PC handling
|
NPI
|
Network Program Interface
|
KZ
|
Security Layer - Validate privs
|
KQ
|
Query Layer
|
RPI
|
Recursive Program Interface
|
KA
|
Access Layer
|
KD
|
Data Layer
|
KT
|
Transaction Layer
|
KC
|
Cache Layer
|
KS
|
Services Layer
|
KJ
|
Lock Manager Layer
|
KG
|
Generic Layer
|
KV
|
Kernel Variables (eg. x$KVIS and X$KVII)
|
S or ODS
|
Operating System Dependencies
|
604. What are the Different Optimisation Techniques
The Various Optimisation techniques are
a) Execute Plan :: we can see the plan of the query and change it accordingly based on the indexes
b) Optimizer_hint ::
set_item_property('DeptBlock',OPTIMIZER_HINT,'FIRST_ROWS');
Select /*+ First_Rows */ Deptno,Dname,Loc,Rowid from dept
where (Deptno > 25)
c) Optimize_Sql ::
By setting the Optimize_Sql = No, Oracle Forms assigns a single cursor for all SQL statements.This slow downs the processing because for evertime the SQL must be parsed whenver they are executed.
f45run module = my_firstform userid = scott/tiger optimize_sql = No
d) Optimize_Tp ::
By setting the Optimize_Tp= No, Oracle Forms assigns seperate cursor only for each query SELECT statement. All other SQL statements reuse the cursor.
f45run module = my_firstform userid = scott/tiger optimize_Tp = No
(Continued on next part...)
Part:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
|