I wonder what's so hard for you to post the query and execution plan?
Printable View
I wonder what's so hard for you to post the query and execution plan?
pando,
the execution plan is so big b/c they trace for 4 days so that the reason why I don't post it on here. I copied The same SQL statement that had a huge a mount of parse and run on SQlplus and it took less than a min.
I am very confused.
there was one huge elasped time in this report so I ran another report with the sort=prsela. I am expeting the see that statement on the of the report; however, it's not and it's show almost the end of the report even the report was showing elapsed time in order DESC.
can someone explain what's going on here???
thanks million
What has 4 days to do with a big execution plan? How big is the execution plan? 100 lines? How about post the query then?
So why were there 585 parses in the last tkprof analysis? Can't you trace just that one statement? And how about posting the query & plan as pando requested.Quote:
Originally posted by learning_bee
The same SQL statement that had a huge a mount of parse and run on SQlplus and it took less than a min.
P.S. the code tags are [ C O D E ] and [ / C O D E ] without the spaces.
below is the execution plan for the query that had huge amount of elapsed time:
Code:
select VN.ACCOUNT_NO.*, VN.ACCOUNT_NO.ROWID
from
VN.ACCOUNT_NO where 1= -1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.233854739097.23 0 98 0 0
Execute 2 0.01 0.18 0 0 0 0
Fetch 2 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.253854739097.41 0 98 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20
Rows Row Source Operation
------- ---------------------------------------------------
0 FILTER
0 TABLE ACCESS FULL ACCOUNT_NO
there is no record on this table, the part I don't understand is why the is so big, when you calculate that time it's more than a year so I think it conflict
Yes -- the query did not take more than a year. So how long did it take?Quote:
there is no record on this table, the part I don't understand is why the is so big, when you calculate that time it's more than a year so I think it conflict
this is only one of the query in the whole trace and the job finish less than a week. other query looks ok to me.
I am really confused about this elapsed time.
what also bug was when I used TKPROF to sort=prsela, the huge elasped time was supposed to be on top of the list but it's not, it's almost to the end of the file whereas every other query are in order (desc)
I tried a similar query - nothing strange about it. But it did produce {SID}P000.trc & {SID}P001.trc - these were from the parallel queries! (it planned a FTS, so it split it into 2 parallel - logical but stupid!)Quote:
Originally posted by learning_bee
Also she mentioned about couple trace file that were start with p and s so I guess it had something to do with pmon and smon. Also there were errors on one of those s file: ora-00604.
Not as confused as we are, only hearing half the story.Quote:
Originally posted by learning_bee
this is only one of the query in the whole trace and the job finish less than a week. other query looks ok to me.
I am really confused about this elapsed time.
You have two queries in this job:
(a) select . . . where 1=-1
(b) something else
is that right?
Do you expect query (b) to run for almost a week? (! ! ! )
Is it conceivable that query (a) was really quite fast?
(My guess is that tkprof has a bug when run against trace files that span a big time-frame - i.e. several days)
(This begs the question of WHY you have "select . . . where 1=-1" in the job at all :confused: )