Re: Query with LONG RAW
Date: Wed, 3 Mar 2010 02:44:47 -0800 (PST)
Message-ID: <365c19dd-7ab5-4c5a-9a96-5ace2a054d01_at_c16g2000yqd.googlegroups.com>
On Mar 2, 9:46 pm, "bob123" <bob..._at_gmail.com> wrote:
> How can I speed up the query below (third party software)
> it takes 30 min to execute.
> Oracle 9.2.0.6
> LONG RAW on PFMQ_MESSAGEDATASTORAGE
I think Charles is right, there is no sign of row chaining - each table row is only causing a single consistent get, which also means you don't have an issue with applying undo due to concurrent modifications.
Your TKPROF output however shows several oddities:
- The total elapsed time is not consistent with what is reported in the "Row Source Operation", and in particular the "Row Source Operations" below the top SORT ORDER BY operation show a greater elapsed time that corresponds to almost 29 minutes (1729 seconds).
- So it looks like that the 29 minutes are actually spent on executing the statement rather than in transporting any huge results (potentially inefficiently) to the client
- Another oddity is that most of your time is spent on physical reads of the index blocks - usually it is the other way around when random table access by ROWID is involved since the random block access to table rows usually takes up most of the time whereas the index blocks are cached very well
- It is also obvious from the timings that your physical block reads on the index segments are slower than on the table segments - the average time for a single index block read seems to be approx. 13 ms, whereas a table block is read on average in between 3 and 5 ms. Are your indexes located in a different tablespace that is on slower disks or that is more busy than the tablespaces with the table blocks?
- How large are your indexes / how many rows are covered by the indexes and how large is your buffer cache used by those indexes? In particular the XPKPF_MESSAGEDATA and XPKPF_MESSAGEDATASTORAGE indexes require each 46,000 physical reads for 130,000 consistent gets / approx. 64,000 accesses (which corresponds to an index height of 2 and therefore 2 consistent gets per access). An index of height 2 with more than physical 46,000 block reads either means that you have a far too small buffer cache so that the same blocks need to be physically read over and over again and age out immediately or your index is simply that huge so that many keys accessed reside in a different block.
You potentially have an issue with (some of) your indexes - if you have a chance for an offline period (or do have a mirror copy of the database) you could run an ANALYZE INDEX ... VALIDATE STRUCTURE and report the results from the INDEX_STATS view. Note that this requires an exclusive lock on the index segment and therefore can not be done online.
Some more observations:
- The query contains a superfluous join condition: "AND md.ID = mds.ID" which is not required and might lead to an underestimate of the cardinality. This in turn could favor the nested loop / index access path chosen by the optimizer
- The aggregate subquery (SELECT MAX()...) doesn't contribute significantly to the execution time and therefore at present seems to be negligible
Given these facts and the large number of physical block reads that you need to perform on the index segments the statement might perform better if you performed full table scans through the tables using multi-block I/O requests. Of course this depends on how large the table segments are, and the full table scan performance (size of multiblock I/O). Since the table blocks seem to get served quicker than the index blocks, this might turn out to complete faster even if the segments to read are significantly larger in size than the number of block read at present.
However the picture might change if you perform more full table scans - and this might also impact the execution of other concurrent activities.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Wed Mar 03 2010 - 04:44:47 CST