RE: Optimizer and block size changes = trouble...
Hi Steve, Waleed
on 8.1.6.0 Solaris I've got the same results as Waleed has.
Shared pool is huge, about 250M and user activity is low at the moment
(users haven't woken up yet :)
Is it NT-specific?
Regards,
Ed
Hi Waleed,
I ran the test below under 8.1.6.0 on NT using SQL*Plus and
I would have
expected the same results under 8.1.6.3 on Solaris.
Do you possibly have a small shared pool with very quick reuse?
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-Original Message-
Sent: Thursday, 10 May 2001 10:01
To: Multiple recipients of list ORACLE-L
Hi Steve,
I tested it on Oracle 8.1.6.3 (Solaris 2.6).
Parse_calls gets incremented every time the sql gets executed but the
version_count continues to be 1.
Regards,
Waleed
-Original Message-
Sent: Wednesday, May 09, 2001 6:15 PM
To: Multiple recipients of list ORACLE-L
Hi Nuno (and list),
Changing 'optimizer_index_caching' and
'optimizer_index_cost_adj' does seem
to
inhibit cursor sharing under 8i. You may want to try the
following test
under
8.0 and see if it is any different.
SQL create table t as select * from dual;
Table created.
SQL analyze table t compute statistics;
Table analyzed.
SQL select count(*) from t;
COUNT(*)
--
1
SQL select parse_calls, version_count from v$sqlarea
where sql_text
= 'select
count(*) from t ';
PARSE_CALLS VERSION_COUNT
--- -
1 1
SQL show parameters optimizer_index
NAME TYPEVALUE
---
--
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
SQL alter session set optimizer_index_caching = 1;
Session altered.
SQL select count(*) from t;
COUNT(*)
--
1
SQL select parse_calls, version_count from v$sqlarea
where sql_text
= 'select
count(*) from t ';
PARSE_CALLS VERSION_COUNT
--- -
2 2
SQL alter session set optimizer_index_cost_adj = 99;
Session altered.
SQL select count(*) from t;
COUNT(*)
--
1
SQL select parse_calls, version_count from v$sqlarea
where sql_text
= 'select
count(*) from t ';
PARSE_CALLS VERSION_COUNT
--- -
3 3
SQL
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-Original Message-
Sent: Thursday, 10 May 2001 0:51
To: Multiple recipients of list ORACLE-L
[snip]
Now, start
playing with OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ.
Incidentally, these can be set at session level but to get
them picked
up you need to do a FLUSH SHARED_POOL. Which kinda defeats the
purpose of making them dynamic in the first place, Mr. ORACLE? Or am
I missing something obvious? I can imagine someone doing this at the
beginning of each batch job and flushing the shared pool each time!
Jeez, some database coders don't have a clue about the real world, do
they?...
[snip]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steve Adams
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access /
Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Shevtsov, Eduard
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Optimizer and block size changes = trouble...
(The stats are a little off because I ran it through a
few times, but version count remains at 1)
mcdonac@EDEV create table t as select * from dual;
Table created.
mcdonac@EDEV analyze table t compute statistics;
Table analyzed.
mcdonac@EDEV select count(*) from t;
COUNT(*)
--
1
mcdonac@EDEV select parse_calls, version_count
2 from v$sqlarea
3 where sql_text= 'select count(*) from t';
no rows selected
mcdonac@EDEV show parameters optimizer_index
NAME TYPEVALUE
---
--
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
mcdonac@EDEV alter session set
optimizer_index_caching = 1;
Session altered.
mcdonac@EDEV select count(*) from t;
COUNT(*)
--
1
mcdonac@EDEV select parse_calls, version_count
2 from v$sqlarea
3 where sql_text= 'select count(*) from t ';
PARSE_CALLS VERSION_COUNT
--- -
11 1
mcdonac@EDEV alter session set
optimizer_index_cost_adj = 99;
Session altered.
mcdonac@EDEV select count(*) from t;
COUNT(*)
--
1
mcdonac@EDEV select parse_calls, version_count
2 from v$sqlarea
3 where sql_text= 'select count(*) from t ';
PARSE_CALLS VERSION_COUNT
--- -
12 1
mcdonac@EDEV select * from v$version;
BANNER
Oracle8i Enterprise Edition Release 8.1.6.3.0 -
Production
PL/SQL Release 8.1.6.3.0 - Production
CORE8.1.6.0.0 Production
TNS for Solaris: Version 8.1.6.3.0 - Production
NLSRTL Version 3.4.0.0.0 - Production
mcdonac@EDEV show parameters shared_pool
NAME TYPEVALUE
---
--
shared_pool_reserved_sizestring 2621440
shared_pool_size string 52428800
--- Steve Adams [EMAIL PROTECTED] wrote: Hi
Waleed,
I ran the test below under 8.1.6.0 on NT using
SQL*Plus and I would have
expected the same results under 8.1.6.3 on Solaris.
Do you possibly have a small shared pool with very
quick reuse?
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-Original Message-
Sent: Thursday, 10 May 2001 10:01
To: Multiple recipients of list ORACLE-L
Hi Steve,
I tested it on Oracle 8.1.6.3 (Solaris 2.6).
Parse_calls gets incremented every time the sql gets
executed but the
version_count continues to be 1.
Regards,
Waleed
-Original Message-
Sent: Wednesday, May 09, 2001 6:15 PM
To: Multiple recipients of list ORACLE-L
Hi Nuno (and list),
Changing 'optimizer_index_caching' and
'optimizer_index_cost_adj' does seem
to
inhibit cursor sharing under 8i. You may want to try
the following test
under
8.0 and see if it is any different.
SQL create table t as select * from dual;
Table created.
SQL analyze table t compute statistics;
Table analyzed.
SQL select count(*) from t;
COUNT(*)
--
1
SQL select parse_calls, version_count from
v$sqlarea where sql_text
= 'select
count(*) from t ';
PARSE_CALLS VERSION_COUNT
--- -
1 1
SQL show parameters optimizer_index
NAME TYPEVALUE
---
--
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
SQL alter session set optimizer_index_caching = 1;
Session altered.
SQL select count(*) from t;
COUNT(*)
--
1
SQL select parse_calls, version_count from
v$sqlarea where sql_text
= 'select
count(*) from t ';
PARSE_CALLS VERSION_COUNT
--- -
2 2
SQL alter session set optimizer_index_cost_adj =
99;
Session altered.
SQL select count(*) from t;
COUNT(*)
--
1
SQL select parse_calls, version_count from
v$sqlarea where sql_text
= 'select
count(*) from t ';
PARSE_CALLS VERSION_COUNT
--- -
3 3
SQL
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-Original Message-
Sent: Thursday, 10 May 2001 0:51
To: Multiple recipients of list ORACLE-L
[snip]
Now, start
playing with OPTIMIZER_INDEX_CACHING and
OPTIMIZER_INDEX_COST_ADJ.
Incidentally, these can be set at session level but
to get them picked
up you need to do a FLUSH SHARED_POOL. Which kinda
defeats the
purpose
RE: Optimizer and block size changes = trouble...
Steve,
In terms of the difference between 8.1.6.0 and
8.1.6.3, I can't remember the version vs bug cross-ref
but is it possible you've got timed stats or tracing
turned on ? It was around 8.1.6.x-ish that Oracle got
this sorted out wasn't it ?
Cheers
Connor
--- Steve Adams [EMAIL PROTECTED] wrote: Hi
Nuno (and list),
Changing 'optimizer_index_caching' and
'optimizer_index_cost_adj' does seem to
inhibit cursor sharing under 8i. You may want to try
the following test under
8.0 and see if it is any different.
SQL create table t as select * from dual;
Table created.
SQL analyze table t compute statistics;
Table analyzed.
SQL select count(*) from t;
COUNT(*)
--
1
SQL select parse_calls, version_count from
v$sqlarea where sql_text = 'select
count(*) from t ';
PARSE_CALLS VERSION_COUNT
--- -
1 1
SQL show parameters optimizer_index
NAME TYPEVALUE
---
--
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
SQL alter session set optimizer_index_caching = 1;
Session altered.
SQL select count(*) from t;
COUNT(*)
--
1
SQL select parse_calls, version_count from
v$sqlarea where sql_text = 'select
count(*) from t ';
PARSE_CALLS VERSION_COUNT
--- -
2 2
SQL alter session set optimizer_index_cost_adj =
99;
Session altered.
SQL select count(*) from t;
COUNT(*)
--
1
SQL select parse_calls, version_count from
v$sqlarea where sql_text = 'select
count(*) from t ';
PARSE_CALLS VERSION_COUNT
--- -
3 3
SQL
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-Original Message-
Sent: Thursday, 10 May 2001 0:51
To: Multiple recipients of list ORACLE-L
[snip]
Now, start
playing with OPTIMIZER_INDEX_CACHING and
OPTIMIZER_INDEX_COST_ADJ.
Incidentally, these can be set at session level but
to get them picked
up you need to do a FLUSH SHARED_POOL. Which kinda
defeats the
purpose of making them dynamic in the first place,
Mr. ORACLE? Or am
I missing something obvious? I can imagine someone
doing this at the
beginning of each batch job and flushing the shared
pool each time!
Jeez, some database coders don't have a clue about
the real world, do
they?...
[snip]
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Steve Adams
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
=
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)
Some days you're the pigeon, some days you're the statue
Do You Yahoo!?
Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk
or your free @yahoo.ie address at http://mail.yahoo.ie
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Optimizer and block size changes = trouble...
SAS=2M, CACHING=50, ADJ=10. My HASH was 10M, then dropped it down all the way to 260K, at which stage it threw out the MERGE JOIN CARTESIAN and went back to HASH JOIN only. So I threw it out completely (default 0), which would have made it 2M given that SAS dependency. But the MERGE JOIN CARTESIAN did disappear with HASH defaulted, even though it was there while I tried HASH 2M! More mystery... So far, Anjo Kolk has provided me with the most plausible explanation and it has nothing to do with any of these parameters... Cheers Nuno Souto [EMAIL PROTECTED] http://www.users.bigpond.net.au/the_Den - Original Message - Default value of hash_area_size is sort_area_size. What was your sort_area_size, and also what values of OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ did you finally use? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Optimizer and block size changes = trouble...
50 for INDEX_CACHING and 10 for ADJ. Cheers Nuno Souto [EMAIL PROTECTED] http://www.users.bigpond.net.au/the_Den - Original Message - So what was the final combination that fixed things? That info might make it easier for one of the list gurus to tell you why it worked... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Optimizer and block size changes = trouble...
Steve,
I got the same results as Waleed on 8.1.6.0.0 on
Win2K, 8.1.6.3 32-bit on Solaris, and 8.1.6.1 64-bit
on Solaris.
Might you be running into bug 1210242 (fixed in
8.1.6.2) or one of the similar bugs in 8i where
cursors aren't shared when timed_statistics are
enabled?
The workaround for many of them is to set
_SQL_EXEC_PROGRESSION_COST=0.
-- Anita
--- Steve Adams [EMAIL PROTECTED] wrote:
Hi Waleed,
I ran the test below under 8.1.6.0 on NT using
SQL*Plus and I would have
expected the same results under 8.1.6.3 on Solaris.
Do you possibly have a small shared pool with very
quick reuse?
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-Original Message-
Sent: Thursday, 10 May 2001 10:01
To: Multiple recipients of list ORACLE-L
Hi Steve,
I tested it on Oracle 8.1.6.3 (Solaris 2.6).
Parse_calls gets incremented every time the sql gets
executed but the
version_count continues to be 1.
Regards,
Waleed
-Original Message-
Sent: Wednesday, May 09, 2001 6:15 PM
To: Multiple recipients of list ORACLE-L
Hi Nuno (and list),
Changing 'optimizer_index_caching' and
'optimizer_index_cost_adj' does seem
to
inhibit cursor sharing under 8i. You may want to try
the following test
under
8.0 and see if it is any different.
SQL create table t as select * from dual;
Table created.
SQL analyze table t compute statistics;
Table analyzed.
SQL select count(*) from t;
COUNT(*)
--
1
SQL select parse_calls, version_count from
v$sqlarea where sql_text
= 'select
count(*) from t ';
PARSE_CALLS VERSION_COUNT
--- -
1 1
SQL show parameters optimizer_index
NAME TYPEVALUE
---
--
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
SQL alter session set optimizer_index_caching = 1;
Session altered.
SQL select count(*) from t;
COUNT(*)
--
1
SQL select parse_calls, version_count from
v$sqlarea where sql_text
= 'select
count(*) from t ';
PARSE_CALLS VERSION_COUNT
--- -
2 2
SQL alter session set optimizer_index_cost_adj =
99;
Session altered.
SQL select count(*) from t;
COUNT(*)
--
1
SQL select parse_calls, version_count from
v$sqlarea where sql_text
= 'select
count(*) from t ';
PARSE_CALLS VERSION_COUNT
--- -
3 3
SQL
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-Original Message-
Sent: Thursday, 10 May 2001 0:51
To: Multiple recipients of list ORACLE-L
[snip]
Now, start
playing with OPTIMIZER_INDEX_CACHING and
OPTIMIZER_INDEX_COST_ADJ.
Incidentally, these can be set at session level but
to get them picked
up you need to do a FLUSH SHARED_POOL. Which kinda
defeats the
purpose of making them dynamic in the first place,
Mr. ORACLE? Or am
I missing something obvious? I can imagine someone
doing this at the
beginning of each batch job and flushing the shared
pool each time!
Jeez, some database coders don't have a clue about
the real world, do
they?...
[snip]
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Steve Adams
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
__
Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices
http://auctions.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: A. Bardeen
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the
Re: Optimizer and block size changes = trouble...
- Original Message - have you tried to disable hash joins explicitly via hash_join_enabled = false ? No. I want to have hash joins. They are darn useful for some of the SQL. But they are contemptuous when they become too familiar. g Could you forward Anjo's explanation? Anjo suggested the optimizer is basing most of its calculations on number of logical reads, rather than physical. This may be due to the particular combination of table sizes and SQL in Peoplesoft. When I changed my block size to half, I doubled all the counts of logical disk reads. These don't necessarily translate into double the physical read operations, because of DB_FILE_MULTIBLOCK_READS. And if the threshold that triggers HASH join all over the place was overtaken, then I should see a blow out of them. Which is consistent, since all of my middle size tables suddenly were turned into hash joins whenever used in a multi-table join! The really large ones went into extremes and became MERGE JOIN CARTESIAN, but that one I still have to rationalize. Bug? He has also indicated he's seen all these probs disappear in 9i, where the optimizer favours CPU + real I/O for its calculations rather than logical disk reads. Makes absolute sense to me. I knew that my logical disk reads had increased for the same SQL, from looking at what was in V$SQLAREA. Even now when things are long back to normal, I still see much increased logical reads compared to the other instances where I still have 16K and same data. Live and learn... Cheers Nuno Souto [EMAIL PROTECTED] http://www.users.bigpond.net.au/the_Den -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Optimizer and block size changes = trouble...
Nuno, thanks for your response - Original Message - have you tried to disable hash joins explicitly via hash_join_enabled = false ? No. I want to have hash joins. They are darn useful for some of the SQL. But they are contemptuous when they become too familiar. g Yes, I see. It would be interesting to see what effect the disabling can do. Of course if you have time and opportunity. Probably event 10053 (before and after) can shed some light and helps to catch the difference of CBO's behaviour Regards, Ed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shevtsov, Eduard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Optimizer and block size changes = trouble...
Sorry. Read my reply to Eduard. You should have got it? If not, let me know and I'll send. Cheers Nuno Souto [EMAIL PROTECTED] http://www.users.bigpond.net.au/the_Den - Original Message - and that is??? c'mon Nuno, we need to know! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Optimizer and block size changes = trouble...
Nuno Souto wrote: Sorry. Read my reply to Eduard. You should have got it? If not, let me know and I'll send. well i missed it. if you could forward it to me off list please? -- Bill Thater Certifiable ORACLE DBA Telergy, Inc [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thater, William INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Optimizer and block size changes = trouble...
I saw it I wrote before I saw your answer :) From: Nuno Souto [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Optimizer and block size changes = trouble... Date: Thu, 10 May 2001 08:11:37 -0800 Sorry. Read my reply to Eduard. You should have got it? If not, let me know and I'll send. Cheers Nuno Souto [EMAIL PROTECTED] http://www.users.bigpond.net.au/the_Den - Original Message - and that is??? c'mon Nuno, we need to know! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Optimizer and block size changes = trouble...
Hola, Dirk! So what was the final combination that fixed things? That info might make it easier for one of the list gurus to tell you why it worked... Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: Nuno Souto [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, May 09, 2001 9:51 AM To: Multiple recipients of list ORACLE-L Subject: Optimizer and block size changes = trouble... Another spiky one. Situation is this: V8.0.6.0, HP/UX 11, Peoplesoft Financials 7.52(heck, does it sound like I'm involved with this mob? g) Block size in DB was 16K. Totally inappropriate for the type of database this is, with huge memory use and horrendous rollback segment size overhead and performance hit. DB_FILE_MULTIBLOCK_READS at 8. Optimizer mode was CHOOSE, all tables/indexes analyzed. Yeah I know, this is the same as ALL_ROWS. Stay with me, it's not that simple! But the explain plans of critical join SQL was OK: using NL because the result set was small, as opposed to using hash joins. This was surprising, given the well known penchant of the optimizer to use hash joins all over the place when ALL_ROWS is active. But I let it go at that. So, dirk the daring here decides to change the block size to 8K. After checking with multiple other sites running same software and making sure 8K is by far the most used block size for this combination, with a few on 4K and NOT A SINGLE ONE on 16K. Peoplesoft recommends 2K, but what do they know? Export/re-create/import/analyze. Set DB_FILE_MULTIBLOCK_READS to 16. All hell breaks loose. Suddenly, all critical join SQL becomes HASH JOIN, no matter what! Hints, no hints, you name it! Heavy disk activity on the TSs with the tables being hash joined, as expected. dirk the daring is beginning to look like dirk the stoopid ass... Nothing else changed. Optimizer is still CHOOSE, tables have been analyzed and properly sized with uniform extent allocations of suitable sizes. Same for indexes. This was also with 16K, so that's not the problem. First things first: Set DB_FILE_MULTIBLOCK_READS lower to try and reduce the optimizer picking full table scans. No joy. Absolutely no change whatsoever! So, I decide to play with HASH_AREA_SIZE and jack it up to see if I can reduce somewhat the overhead of all these hash joins. Great! Now I get MERGE JOIN CARTESIAN thrown in together with HASH JOIN in my explain plans. Did I mention hell breaking loose before? You should see the I/O and CPU use in this box by now. The EMC was throwing a fit... dirk the daring is now feeling like someone is standing right behind him, doing rude things to his ass... OK, time for a coffee break and some serious thinking! No way I'm gonna go back to 16K, I know the problems I was having. Let's fix this. First, set OPTIMIZER_MODE=FIRST_ROWS. No effect. **NO EFFECT** What the heck is going on here? OK, re-size HASH_AREA_SIZE. The smaller I go, the more I get rid of MERGE JOIN CARTESIAN and the more it goes back to HASH JOIN only. Hmm, that's an unexpected result. But I'll live with that. So, drop HASH_AREA_SIZE completely. Throw it away. Shoo. Now, start playing with OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ. Incidentally, these can be set at session level but to get them picked up you need to do a FLUSH SHARED_POOL. Which kinda defeats the purpose of making them dynamic in the first place, Mr. ORACLE? Or am I missing something obvious? I can imagine someone doing this at the beginning of each batch job and flushing the shared pool each time! Jeez, some database coders don't have a clue about the real world, do they?... Anyways, I digress. Check, adjust, shutdown/restart, back to check, repeat. Eventually, I find a combination that makes the vast majority of HASH joins go away and I get my NL back for short result sets and the HASH to work only where I want it. All is well. Throw in lots of users, lots of batch. System is responding well, no I/O blow outs, all is even, CPU being used where I want it, even load in the system, good memory use, rollback segs back to normal size, great throughput and response time, yadda-yadda-ho-hum-back-to-normal. dirk the daring is now dirk the hero. However, dirk is an arsehole who doesn't like to be kicked in the teeth by just another piece of software, regardless of who makes it. He wants to know what the heck went wrong here and why did the optimizer behave like a temperamental spoiled little brat. Any ideas greatly appreciated. Cheers Nuno Souto [EMAIL PROTECTED] http://www.users.bigpond.net.au/the_Den -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: Optimizer and block size changes = trouble...
Default value of hash_area_size is sort_area_size. What was your sort_area_size, and also what values of OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ did you finally use? Alex Hillman -Original Message- Sent: Wednesday, May 09, 2001 10:51 AM To: Multiple recipients of list ORACLE-L Another spiky one. Situation is this: V8.0.6.0, HP/UX 11, Peoplesoft Financials 7.52(heck, does it sound like I'm involved with this mob? g) Block size in DB was 16K. Totally inappropriate for the type of database this is, with huge memory use and horrendous rollback segment size overhead and performance hit. DB_FILE_MULTIBLOCK_READS at 8. Optimizer mode was CHOOSE, all tables/indexes analyzed. Yeah I know, this is the same as ALL_ROWS. Stay with me, it's not that simple! But the explain plans of critical join SQL was OK: using NL because the result set was small, as opposed to using hash joins. This was surprising, given the well known penchant of the optimizer to use hash joins all over the place when ALL_ROWS is active. But I let it go at that. So, dirk the daring here decides to change the block size to 8K. After checking with multiple other sites running same software and making sure 8K is by far the most used block size for this combination, with a few on 4K and NOT A SINGLE ONE on 16K. Peoplesoft recommends 2K, but what do they know? Export/re-create/import/analyze. Set DB_FILE_MULTIBLOCK_READS to 16. All hell breaks loose. Suddenly, all critical join SQL becomes HASH JOIN, no matter what! Hints, no hints, you name it! Heavy disk activity on the TSs with the tables being hash joined, as expected. dirk the daring is beginning to look like dirk the stoopid ass... Nothing else changed. Optimizer is still CHOOSE, tables have been analyzed and properly sized with uniform extent allocations of suitable sizes. Same for indexes. This was also with 16K, so that's not the problem. First things first: Set DB_FILE_MULTIBLOCK_READS lower to try and reduce the optimizer picking full table scans. No joy. Absolutely no change whatsoever! So, I decide to play with HASH_AREA_SIZE and jack it up to see if I can reduce somewhat the overhead of all these hash joins. Great! Now I get MERGE JOIN CARTESIAN thrown in together with HASH JOIN in my explain plans. Did I mention hell breaking loose before? You should see the I/O and CPU use in this box by now. The EMC was throwing a fit... dirk the daring is now feeling like someone is standing right behind him, doing rude things to his ass... OK, time for a coffee break and some serious thinking! No way I'm gonna go back to 16K, I know the problems I was having. Let's fix this. First, set OPTIMIZER_MODE=FIRST_ROWS. No effect. **NO EFFECT** What the heck is going on here? OK, re-size HASH_AREA_SIZE. The smaller I go, the more I get rid of MERGE JOIN CARTESIAN and the more it goes back to HASH JOIN only. Hmm, that's an unexpected result. But I'll live with that. So, drop HASH_AREA_SIZE completely. Throw it away. Shoo. Now, start playing with OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ. Incidentally, these can be set at session level but to get them picked up you need to do a FLUSH SHARED_POOL. Which kinda defeats the purpose of making them dynamic in the first place, Mr. ORACLE? Or am I missing something obvious? I can imagine someone doing this at the beginning of each batch job and flushing the shared pool each time! Jeez, some database coders don't have a clue about the real world, do they?... Anyways, I digress. Check, adjust, shutdown/restart, back to check, repeat. Eventually, I find a combination that makes the vast majority of HASH joins go away and I get my NL back for short result sets and the HASH to work only where I want it. All is well. Throw in lots of users, lots of batch. System is responding well, no I/O blow outs, all is even, CPU being used where I want it, even load in the system, good memory use, rollback segs back to normal size, great throughput and response time, yadda-yadda-ho-hum-back-to-normal. dirk the daring is now dirk the hero. However, dirk is an arsehole who doesn't like to be kicked in the teeth by just another piece of software, regardless of who makes it. He wants to know what the heck went wrong here and why did the optimizer behave like a temperamental spoiled little brat. Any ideas greatly appreciated. Cheers Nuno Souto [EMAIL PROTECTED] http://www.users.bigpond.net.au/the_Den -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB
RE: Optimizer and block size changes = trouble...
I saw some situations where you have to delete the statistics, analyze the tables and all the indexed columns and increase the default number of histograms. So the main issue is deleting the statistics first and then providing optimizer more data about the data distribution and the cardinality. -Original Message- Sent: Wednesday, May 09, 2001 10:51 AM To: Multiple recipients of list ORACLE-L Another spiky one. Situation is this: V8.0.6.0, HP/UX 11, Peoplesoft Financials 7.52(heck, does it sound like I'm involved with this mob? g) Block size in DB was 16K. Totally inappropriate for the type of database this is, with huge memory use and horrendous rollback segment size overhead and performance hit. DB_FILE_MULTIBLOCK_READS at 8. Optimizer mode was CHOOSE, all tables/indexes analyzed. Yeah I know, this is the same as ALL_ROWS. Stay with me, it's not that simple! But the explain plans of critical join SQL was OK: using NL because the result set was small, as opposed to using hash joins. This was surprising, given the well known penchant of the optimizer to use hash joins all over the place when ALL_ROWS is active. But I let it go at that. So, dirk the daring here decides to change the block size to 8K. After checking with multiple other sites running same software and making sure 8K is by far the most used block size for this combination, with a few on 4K and NOT A SINGLE ONE on 16K. Peoplesoft recommends 2K, but what do they know? Export/re-create/import/analyze. Set DB_FILE_MULTIBLOCK_READS to 16. All hell breaks loose. Suddenly, all critical join SQL becomes HASH JOIN, no matter what! Hints, no hints, you name it! Heavy disk activity on the TSs with the tables being hash joined, as expected. dirk the daring is beginning to look like dirk the stoopid ass... Nothing else changed. Optimizer is still CHOOSE, tables have been analyzed and properly sized with uniform extent allocations of suitable sizes. Same for indexes. This was also with 16K, so that's not the problem. First things first: Set DB_FILE_MULTIBLOCK_READS lower to try and reduce the optimizer picking full table scans. No joy. Absolutely no change whatsoever! So, I decide to play with HASH_AREA_SIZE and jack it up to see if I can reduce somewhat the overhead of all these hash joins. Great! Now I get MERGE JOIN CARTESIAN thrown in together with HASH JOIN in my explain plans. Did I mention hell breaking loose before? You should see the I/O and CPU use in this box by now. The EMC was throwing a fit... dirk the daring is now feeling like someone is standing right behind him, doing rude things to his ass... OK, time for a coffee break and some serious thinking! No way I'm gonna go back to 16K, I know the problems I was having. Let's fix this. First, set OPTIMIZER_MODE=FIRST_ROWS. No effect. **NO EFFECT** What the heck is going on here? OK, re-size HASH_AREA_SIZE. The smaller I go, the more I get rid of MERGE JOIN CARTESIAN and the more it goes back to HASH JOIN only. Hmm, that's an unexpected result. But I'll live with that. So, drop HASH_AREA_SIZE completely. Throw it away. Shoo. Now, start playing with OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ. Incidentally, these can be set at session level but to get them picked up you need to do a FLUSH SHARED_POOL. Which kinda defeats the purpose of making them dynamic in the first place, Mr. ORACLE? Or am I missing something obvious? I can imagine someone doing this at the beginning of each batch job and flushing the shared pool each time! Jeez, some database coders don't have a clue about the real world, do they?... Anyways, I digress. Check, adjust, shutdown/restart, back to check, repeat. Eventually, I find a combination that makes the vast majority of HASH joins go away and I get my NL back for short result sets and the HASH to work only where I want it. All is well. Throw in lots of users, lots of batch. System is responding well, no I/O blow outs, all is even, CPU being used where I want it, even load in the system, good memory use, rollback segs back to normal size, great throughput and response time, yadda-yadda-ho-hum-back-to-normal. dirk the daring is now dirk the hero. However, dirk is an arsehole who doesn't like to be kicked in the teeth by just another piece of software, regardless of who makes it. He wants to know what the heck went wrong here and why did the optimizer behave like a temperamental spoiled little brat. Any ideas greatly appreciated. Cheers Nuno Souto [EMAIL PROTECTED] http://www.users.bigpond.net.au/the_Den -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL
RE: Optimizer and block size changes = trouble...
Hi Nuno (and list), Changing 'optimizer_index_caching' and 'optimizer_index_cost_adj' does seem to inhibit cursor sharing under 8i. You may want to try the following test under 8.0 and see if it is any different. SQL create table t as select * from dual; Table created. SQL analyze table t compute statistics; Table analyzed. SQL select count(*) from t; COUNT(*) -- 1 SQL select parse_calls, version_count from v$sqlarea where sql_text = 'select count(*) from t '; PARSE_CALLS VERSION_COUNT --- - 1 1 SQL show parameters optimizer_index NAME TYPEVALUE --- -- optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 SQL alter session set optimizer_index_caching = 1; Session altered. SQL select count(*) from t; COUNT(*) -- 1 SQL select parse_calls, version_count from v$sqlarea where sql_text = 'select count(*) from t '; PARSE_CALLS VERSION_COUNT --- - 2 2 SQL alter session set optimizer_index_cost_adj = 99; Session altered. SQL select count(*) from t; COUNT(*) -- 1 SQL select parse_calls, version_count from v$sqlarea where sql_text = 'select count(*) from t '; PARSE_CALLS VERSION_COUNT --- - 3 3 SQL @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Thursday, 10 May 2001 0:51 To: Multiple recipients of list ORACLE-L [snip] Now, start playing with OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ. Incidentally, these can be set at session level but to get them picked up you need to do a FLUSH SHARED_POOL. Which kinda defeats the purpose of making them dynamic in the first place, Mr. ORACLE? Or am I missing something obvious? I can imagine someone doing this at the beginning of each batch job and flushing the shared pool each time! Jeez, some database coders don't have a clue about the real world, do they?... [snip] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Optimizer and block size changes = trouble...
Hi Steve, I tested it on Oracle 8.1.6.3 (Solaris 2.6). Parse_calls gets incremented every time the sql gets executed but the version_count continues to be 1. Regards, Waleed -Original Message- Sent: Wednesday, May 09, 2001 6:15 PM To: Multiple recipients of list ORACLE-L Hi Nuno (and list), Changing 'optimizer_index_caching' and 'optimizer_index_cost_adj' does seem to inhibit cursor sharing under 8i. You may want to try the following test under 8.0 and see if it is any different. SQL create table t as select * from dual; Table created. SQL analyze table t compute statistics; Table analyzed. SQL select count(*) from t; COUNT(*) -- 1 SQL select parse_calls, version_count from v$sqlarea where sql_text = 'select count(*) from t '; PARSE_CALLS VERSION_COUNT --- - 1 1 SQL show parameters optimizer_index NAME TYPEVALUE --- -- optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 SQL alter session set optimizer_index_caching = 1; Session altered. SQL select count(*) from t; COUNT(*) -- 1 SQL select parse_calls, version_count from v$sqlarea where sql_text = 'select count(*) from t '; PARSE_CALLS VERSION_COUNT --- - 2 2 SQL alter session set optimizer_index_cost_adj = 99; Session altered. SQL select count(*) from t; COUNT(*) -- 1 SQL select parse_calls, version_count from v$sqlarea where sql_text = 'select count(*) from t '; PARSE_CALLS VERSION_COUNT --- - 3 3 SQL @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Thursday, 10 May 2001 0:51 To: Multiple recipients of list ORACLE-L [snip] Now, start playing with OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ. Incidentally, these can be set at session level but to get them picked up you need to do a FLUSH SHARED_POOL. Which kinda defeats the purpose of making them dynamic in the first place, Mr. ORACLE? Or am I missing something obvious? I can imagine someone doing this at the beginning of each batch job and flushing the shared pool each time! Jeez, some database coders don't have a clue about the real world, do they?... [snip] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Optimizer and block size changes = trouble...
Hi Waleed, I ran the test below under 8.1.6.0 on NT using SQL*Plus and I would have expected the same results under 8.1.6.3 on Solaris. Do you possibly have a small shared pool with very quick reuse? @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Thursday, 10 May 2001 10:01 To: Multiple recipients of list ORACLE-L Hi Steve, I tested it on Oracle 8.1.6.3 (Solaris 2.6). Parse_calls gets incremented every time the sql gets executed but the version_count continues to be 1. Regards, Waleed -Original Message- Sent: Wednesday, May 09, 2001 6:15 PM To: Multiple recipients of list ORACLE-L Hi Nuno (and list), Changing 'optimizer_index_caching' and 'optimizer_index_cost_adj' does seem to inhibit cursor sharing under 8i. You may want to try the following test under 8.0 and see if it is any different. SQL create table t as select * from dual; Table created. SQL analyze table t compute statistics; Table analyzed. SQL select count(*) from t; COUNT(*) -- 1 SQL select parse_calls, version_count from v$sqlarea where sql_text = 'select count(*) from t '; PARSE_CALLS VERSION_COUNT --- - 1 1 SQL show parameters optimizer_index NAME TYPEVALUE --- -- optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 SQL alter session set optimizer_index_caching = 1; Session altered. SQL select count(*) from t; COUNT(*) -- 1 SQL select parse_calls, version_count from v$sqlarea where sql_text = 'select count(*) from t '; PARSE_CALLS VERSION_COUNT --- - 2 2 SQL alter session set optimizer_index_cost_adj = 99; Session altered. SQL select count(*) from t; COUNT(*) -- 1 SQL select parse_calls, version_count from v$sqlarea where sql_text = 'select count(*) from t '; PARSE_CALLS VERSION_COUNT --- - 3 3 SQL @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Thursday, 10 May 2001 0:51 To: Multiple recipients of list ORACLE-L [snip] Now, start playing with OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ. Incidentally, these can be set at session level but to get them picked up you need to do a FLUSH SHARED_POOL. Which kinda defeats the purpose of making them dynamic in the first place, Mr. ORACLE? Or am I missing something obvious? I can imagine someone doing this at the beginning of each batch job and flushing the shared pool each time! Jeez, some database coders don't have a clue about the real world, do they?... [snip] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
