Home » SQL & PL/SQL » SQL & PL/SQL » view create replace performance issue (OracleDB 10.2, RHEL4)
view create replace performance issue [message #439328] Fri, 15 January 2010 11:46 Go to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
We have a view on production where we issue a 'create or replace' statement three times daily. When the 'create or replace' is first issued, regardless of time of day, the execution takes about sixty seconds. Each successive time the 'create or replace' is issued, the execution takes two seconds.
The kicker is that each time we replace the view, it's with a different version of the table (which is created with the same code, data is aggregated throughout the day):
create table tableA as ...;
create or replace view data_view as select * from tableA;

This initial execution will take 60 seconds.

Subsequent executions will take 2 seconds:
create table tableB as ...;
create or replace view data_view as select * from tableB;

create table tableC as ...;
create or replace view data_view as select * from tableC;


Has anyone experienced something like this before? We're at a bit of a loss.
Thanks.
Re: view create replace performance issue [message #439329 is a reply to message #439328] Fri, 15 January 2010 12:12 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Are you really working on Production database?? I dont think so.
Why you want to do create or replace so many times? Would you please tell the reasons...

Regards,
Ved
Re: view create replace performance issue [message #439332 is a reply to message #439329] Fri, 15 January 2010 12:31 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
We create the table on our warehouse, ship it over to production, then initiate the view drop/create statement. The table has about 1 million rows. This is done 3 times daily. We need refreshed data to fulfill business requirements. Do you have any recommendations?
Again, the first time it's run, execution takes 60 seconds. Each time after that, each execution takes 2 seconds.
Re: view create replace performance issue [message #439335 is a reply to message #439328] Fri, 15 January 2010 12:41 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
We have a view on production where we issue a 'create or replace' statement three times daily.

Why do you do this?
Do you recreate, recompile, relink one your program in production three times each day?
Do you envisage to do this?
Views are part of your CODE.

Quote:
it's with a different version of the table (which is created with the same code, data is aggregated throughout the day):

Berk! I do not want to work at your site.
What a awful design!
Someone needs to be fire, for sure. And his boss with him to have allowed this.

When you use a hammer to screw it is not surprising you encounter some problems.
Use Oracle properly!

Regards
Michel
Re: view create replace performance issue [message #439338 is a reply to message #439335] Fri, 15 January 2010 13:00 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
So you have no constructive methods for diagnosing the issue? Why is the design so awful? We maintain the same view name, but replace the data in the underlying table. Code is not recompiled. This updated data is used to enable our application to make better decisions regarding our users.

Your insulting responses are useless and nonproductive.

Don't worry, we wouldn't hire you in the first place.
Re: view create replace performance issue [message #439340 is a reply to message #439338] Fri, 15 January 2010 13:18 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
We maintain the same view name, but replace the data in the underlying table. Code is not recompiled.

1/ You do not replace the data on the underlying table, you replace the table
2/ You COMPILE each create is a compilation
3/ You then invalidate ALL your dependents, procedures and so on
4/ You have to recompilate ALL these dependent objects

Do you see why it is awful?
It is like each time you want to use a program, you modify the source of a procedure, recreate a function, have to recompile them and to relink all programs that use them.

Quote:
Your insulting responses are useless and nonproductive.

Only the answers that do not contradict you are useful?
OK, go on with your design, it is not my problem.

Regards
Michel

Re: view create replace performance issue [message #439341 is a reply to message #439338] Fri, 15 January 2010 13:21 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The problem is, the way you do it is like having bought a car, then hitched 40 cats in front of it to pull it.

Of course you can try to make it work with the cats, but it would be simpler to just go ahead start the engine.

Two ideas what you could to to figure out why the initial create takes 60 seconds is :

1) query the database waits while it is running.
2) trace the session that is creating the view and analyze the trace file with tkprof.

Of course, some "normal" ways to to it would be either to use a materialized view instead of the table, or if you have to use a table for some reason use merge to update the data.
Re: view create replace performance issue [message #439343 is a reply to message #439340] Fri, 15 January 2010 13:26 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Nothing needs to be manually recompiled:
1. We have the view and the underlying table.
2. We create a table to replace the existing underlying table.
3. We issue a create or replace on the view.
4. The view is now pointing to the new table.

Contradictory comments are welcome. Insulting comments indicating someone should lose their lively hood and insinuations that we are so inept and beneath someone of your lofty stature are not.
You stay classy Michel Cadot.
Re: view create replace performance issue [message #439344 is a reply to message #439341] Fri, 15 January 2010 13:32 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Thanks for the response. We tried materialized views, but that took way longer than the 2 seconds it takes to switch the view over to a new table. What's confounding is the different performance for each view switch.
I'll investigate merge, but this 'awful' method has been working for us for awhile now.
Thanks.
Re: view create replace performance issue [message #439345 is a reply to message #439328] Fri, 15 January 2010 13:40 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
While Michel is being excessively blunt this:

Quote:

1/ You do not replace the data on the underlying table, you replace the table
2/ You COMPILE each create is a compilation
3/ You then invalidate ALL your dependents, procedures and so on
4/ You have to recompilate ALL these dependent objects


Sounds like a pretty accurate description based on what you've told us, and you're going to struggle to find an oracle expert who will tell you creating tables on the fly is anything other than a bad idea.
We've seen lots of people try stuff like this but I don't think any of us have yet seen example where not recreating objects at runtime didn't work better - both in terms of performance and code maintenance.

If you want to find out why it's running slow ThomasG has given two options, but I seriously suggest you look long and hard at changing your code structure here.
Re: view create replace performance issue [message #439346 is a reply to message #439345] Fri, 15 January 2010 13:44 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
A coherent, thoughtful reply. Thanks cookiemonster!
Re: view create replace performance issue [message #439349 is a reply to message #439346] Fri, 15 January 2010 13:54 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thank you.

Regards
Michel
Re: view create replace performance issue [message #439350 is a reply to message #439328] Fri, 15 January 2010 13:59 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you give us more details on the logic behind this view switching we may be able to give you some better ideas on replacements. At the moment there's too little information to make anything other than generalised guesses.
And are you sure it's not the table creation that's taking the time? Seeing as how you appear to do that and the view create as one operation.
Re: view create replace performance issue [message #439351 is a reply to message #439343] Fri, 15 January 2010 14:02 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>Nothing needs to be manually recompiled:
While the statement above is true, it totally ignores what happens within the DB out of sight of the initiator.
Objects do go invalid & need to recompiled or revalidated.
Doing DDL on a regular basis in a Production environment does not conform to any Best Practice that I know about.
In Best Practices DDL only occur during application upgrades.
It is relatively speaking EXPENSIVE to do DDL; when compared to DML.
Re: view create replace performance issue [message #439427 is a reply to message #439328] Sat, 16 January 2010 13:58 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
I can think of some reasons off hand why the first create of the day might take much longer.

1) as was already suggested, your time may be a result of data in the table you are creating. More data would mean more time to create it, less data would mean less time. If you are pulling/pushing from a remote table to a local one, then network time to move the data could be responsible.

2) recursive sql. The first time you do a create table or create view, you will be loading a lot of sql statements that oracle will do behind the scenes. This is recursive sql (sql that oracle executes on your behalf in order to do the statement you really want done). It takes time to get this sql into the cursor cache, validate it, compile it, etc. The second time you issue the create table/view in your process, all this will already be in the cache ready to go.

3) Continuing with the assumption that your create table pulls from a remote database, the first time you talk to this remote database, you must do a lot of network activities including creating a session on the remote system. This will take time as well. I could surmize similarly to #2, after you have done this once, much of the work will not have to be repeated for subsequent operations.

Still 60 seconds seems like along time.

You might wish to examine partition swap operations (known as EXCHANGE PARTITION clause of the ALTER TABLE statement) rather than view create as a means of moving your table across. Naturally this is a more sophisticated way of doing what you are doing and as such you will want to evaluate the differences between your current approach and this suggestion. It would allow you to only move the data without recreating the view.

alter table txyz
exchange partition p1 with table <sometable>
including indexes
without validation
/

BURLESON

oracle

other example

The basic idea of course is you create an independent table that is unseen, and then to make it seen, swap it in using exchange partition. No need to recreate your view since the view definition will never change. You will have to test for yourself if this is faster or slower or meets your timing needs.

Good luck, Kevin
Re: view create replace performance issue [message #439442 is a reply to message #439427] Sat, 16 January 2010 19:00 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I've been disappointed before by the treatment handed out by "experts" on this site, but never so much as in this post.

It is fairly clear to me that TLegend has a near-real-time data warehouse - one that is kept up to date during business hours. I have been working on a system with a similar problem for a while now and I appreciate the challenges.

There is a concept called Transactional Consistency that is a NIGHTMARE for a NRT Data Warehouse. i.e. If two rows form part of the same logical source system transaction, then they need to be committed in the DW in the same logical transaction. Since the DW has no understanding of the transaction management of the source system, it is simpler to do a big-bang update of data. Of course, this can be done with a MERGE or an ATOMIC REFRESH MV committing only once the entire table has been loaded, but then that eliminates direct path loads, disabling constraints, and rebuilding indexes as methods to improve the performance of a high volume load. This is very much the equivalent of the cat analogy because these techniques are the bread-and-butter (sorry about the mixed metaphor) of an ETL.

This really is a much thornier problem that you guys are giving credit.

Since the problem is only happening on the first execution of the day, I would venture to suggest that something that happens overnight is causing it. Do you do a cold backup overnight? Bounce the database? I did a quick test and found that when you do the switch, procedures that use the view stay compiled, but maybe that's Oracle being nice and recompiling them for me, however it does NOT recompile dependent objects if I drop then create the view. Maybe Oracle is very clever and is working out whether it needs to invalidate dependent objects. If so, it would need information about those objects in the SGA, which, if you had bounced the database overnight, would be empty. After the first exchange, the SGA would contain all of the dependent object information and Oracle's impact analysis would be faster. Still, you'd need a lot of dependent objects to account for 60sec.

Kevin supplied a good suggestion of using Partition Exchange, but that's not going to provide near-instantaneous loading if there are Global Indexes on the table.

The only other suggestion I can think of to achieve the appearance of instant loading is to have two databases and swap the connection credentials on your front end once the "down" database has been refreshed. This is a pretty extreme measure though, when I see NOTHING wrong with view-over-table-switcheroo.

The only reservation I have about your architecture is I don't know what would happen with open cursors. Would they continue to work? Would they block the View rebuild? I'd be interested to know the answer, but not so interested that I'm inspired to set up my own test case.

As to the original problem, do the trace as ThomasG suggested.

Even if you find the problem, you may not be able to fix it. Especially if it is a consequence of an overnight database bounce. I'll leave you with two alternative suggestions:

SUGGESTION 1: If you find from the trace that dependent objects are being recompiled in the background, shield your dependent objects with a second view:
CREATE TABLE t1 AS ....
CREATE VIEW v1 AS SELECT * FROM t1
CREATE VIEW vv1 AS SELECT * FROM v1

CREATE TABLE t2 AS ...
CREATE OR REPLACE v1 AS SELECT * FROM t2;

If all dependent objects are selecting from vv1 instead of v1, then vv1 should be the only object recompiled when v1 is rebuilt. ie. Invalidation/recompilation of objects - if it happens at all - should not chain.


SUGGESTION 2: Create your view to select from both tables and filter results based on a row in a switching table.
CREATE TABLE t1 AS ....
CREATE TABLE t2 AS ....
CREATE VIEW v1 AS
SELECT * FROM t1 WHERE 1 = (SELECT tnum FROM switch)
UNION
SELECT * FROM t2 WHERE 2 = (SELECT tnum FROM switch)


In this way, when the switch table contains 1, you can truncate and reload t2, and vice-versa. If you are using v10.2, you will also be able to muck about with indexes and constraints on T2. With 10.1 and earlier, this would affect the execution plan on T1 due to Partitioned Views optimisation.

Ross Leishman
Re: view create replace performance issue [message #439534 is a reply to message #439328] Mon, 18 January 2010 03:31 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Just for clarification, is it the case that the first time you execute any of these statements it takes 60 seconds, or is it that you always execute a specific set of statements first, and this set of statements is takeing longer.

The point I'm trying to get at is whether the time is being taken up by a specific statement, or whether all statements run slowly the first time.
Previous Topic: Difficulty in resolving Date ORA-12899: value too large for column (merged)
Next Topic: Tuning /index
Goto Forum:
  


Current Time: Thu Sep 29 09:02:10 CDT 2016

Total time taken to generate the page: 0.05545 seconds