MY INTERVIEW WITH YODLEE

Hi All,
           This post is to share my experience of interview with yodlee:

Q1)What are the activities you do daily?
Ans:                        I replied:
Let me introduce myself,My name is Rafiuddin Alvi I'm  working as ORACLE DBA since 3 yrs.Currently I'm working with OPENLANE,Previously I used to work with PENTA-C.I have done my oracle 9i,10g & 11g OCP CERTIFICATION & I'm RAC Certified Expert.I have worked on 4 platforms :HP/UX,LINUX,SOLARIS & IBM-AIX included windows servers.My JOB RESPONSIBILITIES includes validating the backup,Checking the performance of the Databases,Checking the tablespace space requirement,Designing the Database and creating the schema changes,starting & shutting of Database as when required,Refreshing the databases as required and Helping the developers to tune the SQL statements.


Q 2)How can we recover the drop table?
Ans:                        I explain with few cases like we can

1)We can restore the entire database to the time where table had dropped  ->Not recommended
2)Restore the tablespace->Better option
3)Restore and recover a subset of the database as a DUMMY database to export

the table data and import it into the primary database. This is the best option
as only the dropped table goes back in time to before the drop

I explained the third case in brief:
Refer the details for the 3 rd case in separate post.

Q 3)How do we recover the database when the redolog file which is current is lost?
Ans:                          This question is really a tricky one I asked to the intervier by mistake is it archived or not,definetly a stupid question because when the group is current it cannot be archived since the logwriter is writing on it.Then suddenly I said I will just switch it by using alter system switch log file;

Finally I answed:
We have to make use of the below command:
SQL>ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP group_number;

        Which is half correct, if the logfile group is corrupt and it avoids archiving.If this is not happening you can perform the below steps:
You have to go for incompelte recovery and Open with resetlogs:


RESTORE CONTROLFILE FROM '';
ALTER DATABASE MOUNT;
RESTORE DATABASE;
RECOVER DATABASE UNTIL TIME "to_date('MAR 18 2010 14:57:00','MON DD YYYY HH24:MI:SS')";
ALTER DATABASE OPEN RESETLOGS;

Q 4)When the users complain Database is slow what will be your reaction?
Ans MY ANSWER:
o
We can just find out and check if it is a network issue by using tools like:
vmstat and iostat.

Then she asked which columns you will look: I was not remembering exactly but these are the columns

vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
4 3 70928 28364 33488 19395224 0 0 2553 1290 0 0 22 2 66 10

iostat
Linux 2.6.9-55.ELsmp (dbdwh01.autc.com) 03/19/2010

avg-cpu: %user %nice %sys %iowait %idle
22.30 0.00 1.72 10.39 65.60

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
cciss/c0d0 67.78 140.94 45.85 2280376542 741888688

I should have mention of few more utility like
*Top
*Sar

Top command Functionalily:
The Unix top command is designed to help users determine which processes are running and which applications are using more memory or processing power than they should be. It is an extremely useful command for those looking to reduce the memory footprint on their Unix installation or for those who like a more responsive, more streamlined computing environment. The Unix top command is a highly useful tool that will come in handy in a variety of situations. Furthermore, by default, the Unix top command will update its findings on a regular basis, usually every few seconds, so that a user can get a real time picture of what the computer is doing at any given moment.

The top command is very easy to use and requires little to no prior knowledge of how Unix commands work. It can be run by anyone, so long as they know how to enter text into the command line or terminal
The Unix top command is designed to help users determine which processes are running and which applications are using more memory or processing power than they should be. It is an extremely useful command for those looking to reduce the memory footprint on their Unix installation or for those who like a more responsive, more streamlined computing environment. The Unix top command is a highly useful tool that will come in handy in a variety of situations. Furthermore, by default, the Unix top command will update its findings on a regular basis, usually every few seconds, so that a user can get a real time picture of what the computer is doing at any given moment.


Columns Which I should look for are:

PID , User ,%CPU, %MEMORY and compare with it with users in oracle and sessions in v$session.

I continued with my answer that we can use statspack to monitor the performance of the database.But the question again arises what we usually see in statspack report.

I answered we should identify top 5 wait events which includes:

The top 5 wait events which I can see are:Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
db file sequential read 1,547,030 167,785 68.61
buffer busy waits 316,992 40,103 16.40
latch free 17,757 17,784 7.27
db file parallel read 6,995 8,257 3.38
db file scattered read 9,969 4,945 2.02

What is buffer busy waits ? she asked which I didn't answered properly and will be explaining in separate post

Than I said I use awr reports instead of statspack report.Than she replied you explain that itself what you see there.

I answered Identifying the wait events as per different test cases and test the performance.But still she was focusing on what I do...

Suddently she said Did you heard what is hints.

Yes,I heard the optimizer hints are use to alter the execution plan of the SQL statement

Q 5 )What are the hints have You used?

I remembered only one hint /*+ all_rows */

So hint I will be studying and will be posting as separate post.

Q 6)How you tune your SQL statement and What you do If the user complain the query is running slow?
Ans: Explained in my post.I answered.But what you see in explain plan or how you interpret the explain plan result is the other question?
Though I didn't explain properly.Let me tell you the steps How we should interprete the output of explain plan or autotrace:

What's an explain plan?

An explain plan is a representation of the access path that is taken when a query is executed within Oracle.

Query processing can be divided into 7 phases:

[1] Syntactic Checks the syntax of the query
[2] Semantic Checks that all objects exist and are accessible
[3] View Merging Rewrites query as join on base tables as opposed to using views
[4] Statement
Transformation Rewrites query transforming some complex constructs into simpler ones where appropriate (e.g. subquery merging, in/or transformation)
[5] Optimization Determines the optimal access path for the query to take. With the Rule Based Optimizer (RBO) it uses a set of heuristics to determine access path. With the Cost Based Optimizer (CBO) we use statistics to analyze the relative costs of accessing objects.
[6] QEP Generation QEP = Query Evaluation Plan
[7] QEP Execution QEP = Query Evaluation Plan

I will explan with eg in my separate post.

Q 7)What is cache fusion?
Ans :
Oracle RAC Cache Fusion and I/O Bandwidth

Oracle RAC Cache Fusion uses a high-speed IPC interconnect to provide cache-to-cache transfers of data blocks between instances in a cluster. This is called data block shipping. This eliminates the disk I/O and optimizes read/write concurrency. Block reads take advantage of the speed of IPC and an interconnecting network.

The cache-to-cache data transfer is performed through the high speed IPC interconnect. The Oracle Global Cache Service (GCS) tracks blocks that were shipped to other instances by retaining block copies in memory. Each such copy is called a past image (PI). The GCS, via the LMSx background process, tracks one or more past image versions (PI) for a block in addition to the traditional GCS resource roles and modes. In the event of a node failure, Oracle can reconstruct the current version of a block by using a saved PI.


Q 8)If I have a 2 node RAC cluster in which each node is having one instance.If I perform delete operation on 1 node and than suddenly that node disconnect will the operation be successfully?

Ans:Yes It should be completed successfully I told.

Reason:
Yes. The database, remember, is on a shared device. The disconnection of one node has no bearing of the data on the shared disks, only a means of accessing it. In theory, as long as the transaction has been committed, the change is permanent to the database.

This was not a great interview for me but it really help me in knowing many things Thats why I say :

GIVING INTERVIEWS IMPROVES YOUR KNOWLEDGE.







Best regards,

Rafi.