Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
Date: Sat, 7 Nov 2009 06:10:10 -0800 (PST)
Message-ID: <23ae41f0-4ad1-4b50-90ec-957e749716ef_at_a39g2000pre.googlegroups.com>
------------------------case 1------------------------------------------------1. Use hint /*+ index(SD SETDETAILS_SETID_IX) */
Result: the result is same as no index hint
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> rem set autotrace trace SQL> set timing on SQL> SQL> variable spc varchar2(15); SQL> variable bfd varchar2(40); SQL> variable efd varchar2(40); SQL> variable p1 varchar2(60); SQL> variable p2 varchar2(60); SQL> exec :spc:='SACRAMENTO';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.39
SQL> exec :bfd:='2005-12-05';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
SQL> exec :efd:='2008-12-04 23:59:59';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.37
SQL> rem exec :p1:='254413'
SQL> exec :p1:='SET07'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
SQL> exec :p2:='Contractor'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.18
SQL> SQL> rem alter session set optimizer_index_caching = 0; SQL> rem alter session set optimizer_index_cost_adj = 100; SQL> rem alter session set optimizer_mode = ALL_ROWS; SQL> alter session set optimizer_index_caching = 90;
Session altered.
Elapsed: 00:00:00.03
SQL> alter session set optimizer_index_cost_adj = 10;
Session altered.
Elapsed: 00:00:00.03
SQL> alter session set optimizer_mode = FIRST_ROWS_100;
Session altered.
Elapsed: 00:00:00.00
SQL> SQL> SQL> SELECT /*+ index(SD SETDETAILS_SETID_IX) */ xfi.serv_prov_code, 2 xfi.b1_per_id1, 3 xfi.b1_per_id2, 4 xfi.b1_per_id3, 5 xfi.feeitem_seq_nbr, 6 xfi.invoice_nbr, 7 xfi.gf_fee_period, 8 xfi.gf_fee, 9 xfi.gf_des, 10 xfi.gf_unit, 11 xfi.gf_udes, 12 finv.invoice_date AS gf_fee_apply_date, 13 xfi.feeitem_invoice_status, 14 xfi.gf_l1, 15 xfi.gf_l2, 16 xfi.gf_l3, 17 xfi.x4feeitem_invoice_udf1, 18 xfi.x4feeitem_invoice_udf2, 19 xfi.x4feeitem_invoice_udf3, 20 xfi.x4feeitem_invoice_udf4, 21 xfi.gf_fee_schedule, 22 xfi.fee_schedule_version, 23 xfi.rec_date, 24 xfi.rec_ful_nam, 25 xfi.rec_status, 26 f4.GF_COD, 27 f4.GF_PRIORITY
28 FROM x4feeitem_invoice xfi, setdetails sd, f4invoice finv, F4FEEITEM f4
29 WHERE sd.serv_prov_code = xfi.serv_prov_code 30 AND sd.b1_per_id1 = xfi.b1_per_id1 31 AND sd.b1_per_id2 = xfi.b1_per_id2 32 AND sd.b1_per_id3 = xfi.b1_per_id3 33 AND xfi.serv_prov_code = f4.serv_prov_code 34 AND xfi.b1_per_id1 = f4.b1_per_id1 35 AND xfi.b1_per_id2 = f4.b1_per_id2 36 AND xfi.b1_per_id3 = f4.b1_per_id3 37 And xfi.feeitem_seq_nbr = f4.feeitem_seq_nbr 38 AND finv.serv_prov_code = xfi.serv_prov_code 39 AND finv.invoice_nbr = xfi.invoice_nbr 40 AND sd.serv_prov_code = :spc 41 AND upper(sd.set_id) = :p1 42 AND xfi.rec_status = 'A' 43 AND xfi.feeitem_invoice_status = 'INVOICED'44 ORDER BY gf_fee_apply_date
45 /
no rows selected
Elapsed: 00:06:07.08
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR
(NULL,NULL,'ALLSTATS LAST'
Plan hash value: 1519767420
| Id | Operation | Name | E-
Rows |
PLAN_TABLE_OUTPUT
| 0 | SELECT STATEMENT |
| |
| 1 | NESTED LOOPS |
| |
| 2 | NESTED LOOPS |
| 117 |
| 3 | NESTED LOOPS |
| 1 |
| 4 | NESTED LOOPS |
| 15 |
| 5 | TABLE ACCESS BY INDEX ROWID| F4INVOICE
| 248K|
|* 6 | INDEX RANGE SCAN | F4INVOICE_DATE_IX| 12 | TABLE ACCESS BY INDEX ROWID | F4FEEITEM
| 3 |
|* 7 | TABLE ACCESS BY INDEX ROWID| X4FEEITEM_INVOICE
| 5 |
|* 8 | INDEX RANGE SCAN | X4FEEITEM_INVOICE_NBR_IX
| 5 |
|* 9 | TABLE ACCESS BY INDEX ROWID | SETDETAILS
| 1 |
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- |* 10 | INDEX RANGE SCAN | SETDETAILS_SETID_IX
| 24 |
|* 11 | INDEX UNIQUE SCAN | F4FEEITEM_PK
| 1 |
| 101 |
Predicate Information (identified by operation id):
6 - access("FINV"."SERV_PROV_CODE"=:SPC) 7 - filter(("XFI"."FEEITEM_INVOICE_STATUS"='INVOICED' AND
"XFI"."REC_STATUS"='A')) PLAN_TABLE_OUTPUT
8 - access("XFI"."SERV_PROV_CODE"=:SPC AND
"FINV"."INVOICE_NBR"="XFI"."INVOICE_NBR") 9 - filter(("SD"."B1_PER_ID1"="XFI"."B1_PER_ID1" AND
"SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" AND
"SD"."B1_PER_ID3"="XFI"."B1_PER_ID3"))
10 - access("SD"."SERV_PROV_CODE"=:SPC AND "SD"."SYS_NC00017$"=:P1)
11 - access("F4"."SERV_PROV_CODE"=:SPC AND
"XFI"."B1_PER_ID1"="F4"."B1_PER_ID1" AND
"XFI"."B1_PER_ID2"="F4"."B1_PER_ID2" AND
"XFI"."B1_PER_ID3"="F4"."B1_PER_ID3" AND
"XFI"."FEEITEM_SEQ_NBR"="F4"."FEEITEM_SEQ_NBR")
PLAN_TABLE_OUTPUT
Note
- Warning: basic plan statistics not available. These are only
collected when
:
- hint 'gather_plan_statistics' is used for the statement or
- parameter 'statistics_level' is set to 'ALL', at session or system leve l
62 rows selected.
Elapsed: 00:00:11.59
SQL> exit
------------------------case 2------------------------------------------------2. Use hint /*+ LEADING(SD) index(SD SETDETAILS_SETID_IX) */
Result: the result is use SD at first in nested loop.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
SQL> rem set autotrace trace SQL> set timing on SQL> SQL> variable spc varchar2(15); SQL> variable bfd varchar2(40); SQL> variable efd varchar2(40); SQL> variable p1 varchar2(60); SQL> variable p2 varchar2(60); SQL> exec :spc:='SACRAMENTO';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> exec :bfd:='2005-12-05';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> exec :efd:='2008-12-04 23:59:59';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> rem exec :p1:='254413'
SQL> exec :p1:='SET07'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> exec :p2:='Contractor'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> SQL> rem alter session set optimizer_index_caching = 0; SQL> rem alter session set optimizer_index_cost_adj = 100; SQL> rem alter session set optimizer_mode = ALL_ROWS; SQL> alter session set optimizer_index_caching = 90;
Session altered.
Elapsed: 00:00:00.00
SQL> alter session set optimizer_index_cost_adj = 10;
Session altered.
Elapsed: 00:00:00.01
SQL> alter session set optimizer_mode = FIRST_ROWS_100;
Session altered.
Elapsed: 00:00:00.00
SQL> SQL> SQL> SELECT /*+ LEADING(SD) index(SD SETDETAILS_SETID_IX) */ xfi.serv_prov_code, 2 xfi.b1_per_id1, 3 xfi.b1_per_id2, 4 xfi.b1_per_id3, 5 xfi.feeitem_seq_nbr, 6 xfi.invoice_nbr, 7 xfi.gf_fee_period, 8 xfi.gf_fee, 9 xfi.gf_des, 10 xfi.gf_unit, 11 xfi.gf_udes, 12 finv.invoice_date AS gf_fee_apply_date, 13 xfi.feeitem_invoice_status, 14 xfi.gf_l1, 15 xfi.gf_l2, 16 xfi.gf_l3, 17 xfi.x4feeitem_invoice_udf1, 18 xfi.x4feeitem_invoice_udf2, 19 xfi.x4feeitem_invoice_udf3, 20 xfi.x4feeitem_invoice_udf4, 21 xfi.gf_fee_schedule, 22 xfi.fee_schedule_version, 23 xfi.rec_date, 24 xfi.rec_ful_nam, 25 xfi.rec_status, 26 f4.GF_COD, 27 f4.GF_PRIORITY
28 FROM x4feeitem_invoice xfi, setdetails sd, f4invoice finv, F4FEEITEM f4
29 WHERE sd.serv_prov_code = xfi.serv_prov_code 30 AND sd.b1_per_id1 = xfi.b1_per_id1 31 AND sd.b1_per_id2 = xfi.b1_per_id2 32 AND sd.b1_per_id3 = xfi.b1_per_id3 33 AND xfi.serv_prov_code = f4.serv_prov_code 34 AND xfi.b1_per_id1 = f4.b1_per_id1 35 AND xfi.b1_per_id2 = f4.b1_per_id2 36 AND xfi.b1_per_id3 = f4.b1_per_id3 37 And xfi.feeitem_seq_nbr = f4.feeitem_seq_nbr 38 AND finv.serv_prov_code = xfi.serv_prov_code 39 AND finv.invoice_nbr = xfi.invoice_nbr 40 AND sd.serv_prov_code = :spc 41 AND upper(sd.set_id) = :p1 42 AND xfi.rec_status = 'A' 43 AND xfi.feeitem_invoice_status = 'INVOICED'44 ORDER BY gf_fee_apply_date
45 /
no rows selected
Elapsed: 00:00:00.65
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR
(NULL,NULL,'ALLSTATS LAST')) ;
Plan hash value: 3678138156
PLAN_TABLE_OUTPUT
| Id | Operation | Name | E-
| OM
em | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT |
| |
| | |
| 1 | SORT ORDER BY | |
112K| 10
PLAN_TABLE_OUTPUT
24 | 1024 | |
|* 2 | HASH JOIN | |112K|
11M| 2154K| 13M (0)|
| 3 | TABLE ACCESS BY INDEX ROWID | F4INVOICE |
248K|
| | |
|* 4 | INDEX RANGE SCAN | F4INVOICE_BATCH_NBR_IX | 248K|
| | |
PLAN_TABLE_OUTPUT
|* 5 | HASH JOIN | |90650 | 6
94K| 694K| 214K (0)|
| 6 | NESTED LOOPS |
| |
| | |
| 7 | NESTED LOOPS | |
90650 |
| | |
| 8 | TABLE ACCESS BY INDEX ROWID| SETDETAILS
| 25 |
| | |
PLAN_TABLE_OUTPUT
|* 9 | INDEX RANGE SCAN | SETDETAILS_SETID_IX1231K|
| 25 |
| | | |* 10 | INDEX RANGE SCAN | X4FEEITEM_INVOICE_PK
| 1 |
| | | |* 11 | TABLE ACCESS BY INDEX ROWID | X4FEEITEM_INVOICE | 3578 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | F4FEEITEM |
PLAN_TABLE_OUTPUT
| | |
|* 13 | INDEX RANGE SCAN | F4FEEITEM_POS_IX | 1231K|
| | |
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
2 - access("FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND
"FINV"."INVOICE_NBR"="XFI"."INVOICE_NBR")
4 - access("FINV"."SERV_PROV_CODE"=:SPC)
5 - access("XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE" AND
"XFI"."B1_PER_ID1"="F4"."B1_PER_ID1" AND
"XFI"."B1_PER_ID2"="F4"."
B1_PER_ID2" AND "XFI"."B1_PER_ID3"="F4"."B1_PER_ID3" AND "XFI"."FEEITEM_SEQ_NBR"="
F4"."FEEITEM_SEQ_NBR") PLAN_TABLE_OUTPUT
9 - access("SD"."SERV_PROV_CODE"=:SPC AND "SD"."SYS_NC00017$"=:P1)
10 - access("XFI"."SERV_PROV_CODE"=:SPC AND
"SD"."B1_PER_ID1"="XFI"."B1_PER_ID
1" AND
"SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" AND
"SD"."B1_PER_ID3"="XFI"."
B1_PER_ID3")
11 - filter(("XFI"."FEEITEM_INVOICE_STATUS"='INVOICED' AND
"XFI"."REC_STATUS"=
'A'))
13 - access("F4"."SERV_PROV_CODE"=:SPC)
PLAN_TABLE_OUTPUT
Note
- Warning: basic plan statistics not available. These are only
collected when
:
- hint 'gather_plan_statistics' is used for the statement or
- parameter 'statistics_level' is set to 'ALL', at session or system leve l
60 rows selected.
Elapsed: 00:00:00.32
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release
11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
E:\tools\syncdata>
------------------------case 3------------------------------------------------3. combination ALL_ROWS + OICA (10) + OIC (90) + NO HINTS
Result: as below
| Id | Operation | Name | E-
| OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT |
| | | | |
| 1 | SORT ORDER BY | |
112K| 1024 | 1024 | | |* 2 | HASH JOIN | | 112K| 11M| 2154K| 13M (0)|4. combination ALL_ROWS + OICA (10) + OIC (0) + NO HINTS
| 3 | TABLE ACCESS BY INDEX ROWID | F4INVOICE |
248K| | | | |* 4 | INDEX RANGE SCAN | F4INVOICE_BATCH_NBR_IX | 248K| | | | |* 5 | HASH JOIN | | 90650 | 694K| 694K| 214K (0)|
| 6 | NESTED LOOPS |
| | | | |
| 7 | NESTED LOOPS | |
90650 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| SETDETAILS
| 25 | | | |
|* 9 | INDEX RANGE SCAN | SETDETAILS_SETID_IX
| 25 | | | |
|* 10 | INDEX RANGE SCAN | X4FEEITEM_INVOICE_PK
| 1 | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | X4FEEITEM_INVOICE | 3578 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | F4FEEITEM |
1231K| | | | |* 13 | INDEX RANGE SCAN | F4FEEITEM_POS_IX | 1231K| | | | ------------------------case 4------------------------------------------------
Result: as below
| Id | Operation | Name | E-
| OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT |
| | | | |
| 1 | SORT ORDER BY | |
112K| 1024 | 1024 | | |* 2 | HASH JOIN | | 112K| 11M| 2154K| 13M (0)|5. combination ALL_ROWS + OICA (100) + OIC (0) + NO HINTS
| 3 | TABLE ACCESS BY INDEX ROWID | F4INVOICE |
248K| | | | |* 4 | INDEX RANGE SCAN | F4INVOICE_BATCH_NBR_IX | 248K| | | | |* 5 | HASH JOIN | | 90650 | 694K| 694K| 1047K (0)|
| 6 | NESTED LOOPS |
| | | | |
| 7 | NESTED LOOPS | |
90650 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| SETDETAILS
| 25 | | | |
|* 9 | INDEX RANGE SCAN | SETDETAILS_SETID_IX
| 25 | | | |
|* 10 | INDEX RANGE SCAN | X4FEEITEM_INVOICE_PK
| 1 | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | X4FEEITEM_INVOICE | 3578 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | F4FEEITEM |
1231K| | | | |* 13 | INDEX RANGE SCAN | F4FEEITEM_POS_IX | 1231K| | | | ------------------------------------------------------------------------------------------------------- ------------------------case 5------------------------------------------------
Result: as below
| Id | Operation | Name | E-
| OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT |
| | | | |
| 1 | SORT ORDER BY | |
112K| 1024 | 1024 | | |* 2 | HASH JOIN | | 112K| 11M| 2154K| 13M (0)|6. If query is expected to retrieve 100,000 rows, but the application front-end will ONLY read just the first 100 rows and then throw away the rest of the query results without bother fetching the remaining rows.
| 3 | TABLE ACCESS BY INDEX ROWID | F4INVOICE |
248K| | | | |* 4 | INDEX RANGE SCAN | F4INVOICE_BATCH_NBR_IX | 248K| | | | |* 5 | HASH JOIN | | 90650 | 694K| 694K| 1047K (0)|
| 6 | NESTED LOOPS |
| | | | |
| 7 | NESTED LOOPS | |
90650 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| SETDETAILS
| 25 | | | |
|* 9 | INDEX RANGE SCAN | SETDETAILS_SETID_IX
| 25 | | | |
|* 10 | INDEX RANGE SCAN | X4FEEITEM_INVOICE_PK
| 1 | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | X4FEEITEM_INVOICE | 3578 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | F4FEEITEM |
1231K| | | | |* 13 | INDEX RANGE SCAN | F4FEEITEM_POS_IX | 1231K| | | | ------------------------------------------------------------------------------------------------------- ------------------------item 6------------------------------------------------
[lsllcm] There are many queries like the case in our environment. So we use FIRST_ROWS_100. I will test different queries in both FIRST_ROWS_100 and ALL_ROWS in 10g db and see difference.
------------------------item 7------------------------------------------------Is it a special case that no rows are returned due to some of the bind
values used? How many rows does this kind of statement return typically?
[lsllcm] The data in table SETDETAILS is skew, most of them have less
than 100 rows returned
based on SETDETAILS_SETID_IX. 10 of them have from 10,000 - 40,000
rows returned.
I use below command to gather statistics.
exec dbms_stats.gather_schema_stats
('TEST',estimate_percent=>100,cascade=> TRUE);
Below are histogram of SYS_NC00017$
COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ---------------------------------- --------------- -------------- SYS_NC00017$ 0 2.502076227359 SYS_NC00017$ 1 3.388853334909 SYS_NC00017$ 2 3.391679403148 SYS_NC00017$ 3 3.391679403478 SYS_NC00017$ 4 3.391679403525 SYS_NC00017$ 5 3.442991320807 SYS_NC00017$ 6 3.492087683254 SYS_NC00017$ 7 3.495116616164 SYS_NC00017$ 8 3.547450636185 SYS_NC00017$ 9 3.649479141472 SYS_NC00017$ 10 3.699779507944 SYS_NC00017$ 11 3.703217969722 SYS_NC00017$ 12 3.703622906366 SYS_NC00017$ 13 3.857771503121 SYS_NC00017$ 14 3.907648005162 SYS_NC00017$ 15 3.956124399555 SYS_NC00017$ 16 3.964259789323 SYS_NC00017$ 17 4.013711600766 SYS_NC00017$ 38 4.013711600766 SYS_NC00017$ 40 4.013711600766 COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ---------------------------------- --------------- -------------- SYS_NC00017$ 41 4.013737736751 SYS_NC00017$ 42 4.013737736908 SYS_NC00017$ 43 4.013739364774 SYS_NC00017$ 44 4.013741723170 SYS_NC00017$ 45 4.013741723170 SYS_NC00017$ 56 4.013748076424 SYS_NC00017$ 63 4.013748076644 SYS_NC00017$ 67 4.013748076645 SYS_NC00017$ 68 4.013748076656 SYS_NC00017$ 69 4.013748076657 SYS_NC00017$ 70 4.013748076669 SYS_NC00017$ 72 4.013748076681 SYS_NC00017$ 73 4.013748076718 SYS_NC00017$ 99 4.013748076765 SYS_NC00017$ 122 4.013748076766 SYS_NC00017$ 123 4.013748076798 SYS_NC00017$ 124 4.013748076801 SYS_NC00017$ 125 4.013748076802 SYS_NC00017$ 140 4.013748076826 SYS_NC00017$ 147 4.013748076826 SYS_NC00017$ 148 4.013748076838 COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ---------------------------------- --------------- -------------- SYS_NC00017$ 151 4.013748076838 SYS_NC00017$ 152 4.013748076919 SYS_NC00017$ 161 4.013748881378 SYS_NC00017$ 188 4.013748881378 SYS_NC00017$ 192 4.013748881378 SYS_NC00017$ 211 4.013748881378 SYS_NC00017$ 212 4.013749655139 SYS_NC00017$ 213 4.013751258162 SYS_NC00017$ 214 4.013752022627 SYS_NC00017$ 215 4.013761551658 SYS_NC00017$ 216 4.063638056887 SYS_NC00017$ 222 4.117383319540 SYS_NC00017$ 223 4.167086228207 SYS_NC00017$ 224 4.271108002299 SYS_NC00017$ 225 4.271744272749 SYS_NC00017$ 226 4.273769335395 SYS_NC00017$ 227 4.319380127847 SYS_NC00017$ 228 4.319380127847 SYS_NC00017$ 229 4.319380127847 SYS_NC00017$ 230 4.319380127847 SYS_NC00017$ 231 4.319380127847 COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ---------------------------------- --------------- -------------- SYS_NC00017$ 232 4.319380127847 SYS_NC00017$ 233 4.319380127847 SYS_NC00017$ 234 4.319380127847 SYS_NC00017$ 238 4.319380127847 SYS_NC00017$ 240 4.319380127847 SYS_NC00017$ 241 4.319380127847 SYS_NC00017$ 242 4.319380127847 SYS_NC00017$ 243 4.319380127847 SYS_NC00017$ 244 4.319380127847 SYS_NC00017$ 245 4.319380127847 SYS_NC00017$ 247 4.319380127847 SYS_NC00017$ 248 4.319380127847 SYS_NC00017$ 249 4.324469045995 SYS_NC00017$ 250 4.376187633557 SYS_NC00017$ 251 4.377607232242 SYS_NC00017$ 252 4.430327275963 SYS_NC00017$ 253 4.530723636900 SYS_NC00017$ 254 4.690372424101 ------------------------item 8------------------------------------------------[lsllcm] The reason to set "_optimizer_cost_based_transformation is to check if
the issue is related to the setting. Now I have set it back to default.
------------------------item 9------------------------------------------------9. You might try collecting system statistics during a busy time period.
To collect the system statistics with a 60 minute monitoring period, enter the following in SQL*Plus (the SQL*Plus command prompt will return immediately):
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('interval',interval=>60)
[lsllcm] I did not gather system statistics, and I check again
scheduled job, no job
gather system staitstics.
At first I use below command to gather statistics.
exec dbms_stats.gather_schema_stats
('TEST',estimate_percent=>100,cascade=> TRUE);
11g has automatic statistics gathering job too.
BEGIN
DBMS_AUTO_TASK_ADMIN.disable( client_name => 'auto optimizer stats
collection',
operation => NULL,
window_name => NULL);
END;
/
------------------------item 10------------------------------------------------10. As you suggested, I change the optimizer setting into default as combination ALL_ROWS + OICA (100) + OIC (0) in test environment. The result is
system looks like to become stable. Even if the query does not choose best
execution plan, it chooses not worst execution plan. Like the test case above,
it has 3 consistent reads in 10g db (it should be best execution plan). In 11g
(ALL_ROWS), it has 3656 consistent reads (it is not best, but it is not worst
too). In 11g (FIRST_ROWS_100), it has 2891740 consistent gets(like worst one). Received on Sat Nov 07 2009 - 08:10:10 CST