Re: performance question
Date: Wed, 12 Nov 2008 18:20:39 -0800 (PST)
Message-ID: <6b321eea-4fe0-4e15-a9fc-47f714725e21@h5g2000yqh.googlegroups.com>
On Nov 12, 8:31 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Nov 12, 12:14 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
(snip)
> > SQL statement (you must make certain that a hard parse of the SQL
> > statement is performed, possibly by changing the spacing in the SQL
> > statement or by adding a comment). The 10053 portion of the trace
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.
>
> I thought the white-space thingy was relaxed for comparing two sql
> statements - or is that just for stored outlines?
>
> rogv: Be sure you don't have any invalid objects (like an index you
> need), also try doing a full-table scan of the table to eliminate the
> possibility of bind-peeking (google if you don't know it).
>
> jg
I should have tested before posting regarding the whitespace
suggestion. The test case:
ALTER SYSTEM FLUSH SHARED_POOL;
SELECT
MS.VALUE
FROM
V$MYSTAT MS,
V$STATNAME SN
WHERE
MS.STATISTIC#=SN.STATISTIC#
AND SN.NAME='parse count (hard)';
SELECT SYSDATE FROM DUAL;
SELECT
MS.VALUE
FROM
V$MYSTAT MS,
V$STATNAME SN
WHERE
MS.STATISTIC#=SN.STATISTIC#
AND SN.NAME='parse count (hard)';
SELECT SYSDATE FROM DUAL;
SELECT
MS.VALUE
FROM
V$MYSTAT MS,
V$STATNAME SN
WHERE
MS.STATISTIC#=SN.STATISTIC#
AND SN.NAME='parse count (hard)';
SELECT SYSDATE FROM DUAL;
SELECT
MS.VALUE
FROM
V$MYSTAT MS,
V$STATNAME SN
WHERE
MS.STATISTIC#=SN.STATISTIC#
AND SN.NAME='parse count (hard)';
SELECT SYSDATE FROM DUAL;
SELECT
MS.VALUE
FROM
V$MYSTAT MS,
V$STATNAME SN
WHERE
MS.STATISTIC#=SN.STATISTIC#
AND SN.NAME='parse count (hard)';
(In the event that the spacing is not apparent in this post, I am
changing the spacing in the SQL statement that is selecting from
DUAL.)
The output:
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL>
SQL> SELECT
2 MS.VALUE
3 FROM
4 V$MYSTAT MS,
5 V$STATNAME SN
6 WHERE
7 MS.STATISTIC#=SN.STATISTIC#
8 AND SN.NAME='parse count (hard)';
VALUE
200
SQL>
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
12-NOV-08 SQL>
SQL> SELECT
2 MS.VALUE
3 FROM
4 V$MYSTAT MS,
5 V$STATNAME SN
6 WHERE
7 MS.STATISTIC#=SN.STATISTIC#
8 AND SN.NAME='parse count (hard)';
VALUE
201
SQL>
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
12-NOV-08 SQL>
SQL> SELECT
2 MS.VALUE
3 FROM
4 V$MYSTAT MS,
5 V$STATNAME SN
6 WHERE
7 MS.STATISTIC#=SN.STATISTIC#
8 AND SN.NAME='parse count (hard)';
VALUE
202
SQL>
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
12-NOV-08 SQL>
SQL> SELECT
2 MS.VALUE
3 FROM
4 V$MYSTAT MS,
5 V$STATNAME SN
6 WHERE
7 MS.STATISTIC#=SN.STATISTIC#
8 AND SN.NAME='parse count (hard)';
VALUE
203
SQL>
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
12-NOV-08 SQL>
SQL> SELECT
2 MS.VALUE
3 FROM
4 V$MYSTAT MS,
5 V$STATNAME SN
6 WHERE
7 MS.STATISTIC#=SN.STATISTIC#
8 AND SN.NAME='parse count (hard)';
VALUE
204
The above output was generated on Oracle 11.1.0.7. Repeating the above, leaving out the flushing of the shared pool returns a value of 204 for the last query against V$MYSTAT.
Stored outlines, at least on 10.2.x and 11.1.x, will tolerate variable whitespace. If I recall correctly, SQL profiles also tolerate variable whitespace, as does the 11g replacement for stored outlines (SQL plan baselines).
Joel, by the way, good suggestion about the possibility of problems with bind variable peeking - I had not considered that as a possibility. It might also be a good idea to query SYS.AUX_STATS$ to see if the CPU statistics differ significantly between the two servers - it could be that accurate statistics were not gathered for one of the database instances.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Wed Nov 12 2008 - 20:20:39 CST