This page needs a newer browser to run its tools. You can still read everything here.

PL/SQL & T-SQL (Oracle and SQL Server) Module 14

Performance: plans, PL/SQL called from SQL and table functions interview questions

Interview questions on “Performance: plans, PL/SQL called from SQL and table functions”.

11 questions: 3 warm-ups and 8 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

Fresher to mid

How do DBMS_XPLAN.DISPLAY and DISPLAY_CURSOR differ, and when do you use each?

Answered in the lesson Oracle execution plans with DBMS_XPLAN
Fresher to mid

Why is a PL/SQL function called from SQL slower than the same logic written in SQL?

Answered in the lesson PL/SQL functions in SQL: avoiding the slowdown
Fresher to mid

How does a pipelined table function differ from a table function that returns a whole collection?

Answered in the lesson Pipelined table functions and SQL macros

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

Mid to senior

A nightly job that read about 10,000 rows of a claims table now takes 45 minutes instead of 2. DISPLAY_CURSOR with ALLSTATS LAST shows E-Rows 100 and A-Rows 150,000 on the step that filters claims by region, under a nested loops join. Walk through how you would diagnose and fix it.
What it tests

Reading the E-Rows and A-Rows gap as the root symptom, checking statistics age and histograms in the dictionary, and choosing between new statistics, a histogram and a pinned plan, with the licensing of each.

First step

The estimate is 1,500 times too low, so the join method was chosen for 100 rows. Check when the claims table was last analyzed and whether its statistics are marked stale (USER_TAB_STATISTICS), then whether the region column has a histogram (USER_TAB_COL_STATISTICS) and how many rows each region really holds.
Follow-ups
- The statistics were gathered last night and the histogram exists. What next?
- How would you keep a good plan without changing the job's SQL?
- What would you monitor so the next regression is caught early?
From the lesson Oracle execution plans with DBMS_XPLAN
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I have a licence key
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

An application runs one query with a bind on ticket status. For 'open' (500 rows) it takes 10 ms; for 'closed' (2 million rows) 90 seconds. V$SQL shows one child cursor with IS_BIND_SENSITIVE = N after thousands of runs. Explain what is happening and what you would change.
What it tests

Linking bind peeking to plan reuse, knowing why a cursor never becomes bind-sensitive, and weighing histograms, SQL plan baselines and separate statements against each other.

First step

One child and IS_BIND_SENSITIVE = N mean adaptive cursor sharing never started: the cursor is not considered sensitive to its bind. The first suspect is a missing histogram on status, because without one every value gets the same estimate and no bind value could lead to a different plan. Check USER_TAB_COL_STATISTICS.
Follow-ups
- Once a histogram exists, what sequence do you expect to see in V$SQL?
- Would CURSOR_SHARING = FORCE help here?
- When would you write two statements instead of relying on adaptive cursor sharing?
From the lesson Oracle execution plans with DBMS_XPLAN
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A nightly settlement report over 3 million card payments selects fx_rate(currency, paid_on) and fee_for(merchant_id) for every row and filters with WHERE fee_for(merchant_id) > 0. Since volumes tripled it misses its window. Walk through how you would decide which fixes apply, and in what order.
What it tests

Counting calls from where each function sits, using how often arguments repeat to pick between filtering, caching and rewriting, and proving each step with plans and timings.

First step

Count the calls before changing anything: two functions in the select list for each returned row, and fee_for again in WHERE for every row the filter tests, so up to 9 million calls a night. Then ask how many distinct arguments each function sees: a night's payments hold a few dozen currencies but thousands of merchants.
Follow-ups
- What would you change first, and what number tells you it worked?
- When would RESULT_CACHE on fx_rate be wrong?
- How do you show the report's result did not change?
From the lesson PL/SQL functions in SQL: avoiding the slowdown
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

To speed up reports, a team turned format_amount(p_amt) into a scalar SQL macro. The reports got faster, but the next morning a PL/SQL batch that builds statement lines with v_line := format_amount(v_amt) wrote files full of SQL text. Explain what happened and how you would serve both kinds of caller.
What it tests

Knowing that a macro is expanded only by SQL, separating SQL and PL/SQL callers, and weighing a macro against the SQL Transpiler and tests that catch the change.

First step

The batch is the clue: PL/SQL never expands a macro, it runs the body and receives the string the body returns, which is the expression's text. Every PL/SQL caller of format_amount now gets that text instead of a formatted amount. Find every caller, SQL and PL/SQL, before choosing the fix.
Follow-ups
- How do you find every PL/SQL caller of the function?
- Would the SQL Transpiler have avoided the incident?
- What test would have caught it before release?
From the lesson PL/SQL functions in SQL: avoiding the slowdown
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

A result-cached function due_label(p_due DATE) RETURN VARCHAR2 returns TO_CHAR(p_due) with no format mask. Customers served by one regional application sometimes see due dates in the other region's format. Explain the cause, fix it, and say when RESULT_CACHE is the wrong tool for a function like this.
What it tests

