1. Tell me something about yourself.ans:-
1. Education
2. Interest
3. Hobby
4. Exp
5. Current role in company
6. Achievement
7. Strength.
2. Project work as in what kind of environment you are working, day to day activities, previous project history, overall experience, activities in various projects.
ans:-
1. Project name
2. Client and project description
3. Environment: Linux 5.4
4. day to day activity:
1. Alert log checking
2. Import export
3. Monitoring database
4. User creation, grant privilege, revokes privilege
5. Creation of tables resizes datafile, tablespace
6. Troubleshooting.
3. A new user has come, what all steps you take to register him.
Ans:-
1. Create use by OEM or sqlplus
2. Provide roles and privilege
3. Provide resource to connect
4. Provide tablespace and temp tablespace.
5. Give user name password and profile.
4. A user says his request is running very slow, what all can you do?
Enable trace and generate the TKPROF file it give which request is slow.
the slowdown is due to a combination of subquery usage
and prepared statement usage, with the Oracle query planner making a poor
choice of data access plan when confronted with the ? value placeholders
in the prepared statement.
Things might be better if we had a non prepared statement path where
the query planner can see the actual values instead of the ? for each request
before making a data access plan.
5. A SQL is taking too much time , waht will you to do make it run faster.
Ans:-
1. Maximum use of indexes, stored procures should be done.
2. Avoid excessive use of complicated joins and cursors.
3. Avoid using conditional operators using columns of different tables.
4. Make use of computed columns and rewriting the query.
6. What is optimizer. Role of it ?
Ans:-
The optimizer determines the most efficient way to execute a SQL statement after considering many factors related to the objects referenced and the conditions specified in the query.
Role:-
1. Determine efficient way to execute the query.
2. Affect execution time.
3. The output from the optimizer is a plan that describes an optimum method of execution.
4. The Oracle server provides the cost-based (CBO) and rule-based (RBO) optimization
7. What can you do if you know the execution plan of any statement?
The execution plan reveals the internal details about the SQL execution including table join types (nested loops, hash join, merge join), and sorting.
review execution plans looking for tuning opportunities as follows:
1. Add indexes (especially function-based indexes)
2. Change the optimizer parms
3. Employ Materialized Views
4. Improve CBO statistics (especially histograms)
5. Cache high-use small tables & indexes
6. Tune the library cache (cursor_sharing, etc)
7. Adjust segment structure (large blocksizes for index range scans, small
8. blocksizes for random OLTP)
8. How will you enable tracing? is it DBA wide or can be for a particular user.
1. Enable trace at instance level
Put the following line in init.ora. It will enable trace for all sessions and the background
Processes
sql_trace = TRUE
2. To enable tracing without restarting database run the following command in sqlplus
SQLPLUS> ALTER SYSTEM SET trace_enabled = TRUE;
Enable trace at session level
ALTER SESSION SET sql_trace = TRUE;
Or
EXECUTE dbms_session.set_sql_trace (TRUE);
3. Enable trace in another session
Find out SID and SERIAL# from v$session. For example:
SELECT * FROM v$session WHERE osuser = OSUSER;
to start trace:
EXECUTE dbms_support.start_trace_in_session (SID, SERIAL#);
5.Tracing an entire database
ALTER SYSTEM SET sql_trace = true SCOPE=MEMORY;
6.Enable tracing for your selected process:
SQL> ALTER SYSTEM SET timed_statistics = true;
SQL> execute dbms_system.set_sql_trace_in_session(8, 13607, true);
9. How to increase the size of database.
If we going to increase the size of datafile the database size will automatically increase
ALTER DATABASE DATAFILE 'filename2' RESIZE 100M;
10. How can you see the execution plan?
Oracle provides the EXPLAIN PLAN function so you may see the details of the execution plan. To use EXPLAIN PLAN, you would issue a statement similar to this:
EXPLAIN PLAN FOR select * from emp;
When you do an EXPLAIN PLAN, Oracle analyzes the statment and populates a table named PLAN_TABLE with information on the execution plan for the statement.
11.What will you do if database is working slowly?
1.Restarting
2.If it doesn't start
You have to try to figure out why it isn't starting
3.If it doesn't stop
Kill off all sessions connected to shutdown immediate or (gulp) do a shutdown abort.
4.Kill some of the process that is unwanted.
12. The database is 24x7. I don't want to enable tracing but still I want to know as to which SQL statements are consuming maximum resources how will you see that?
13.Difference between char and varchar2. Should oracle remove its 'char' datataype?
Ans:-
CHAR is used to store fixed length character strings where as Varchar2 can store variable length character strings. However, for performance sake Char is quit faster than Varchar2.
If we have char name[10] and store “abcde”, then 5 bytes will be filled with null values, whereas in case of varchar2 name[10] 5 bytes will be used and other 5 bytes will be freed.
14.if a developer tells you to create a table, how will you do it and steps you will take. You will use front-end or backend script to do this?
Ans:- generate the TKPROF file it give which request is slow.
1. Define table name
2. Column name
3. Column data-type and its length
4. Define tablespace
5. Define constraint like foreign key primary key check key not null etc
I will use oem for this operation as well as we can do it from sql*plus.
15.how good are you in PL/SQL?
Ans:-not good but i know the basic.
what are cursors?
Ans:-
Cursor is used to access the access the result set present in the memory. This result set contains the records returned on execution of a query.
They are of 2 types:
1. Explicit
2. Implicit
Capabilities of cursors:
1. Cursor reads every row one by one.
2. Cursors can be used to update a set of rows or a single specific row in a resultset
3. Cursors can be positioned to specific rows.
4. Cursors can be parameterized and hence are flexible.
5. Cursors lock row(s) while updating them.
16.difference between views and materialized views.
Ans:
View:-It is a just snapshot of a table it like a window where user can see the data only that provided by sysdba.
Materlised View:-It used to increase query performance because it contain the result of a query.
17.Are you involved in designing database.? How do you manage relationships in tables?(parent-child relationships)?
Ans:-
Yes, ...
