|
Oracle Interview Questions and Answers
When is cost based optimization triggered? (for DBA)
It's important to have statistics on all tables for the
CBO (Cost Based Optimizer) to work correctly. If one
table involved in a statement does not have statistics,
Oracle has to revert to rule-based optimization for that
statement. So you really want for all tables to have
statistics right away; it won't help much to just have
the larger tables analyzed.
Generally, the CBO can change the execution plan when
you:
1. Change statistics of objects by doing an ANALYZE;
2. Change some initialization parameters (for example:
hash_join_enabled, sort_area_size,
db_file_multiblock_read_count).
How can one optimize %XYZ% queries? (for DBA)
It is possible to improve %XYZ% queries by forcing the
optimizer to scan all the entries from the index instead
of the table. This can be done by specifying hints. If
the index is physically smaller than the table (which is
usually the case) it will take less time to scan the
entire index than to scan the entire table.
What Enter package procedure does ?
Enter Validate-data in the current validation unit.
Where can one find I/O statistics per table? (for DBA)
The UTLESTAT report shows I/O per tablespace but one
cannot see what tables in the tablespace has the most
I/O. The $ORACLE_HOME/rdbms/admin/catio.sql script
creates a sample_io procedure and table to gather the
required information. After executing the procedure, one
can do a simple SELECT * FROM io_per_object; to extract
the required information. For more details, look at the
header comments in the $ORACLE_HOME/rdbms/admin/catio.sql
script.
My query was fine last week and now it is slow. Why?
(for DBA)
The likely cause of this is because the execution plan
has changed. Generate a current explain plan of the
offending query and compare it to a previous one that
was taken when the query was performing well. Usually
the previous plan is not available.
Some factors that can cause a plan to change are:
. Which tables are currently analyzed? Were they
previously analyzed? (ie. Was the query using RBO and
now CBO?)
. Has OPTIMIZER_MODE been changed in INIT.ORA?
. Has the DEGREE of parallelism been defined/changed on
any table?
. Have the tables been re-analyzed? Were the tables
analyzed using estimate or compute? If estimate, what
percentage was used?
. Have the statistics changed?
. Has the INIT.ORA parameter
DB_FILE_MULTIBLOCK_READ_COUNT been changed?
. Has the INIT.ORA parameter SORT_AREA_SIZE been
changed?
. Have any other INIT.ORA parameters been changed?
. What do you think the plan should be? Run the query
with hints to see if this produces the required
performance.
Why is Oracle not using the damn index? (for DBA)
This problem normally only arises when the query plan is
being generated by the Cost Based Optimizer. The usual
cause is because the CBO calculates that executing a
Full Table Scan would be faster than accessing the table
via the index.
Fundamental things that can be checked are:
. USER_TAB_COLUMNS.NUM_DISTINCT - This column defines
the number of distinct values the column holds.
. USER_TABLES.NUM_ROWS - If NUM_DISTINCT = NUM_ROWS then
using an index would be preferable to doing a FULL TABLE
SCAN. As the NUM_DISTINCT decreases, the cost of using
an index increase thereby is making the index less
desirable.
. USER_INDEXES.CLUSTERING_FACTOR - This defines how
ordered the rows are in the index. If CLUSTERING_FACTOR
approaches the number of blocks in the table, the rows
are ordered. If it approaches the number of rows in the
table, the rows are randomly ordered. In such a case, it
is unlikely that index entries in the same leaf block
will point to rows in the same data blocks.
. Decrease the INIT.ORA parameter
DB_FILE_MULTIBLOCK_READ_COUNT - A higher value will make
the cost of a FULL TABLE SCAN cheaper.
. Remember that you MUST supply the leading column of an
index, for the index to be used (unless you use a FAST
FULL SCAN or SKIP SCANNING).
. There are many other factors that affect the cost, but
sometimes the above can help to show why an index is not
being used by the CBO. If from checking the above you
still feel that the query should be using an index, try
specifying an index hint. Obtain an explain plan of the
query either using TKPROF with TIMED_STATISTICS, so that
one can see the CPU utilization, or with AUTOTRACE to
see the statistics. Compare this to the explain plan
when not using an index.
When should one rebuild an index? (for DBA)
You can run the 'ANALYZE INDEX VALIDATE STRUCTURE'
command on the affected indexes - each invocation of
this command creates a single row in the INDEX_STATS
view. This row is overwritten by the next ANALYZE INDEX
command, so copy the contents of the view into a local
table after each ANALYZE. The 'badness' of the index can
then be judged by the ratio of 'DEL_LF_ROWS' to 'LF_ROWS'.
What are the unrestricted procedures used to change the
popup screen position during run time ?
Anchor-view
Resize -View
Move-View.
What is an Alert ?
An alert is window that appears in the middle of the
screen overlaying a portion of the current display.
Deleting a page removes information about all the fields
in that page ?
a. True. b. False
a. True.
Two popup pages can appear on the screen at a time ?Two
popup pages can appear on the screen at a time ?
a. True. b. False?
a. True.
Classify the restricted and unrestricted procedure from
the following.
a. Call
b. User-Exit
c. Call-Query
d. Up
e. Execute-Query
f. Message
g. Exit-From
h. Post
i. Break?
a. Call - unrestricted
b. User Exit - Unrestricted
c. Call_query - Unrestricted
d. Up - Restricted
e. Execute Query - Restricted
f. Message - Restricted
g. Exit_form - Restricted
h. Post - Restricted
i. Break - Unrestricted.
What is an User Exits ?
A user exit is a subroutine which are written in
programming languages using pro*C pro *Cobol , etc.,
that link into the SQL * forms executable.
What is a Trigger ?
A piece of logic that is executed at or triggered by a
SQL *forms event.
What is a Package Procedure ?
A Package procedure is built in PL/SQL procedure.
What is the maximum size of a form ?
255 character width and 255 characters Length.
What is the difference between system.current_field and
system.cursor_field ?
1. System.current_field gives name of the field.
2. System.cursor_field gives name of the field with
block name.
List the system variables related in Block and Field?
1. System.block_status
2. System.current_block
3. System.current_field
4. System.current_value
5. System.cursor_block
6. System.cursor_field
7. System.field_status.
What are the different types of Package Procedure ?
1. Restricted package procedure.
2. Unrestricted package procedure.
What are the types of TRIGGERS ?
1. Navigational Triggers.
2. Transaction Triggers.
Identify package function from the following ?
1. Error-Code
2. Break
3. Call
4. Error-text
5. Form-failure
6. Form-fatal
7. Execute-query
8. Anchor View
9. Message_code?
1. Error_Code
2. Error_Text
3. Form_Failure
4. Form_Fatal
5. Message_Code
Can you attach an lov to a field at run-time? if yes,
give the build-in name.?
Yes. Set_item_proprety
Is it possible to attach same library to more than one
form?
Yes
Can you attach an lov to a field at design time?
Yes
List the windows event triggers available in Forms 4.0?
When-window-activated,
when-window-closed,
when-window-deactivated,
when-window-resized
What are the triggers associated with the image item?
When-Image-activated(Fires when the operator double
clicks on an image Items)
When-image-pressed(fires when the operator selects or
deselects the image item)
What is a visual attribute?
Visual Attributes are the font, color and pattern
characteristics of objects that operators see and
intract with in our application.
How many maximum number of radio buttons can you assign
to a radio group?
Unlimited no of radio buttons can be assigned to a radio
group
Page Numbers :
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
Have a Question ?
post your questions here. It
will be answered as soon as possible.
Check
Job Interview Questions
for more Interview Questions with Answers
|
