Wednesday, July 13, 2005
I can't let you do that Dave
I was looking at speeding up a particular set of Create Table scripts today. This isn't something I'd normally bother much with - you create the tables once and that is it. However this script may be run repeatedly in development and so the developers wanted me to take a quick look see what you can do?
I fired up EM Console and took a look at the session information dialog, primarily to see how long certain operations were taking via the nice long operations tab. Whilst I was there I took a peek at the SQL tab (to see if the current statement was the one I thought it was). I was greeted with the dialog below
It's that last sentence I have problems with. One of my CREATE TABLE statements takes 25 minutes out of an elapsed time of 31 minutes. I absolutely do want to focus my tuning efforts on that statement. Now I can't use an explain plan, though I can pull the select out of the CTAS and look at that, but saying that I should look at some other process entirely because a particular tuning tool won't work for that statement is just daft.
10 Comments
I fired up EM Console and took a look at the session information dialog, primarily to see how long certain operations were taking via the nice long operations tab. Whilst I was there I took a peek at the SQL tab (to see if the current statement was the one I thought it was). I was greeted with the dialog below
It's that last sentence I have problems with. One of my CREATE TABLE statements takes 25 minutes out of an elapsed time of 31 minutes. I absolutely do want to focus my tuning efforts on that statement. Now I can't use an explain plan, though I can pull the select out of the CTAS and look at that, but saying that I should look at some other process entirely because a particular tuning tool won't work for that statement is just daft.
10 Comments:
I screwed up. Can't operate the blogger add picture dialog properly. Its fixed now - though you'll probably have to refresh the page.
But Niall, you can get explain plans for CREATEs
ops$tkyte-ORA9IR2> @plan "create table tt as select * from scott.emp"
0 rows deleted.
old 1: explain plan for &1
new 1: explain plan for create table tt as select * from scott.emp
Explained.
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | |
| 1 | LOAD AS SELECT | | | | |
| 2 | TABLE ACCESS FULL | EMP | | | |
----------------------------------------------------------------------
Note: rule based optimization
10 rows selected.
an alter -- perhaps not, but a create you should
ops$tkyte-ORA9IR2> @plan "create table tt as select * from scott.emp"
0 rows deleted.
old 1: explain plan for &1
new 1: explain plan for create table tt as select * from scott.emp
Explained.
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | |
| 1 | LOAD AS SELECT | | | | |
| 2 | TABLE ACCESS FULL | EMP | | | |
----------------------------------------------------------------------
Note: rule based optimization
10 rows selected.
an alter -- perhaps not, but a create you should
so its just all wrong then. excellent.
The alter was an example of the error rather than the particular issue. The issue went away with the old create table ... nologging... as select
The alter was an example of the error rather than the particular issue. The issue went away with the old create table ... nologging... as select
Niall said
The issue went away with the old create table ... nologging... as select
spoke too soon. Now they have got something that completes quicker they've worked out the results are wrong...
The issue went away with the old create table ... nologging... as select
spoke too soon. Now they have got something that completes quicker they've worked out the results are wrong...
I must be missing some point. The create table/load select part of the explain plan just says you are creating the table as select. All you are interested in is the select part, right? And you can do that without the cool trick. Subject to the veracity of explain plan, of course. So what's daft about the advice about focusing your tuning efforts?
As far as OEM originating myths, I have a feeling it merely codifies existing ones (as I would expect the coders would be trying to automate existing "knowledge"), I would be curious as to which anyone thinks originated there. To me, OEM is the "killer app" that broke me of my total command line bias. I can just drill down to a currently running statement and get the plan. How cool is that?
On my home browser with its admittedly odd display settings, your dialog display overwrites the right column (and doesn't display at all in "show original post" in the comments section).
As far as OEM originating myths, I have a feeling it merely codifies existing ones (as I would expect the coders would be trying to automate existing "knowledge"), I would be curious as to which anyone thinks originated there. To me, OEM is the "killer app" that broke me of my total command line bias. I can just drill down to a currently running statement and get the plan. How cool is that?
On my home browser with its admittedly odd display settings, your dialog display overwrites the right column (and doesn't display at all in "show original post" in the comments section).
Hi Joel
the point is that OEM 9I doesn't give you the plan for the create at all.
Had Tom's create statement been run and then OEM used you wouldn't have got the plan at all.
the point is that OEM 9I doesn't give you the plan for the create at all.
Had Tom's create statement been run and then OEM used you wouldn't have got the plan at all.
Had Tom's create statement been run and then OEM used you wouldn't have got the plan at all.
Oh, I see what I'm missing, if you run the statement and while it's running drill down to it and get that message from OEM, that would be dumb, because you'd have to explain the select separately and OEM should be bright enough to figure out a CTAS does have a select.
Post a Comment
Oh, I see what I'm missing, if you run the statement and while it's running drill down to it and get that message from OEM, that would be dumb, because you'd have to explain the select separately and OEM should be bright enough to figure out a CTAS does have a select.
