|
-
CBO not using index
from production environment on 9.2.0.4 Solaris
table was analyzed in the past hour
PHP Code:
1 SELECT /*+ index (METER_READING METER_READING_PK)*/
15:30:48 2 MAX(rcpt_dt)
15:30:48 3 FROM METER_READING
15:30:48 4 WHERE
15:30:48 5 org_id = 'XX' AND
15:30:48 6 svc_type_cd = 'E' AND
15:30:48 7 cust_id = '062141268001' AND
15:30:48 8 prem_seq_nbr = 1 AND
15:30:48 9 meter_seq_nbr = 1 AND
15:30:48 10 rdng_nbr >= 0
15:30:48 11 AND rcpt_dt > SYSDATE-60;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=660 Card=1 Bytes=31)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'METER_READING' (Cost=660 Card=47 Bytes=1457)
3 2 INDEX (RANGE SCAN) OF 'METER_READING_PK' (UNIQUE) (Cost=5 Card=937)
from a copy of production I export/import into another environment every morning and analyze. On a different db 9.2.0.4 also Solaris
PHP Code:
1 SELECT /*+ index (METER_READING METER_READING_PK)*/
15:31:27 2 MAX(rcpt_dt)
15:31:27 3 FROM METER_READING
15:31:27 4 WHERE
15:31:27 5 org_id = 'XX' AND
15:31:27 6 svc_type_cd = 'E' AND
15:31:27 7 cust_id = '062141268001' AND
15:31:27 8 prem_seq_nbr = 1 AND
15:31:27 9 meter_seq_nbr = 1 AND
15:31:27 10 rdng_nbr >= 0
15:31:27 11 AND rcpt_dt > SYSDATE-60;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=31)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'METER_READING' (Cost=5 Card=46 Bytes=1426)
3 2 INDEX (RANGE SCAN) OF 'METER_READING_PK' (UNIQUE) (Cost=2 Card=929)
I can't figure out why the production system thinks that the cost is so high. Why would that be??
Last edited by Cookies; 01-26-2004 at 04:46 PM.
- Cookies
-
init.ora parameters?
statistics?
-
Production:
- aq_tm_processes = 1
background_dump_dest = /u01/app/oracle/admin/kop/bdump
compatible = 9.2.0.4.0
control_files = ('/u01/app/oracle/oradata/kop/control01.ctl', '/u01/app/oracle/oradata/kop/control02.ctl', '/u01/app/oracle/oradata/kop/control03.ctl')
core_dump_dest = /u01/app/oracle/admin/kop/cdump
db_block_size = 8192
db_cache_size = 218103808
db_domain = ''
db_file_multiblock_read_count = 16
db_files = 400
db_name = kop
dispatchers = '(PROTOCOL=TCP) (SERVICE=kop1XDB)'
dml_locks = 1600
enqueue_resources = 2020
fast_start_mttr_target = 0
hash_area_size = 16384000
hash_join_enabled = TRUE
instance_name = kop1
java_pool_size = 33554432
job_queue_processes = 2
large_pool_size = 16777216
log_archive_dest_1 = 'LOCATION=/u03/oradata/kop/archive/arch'
log_buffer = 983040
log_checkpoint_interval = 100000000
log_checkpoint_timeout = 18000000
max_dump_file_size = 100000
max_rollback_segments = 80
open_cursors = 400
optimizer_features_enable = 9.2.0
pga_aggregate_target = 105906176
processes = 150
query_rewrite_enabled = FALSE
remote_login_passwordfile = NONE
service_names = kop1.xxx.com
session_cached_cursors = 200
sessions = 300
shared_pool_reserved_size = 5033164
shared_pool_size = 117440512
shared_server_sessions = 295
shared_servers = 1
sort_area_retained_size = 65536
sort_area_size = 8192000
star_transformation_enabled = FALSE
timed_statistics = TRUE
transactions = 400
undo_management = AUTO
undo_retention = 900
undo_tablespace = UNDO
user_dump_dest = /u01/app/oracle/admin/kop/udump
Copy of Production:
- background_dump_dest = /export/oracle/u01/app/oracle/admin/kod/bdump
compatible = 9.2.0.4
control_files = ('/export/oracle/u03/app/oracle/oradata/kod/control01.ctl', '/export/oracle/u01/app/oracle/oradata/kod/control02.ctl', '/export/oracle/u03/app/oracle/oradata/kod/control03.ctl')
core_dump_dest = /export/oracle/u01/app/oracle/admin/kod/cdump
db_block_size = 8192
db_cache_size = 218103808
db_domain = 'xxx.com'
db_file_multiblock_read_count = 16
db_files = 400
db_keep_cache_size = 16777216
db_name = kod
hash_area_size = 16384000
instance_name = kod1
job_queue_processes = 2
large_pool_size = 33554432
log_archive_dest_1 = 'LOCATION=/export/oracle/u01/app/oracle/product/9.2.0/dbs/arch'
log_buffer = 983040
log_checkpoint_interval = 100000000
log_checkpoint_timeout = 18000000
max_dump_file_size = 100000
max_enabled_roles = 30
open_cursors = 400
optimizer_features_enable = 9.2.0
os_authent_prefix = ''
pga_aggregate_target = 52428800
processes = 100
remote_login_passwordfile = NONE
remote_os_authent = TRUE
service_names = kod1.xxx.com
session_cached_cursors = 100
sessions = 300
shared_pool_size = 100663296
sort_area_retained_size = 65536
sort_area_size = 6553600
timed_statistics = TRUE
transactions = 400
undo_management = AUTO
undo_tablespace = undo
user_dump_dest = /export/oracle/u01/app/oracle/admin/kod/udump
workarea_size_policy = auto
Last edited by Cookies; 01-26-2004 at 08:45 PM.
- Cookies
-
-
Is it because I didn't analyze the imported copy?
Production indexes:
I just created METER_READING_IND_01 to alleviate the current problem since the disk reads were getting out of hand due to the CBO not using the PK index due to the initial problem above.
PHP Code:
OWNER INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENES COMPRESS PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_FREE LOG BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANAL DEGREE PAR T G S BUFFER_ USE
-------------- ----------------------- ------------- ------------- --------------- ----------- --------- -------- ------------- ------------------ ---------- ---------- -------------- ----------- ----------- ----------- --------- --- ---------- ----------- ------------- ----------------------- ----------------------- ----------------- -------- ---------- ----------- --------- ------- --- - - - ------- ---
PLATFORM METER_READING_PK NORMAL PLATFORM METER_READING TABLE UNIQUE ENABLED 5 PLAT_IND_01 2 255 65536 1 2147483645 10 NO 2 883 343161 1 1 267123 VALID 343161 343161 26-JAN-04 1 NO N N N DEFAULT NO
PLATFORM METER_READING_IND_01 NORMAL PLATFORM METER_READING TABLE NONUNIQUE DISABLED PLAT_IND_01 2 255 65536 1 2147483645 10 NO 2 1940 342059 1 1 267136 VALID 343161 343161 26-JAN-04 1 NO N N N DEFAULT NO
Copy of Production:
PHP Code:
OWNER INDEX_NAME INDEX_TYPE TABLE_TYPE UNIQUENES COMPRESS PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_FREE LOG STATUS NUM_ROWS SAMPLE_SIZE LAST_ANAL DEGREE INSTANCES PAR T G S BUFFER_ USE
---------- ----------------- ------------- ----------- --------- -------- ------------- ------------------------------ ---------- ---------- -------------- ----------- ----------- ----------- --------- --- -------- ---------- ----------- --------- --------- ----------- --- - - - ------- ---
PROD_COPY METER_READING_PK NORMAL TABLE UNIQUE ENABLED 5 USERS 2 255 65536 1 2147483645 10 NO VALID 1 1 NO N N N DEFAULT NO
- Cookies
-
Hey!, but it does use index!.
i have an idea, have you heard about stability plan?
is a feature that let your statistics be portable to another oracle database, so, you have to generate OUTLINES for your query, and you can take it to another database and you will be runing the query ander same environment, of course, if your target database is on better server..you will not be get improved performance cos the statistics remains exactly as you generated.
Best luck.
-
In Test
CLUSTERING_FACTOR = 267123
but missing in the production. Why? Compare these two.
-
what is the result after running the analyse?
-
Production (the new index seems to work well):
PHP Code:
HEIGHT BLOCKS NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ------------------ ------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
3 1024 METER_READING_PK 347097 885 6247746 7992 884 7 25913 8028 0 0 347097 1 7129116 6295083 89 1 4 956 21424 5 0
HEIGHT BLOCKS NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ------- --------------------- ------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
3 2176 METER_READING_IND_01 347214 1985 14064437 7996 1984 13 61099 8028 0 0 346112 3 15976424 14125536 89 1.00318394 4.00159197 0 0 5 40
1 SELECT
2 MAX(rcpt_dt)
3 FROM METER_READING
4 WHERE
5 org_id = 'ME' AND
6 svc_type_cd = 'E' AND
7 cust_id = '062141268001' AND
8 prem_seq_nbr = 1 AND
9 meter_seq_nbr = 1 AND
10 rdng_nbr >= 0
11* AND rcpt_dt > SYSDATE-60
10:10:13 platform@kop1> /
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=31)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'METER_READING_IND_01' (NON-UNIQUE) (Cost=8 Card=47 Bytes=1457)
10:10:16 platform@kop1> SELECT /*+ index (METER_READING METER_READING_PK)*/
10:20:55 2 MAX(rcpt_dt)
10:20:55 3 FROM METER_READING
10:20:55 4 WHERE
10:20:55 5 org_id = 'ME' AND
10:20:55 6 svc_type_cd = 'E' AND
10:20:55 7 cust_id = '062141268001' AND
10:20:55 8 prem_seq_nbr = 1 AND
10:20:55 9 meter_seq_nbr = 1 AND
10:20:55 10 rdng_nbr >= 0
10:20:55 11 AND rcpt_dt > SYSDATE-60;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=737 Card=1 Bytes=31)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'METER_READING' (Cost=737 Card=47 Bytes=1457)
3 2 INDEX (RANGE SCAN) OF 'METER_READING_PK' (UNIQUE) (Cost=5 Card=949)
10:20:57 platform@kop1> edit
Wrote file afiedt.buf
1 SELECT /*+ FULL (METER_READING)*/
2 MAX(rcpt_dt)
3 FROM METER_READING
4 WHERE
5 org_id = 'ME' AND
6 svc_type_cd = 'E' AND
7 cust_id = '062141268001' AND
8 prem_seq_nbr = 1 AND
9 meter_seq_nbr = 1 AND
10 rdng_nbr >= 0
11* AND rcpt_dt > SYSDATE-60
10:21:13 platform@kop1> /
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=273 Card=1 Bytes=31)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'METER_READING' (Cost=273 Card=47 Bytes=1457)
Copy of Production:
PHP Code:
10:08:46 prod_copy@kod1> SELECT /*+ index (METER_READING METER_READING_PK */
10:09:08 2 MAX(rcpt_dt)
10:09:08 3 FROM METER_READING
10:09:08 4 WHERE
10:09:08 5 org_id = 'ME' AND
10:09:08 6 svc_type_cd = 'E' AND
10:09:08 7 cust_id = '062141268001' AND
10:09:08 8 prem_seq_nbr = 1 AND
10:09:08 9 meter_seq_nbr = 1 AND
10:09:08 10 rdng_nbr >= 0
10:09:08 11 AND rcpt_dt > SYSDATE-60;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=750 Card=1 Bytes=24)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'METER_READING' (Cost=750 Card=47 Bytes=1128)
3 2 INDEX (RANGE SCAN) OF 'METER_READING_PK' (UNIQUE) (Cost=5 Card=950)
10:09:09 prod_copy@kod1> edit
Wrote file afiedt.buf
1 SELECT
2 MAX(rcpt_dt)
3 FROM METER_READING
4 WHERE
5 org_id = 'ME' AND
6 svc_type_cd = 'E' AND
7 cust_id = '062141268001' AND
8 prem_seq_nbr = 1 AND
9 meter_seq_nbr = 1 AND
10 rdng_nbr >= 0
11* AND rcpt_dt > SYSDATE-60
10:09:59 prod_copy@kod1> /
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=24)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'METER_READING_IND_01' (NON-UNIQUE) (Cost=8 Card=47 Bytes=1128)
10:13:58 prod_copy@kod1> edit
Wrote file afiedt.buf
1 SELECT /*+ FULL (meter_reading)*/
2 MAX(rcpt_dt)
3 FROM METER_READING
4 WHERE
5 org_id = 'ME' AND
6 svc_type_cd = 'E' AND
7 cust_id = '062141268001' AND
8 prem_seq_nbr = 1 AND
9 meter_seq_nbr = 1 AND
10 rdng_nbr >= 0
11* AND rcpt_dt > SYSDATE-60
10:15:15 prod_copy@kod1> /
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=267 Card=1 Bytes=24)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'METER_READING' (Cost=267 Card=47 Bytes=1128)
- Cookies
-
here is another example of where I get confused.
As you see the CBO wants to use a FTS but using the PK looks to me to show better performance.
PHP Code:
SELECT /* PerformanceDetailDataAccess */
end_dt AS intvl_end_dt,
baseline_nbr AS adj_base,
demand_nbr AS actual_demand,
reduction_nbr AS reduction,
intvl_part_nbr AS part_count,
intvl_over_nbr AS over_count
FROM dr_agg_interval WHERE
org_id = 'LT' AND
event_id = '03111100' AND
end_dt > SYSDATE-360
ORDER BY end_dt ASC
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 16 0 20
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.01 0.01 0 16 0 20
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 48
Rows Row Source Operation
------- ---------------------------------------------------
20 SORT ORDER BY (cr=16 r=0 w=0 time=3856 us)
20 TABLE ACCESS FULL DR_AGG_INTERVAL (cr=16 r=0 w=0 time=3495 us)
*************************************************************
SELECT /* PerformanceDetailDataAccess */
/*+ index (dr_agg_interval dr_agg_interval_pk)*/
end_dt AS intvl_end_dt,
baseline_nbr AS adj_base,
demand_nbr AS actual_demand,
reduction_nbr AS reduction,
intvl_part_nbr AS part_count,
intvl_over_nbr AS over_count
FROM dr_agg_interval WHERE
org_id = 'LT' AND
event_id = '03111100' AND
end_dt > SYSDATE-360
ORDER BY end_dt ASC
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 4 0 20
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.01 0.00 0 4 0 20
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 48
Rows Row Source Operation
------- ---------------------------------------------------
20 SORT ORDER BY (cr=4 r=0 w=0 time=1243 us)
20 TABLE ACCESS BY INDEX ROWID DR_AGG_INTERVAL (cr=4 r=0 w=0 time=1011 us)
20 INDEX RANGE SCAN DR_AGG_INTERVAL_PK (cr=3 r=0 w=0 time=919 us)(object id 40236)
*******************************************************
- Cookies
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote