Re: "'Resultcache" effect in 10.2.0.4 ??
From: <stevedhoward_at_gmail.com>
Date: Mon, 27 Jul 2009 16:26:39 -0700 (PDT)
Message-ID: <8f9543e5-a9fb-4250-91bf-5bcd5ad52a17_at_c29g2000yqd.googlegroups.com>
On Jul 23, 3:25 pm, jlchiappa <jlchia..._at_gmail.com> wrote:
> Iīm seeing this behavior in a coleague database : the original 9.2.0.7
> EE db was upgraded to 10.2.0.4 EE in the same Solaris 9 box (big one,
> with 32 processors and 64 Mb of RAM), and since then, for big and
> complexes queries (ie, with subqueries, and/or views from views,
> inline views, WITH clause, the hairy ones) the first execution is
> 'slow', say, 15 minutes or alike, and the second execution is 5 times
> or so faster (say 3 minutes or alike). The details :
>
> a) even if an ALTER SESSION FLUSH BUFFER_CACHE and FLUSH SHARED POOL
> the behavior is the same, so the dbīs buffer caching does not explain
> it
>
> b) same if the main SQL text is altered (causing a hard parse), so the
> SQL caching does not explain it
>
> c) strangely, even if the SQL is something like :
>
> SELECT columns FROM tables WHERE columns IN (complex subquery) AND
> column = 'X'
>
> if the columnsī clause is altered, or if the WHERE clause is altered,
> or even if the subquery is altered in a small way (example, subquery
> list of columns is altered togheter with , the behavior occurs, only
> if the subquery is altered allowing the return of a diferent resultset
> the 2nd execution is slower again
>
> d) if the database is shutdown, the next execution becomes 'slow'
> again
>
> e) the database is not mine, so I could not to get a 10046 trace, and
> canīt post a full SQL case, but consulting the V$SQLnn views we see
> the execution plan as the same in both executions , Iīm trying yet to
> get the trace 10046 from a small but reproductible example
>
> f) the datafiles are (and was in 9i epoch) in the same IBM Shark
> storage, using RAID-5 with cooked filesystems (no ASM), and with LMT
> tablespaces
>
> in my opinion :
>
> 1. the cooked FSs introduces the possibility of OS-caching - it donīt
> showed in 9i, maybe due to the setting of filesystem_options in the
> ocasion
>
> 2. the fact of the effect disapears after db shutdown donīt denies the
> OS-caching, imho
>
> 3. only if and when I can get a 10046 trace I will see if OS-caching
> in action, my plan will be :
>
> - trace 10046 in 2 different session execute the same SQL but with
> small alterations in text forcing a parse each time, and clearing db
> buffer cache each time
>
> - the plans WILL be the same for both execs, and the I/Os too :
> comparing the times for the I/Os (its registered in the 10046) , if
> the second-execution I/Os times are faster itīs proved the OS-caching
> option
>
> 4. donīt know if c) denies the OS-caching possibility - anyway, if the
> subquery resultset change but the blocks required are cached in the
> OS, it must not occurs, I think
>
> Any opinions/comments will be welcome.
>
> Regards,
>
> Chiappa
Date: Mon, 27 Jul 2009 16:26:39 -0700 (PDT)
Message-ID: <8f9543e5-a9fb-4250-91bf-5bcd5ad52a17_at_c29g2000yqd.googlegroups.com>
On Jul 23, 3:25 pm, jlchiappa <jlchia..._at_gmail.com> wrote:
> Iīm seeing this behavior in a coleague database : the original 9.2.0.7
> EE db was upgraded to 10.2.0.4 EE in the same Solaris 9 box (big one,
> with 32 processors and 64 Mb of RAM), and since then, for big and
> complexes queries (ie, with subqueries, and/or views from views,
> inline views, WITH clause, the hairy ones) the first execution is
> 'slow', say, 15 minutes or alike, and the second execution is 5 times
> or so faster (say 3 minutes or alike). The details :
>
> a) even if an ALTER SESSION FLUSH BUFFER_CACHE and FLUSH SHARED POOL
> the behavior is the same, so the dbīs buffer caching does not explain
> it
>
> b) same if the main SQL text is altered (causing a hard parse), so the
> SQL caching does not explain it
>
> c) strangely, even if the SQL is something like :
>
> SELECT columns FROM tables WHERE columns IN (complex subquery) AND
> column = 'X'
>
> if the columnsī clause is altered, or if the WHERE clause is altered,
> or even if the subquery is altered in a small way (example, subquery
> list of columns is altered togheter with , the behavior occurs, only
> if the subquery is altered allowing the return of a diferent resultset
> the 2nd execution is slower again
>
> d) if the database is shutdown, the next execution becomes 'slow'
> again
>
> e) the database is not mine, so I could not to get a 10046 trace, and
> canīt post a full SQL case, but consulting the V$SQLnn views we see
> the execution plan as the same in both executions , Iīm trying yet to
> get the trace 10046 from a small but reproductible example
>
> f) the datafiles are (and was in 9i epoch) in the same IBM Shark
> storage, using RAID-5 with cooked filesystems (no ASM), and with LMT
> tablespaces
>
> in my opinion :
>
> 1. the cooked FSs introduces the possibility of OS-caching - it donīt
> showed in 9i, maybe due to the setting of filesystem_options in the
> ocasion
>
> 2. the fact of the effect disapears after db shutdown donīt denies the
> OS-caching, imho
>
> 3. only if and when I can get a 10046 trace I will see if OS-caching
> in action, my plan will be :
>
> - trace 10046 in 2 different session execute the same SQL but with
> small alterations in text forcing a parse each time, and clearing db
> buffer cache each time
>
> - the plans WILL be the same for both execs, and the I/Os too :
> comparing the times for the I/Os (its registered in the 10046) , if
> the second-execution I/Os times are faster itīs proved the OS-caching
> option
>
> 4. donīt know if c) denies the OS-caching possibility - anyway, if the
> subquery resultset change but the blocks required are cached in the
> OS, it must not occurs, I think
>
> Any opinions/comments will be welcome.
>
> Regards,
>
> Chiappa
If you haven't seen a 10046 trace, how can you be sure what is below is true?
<<
3. only if and when I can get a 10046 trace I will see if OS-caching
in action, my plan will be :
- trace 10046 in 2 different session execute the same SQL but with small alterations in text forcing a parse each time, and clearing db buffer cache each time
- the plans WILL be the same for both execs, and the I/Os too : comparing the times for the I/Os (its registered in the 10046) , if the second-execution I/Os times are faster itīs proved the OS-caching option
>>
IMHO, you are just guessing until you can actually get a trace. Also, I wouldn't change anything. Run it once with a trace, log out, log in, and run it again with a trace.
Out of curiosity, it sounds like your colleague isn't very interested in getting this fixed if he/she can't provide you with a trace? Received on Mon Jul 27 2009 - 18:26:39 CDT