Seeing that a cached result depends on a session setting the cache key ignores, the three documented fixes, and the conditions under which a result cache helps or hurts.

First step

The result depends on NLS_DATE_FORMAT, a session setting, while the cache is shared by every session and keyed only by the argument. Whichever session computes a date first decides the format every other session reads until the result is invalidated or aged out. Confirm by checking the NLS settings of the two applications.
Follow-ups
- Which of the fixes would you choose, and why?
- Why did the bug show up only sometimes?
- What would make you drop RESULT_CACHE from this function entirely?
From the lesson PL/SQL functions in SQL: avoiding the slowdown
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A billing report must turn each of about 2 million monthly invoices into tax lines: one CGST and one SGST line for a sale within a state, one IGST line for a sale across states. A colleague proposes a pipelined function that reads the invoices through a REF CURSOR. Would you build it that way? Defend your choice.
What it tests

Deciding by where the rows come from, knowing what the optimizer can and cannot see through a pipelined function or a table SQL macro, and weighing per-row PL/SQL against a query.

First step

Notice that every value already sits in the invoices table: the lines are a fixed split of each row, not new data. That points to a query, a view or a table SQL macro that joins each invoice to a small rule set, rather than PL/SQL that fetches and pipes 2 million rows. Then check what the report filters on, because only a query the optimizer can see lets it use indexes and partitions.
Follow-ups
- What if each line needs a rate from a rules table that changes during the year?
- How would you show that the macro version reads fewer rows than the pipelined one?
- When would a pipelined function still be the right tool here?
From the lesson Pipelined table functions and SQL macros
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

A package declares a cursor over a large table; its pipelined function opens the cursor, pipes transformed rows and closes it after the loop. Reports read the first 20 rows. The second report in a session fails with ORA-06511, cursor already open. Explain the cause, the fix and how you would prove it.
What it tests

Linking a short read to NO_DATA_NEEDED, knowing that a package cursor outlives the call while a CURSOR expression closes with the function, and proving the fix with a short read repeated in one session.

First step

A 20-row read stops the function early: once the report has its rows, PIPE ROW raises NO_DATA_NEEDED, so the CLOSE after the loop never runs. The cursor belongs to the package, so it stays open in the session and the next OPEN raises ORA-06511. Add a NO_DATA_NEEDED handler that closes the cursor if it is open, placed before any OTHERS handler.
Follow-ups
- Why does the first report succeed while the second fails?
- Would passing the rows in as a CURSOR expression avoid the problem?
- How do you test the fix without a 20-row report?
From the lesson Pipelined table functions and SQL macros
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

Exports of about 40 tables must leave out every column whose name ends in _PHONE or _EMAIL, and new tables keep arriving. A teammate suggests one hand-written view per table. Would you use views, a table SQL macro or a polymorphic table function? Defend the choice and say what the export query must pass as literals.
What it tests

Seeing that the column set depends on the table, knowing how a describe-only polymorphic table function shapes its result while the query compiles, and handling the literal-argument and LIKE-wildcard details.

First step

The columns to drop differ per table and change as tables arrive, so the rule belongs where it can read each table's columns: a polymorphic table function whose DESCRIBE marks matching columns as not passed through. Views would mean 40 definitions kept by hand. Then settle the matching rule: the suffix must be a literal in the export query, and the underscore must be escaped in LIKE.
Follow-ups
- What happens if the export passes the suffix in a bind variable?
- How do you keep a column such as SALES_PHONE_COUNT?
- What would you test before the export goes live?
From the lesson Pipelined table functions and SQL macros
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes

All PL/SQL & T-SQL (Oracle and SQL Server) interview questions · The PL/SQL & T-SQL (Oracle and SQL Server) track

Warm-up questions

How do DBMS_XPLAN.DISPLAY and DISPLAY_CURSOR differ, and when do you use each?

Why is a PL/SQL function called from SQL slower than the same logic written in SQL?

How does a pipelined table function differ from a table function that returns a whole collection?

Questions with model answers

A nightly job that read about 10,000 rows of a claims table now takes 45 minutes instead of 2. DISPLAY_CURSOR with ALLSTATS LAST shows E-Rows 100 and A-Rows 150,000 on the step that filters claims by region, under a nested loops join. Walk through how you would diagnose and fix it.

An application runs one query with a bind on ticket status. For 'open' (500 rows) it takes 10 ms; for 'closed' (2 million rows) 90 seconds. V$SQL shows one child cursor with IS_BIND_SENSITIVE = N after thousands of runs. Explain what is happening and what you would change.

A nightly settlement report over 3 million card payments selects fx_rate(currency, paid_on) and fee_for(merchant_id) for every row and filters with WHERE fee_for(merchant_id) > 0. Since volumes tripled it misses its window. Walk through how you would decide which fixes apply, and in what order.