Here’s a little quirk of execution plans that came up recently on the Oak Table network. If you call a function in a query, and do some sorting with the results, where does the work of calling the function get reported in the execution plan if you trace the query or look at the in-memory rowsource execution stats. Let’s take a look at a simple example:
create table t1 as select rownum id, lpad(rownum,200) padding from all_objects where rownum <= 2500 ; create table t2 as select * from t1 ; -- collect stats create or replace function f (i_target in number) return number as m_target number; begin select max(id) into m_target from t1 where id <= i_target; return m_target; end; /
Warning – this is a catch question, and I haven’t given you enough information to have any idea of the right answer; though, by telling you that I haven’t given you enough information to have any idea of the right answer, you now have some information that might help you to get closer to the right answer.
I have a simple heap table with no indexes. Immediately after flushing the buffer_cache I’ve run a query that looks ike this:
select max(column_ZZZ) from table_X;
The most significant session stats for this operation are as follows:
A recent (Jan 2013) post on the OTN database forum reported a performance problem on Oracle 220.127.116.11 (so no AWR), and posted a complete statspack report to one of the public file-sharing sites. It’s been some time since I did a quick run through the highlights of trouble-shooting with statspack, so I’ve picked out a few points from this one to comment on.
As usual, although this specific report is Statspack, the same analysis would have gone into looking at a more modern AWR report, although I will make a couple of comments at the end about the extra material that would have been available by default with the AWR report that would have helped us help the OP.
When doing updates with statements that use correlated subqueries, it’s important to make sure that your brackets (parentheses) are in the right place. Here are two statements that look fairly similar but have remarkably different results – and sometimes people don’t realise how different the statements are:
update t1 set padding = ( select t2.padding from t2 where t2.id = t1.id ) where t1.small_vc <= lpad(20,10,'0') ; update t1 set padding = ( select t2.padding from t2 where t2.id = t1.id and t1.small_vc <= lpad(20,10,'0') ) ;
Sometimes you find bugs on MOS (Metalink, OCIS, whatever) that make you feel positively ill. I’ve just been on a customer site where (in passing) they mentioned that one of their historic queries avainst v$sqlstats now tool just over one second (CPU) in 18.104.22.168 when it had previously taken about 200ms on 10.2.0.4***. After a little checking it seemed likely that the change was possibly related to the fact that they had increased the size of the SGA significantly, allowing for a much larger shared pool and library cache; however there have been numerous code changes in the shared pool area on the route from 10g to 11g, so I decided to check MOS to see if anyone else had seen a similar problem. I found this:
Bug 13550185 : QUERIES ON V$SQLSTATS MAY CAUSE CRASH OR HANG
Fixed in 12.1
I’ve given examples in the past of how you can be suprised by Oracle when a mechanism that has “always worked” in the past suddenly just doesn’t work because some unexpected internal coding mechanism hits a boundary condition. One example I gave of this was rebuilding indexes online – where a limitation on the key size of index organized tables made it impossible to do an online rebuild of an index on a heap table because of an ORA-01450 (maximum key length) error that was raised against the (index-organized) journal table that Oracle creates internally to support the rebuild.
A recent question on the OTN Database forum:
If the block size of the database is 8K and average row length is 2K and if we select all column of the table the I/O show that it had read more blocks then compare to specific column of the same table. Why is this?
Secondly if Oracle brings a complete block is the db buffer cache, while reading block from disk, they why there is a difference of block count in two queries. This difference reveals to me when I check the EXPLAIN PLAN for two different queries against the same table but one select all columns and the other one select specific column.
Kindly help me in clearing this confusion.
I can’t see anything subtle and complex in the problem as stated, so why doesn’t the OP give a clear explanation of what’s puzzling them?
I’ve been working on a lot of good schtuff lately on the area of capacity planning. And I’ve greatly improved my time to generate workload characterization visualization and analysis using my AWR scripts which I enhanced to fit on the analytics tool that I’ve been using.. and that is Tableau.
So I’ve got a couple of performance and capacity planning use case scenarios which I will blog in parts in the next few days or weeks. But before that I need to familiarize you on how I mine this valuable AWR performance data.
Let’s get started with the AWR top events, the same top events that you see in your AWR reports but presented in a time series manner across SNAP_IDs…
A recent post on the OTN database forum raises a problem with v$sql_shared_memory:
query to V$SQL_SHARED_MEMORY don’t return rows
please explain why ?
A follow-up posting then describes how the OP picked the view definition from v$fixed_view_definitions and use the text of that query instead of the view itself – and still got no rows returned:
Lately I’ve been busy on projects and research so I’m putting more and more stuff on my wiki/braindump… and really I need to catch up on blogging.
I have a longer draft post about SQLTXPLAIN.. but I haven’t finished it yet so I’ll just go with the quick post for now.
I’ve been using SQLTXPLAIN for quite a while, and I can say I’m a really big fan. It’s a cool tool to use for systematic SQL Tuning so I got them all neatly documented here http://karlarao.tiddlyspot.com/#SQLTXPLAIN.