How to compare the performance after rewrite a view to a procedure

[huchen]

Hello,

I have a big view which has very complicated busness logic.
The cost of the execution plan(explain plan for) is very high.
If I rewrite this view to a stored procedure that calculates for each field and store the value to an interim table, I believe it will improve the performance dramatically.

My question is:
How do I compare the performance result?

Here is what I want:

Original:
explain plan for
select * from vw_big where id =11;
select plan_table_output from table(dbms_xplan.display());

after I change:
explain plan for
execute sp_get_vw_data(11); --this sp will insert to table tbl_1
select * from tbl_1
select plan_table_output from table(dbms_xplan.display());

I know this does not work because explain plan just work for query.
How can I get the cost from my new method?

Thank you very much

 

[SantaMufasa]

Huchen,

The way I compare different sets of code to do the same thing is:[ul][li]connect to an account in SQL*Plus that has access to all of the related objects.[/li][li]Issue the SQL*Plus command:

set timing on

[/li][li]Run version 1 of the code.[/li][li]Run version 2 of the code.[/li][li]Compare the timing results.[/li][/ul]Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[Dagon]

You could get plans for queries within the SP by turning trace on a session or database level. However, you can't get a plan for the SP itself because an SP would consist of lines of code (if then else, loops, assignments etc) that are not SQL statements.

 

[jimirvine]

If I rewrite this view to a stored procedure that calculates for each field and store the value to an interim table, I believe it will improve the performance dramatically.

Pretty unlikely. What makes you think that you can do it faster in PL/SQL than Oracle can do in SQL?

 

[Dagon]

You might want to consider a materialized view instead of an ordinary view. This would effectively crystalize the results from the view and store them in another table where it could be accessed without having to re-do all the SQL.

 

[huchen]

Thanks to everybody for your time.

Santa, "set timing on" helps. but if the data is already in cache, and I run my new method using same dataset, the result will not be accurate. What do you think?


Dagon, materialized view will not work for my situation because the data is dynamic for each transaction. We have hundreds of transactions at same time at peak time. That is why I want to tune it.

Jimirvine, There are many sub-query in the view and it queries same table handreds of times. A SP will only query a table one time and calculate each field. There will be less I/O. Somebody else wrote the view and it has very complicated business logic. I am not sure how much better my approach will be, that is why I ask for help here.

Thank you very much.

 

[jaxtell]

I'm confused. If the view has complicated business logic, won't your SP require it to retrieve the same result set? If you can simply the logic, why not just rewrite the view with your calculations for fields. As long as those fields aren't in a WHERE clause, it should perform ok. I'm assuming they won't be if you can get there with a stored procedure. Can you clear your cache to get more accurate timing results? Thats how I do it. Run query, flush sga, flush buffer cache, repeat.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/