How to check explain plan of a nested table query?

Kinjan Bhavsar

Hi All,

I have a PL/SQL block in which I have used TYPE and I am querying a large table and using bulk collect storing the records into TYPE example:- tasks table so TASK_TYPE.

Then, after this, I am using this type in other queries using IN clause as the example shown below

select a1, a2 from table1 where task_id in (select t.task_id from TABLE(TASK_TYPE) t)

So, now in some cases, the query takes time and as it is a nested table, I can't see an explain plan. Is there a way I can find the explain plan of a query using nested table type? Is there a way, I can write some code in PL/SQL itself which stored the value in some table, etc?

I tried using a dummy table but that won't give me an exact idea of what exactly is happening in real time.

Thanks

Jonathan Lewis

Nice to know you got it working.

It showed me COLLECTION ITERATOR PICKLER FETCH which as far as I am aware is about the nested table.

Yes, that's basically Oracle treating a collection as a table.

There really are no advanced steps, although it helps to know what transformations the optimizer can do so that when the plan doesn't seem to match your query you can understand how Oracle has rewritten your query before optimising it. For example your "IN (subquery)" might show a plan with

Filter
  main table 
  table from subquery

or it might show a plan like

nested loop
  view  VW_NSO_1
    hash unique
      table from subquery
  main table
    index from main table 

The first is Oracle operating your subquery as a subquery, the second is Oracle unnesting the subquery to create a distinct list of values from the subquery, then using that to drive into the main table using a nested loop. (There are 3 or 4 more possibly shapes even for a query with such a simple shape.

The other key feature is that you need to know something about the volumes and patterns of data, and the effectiveness of the indexes that might get you from table to table. E.g. a typical customer has placed 100 orders in the last 5 years, on a typical day we get 2,000 orders. So if I want to see the orders for a specific customer on a specific data should I use the index on customer id to pick up 100 scattered rows (100 disc reads) and discard all (but one) or them, or should I use the index to pick up all the orders on a day and discard all but one of them - and does it make a difference whether the date is in the last week, or 6 months ago.

Imagine putting all the data in a set of filing cabinets, and having to open a drawer and take out one sheet of paper every time your query visits a table. How would you avoid opening drawers?

The other points are:

a) don't trust the cost - it's an estimate and can be badly fooled by caching and other optimisation tricks.

b) the SQL Monitor (or Rowsource Execution Statistics if you're not licenced for ASH and AWR) give you a good way to see where in the plan the time went, and to compare actual volumes of data against predicted volumes.

Regards

Jonathan Lewis

jflack

You might want to try changing:

WHERE task_id in (SELECT t.task_id FROM TABLE(task_type) t)

to:

WHERE task_id MEMBER OF task_type

The nested table operators are very powerful, and don't require a translation to a relational table. But if you need to see an explain plan - put the query in SQL Developer.

P.S. this is more a question for the SQL & PL/SQL community.

Kinjan Bhavsar

Hi jflack,

I tried SQL Developer but it gives me an error regarding nested table.

Also, is it showing in different category, I want to ask this question to SQL and PL/SQL community. Can I change the discussion group or do I need to repost it?

User_H3J7U

https://community.oracle.com/tech/developers/discussion/comment/16802819#Comment_16802819

MEMBER OF is much slower than IN (SELECT from TABLE()).

Secondly, it is not applicable to a single attribute of a objects collection.

Jonathan Lewis

So, now in some cases, the query takes time and as it is a nested table, I can't see an explain plan.

Do you mean that you can't get an execution plan because your query is embedded in a PL/SQL block? Or are you having a problem because the thing named "TASK_TYPE" in your one-line example is actually a PL/SQL variable of a table type that you have declared in your schema and you haven't found a way of describing it to SQL Developer when you try to use auotrace on a standalone copy of the query?

General principle of getting an execution plan (from SQL Developer and similar tools) in circumstances where you want to know what really happened.

1) Find some text in the first 1,000 characters of the query that will definitely identify the query. A simple trick is to embed as a "hint" some identifying information, e.g.

select /*+ {any genuine hints} HELPHELPHELP */ * from ....

2) Use the tool to query v$sql to find any statements with that embedded text

select sql_id, child_number, last_active_time, sql_text 
from v$sql 
where sql_text like '%HELPHELPHELP%'
and sql_text not like '%v$sql%'

