Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Shah.

Asked: December 01, 2016 - 5:37 am UTC

Last updated: December 02, 2016 - 2:40 pm UTC

Version: 10g

Viewed 1000+ times

You Asked

Hi,
I have a DW activity.

Steps include.
Drop index on Table A
Truncate Table A.
Load table A with millions of records.
Create indexes back on table A.

Drop index on Table B
Truncate Table B.
Load table B with millions of records.
Create indexes back on table B.

Insert into Table X by joining TabA and TabB.

The result.
The first day
Tab A : 19 million
Tab B : 10 million
query and insert into Tab X load activity completed in around 15 mins.

The second day
Tab A : 13 million
Tab B : 9 million
the Tab X load took 5 hours.

Issue I doubt:
What I doubt is the plan changed and the query on tab A Tab B was taking time to execute.

Can I solve this by gather stats on table A and B?
If so, should I gather stats on table A an B
after I load the table
or
after I create the index
respectively.

what is the best approach .

Regards,
Shah


and Chris said...

Why do you doubt the plan changed? It's one of the most likely explanations for such a large time difference.

Will gathering stats fix the problem? Maybe. It depends upon what the issue is!

Oracle Database automatically gathers stats on indexes when you create them. So you could gather stats after the loading but before creating the indexes. Personally I'd wait until after you've done all processing on the table before gathering stats though.

Before you get carried away though, try to figure out exactly why the second run took so much longer. If you have AWR or statspack data available, check that to see what the difference between the executions was. This will clue you into the real problem and help you figure out what do to.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Shah ., December 01, 2016 - 8:09 pm UTC

Thanks Chris,

Well I took the same piece of data into another environment.. ran the same steps... it came out in around 15 mins...


Connor McDonald
December 02, 2016 - 2:37 am UTC

Glad we could help

Shah ., December 01, 2016 - 8:09 pm UTC

Thanks Chris,

Well I took the same piece of data into another environment.. ran the same steps... it came out in around 15 mins...


Shah ., December 01, 2016 - 8:09 pm UTC

Thanks Chris,

Well I took the same piece of data into another environment.. ran the same steps... it came out in around 15 mins...


investigate on sql qry

A reader, December 02, 2016 - 8:51 am UTC

"If you have AWR or statspack data available, check that to see what the difference between the executions was. This will clue you into the real problem and help you figure out what do to. "

i have a qry (merge into GTT using a select) :
how look at tre AWR report to find out the reason of slowness ?

from the report :

CPU Time (s) : 1,300.10
Executions : 2
UIO per Exec (s) : 2,977.82
CPU per Exec (s) : 650.05
%Total : 8.67
Elapsed Time (s) : 9,365.86
%CPU : 13.88
%IO : 63.59
SQL Id : 8urny8p02u49a
Gets per Exec : 82,103,589.50
%Total : 5.86
Reads per Exec : 79,046,315.00
UnOptimized Read Reqs : 11,420,196
Physical Read Reqs : 11,420,196
Chris Saxon
December 02, 2016 - 9:20 am UTC

The SQL stats sections will show you the difference in work the queries did in the two periods.

You can get the past plans using dbms_xplan.display_awr:

https://uhesse.com/2010/03/26/retrieve-sql-and-execution-plan-from-awr-snapshots/

so?

A reader, December 02, 2016 - 10:26 am UTC

Tkx chris for replying
i get the plans and see the link

but... what tells me WHY the execution has taken 2 hours instead of minutes before ?

Chris Saxon
December 02, 2016 - 2:40 pm UTC

If you find you have two different execution plans, then that's almost certainly your "why".

If it's the same plan for both runs you need to do some investigating. e.g. Was it waiting on something one time but not the other? Did it do lots more physical IO? Was IO much slower for some reason?

You need to compare the periods to find out what's different.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.