| What kind of pl sql questions to expect for a client technical interview [message #432168] |
Sun, 22 November 2009 11:26  |
sqlstar_student
Messages: 42
Registered: November 2007
|
Member |
|
|
Please advise all the experienced professionals
What kind of questions should i expect in the client facing technical interview i am having for a PL SQL Developer with Performance Tuning interview. The Client project is mostly a Financial Project.
Yes of course i would be asked basically tell me about the projects that you been involved in based on which they ask the questions, basically i am not highly experienced 3 years and my experience involves limited performance tuning, developing scripts for supporting front end web applications.
What kind of questions( you dont need to list out the questions specifically, it would be helpful if you could advise the areas which i should look into, also any links where i can read regarding the area you suggest when facing a client interview).
Example: Financial Transactions would deal a lot with Trasaction Processing so i should prepare for questions on Mutating Table, deadlocking.
Also i am aware of Collections,External Tables. Would these be discussed.
Any advise would be very much helpful.
My experience does not heavily involve any financial experience and the job specification didnt state so i got through the first 2 interview rounds. Would it be a disadvantage if i said i held no finanicial experience, i am just assuming by hearing from some sources that the client is finance area.
I would be recruited only if the client approves, so i need to look into as much as possible, there is time to prepare so kindly advise.
Regards.
|
|
|
|
|
|
| Re: What kind of pl sql questions to expect for a client technical interview [message #432170 is a reply to message #432168] |
Sun, 22 November 2009 11:54  |
sqlstar_student
Messages: 42
Registered: November 2007
|
Member |
|
|
Thanks Micheal,
I observe the Execution Plan it will list out the queries which are running and the statistics. I would observe if they are missing any indexes on the joining conditions or frequently referenced columns and add them.
In order to observe the execution plan without having to put pressure on the system i run the problematic queries with EXPLAIN PLAN for select statement.
If using cursors for performing any DML operations then i would use Pl sql objects such as Collections in the cursors as they would improve performance by preventing switching between sql, pl sql engine.
Using EXISTS,NOT EXISTS instead of IN, NOT IN as we can assign a condition for Exists, Not exists which prevents full table scan which happends with in,not in.
I should read more about cost based optimiser which is the inbuilt algorithm engine since Oracle 9i which calculates the best possible path for the query to take.
Using hints for checking for performance improvement such as
select /*+INDEX(a, a_ind1)*/ from tab a where col = abc
select /*+FIRST_ROWS*/,col1,col2 from table where condition.
Using Ref Cursors which are basically user defined cursors instead of cursors as ref cursors are dynamic.
Please advise.
Regards
|
|
|
|
|
|
| Re: What kind of pl sql questions to expect for a client technical interview [message #432184 is a reply to message #432172] |
Sun, 22 November 2009 16:39 |
sqlstar_student
Messages: 42
Registered: November 2007
|
Member |
|
|
Thank you for listing down all the prerequisites before going through explain plan. It would be better to tackle the interview by starting this way.
Transactions
A Transaction is an Action/exchange of resources or objects or data initiated by the system or user.
2 types
Implicit Transaction
They are undertaken by the Oracle Server and an simple example is a Query which is dependent on the data from a sub query. If the sub query fails then the Main Query fails and raises an exception
Explicit Transaction
These are User defined trasactions which have
Begin
Savepoint savepoint_name
COMMIT/ROLLBACK
END
Doubt 1
I have to Admit i did not had any idea of how Oracle undertakes row level locking, till you asked and i looked it up and this is what i understood. I referred
http://www.dba-oracle.com/t_savepoint.htm
Oracle obtains locks on a row/record that it is trying to modify and those locks are not released till Commit/rollback/rollback to savepoint.
However rollback to save point is not fully supported by Oracle.
Transaction 1 is modifying a table and has acquired lock on the entire table/records it is trying to modify.
Transaction 2 is waiting to obtain lock on the record B of the table Transaction 1 is modifying
Transaction 1 is updating a record A
Savepoint s_a;
Transaction 1 is modifying record B
rollback to s_a;
Then the lock on transaction B should be released and Transaction 2 should be able to access the Record B
BUT
what happens is it cannot access B till the entire transaction is COMMIT or ROLLBACK
Because
if Transaction A
Modify RecordA
savepoint s_a;
Modify Record B
rollback to s_a; Locks released on B
If locks are released on rollback to savepoint.
Meanwhile Transaction 2
Modify Record B
COMMIT
Transaction 1
Modify Record C
Exception is thrown;
ROLLBACK
Then will the changes to Record B made by Transaction 2 are rolled back.? In order to prevent that Oracle only supports Locking on full commit or rollback issued by earlier transaction.
In order to prevent that Locks are granted only on full Commit or ROllback and not on Rollback to save point.
Please correct me i feel that my understanding of the resource locking is unclear.
In case i am wrong in my understanding please explain/direct me to any links where i can gain a better understanding.
Will there be a deadlock between Transaction 2, 1
2
I know this that if a Transaction I is calling Transaction II and T II successful then at the end of T I there is a Rollback but T I is not rolled back
T I
EXEC T II( T II statements commit;) If it commits
ROLLBACK
END
or
T I
EXEC T II( T II statements commit;) If it throws exception
COMMIT
END
If T II throws exception T I is rolled back in order to prevent that we declare PRAGMA AUTONOMOUS TRANSACTIONS in T II, which means the actions of T II wont affect T I.
Doubt 3
Deadlocking Issue how to resolve
Person A is debiting account
Person B is crediting the same account A
Both at the same instance
then how to resolve this which action is done.
Thank you
|
|
|
|
|
|
| Re: What kind of pl sql questions to expect for a client technical interview [message #432241 is a reply to message #432199] |
Mon, 23 November 2009 03:24 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Although, I would have do amend what Michael said a little on the order of the steps. (At least if "some code" is not a single function/procedure, but a bigger application. Where you have to IDENTIFY the problematic code first)
If you have an application with maybe 50 million lines of code, it is not really feasible to get the business rules first, and then start reading the code to find "bad code".
You might end up finding tons of "bad code", that COULD be changed to optimize the application. But if 95% of that bad code is being run once a week/once a month for a single job, you might end up optimizing code from running 5 seconds a week on a Sunday night batch to run 0.1 seconds a week on a Sunday night batch. Which might not be really worth it.
So there one of the first steps would definitely be having a look at the "Top" IO / CPU / Time consuming SQL queries during a normal business day (via Statspack, AWR or v$sqlarea), and have a closer look at them first.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
