Home » Other » Training & Certification » What kind of pl sql questions to expect for a client technical interview (Oracle 9i/10g)
What kind of pl sql questions to expect for a client technical interview [message #432168] Sun, 22 November 2009 11:26 Go to next message
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 #432169 is a reply to message #432168] Sun, 22 November 2009 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you to improve the performances of some code?

Regards
Michel
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 Go to previous messageGo to next message
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 #432172 is a reply to message #432170] Sun, 22 November 2009 12:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not correct.
You ask for the business rules.
You ask for the specifications.
You remove all reports that no one will read.
You remove all not mandatory output.
You remove the maximum of generic code (dynamic SQL).
You replace all possible procedural codes by SQL statements.
You (most) always use bind variables and must have very good reasons to not use them.
You always prepare statement and use statement that you can share.
You never use cursor loop unless there are very good reasons.
You never fetch rows one per one but array per array.
You never lock resources more than necessary and you release them as far as possible (following the business needs).
You never commit inside a loop.
You comment and indent the code (including SQL statements that are parts of the code). A clean code is often a code that is already optimized and anyway a code that is more easy to optimize.

When all this is fixed maybe you have a look at execution plan but most likely if statistics are up to date, execution plan are good.

Next questions:
- what is a transaction?
- how Oracle lock resources?

Regards
Michel
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 Go to previous messageGo to next message
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 #432199 is a reply to message #432184] Sun, 22 November 2009 23:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Concepts
Chapter 4 Transaction Management
Chapter 13 Data Concurrency and Consistency

These are the two main chapters in this book about this question.
For developer you should also read (but whole book is worth it):
Chapter 5 Schema Objects
Chapter 18 Partitioned Tables and Indexes
Chapter 21 Data Integrity
Chapter 22 Triggers
Part IV Oracle Database Application Development

Regards
Michel

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 Go to previous messageGo to next message
ThomasG
Messages: 3211
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.
Re: What kind of pl sql questions to expect for a client technical interview [message #432519 is a reply to message #432199] Tue, 24 November 2009 12:41 Go to previous message
sqlstar_student
Messages: 42
Registered: November 2007
Member
Thanks a lot, Micheal and Thomas for your valuable suggestions and time.

I will definitely go though the links provided.

Regards
Previous Topic: 1z0-051 11G OCA
Next Topic: lost of certificate
Goto Forum:
  


Current Time: Thu Mar 28 05:38:53 CDT 2024