3) Execute a query against table(dbms_xplan.display_cursor()) using the relevant SQL_ID that you will probably have found in step 2. Note that there may be multiple child plans, so you may need to check the last_active_time (format to include both date and time) to pick the right child. (If you don't specify a child number in the call, the default is child 0 - and explicitly supplying NULL will report them all.)

select * from table(dbms_xplan.display_cursor('somesqlid',null));

Regards

Jonathan Lewis

Jonathan Lewis

@jflack

WHERE task_id MEMBER OF task_type

Further to the comments by @User_H3J7U

Even if the list of columns in the relational table being queried were an exact match for the columns of the object type underlying the table table of task_type, the need to cast the columns into the object type would limit the optimizer, e.g.

select  *
from    t1
where   my_typ(object_id, rn) member of my_typ_tbl(my_typ(1,1),my_typ(100,100))
;

The only possible execution paths for this query would require Oracle to consider every row in the t1 table, casting to my_typ, then operation the "member of" functionality.

On the other hand, the original "in subquery" approach would allow Oracle to unnest the subquery, convert once, then do a join between two relation data sets - perhaps by index-driven nested loop if the (object) table were small enough.

Regards

Jonathan Lewis

Kinjan Bhavsar

@Jonathan Lewis

Do you mean that you can't get an execution plan because your query is embedded in a PL/SQL block?

• No in this case, but I would like to know the process

Are you having a problem because the thing named "TASK_TYPE" in your one-line example is actually a PL/SQL variable of a table type that you have declared in your schema and you haven't found a way of describing it to SQL Developer when you try to use auto trace on a standalone copy of the query?

• Yes, I am trying this same process but I tried by storing the records which are getting bulk collected in the TASK_TYPE in a dummy table but in that cost is appearing different which is shared by my DBA.

Jonathan Lewis

The drawback to the dummy table approach is that Oracle may have a good idea of how many rows there are in a real table while it's assuming there are 8,168 (if you're using a default 8KB biock size in the database) in the in-memory table.

Practically speaking this is only a variation of the standard development/production problem that if the development data looks different from the production data then the same query can produce different execution plans - it's just that it's a little harder to fake a better approximation of the production setup.

You can use the method I've outlined to find the plans you need to see. Since you've got a table with representative data in it you could write a test harness that loads the data into the variable then executes a version of the query with an added identifier.

The plan you get might not match the plan the DBA supplies - but there could be many reasons for this, and the DBA should be working with you to identify why the difference can appear.

You said at the outset:

So, now in some cases, the query takes time a

Does this mean that the same query takes a lot more time than others, or is it that different queries using this strategy take widely varying amounts of time.

If it's the former then the DBA needs to supply you with production execution plans for good times and bad times, and it's the DBA's job to explain to you what the change in plan indicates.

If it's the latter then there's no automatic reason why a strategy you use for table X should work as well for table Y - the plan and performance are down to data volumes and patterns (and optimizer errors).

Regards

Jonathan Lewis

Kinjan Bhavsar

Thanks.

I tried your approach and I can see the plan of SQL by finding it using sqltext and then using the same SQLid to find the execution plan.

Since you've got a table with representative data in it you could write a test harness that loads the data into the variable then executes a version of the query with an added identifier.

• Apologies I didn't understand this point, are you saying that I can bulk collect the data into TYPE and use your approach to find the correct execution plan? If yes, I tried the same and it worked.
• It showed me COLLECTION ITERATOR PICKLER FETCH which as far as I am aware is about the nested table.

Also, if you don't mind can I ask you few more questions? What are some advanced steps which a developer can follow to check in the detail about the execution plan and improve the performance? Indexes, data verification, re-writing queries in a different way to improve the cost and execution time are some of the steps I follow but are there any more steps that are worth trying?

Jonathan Lewis

Nice to know you got it working.

It showed me COLLECTION ITERATOR PICKLER FETCH which as far as I am aware is about the nested table.

Yes, that's basically Oracle treating a collection as a table.

There really are no advanced steps, although it helps to know what transformations the optimizer can do so that when the plan doesn't seem to match your query you can understand how Oracle has rewritten your query before optimising it. For example your "IN (subquery)" might show a plan with

Filter
  main table 
  table from subquery

or it might show a plan like

nested loop
  view  VW_NSO_1
    hash unique
      table from subquery
  main table
    index from main table 

The first is Oracle operating your subquery as a subquery, the second is Oracle unnesting the subquery to create a distinct list of values from the subquery, then using that to drive into the main table using a nested loop. (There are 3 or 4 more possibly shapes even for a query with such a simple shape.

The other key feature is that you need to know something about the volumes and patterns of data, and the effectiveness of the indexes that might get you from table to table. E.g. a typical customer has placed 100 orders in the last 5 years, on a typical day we get 2,000 orders. So if I want to see the orders for a specific customer on a specific data should I use the index on customer id to pick up 100 scattered rows (100 disc reads) and discard all (but one) or them, or should I use the index to pick up all the orders on a day and discard all but one of them - and does it make a difference whether the date is in the last week, or 6 months ago.

Imagine putting all the data in a set of filing cabinets, and having to open a drawer and take out one sheet of paper every time your query visits a table. How would you avoid opening drawers?

The other points are:

a) don't trust the cost - it's an estimate and can be badly fooled by caching and other optimisation tricks.

b) the SQL Monitor (or Rowsource Execution Statistics if you're not licenced for ASH and AWR) give you a good way to see where in the plan the time went, and to compare actual volumes of data against predicted volumes.

Regards

Jonathan Lewis

Kinjan Bhavsar

Thanks @Jonathan Lewis

I will keep a note of all these points and keep using them whenever I face a performance issue.