We’ve been struggling with access to the V$DATAFILE view throughout the development of a recent warehouse project. It would appear it’s because we’ve got rather a large number of datafiles making up our database – of the order of 10,000 and this is having an effect on the underlying query against the X$ tables.
I posted a while back on the benefits of having a higher granularity of partitions to tablespaces to datafiles…but this issue seems to fall under that of “Cons”.
The problem appears when doing a query like:
select * from v$datafile where file#=29
/
Which then, sometimes, gives the error:
ERROR:
ORA-00235: controlfile fixed table inconsistent due to concurrent update
I guess that’s because with having so many files involved in the database the query takes quite a while to run and that during the process, the controlfile changes and therefore the query doesn’t believe it will get a consistent set of results…so it bombs out with the error.
So, why does it take so long in the first place ? Even if we have 10,000 files that’s still not an enormous number and surely shouldn’t take 10’s of seconds to query.
If we use explain plan we can see the expected execution plan looks like this:
--------------------------------------------------------
Id Operation Name
--------------------------------------------------------
0 SELECT STATEMENT
1 CONCATENATION
2 MERGE JOIN
3 MERGE JOIN
4 SORT JOIN
5 MERGE JOIN
6 SORT JOIN
7 FIXED TABLE FULL X$KCVFH
* 8 SORT JOIN
* 9 FIXED TABLE FULL X$KCCFN
* 10 SORT JOIN
* 11 FIXED TABLE FIXED INDEX X$KCCFE (ind:1)
* 12 SORT JOIN
* 13 FIXED TABLE FULL X$KCCFN
14 MERGE JOIN
15 SORT JOIN
16 MERGE JOIN
17 SORT JOIN
18 MERGE JOIN
19 SORT JOIN
20 FIXED TABLE FULL X$KCVFH
* 21 SORT JOIN
* 22 FIXED TABLE FULL X$KCCFN
* 23 SORT JOIN
* 24 FIXED TABLE FIXED INDEX X$KCCFE (ind:1)
* 25 FILTER
* 26 SORT JOIN
27 FIXED TABLE FULL X$KCCFN
--------------------------------------------------------
We can see that this plan involves numerous sort / merge joins but we can’t see anything indicating the Rows retrieved at each operation in the execution plan, nor the Bytes, Cost or Time values for the operations.
I’m a little confused at not being able to see Rows/Bytes/Cost/Time in the plan especially if I do another query against V$SESSION I can see such information:
---------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 1065 0 (0) 00:00:01
1 NESTED LOOPS 1 1065 0 (0) 00:00:01
2 FIXED TABLE FULL X$KSUSE 1 856 0 (0) 00:00:01
3 FIXED TABLE FIXED INDEX X$KSLED (ind:2) 1 209 0 (0) 00:00:01
I wondered whether it was because the stats hadn’t been gathered for some of the fixed objects (X$ tables) so after reading this (metalink login required) I issued the command :
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
…expecting to see the stats appear – they didn’t…so I’m still confused as to why I don’t see those important stats in some circumstances.
Anyway, looking at my sample query above we can all see the wonderful example of “lazyitis” in my SQL can’t we ? – the use of SELECT * instead of the actual columns I want. Trying the same again but only selecting FILE# and NAME columns gives a much quicker execution.
If we examine the trace files to look at the waits involved we see that the slow running “SELECT *” execution incurs many “db file sequential read” waits whilst the “SELECT file#,name” execution incurs none. On my 10gr2 on XP environment there were 17165 occurrences in the trace file with approximately 18K wait events in total – so a very large proportion of the excess time is down to this db file sequential reading…i.e. the sorts are much more costly when we select, unnecessarily, the whole row instead of just the columns we require.
Why was I trying to use V$DATAFILE ? Well, I was reading the Oak Table Insights book the other night and the chapter by Gaja Krishna Vaidyanatha on Compulsive Tuning Disorder which suggested the use of V$SESSION_WAIT_HISTORY to track the wait history by session when diagnosing performance problems. From that I took the query Gaja used and extended it a little to try and pinpoint the data or temp file which related to the wait event – where that wait event was for a block in a file – just to give a more complete picture. The treating form has emerged as djpaulkom.tv levitra without prescription a modern habit in young males to puffing several cigarettes a day. It could be harmful as it is more effective that the tablet and it is easily available out there. tadalafil india It improves stamina, power, libido cialis generic price and energy levels considerably. buy cialis from india In earlier time, driver’s ed was traditionally coached in normal high school classes in California.
The final query is here if you’re interested – it works for serial or parallel queries. As ever, suggestions/corrections are welcome.
Now that I look at it, there is another interesting aspect to the query. When I tried to join in to V$TEMPFILE to pick up the name for the TEMP file being waited on it wasn’t coming back wi
th anything. Further investigation revealed that the file# on the wait event was some massive number over 32K in size when we only have 50 temp files. I raised it with support who eventually came back with the knowledge that it’s not an absolute file number but a relative one which you need to subtract the value of the initialisation parameter “db_files” from. Once I factored that into the SQL it started to work fine.
I was suffering from “lazyitis” as above which was causing my query to take ages to return – changing it to only use the FILE# and NAME columns makes sure the query returns in around 30s even on a warehouse with over 10,000 files.
On another note, my colleague Anthony pointed this out to me the other day…made me laugh so thought I’d share it with you.