Mark A. Parsons
2009-11-06 18:17:47 UTC
You've got statement cache enabled (statement cache > 0; literal autoparam = 1).
The issue you're seeing can occur if 2 statements have the same hash key, but could generate different query plans based
on different literal values. The problem arises when the first instance of the query generates a plan that is good for
its own literal values, but said plan is 'bad' when re-used by a follow-on query's with different literal values.
One example where this can occur is when different sets of literal values have greatly diverging statistics, eg, from
the ID_XACT_CASH table we see:
XACT_NUM_REF = 0 => 66.7% of the table is 0's
XACT_NUM_REF != 0 => non-zero values appear to be unique
----------------
Tracking down this kind of problem is a bit of a hit-or-miss situation since you need to determine the literal values
that were used to create the initial query plan. This typically requires monitoring via monSysSQLText or auditing.
Possible workarounds include:
- disabling statement cache (at the dataserver or session level); the idea is to force the optimizer to generate a new
query plan for each set of literal values; obviously this eliminates the benefit of using statement cache
- recode the problematic query(s) to have a slightly different format (eg, re-order table names, re-order WHERE clauses,
use different table aliases) when a known set of literal values would require a different query plan; the objective is
to insure the generation of different 'good' query plans while benefitting from the use of statement cache; the downside
is that the developer needs to understand when to differentiate their coding style based on different literal values.
----------------
Some additional questions:
1 - For the 'good' query plan you cut off the first few lines of the output. I would expect to see a line of text like
one of the following:
-- the following is generated the first time a hashed query
-- is seen, ie, it does not have a match in statement cache;
--
-- in this case a *new* entry is added to statement cache and
-- a *new* query plan is generated
STEP 1
The type of query is EXECUTE.
Executing a newly cached statement (SSQL_ID = 407371617).
-- the following is generated when a hashed query finds a match
-- in statement cache;
--
-- in this case a *previously* cached statement is matched and
-- its corresponding query plan is re-used
OR
STEP 1
The type of query is EXECUTE.
Executing a previously cached statement (SSQL_ID = 439371731).
At this point I'm trying to figure out if the 'bad' and 'good' query plans are using the same cached statement (ie, do
they have the same SSQL_ID value?). [My guess would be 'no' due to the different query plans.]
If you happen to be capturing MDA pipe table data you may be able to track down the SQL of the 'first' query that
generated the 'bad' query plan.
Consider:
monCachedProcedures.ObjectID = SSQL_ID
monCachedStatement.SSQLID = SSQL_ID
monSysStatement.ProcedureID = SSQL_ID
Look for SSQL_ID values in monSysStatement with the oldest StartTime value. Then use the associated [SPID/]KPID/BatchID
tuple to find the matching monSysSQLText entry(s).
2 - What query plan should the 'bad' query be running under? this requires either flushing the 'bad' query plan (and
statement) from statement cache, or disabling statement cache at the session level; for this case we'll just disable
statement cache. At this point I think it would probably be a good idea to run a couple different queries with
statement cache disabled so that we can get a better idea of what's going on so, could you run the following and post
back here with the results:
NOTE: I'm assuming you have not updated statistics on ID_XACT_CASH, ie, optdiag still shows stats as having been
updated on Oct 11th. The issue here is being able to run a copy of your query with an out-of-bounds value.
==============================
set showplan on
set statistics io on
set statement_cache off
go
-- XACT_NUM_REF within histogram bounds
-- and unique (?)
select SETTLE_DAT from ID_XACT_CASH
where XACTC_NUM =
(select max(XACTC_NUM)
from ID_XACT_CASH where XACT_NUM_REF = 23966703)
go
-- XACT_NUM_REF outside of histogram bounds
-- but still unique (?)
select SETTLE_DAT from ID_XACT_CASH
where XACTC_NUM =
(select max(XACTC_NUM)
from ID_XACT_CASH where XACT_NUM_REF = 28208047)
go
-- XACT_NUM_REF within histogram bounds
-- but not unique; '0' represents 66.7%
-- of the records in the table and
-- probably generates a different query plan
select SETTLE_DAT from ID_XACT_CASH
where XACTC_NUM =
(select max(XACTC_NUM)
from ID_XACT_CASH where XACT_NUM_REF = 0)
go
==============================
The idea is to get an idea of the different query plans that can be generated for the desired query given different
literal values. Hopefully one of these will match up with the 'bad' query plan you've seen in use.
The issue you're seeing can occur if 2 statements have the same hash key, but could generate different query plans based
on different literal values. The problem arises when the first instance of the query generates a plan that is good for
its own literal values, but said plan is 'bad' when re-used by a follow-on query's with different literal values.
One example where this can occur is when different sets of literal values have greatly diverging statistics, eg, from
the ID_XACT_CASH table we see:
XACT_NUM_REF = 0 => 66.7% of the table is 0's
XACT_NUM_REF != 0 => non-zero values appear to be unique
----------------
Tracking down this kind of problem is a bit of a hit-or-miss situation since you need to determine the literal values
that were used to create the initial query plan. This typically requires monitoring via monSysSQLText or auditing.
Possible workarounds include:
- disabling statement cache (at the dataserver or session level); the idea is to force the optimizer to generate a new
query plan for each set of literal values; obviously this eliminates the benefit of using statement cache
- recode the problematic query(s) to have a slightly different format (eg, re-order table names, re-order WHERE clauses,
use different table aliases) when a known set of literal values would require a different query plan; the objective is
to insure the generation of different 'good' query plans while benefitting from the use of statement cache; the downside
is that the developer needs to understand when to differentiate their coding style based on different literal values.
----------------
Some additional questions:
1 - For the 'good' query plan you cut off the first few lines of the output. I would expect to see a line of text like
one of the following:
-- the following is generated the first time a hashed query
-- is seen, ie, it does not have a match in statement cache;
--
-- in this case a *new* entry is added to statement cache and
-- a *new* query plan is generated
STEP 1
The type of query is EXECUTE.
Executing a newly cached statement (SSQL_ID = 407371617).
-- the following is generated when a hashed query finds a match
-- in statement cache;
--
-- in this case a *previously* cached statement is matched and
-- its corresponding query plan is re-used
OR
STEP 1
The type of query is EXECUTE.
Executing a previously cached statement (SSQL_ID = 439371731).
At this point I'm trying to figure out if the 'bad' and 'good' query plans are using the same cached statement (ie, do
they have the same SSQL_ID value?). [My guess would be 'no' due to the different query plans.]
If you happen to be capturing MDA pipe table data you may be able to track down the SQL of the 'first' query that
generated the 'bad' query plan.
Consider:
monCachedProcedures.ObjectID = SSQL_ID
monCachedStatement.SSQLID = SSQL_ID
monSysStatement.ProcedureID = SSQL_ID
Look for SSQL_ID values in monSysStatement with the oldest StartTime value. Then use the associated [SPID/]KPID/BatchID
tuple to find the matching monSysSQLText entry(s).
2 - What query plan should the 'bad' query be running under? this requires either flushing the 'bad' query plan (and
statement) from statement cache, or disabling statement cache at the session level; for this case we'll just disable
statement cache. At this point I think it would probably be a good idea to run a couple different queries with
statement cache disabled so that we can get a better idea of what's going on so, could you run the following and post
back here with the results:
NOTE: I'm assuming you have not updated statistics on ID_XACT_CASH, ie, optdiag still shows stats as having been
updated on Oct 11th. The issue here is being able to run a copy of your query with an out-of-bounds value.
==============================
set showplan on
set statistics io on
set statement_cache off
go
-- XACT_NUM_REF within histogram bounds
-- and unique (?)
select SETTLE_DAT from ID_XACT_CASH
where XACTC_NUM =
(select max(XACTC_NUM)
from ID_XACT_CASH where XACT_NUM_REF = 23966703)
go
-- XACT_NUM_REF outside of histogram bounds
-- but still unique (?)
select SETTLE_DAT from ID_XACT_CASH
where XACTC_NUM =
(select max(XACTC_NUM)
from ID_XACT_CASH where XACT_NUM_REF = 28208047)
go
-- XACT_NUM_REF within histogram bounds
-- but not unique; '0' represents 66.7%
-- of the records in the table and
-- probably generates a different query plan
select SETTLE_DAT from ID_XACT_CASH
where XACTC_NUM =
(select max(XACTC_NUM)
from ID_XACT_CASH where XACT_NUM_REF = 0)
go
==============================
The idea is to get an idea of the different query plans that can be generated for the desired query given different
literal values. Hopefully one of these will match up with the 'bad' query plan you've seen in use.
Good afternoon,
We used to work on Sybase ASE 12.5 and now we've started
upgrading our application on ASE15.
During our test I notice a query which sometimes works fine
but sometines is taking to much time (more than 10 mins)
select SETTLE_DAT from ID_XACT_CASH where XACTC_NUM =
(select max(XACTC_NUM) from ID_XACT_CASH where XACT_NUM_REF
= 28208047)
On this table, I have an index, let's say ind1, on
XACT_NUM_REF and another index, let's ind2, on XACTC_NUM
running this query on ASE12.5, I have no problem and the
showplan shows us that we always use ind1
running this query on ASE15, sometimes it uses ind1 (I
notice that it uses this inde when the value we search is in
the histogram) and the query works fine but sometime it uses
ind2 and it takes ages because it seems to scan the index.
I don't understand this behavior and I'd appreciate any
advices
thanks
Simon
ps:find attached the query plan, optdiag
------------------------------------------------------------------------
THIS SHOWPLAN IS GOOD
IT'S USED WHEN "XACT_NUM_REF = <VALUE IN HISTOGRAM>"
2> select SETTLE_DAT from ID_XACT_CASH
3> where XACTC_NUM = (select max(XACTC_NUM) from ID_XACT_CASH where XACT_NUM_REF =23966703)
4> go
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SET OPTION OFF.
QUERY PLAN FOR STATEMENT 2 (at line 2).
STEP 1
The type of query is SELECT.
4 operator(s) under root
|ROOT:EMIT Operator (VA = 4)
|
| |SEQUENCER Operator (VA = 3) has 2 children.
| |
| | |SCALAR AGGREGATE Operator (VA = 1)
| | | Evaluate Ungrouped MAXIMUM AGGREGATE.
| | |
| | | |SCAN Operator (VA = 0)
| | | | FROM TABLE
| | | | ID_XACT_CASH
| | | | Index : X4_ID_XACT_CASH
| | | | Forward Scan.
| | | | Positioning by key.
| | | | XACT_NUM_REF ASC
| | | | Using I/O Size 2 Kbytes for index leaf pages.
| | | | With LRU Buffer Replacement Strategy for index leaf pages.
| | | | Using I/O Size 2 Kbytes for data pages.
| | | | With LRU Buffer Replacement Strategy for data pages.
| |
| | |SCAN Operator (VA = 2)
| | | FROM TABLE
| | | KBC..ID_XACT_CASH
| | | Index : X1_ID_XACT_CASH
| | | Forward Scan.
| | | Positioning by key.
| | | XACTC_NUM ASC
| | | Using I/O Size 2 Kbytes for index leaf pages.
| | | With LRU Buffer Replacement Strategy for index leaf pages.
| | | Using I/O Size 2 Kbytes for data pages.
| | | With LRU Buffer Replacement Strategy for data pages.
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
Total writes for this command: 0
Execution Time 0.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.
SETTLE_DAT
--------------------------
Jan 28 2009 12:00AM
==================== Lava Operator Tree ====================
Emit
(VA = 4)
r:1 er:2
cpu: 0
/
Sequencer
(VA = 3)
r:1 er:2
/ \
ScalarAgg IndexScan
Max X1_ID_XACT_CASH
(VA = 1) (VA = 2)
r:1 er:1 r:1 er:2
cpu: 0 l:5 el:6
p:3 ep:6
/
IndexScan
X4_ID_XACT_CASH
(VA = 0)
r:1 er:2
l:5 el:6
p:5 ep:6
============================================================
Table: ID_XACT_CASH scan count 1, logical reads: (regular=5 apf=0 total=5), physical reads: (regular=5 apf=0 total=5), apf IOs used=0
Table: KBC..ID_XACT_CASH scan count 1, logical reads: (regular=5 apf=0 total=5), physical reads: (regular=3 apf=0 total=3), apf IOs used=1
Total writes for this command: 0
Execution Time 0.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 113 ms.
(1 row affected)
*****************************************************************************************************************************************
*****************************************************************************************************************************************
*****************************************************************************************************************************************
THIS SHOWPLAN ISN'T GOOD
select SETTLE_DAT from ID_XACT_CASH
where XACTC_NUM = (select max(XACTC_NUM) from ID_XACT_CASH where XACT_NUM_REF = 28208047)
go
Total writes for this command: 0
Total writes for this command: 0
Execution Time 0.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
Total writes for this command: 0
Execution Time 0.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
Total writes for this command: 0
Execution Time 0.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.
QUERY PLAN FOR STATEMENT 1 (at line 2).
STEP 1
The type of query is EXECUTE.
Executing a previously cached statement (SSQL_ID = 106302417).
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
QUERY PLAN FOR STATEMENT 1 (at line 0).
STEP 1
The type of query is DECLARE.
QUERY PLAN FOR STATEMENT 2 (at line 1).
STEP 1
The type of query is SELECT.
5 operator(s) under root
|ROOT:EMIT Operator (VA = 5)
|
| |SEQUENCER Operator (VA = 4) has 2 children.
| |
| | |SCALAR AGGREGATE Operator (VA = 2)
| | | Evaluate Ungrouped MAXIMUM AGGREGATE.
| | | Scanning only up to the first qualifying row.
| | |
| | | |RESTRICT Operator (VA = 1)(3)(0)(0)(0)(0)
| | | |
| | | | |SCAN Operator (VA = 0)
| | | | | FROM TABLE
| | | | | ID_XACT_CASH
| | | | | Index : X1_ID_XACT_CASH
| | | | | Backward scan.
| | | | | Positioning at index end.
| | | | | Using I/O Size 2 Kbytes for index leaf pages.
| | | | | With LRU Buffer Replacement Strategy for index leaf pages.
| | | | | Using I/O Size 2 Kbytes for data pages.
| | | | | With LRU Buffer Replacement Strategy for data pages.
| |
| | |SCAN Operator (VA = 3)
| | | FROM TABLE
| | | ID_XACT_CASH
| | | Index : X1_ID_XACT_CASH
| | | Forward Scan.
| | | Positioning by key.
| | | XACTC_NUM ASC
| | | Using I/O Size 2 Kbytes for index leaf pages.
| | | With LRU Buffer Replacement Strategy for index leaf pages.
| | | Using I/O Size 2 Kbytes for data pages.
| | | With LRU Buffer Replacement Strategy for data pages.
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
==================== Lava Operator Tree ====================
Emit
(VA = 5)
r:0 er:2
cpu: 0
/
Sequencer
(VA = 4)
r:0 er:2
/ \
ScalarAgg IndexScan
Max X1_ID_XACT_CASH
(VA = 2) (VA = 3)
r:1 er:1 r:0 er:2
cpu: 88400 l:0 el:6
p:0 ep:6
/
Restrict
(3)(0)(0)(0)(0)
(VA = 1)
r:0 er:1.943e+06
/
IndexScan
X1_ID_XACT_CASH
(VA = 0)
r:0 er:1.943e+06
l:7.184e+06 el:6
p:685388 ep:6
============================================================
(regular=159918 apf=525470 total=685388), apf IOs used=520206
Table: ID_XACT_CASH scan count 1, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0),
apf IOs used=0
Total writes for this command: 5
Execution Time 884.
Adaptive Server cpu time: 88400 ms. Adaptive Server elapsed time: 1170406 ms.
SETTLE_DAT
--------------------------
(0 rows affected)
Execution Time 0.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 1170406 ms.
*****************************************************************************************************************************************
*****************************************************************************************************************************************
*****************************************************************************************************************************************
HERE'S THE OPTDIAG FOR THIS TABLE
OptDiag/15.0.3/EBF 16548 ESD#1/P/Sun_svr4/OS 5.8/ase1503/2680/64-bit/OPT/Thu Mar 5 00:49:21 2009
Adaptive Server Enterprise/15.0.3/EBF 16548 ESD#1/P/Sun_svr4/OS 5.8/ase1503/2680/64-bit/FBO/Thu Mar 5 09:39:28 2009
Server name: "CLIENT1500"
Specified database: "xxx"
Specified table owner: not specified
Specified table: "ID_XACT_CASH"
Specified column: not specified
Table owner: "dbo"
Table name: "ID_XACT_CASH"
Statistics for table: "ID_XACT_CASH"
Data page count: 1372969
Empty data page count: 460
Data row count: 8224010.0000000000000000
Forwarded row count: 7.0000000000000000
Deleted row count: 15254.0000000000000000
Data page CR count: 142389.0000000000000000
OAM + allocation page count: 21451
First extent data pages: 2523
Data row size: 285.9223783223507667
Parallel join degree: 0.0000000000000000
Unused page count: 7865
OAM page count: 86
Data page cluster ratio: 0.9999900000000000
Space utilization: 0.8554743111926301
Large I/O efficiency: 0.9999300048996574
Statistics for index: "X9_ID_XACT_CASH" (nonclustered)
Index column list: "XACT_DAT"
Leaf count: 28603
Empty leaf page count: 0
Data page CR count: 408677.0000000000000000
Index page CR count: 2355.0000000000000000
Data row CR count: 733626.0000000000000000
First extent leaf pages: 880
Leaf row size: 6.0378037737678119
Index height: 3
Data page cluster ratio: 0.5781362587467953
Index page cluster ratio: 0.9999900000000000
Data row cluster ratio: 0.9999900000000000
Space utilization: 0.8671354973297203
Large I/O efficiency: 0.9999300048996574
Statistics for index: "X8_ID_XACT_CASH" (nonclustered)
Index column list: "SETTLE_DAT"
Leaf count: 28585
Empty leaf page count: 0
Data page CR count: 327911.0000000000000000
Index page CR count: 2423.0000000000000000
Data row CR count: 606500.0000000000000000
First extent leaf pages: 524
Leaf row size: 6.0378167480989546
Index height: 3
Data page cluster ratio: 0.6405272487739604
Index page cluster ratio: 0.9999900000000000
Data row cluster ratio: 0.9999900000000000
Space utilization: 0.8676833978429003
Large I/O efficiency: 0.9999300048996574
Statistics for index: "X7_ID_XACT_CASH" (nonclustered)
Index column list: "EXT_REF"
Leaf count: 43891
Empty leaf page count: 0
Data page CR count: 189222.0000000000000000
Index page CR count: 5237.0000000000000000
Data row CR count: 1175901.0000000000000000
First extent leaf pages: 1160
Leaf row size: 9.8068727969646918
Index height: 3
Data page cluster ratio: 0.9824172738659694
Index page cluster ratio: 0.9999900000000000
Data row cluster ratio: 0.9999900000000000
Space utilization: 0.9178561516286179
Large I/O efficiency: 0.9999300048996574
Statistics for index: "X6_ID_XACT_CASH" (nonclustered)
Index column list: "CONT_REF_NUM", "ID_ORDER", "BUS_STATE"
Leaf count: 30315
Empty leaf page count: 7
Data page CR count: 164003.0000000000000000
Index page CR count: 5955.0000000000000000
Data row CR count: 992804.0000000000000000
First extent leaf pages: 1768
Leaf row size: 6.6140383731755463
Index height: 3
Data page cluster ratio: 0.9999900000000000
Index page cluster ratio: 0.9183442660031667
Data row cluster ratio: 0.9999900000000000
Space utilization: 0.8962490210001021
Large I/O efficiency: 0.6362982153137592
Statistics for index: "X5_ID_XACT_CASH" (nonclustered)
Index column list: "EVT_REF_NUM"
Leaf count: 27536
Empty leaf page count: 0
Data page CR count: 155775.0000000000000000
Index page CR count: 5022.0000000000000000
Data row CR count: 1041164.0000000000000000
First extent leaf pages: 1155
Leaf row size: 6.2148671636569892
Index height: 3
Data page cluster ratio: 0.9999900000000000
Index page cluster ratio: 0.9344235079272848
Data row cluster ratio: 0.9999900000000000
Space utilization: 0.9271511772398635
Large I/O efficiency: 0.6853843090402230
Statistics for index: "X4_ID_XACT_CASH" (nonclustered)
Index column list: "XACT_NUM_REF"
Leaf count: 39589
Empty leaf page count: 0
Data page CR count: 172107.0000000000000000
Index page CR count: 3782.0000000000000000
Data row CR count: 1105876.0000000000000000
First extent leaf pages: 707
Leaf row size: 8.6771712237168810
Index height: 3
Data page cluster ratio: 0.9994177540074900
Index page cluster ratio: 0.9999900000000000
Data row cluster ratio: 0.9999900000000000
Space utilization: 0.9003745113857053
Large I/O efficiency: 0.9999300048996574
Statistics for index: "X3_ID_XACT_CASH" (nonclustered)
Index column list: "BUS_PROC_NUM", "BUS_NUM"
Leaf count: 74928
Empty leaf page count: 0
Data page CR count: 1385310.0000000000000000
Index page CR count: 3690.0000000000000000
Data row CR count: 1840980.0000000000000000
First extent leaf pages: 388
Leaf row size: 16.5611851096783873
Index height: 3
Data page cluster ratio: 0.2729515753104227
Index page cluster ratio: 0.9999900000000000
Data row cluster ratio: 0.9316241037739429
Space utilization: 0.9079602329248143
Large I/O efficiency: 0.9999300048996574
Statistics for index: "X2_ID_XACT_CASH" (clustered)
Index column list: "PF_COD", "CUR_COD", "SETTLE_DAT"
Leaf count: 50520
Empty data page count: 0
Data page CR count: 139638.0000000000000000
Index page CR count: 10886.0000000000000000
Data row CR count: 473206.0000000000000000
First extent leaf pages: 2177
Leaf row size: 9.3513063315470379
Index height: 3
Data page cluster ratio: 0.9999900000000000
Index page cluster ratio: 0.8965954077593032
Data row cluster ratio: 0.9999900000000000
Space utilization: 0.7603761715689907
Large I/O efficiency: 0.5801028844387286
Statistics for index: "X1_ID_XACT_CASH" (nonclustered)
Index column list: "XACTC_NUM"
Leaf count: 66144
Empty leaf page count: 0
Data page CR count: 1500494.0000000000000000
Index page CR count: 2139.0000000000000000
Data row CR count: 1813890.0000000000000000
First extent leaf pages: 56
Leaf row size: 14.0000000000000000
Index height: 3
Data page cluster ratio: 0.1908243608002547
Index page cluster ratio: 0.9999900000000000
Data row cluster ratio: 0.9355779778699813
Space utilization: 0.8694750762058454
Large I/O efficiency: 0.9999300048996574
Statistics for column: "BUS_NUM"
Last update of column statistics: Jun 8 2008 7:41:32:483PM
Range cell density: 0.0000036267645423
Total density: 0.0000036267645423
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000004)
Unique total values: default used (0.000004)
Average column width: default used (6.00)
Histogram for column: "BUS_NUM"
Column datatype: numeric(10,0)
Requested step count: 20
Actual step count: 17
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 <= 13679
2 0.07298563 <= 7207469
3 0.07297335 <= 7538055
4 0.07297335 <= 7890791
5 0.07297320 <= 8243459
6 0.07297320 <= 8614775
7 0.09728469 <= 9127041
8 0.05661477 <= 9469095
9 0.05661433 <= 9803968
10 0.05661463 <= 10107018
11 0.05661448 <= 10431174
12 0.05661433 <= 10754550
13 0.05661448 <= 11063295
14 0.05661448 <= 11377304
15 0.05661433 <= 11687135
16 0.02836652 <= 11836837
17 0.05655425 <= 12163815
Statistics for column: "BUS_PROC_NUM"
Last update of column statistics: Oct 11 2009 6:54:04:010PM
Range cell density: 0.0000002685374071
Total density: 0.0000002685374071
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000000)
Unique total values: default used (0.000000)
Average column width: default used (6.00)
Histogram for column: "BUS_PROC_NUM"
Column datatype: numeric(10,0)
Requested step count: 20
Actual step count: 20
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 <= 14527
2 0.05292683 <= 11639590
3 0.05292658 <= 12078716
4 0.05292670 <= 12453242
5 0.05292670 <= 12863506
6 0.05240834 <= 13256743
7 0.05085497 <= 13640307
8 0.05085485 <= 14059212
9 0.05085460 <= 14456989
10 0.05085460 <= 14834182
11 0.05341300 <= 15251984
12 0.05597384 <= 15693298
13 0.05597371 <= 16133678
14 0.05597384 <= 16545171
15 0.06996837 <= 17092670
16 0.05077101 <= 17500307
17 0.05077101 <= 17906642
18 0.05077101 <= 18336666
19 0.05077076 <= 18750076
20 0.03807930 <= 19054688
Statistics for column group: "BUS_PROC_NUM", "BUS_NUM"
Last update of column statistics: Oct 11 2009 6:54:04:010PM
Range cell density: 0.0000002685374071
Total density: 0.0000002685374071
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000000)
Unique total values: default used (0.000000)
Average column width: default used (6.00)
Statistics for column: "BUS_STATE"
Last update of column statistics: Jun 8 2008 7:40:40:426PM
Range cell density: 0.0000000000000000
Total density: 0.3210454526881976
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000000)
Unique total values: default used (0.321045)
Average column width: default used (3.00)
Histogram for column: "BUS_STATE"
Column datatype: varchar(3)
Requested step count: 20
Actual step count: 7
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 < "OP"
2 0.44702742 = "OP"
3 0.00000000 < "OPC"
4 0.27648506 = "OPC"
5 0.19545075 <= "OPD"
6 0.00000000 < "OPU"
7 0.08103678 = "OPU"
Statistics for column: "CONT_REF_NUM"
Last update of column statistics: Oct 11 2009 6:49:54:900PM
Range cell density: 0.0000004180041736
Total density: 0.3047613429921103
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000000)
Unique total values: default used (0.304761)
Average column width: default used (6.00)
Histogram for column: "CONT_REF_NUM"
Column datatype: numeric(10,0)
Requested step count: 20
Actual step count: 6
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 < 0
2 0.96720958 = 0
3 0.01008852 <= 390922
4 0.01008497 <= 441745
5 0.01008522 <= 495947
6 0.00253171 <= 510491
Statistics for column group: "CONT_REF_NUM", "ID_ORDER"
Last update of column statistics: Oct 11 2009 6:49:54:900PM
Range cell density: 0.0000002890580522
Total density: 0.3047613387639130
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000000)
Unique total values: default used (0.304761)
Average column width: default used (6.00)
Statistics for column group: "CONT_REF_NUM", "ID_ORDER", "BUS_STATE"
Last update of column statistics: Oct 11 2009 6:49:54:900PM
Range cell density: 0.0000001735188990
Total density: 0.3047613349753358
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000000)
Unique total values: default used (0.304761)
Average column width: default used (3.00)
Statistics for column: "CUR_COD"
Last update of column statistics: Jun 8 2008 7:43:14:536PM
Range cell density: 0.0115828804789111
Total density: 0.4402907520392507
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.011583)
Unique total values: default used (0.440291)
Average column width: default used (3.00)
Histogram for column: "CUR_COD"
Column datatype: varchar(3)
Requested step count: 20
Actual step count: 13
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 <= " ÿÿ"
2 0.00000117 <= " "
3 0.07281137 <= "CZK"
4 0.02081929 < "EUR"
5 0.64543837 = "EUR"
6 0.05919547 <= "JPY"
7 0.00580888 <= "NZ9"
8 0.02473281 <= "PLN"
9 0.01450210 <= "SKK"
10 0.01553698 <= "US9"
11 0.00000000 < "USD"
12 0.13908349 = "USD"
13 0.00207006 <= "ZAR"
Statistics for column: "EVT_REF_NUM"
Last update of column statistics: Oct 11 2009 6:51:08:910PM
Range cell density: 0.0000707704887416
Total density: 0.4394391045589158
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000071)
Unique total values: default used (0.439439)
Average column width: default used (6.00)
Histogram for column: "EVT_REF_NUM"
Column datatype: numeric(10,0)
Requested step count: 20
Actual step count: 16
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 < 0
2 0.66288406 = 0
3 0.01974253 <= 155052
4 0.02001927 <= 203652
5 0.02018353 <= 256361
6 0.01990862 <= 258395
7 0.02673373 <= 351015
8 0.02569444 <= 407719
9 0.02562602 <= 435411
10 0.02562418 <= 479433
11 0.02562394 <= 509601
12 0.02562687 <= 540052
13 0.02570570 <= 574278
14 0.02562736 <= 603719
15 0.02562382 <= 634062
16 0.02537595 <= 683586
Statistics for column: "EXT_REF"
Last update of column statistics: Oct 11 2009 6:48:20:913PM
Range cell density: 0.0000060452573327
Total density: 0.5131813538438641
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000006)
Unique total values: default used (0.513181)
Average column width: default used (30.00)
Histogram for column: "EXT_REF"
Column datatype: varchar(30)
Requested step count: 20
Actual step count: 13
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 < " "
2 0.71636558 = " "
3 0.01614545 <= "021130000854564"
4 0.02300129 <= "08161C004284"
5 0.02737523 <= "1"
6 0.02716531 <= "CP22553662_DIVCOR"
7 0.02716543 <= "OS21851266DIVREC"
8 0.02716543 <= "TEMPA_S_LU0003298733_20080318"
9 0.02716531 <= "TEMPDAT021130000803660"
10 0.02716531 <= "TEMPDAT_1463-103338-0002"
11 0.02716543 <= "TEMPDAT_2451-38979-0001"
12 0.02716531 <= "TEMPU 0021130000857723"
13 0.02695491 <= "YIDF00005 BEL"
Statistics for column: "ID_ORDER"
Last update of column statistics: Jun 8 2008 7:42:20:546PM
Range cell density: 0.0007211579226208
Total density: 0.9359033575710457
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000721)
Unique total values: default used (0.935903)
Average column width: default used (6.00)
Histogram for column: "ID_ORDER"
Column datatype: numeric(10,0)
Requested step count: 20
Actual step count: 6
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 < 0
2 0.96716577 = 0
3 0.00000000 < 1
4 0.01569575 = 1
5 0.01569619 <= 2
6 0.00144232 <= 4
Statistics for column: "PF_COD"
Last update of column statistics: Oct 11 2009 6:44:14:813PM
Range cell density: 0.0013343939688789
Total density: 0.0013343939688789
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.001334)
Unique total values: default used (0.001334)
Average column width: default used (12.00)
Histogram for column: "PF_COD"
Column datatype: varchar(12)
Requested step count: 20
Actual step count: 19
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 <= "000053ÿÿÿÿÿÿ"
2 0.05681435 <= "10059537"
3 0.05677176 <= "300092"
4 0.05019132 <= "300200"
5 0.05780162 <= "30060163"
6 0.05123625 <= "300812"
7 0.05124457 <= "30112409"
8 0.06030004 <= "482354"
9 0.06035133 <= "500344"
10 0.05090920 <= "600065"
11 0.06325598 <= "600932"
12 0.05861497 <= "700245"
13 0.05511496 <= "700623"
14 0.05527898 <= "700778"
15 0.05227162 <= "700849"
16 0.05864948 <= "900919"
17 0.05744972 <= "CSOBAMPRIV"
18 0.05756099 <= "MILAV"
19 0.04618287 <= "~SIMU00071"
Statistics for column group: "PF_COD", "CUR_COD"
Last update of column statistics: Oct 11 2009 6:44:14:813PM
Range cell density: 0.0006717721935544
Total density: 0.0006717721935544
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000672)
Unique total values: default used (0.000672)
Average column width: default used (3.00)
Statistics for column group: "PF_COD", "CUR_COD", "SETTLE_DAT"
Last update of column statistics: Oct 11 2009 6:44:14:813PM
Range cell density: 0.0000090754174290
Total density: 0.0000090754174290
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000009)
Unique total values: default used (0.000009)
Average column width: default used (8.00)
Statistics for column: "SETTLE_DAT"
Last update of column statistics: Oct 11 2009 6:46:51:910PM
Range cell density: 0.0029695237519826
Total density: 0.0035121512259891
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.002970)
Unique total values: default used (0.003512)
Average column width: default used (8.00)
Histogram for column: "SETTLE_DAT"
Column datatype: datetime
Requested step count: 20
Actual step count: 20
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 <= "Jul 29 2002 11:59:59:996PM"
2 0.06219992 <= "Nov 27 2007 12:00:00:000AM"
3 0.06168927 <= "Jan 10 2008 12:00:00:000AM"
4 0.05600284 <= "Feb 29 2008 12:00:00:000AM"
5 0.05663430 <= "Apr 14 2008 12:00:00:000AM"
6 0.05238313 <= "May 26 2008 12:00:00:000AM"
7 0.05521667 <= "Jul 2 2008 12:00:00:000AM"
8 0.06318512 <= "Aug 18 2008 12:00:00:000AM"
9 0.05147235 <= "Sep 23 2008 12:00:00:000AM"
10 0.04563660 <= "Oct 24 2008 12:00:00:000AM"
11 0.04529253 <= "Nov 25 2008 12:00:00:000AM"
12 0.05899832 <= "Jan 9 2009 12:00:00:000AM"
13 0.05678840 <= "Feb 28 2009 12:00:00:000AM"
14 0.05214322 <= "Apr 7 2009 12:00:00:000AM"
15 0.04678544 <= "May 8 2009 12:00:00:000AM"
16 0.05665829 <= "Jun 23 2009 12:00:00:000AM"
17 0.05791117 <= "Aug 3 2009 12:00:00:000AM"
18 0.05272830 <= "Sep 15 2009 12:00:00:000AM"
19 0.05136281 <= "Oct 23 2009 12:00:00:000AM"
20 0.01691131 <= "Aug 9 8930 12:00:00:000AM"
Statistics for column: "XACTC_NUM"
Last update of column statistics: Oct 11 2009 6:55:14:000PM
Range cell density: 0.0000001223993504
Total density: 0.0000001223993504
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000000)
Unique total values: default used (0.000000)
Average column width: default used (6.00)
Histogram for column: "XACTC_NUM"
Column datatype: numeric(10,0)
Requested step count: 20
Actual step count: 20
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 <= 20062
2 0.05210210 <= 17035148
3 0.05210198 <= 17680784
4 0.05210198 <= 18275660
5 0.05210198 <= 18869950
6 0.05189353 <= 19477977
7 0.05126427 <= 20052776
8 0.05126427 <= 20688614
9 0.05126427 <= 21301556
10 0.05126427 <= 21897237
11 0.05403430 <= 22563557
12 0.05680211 <= 23225034
13 0.05680211 <= 23860509
14 0.05680211 <= 24518212
15 0.07100411 <= 25312589
16 0.05035717 <= 25903400
17 0.05035705 <= 26486884
18 0.05035705 <= 27071780
19 0.05035705 <= 27659515
20 0.03776828 <= 28067060
Statistics for column: "XACT_DAT"
Last update of column statistics: Oct 11 2009 6:45:50:810PM
Range cell density: 0.0028317239478099
Total density: 0.0029619684151046
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.002832)
Unique total values: default used (0.002962)
Average column width: default used (8.00)
Histogram for column: "XACT_DAT"
Column datatype: datetime
Requested step count: 20
Actual step count: 20
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 <= "Jun 1 2000 11:59:59:996PM"
2 0.06044300 <= "Nov 15 2007 12:00:00:000AM"
3 0.05556417 <= "Jan 3 2008 12:00:00:000AM"
4 0.06027654 <= "Feb 15 2008 12:00:00:000AM"
5 0.05897715 <= "Mar 31 2008 12:00:00:000AM"
6 0.06129796 <= "May 15 2008 12:00:00:000AM"
7 0.05712427 <= "Jun 27 2008 12:00:00:000AM"
8 0.05601251 <= "Aug 6 2008 12:00:00:000AM"
9 0.06052770 <= "Sep 23 2008 12:00:00:000AM"
10 0.04661347 <= "Oct 27 2008 12:00:00:000AM"
11 0.05828730 <= "Dec 10 2008 12:00:00:000AM"
12 0.05790579 <= "Jan 23 2009 12:00:00:000AM"
13 0.04548898 <= "Mar 3 2009 12:00:00:000AM"
14 0.04638715 <= "Apr 2 2009 12:00:00:000AM"
15 0.05604018 <= "May 8 2009 12:00:00:000AM"
16 0.05116966 <= "Jun 22 2009 12:00:00:000AM"
17 0.04839805 <= "Jul 23 2009 12:00:00:000AM"
18 0.05202388 <= "Sep 5 2009 12:00:00:000AM"
19 0.04701592 <= "Oct 5 2009 12:00:00:000AM"
20 0.02044632 <= "Aug 9 8930 12:00:00:000AM"
Statistics for column: "XACT_NUM_REF"
Last update of column statistics: Oct 11 2009 6:52:32:006PM
Range cell density: 0.0000001226088895
Total density: 0.4457938590878456
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000000)
Unique total values: default used (0.445794)
Average column width: default used (6.00)
Histogram for column: "XACT_NUM_REF"
Column datatype: numeric(10,0)
Requested step count: 20
Actual step count: 16
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 < 0
2 0.66767794 = 0
3 0.02062882 <= 17316868
4 0.02062870 <= 18099445
5 0.02062870 <= 18796952
6 0.02062870 <= 19637181
7 0.02780705 <= 20603921
8 0.02466665 <= 21419752
9 0.02466665 <= 22225187
10 0.02466665 <= 23017404
11 0.02466665 <= 23966703
12 0.02466665 <= 24756060
13 0.02466665 <= 25670126
14 0.02466665 <= 26513721
15 0.02466665 <= 27281638
16 0.02466690 <= 28049323
No statistics for remaining columns: "AMT_CURRENT"
(default values used) "AMT_GROSS"
"BO_ACCOUNTING"
"COMMENT_1"
"INST_NUM"
"QTY"
"REC_CRE_DAT"
"REC_CRE_GMT_DAT"
"REC_CRE_GMT_TIM"
"REC_CRE_ORIG"
"REC_CRE_TZ"
"REC_CRE_USR"
"REC_MOD_DAT"
"REC_MOD_GMT_DAT"
"REC_MOD_GMT_TIM"
"REC_MOD_ORIG"
"REC_MOD_TZ"
"REC_MOD_USR"
"STRATEGY_COD"
"TAX_CREDIT"
"WHT_AMT"
"WHT_RATE"
"XACTC_FLG"
"XACTC_SIGN"
"XACTC_TYP"
Optdiag succeeded.
We used to work on Sybase ASE 12.5 and now we've started
upgrading our application on ASE15.
During our test I notice a query which sometimes works fine
but sometines is taking to much time (more than 10 mins)
select SETTLE_DAT from ID_XACT_CASH where XACTC_NUM =
(select max(XACTC_NUM) from ID_XACT_CASH where XACT_NUM_REF
= 28208047)
On this table, I have an index, let's say ind1, on
XACT_NUM_REF and another index, let's ind2, on XACTC_NUM
running this query on ASE12.5, I have no problem and the
showplan shows us that we always use ind1
running this query on ASE15, sometimes it uses ind1 (I
notice that it uses this inde when the value we search is in
the histogram) and the query works fine but sometime it uses
ind2 and it takes ages because it seems to scan the index.
I don't understand this behavior and I'd appreciate any
advices
thanks
Simon
ps:find attached the query plan, optdiag
------------------------------------------------------------------------
THIS SHOWPLAN IS GOOD
IT'S USED WHEN "XACT_NUM_REF = <VALUE IN HISTOGRAM>"
2> select SETTLE_DAT from ID_XACT_CASH
3> where XACTC_NUM = (select max(XACTC_NUM) from ID_XACT_CASH where XACT_NUM_REF =23966703)
4> go
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SET OPTION OFF.
QUERY PLAN FOR STATEMENT 2 (at line 2).
STEP 1
The type of query is SELECT.
4 operator(s) under root
|ROOT:EMIT Operator (VA = 4)
|
| |SEQUENCER Operator (VA = 3) has 2 children.
| |
| | |SCALAR AGGREGATE Operator (VA = 1)
| | | Evaluate Ungrouped MAXIMUM AGGREGATE.
| | |
| | | |SCAN Operator (VA = 0)
| | | | FROM TABLE
| | | | ID_XACT_CASH
| | | | Index : X4_ID_XACT_CASH
| | | | Forward Scan.
| | | | Positioning by key.
| | | | XACT_NUM_REF ASC
| | | | Using I/O Size 2 Kbytes for index leaf pages.
| | | | With LRU Buffer Replacement Strategy for index leaf pages.
| | | | Using I/O Size 2 Kbytes for data pages.
| | | | With LRU Buffer Replacement Strategy for data pages.
| |
| | |SCAN Operator (VA = 2)
| | | FROM TABLE
| | | KBC..ID_XACT_CASH
| | | Index : X1_ID_XACT_CASH
| | | Forward Scan.
| | | Positioning by key.
| | | XACTC_NUM ASC
| | | Using I/O Size 2 Kbytes for index leaf pages.
| | | With LRU Buffer Replacement Strategy for index leaf pages.
| | | Using I/O Size 2 Kbytes for data pages.
| | | With LRU Buffer Replacement Strategy for data pages.
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
Total writes for this command: 0
Execution Time 0.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.
SETTLE_DAT
--------------------------
Jan 28 2009 12:00AM
==================== Lava Operator Tree ====================
Emit
(VA = 4)
r:1 er:2
cpu: 0
/
Sequencer
(VA = 3)
r:1 er:2
/ \
ScalarAgg IndexScan
Max X1_ID_XACT_CASH
(VA = 1) (VA = 2)
r:1 er:1 r:1 er:2
cpu: 0 l:5 el:6
p:3 ep:6
/
IndexScan
X4_ID_XACT_CASH
(VA = 0)
r:1 er:2
l:5 el:6
p:5 ep:6
============================================================
Table: ID_XACT_CASH scan count 1, logical reads: (regular=5 apf=0 total=5), physical reads: (regular=5 apf=0 total=5), apf IOs used=0
Table: KBC..ID_XACT_CASH scan count 1, logical reads: (regular=5 apf=0 total=5), physical reads: (regular=3 apf=0 total=3), apf IOs used=1
Total writes for this command: 0
Execution Time 0.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 113 ms.
(1 row affected)
*****************************************************************************************************************************************
*****************************************************************************************************************************************
*****************************************************************************************************************************************
THIS SHOWPLAN ISN'T GOOD
select SETTLE_DAT from ID_XACT_CASH
where XACTC_NUM = (select max(XACTC_NUM) from ID_XACT_CASH where XACT_NUM_REF = 28208047)
go
Total writes for this command: 0
Total writes for this command: 0
Execution Time 0.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
Total writes for this command: 0
Execution Time 0.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
Total writes for this command: 0
Execution Time 0.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.
QUERY PLAN FOR STATEMENT 1 (at line 2).
STEP 1
The type of query is EXECUTE.
Executing a previously cached statement (SSQL_ID = 106302417).
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
QUERY PLAN FOR STATEMENT 1 (at line 0).
STEP 1
The type of query is DECLARE.
QUERY PLAN FOR STATEMENT 2 (at line 1).
STEP 1
The type of query is SELECT.
5 operator(s) under root
|ROOT:EMIT Operator (VA = 5)
|
| |SEQUENCER Operator (VA = 4) has 2 children.
| |
| | |SCALAR AGGREGATE Operator (VA = 2)
| | | Evaluate Ungrouped MAXIMUM AGGREGATE.
| | | Scanning only up to the first qualifying row.
| | |
| | | |RESTRICT Operator (VA = 1)(3)(0)(0)(0)(0)
| | | |
| | | | |SCAN Operator (VA = 0)
| | | | | FROM TABLE
| | | | | ID_XACT_CASH
| | | | | Index : X1_ID_XACT_CASH
| | | | | Backward scan.
| | | | | Positioning at index end.
| | | | | Using I/O Size 2 Kbytes for index leaf pages.
| | | | | With LRU Buffer Replacement Strategy for index leaf pages.
| | | | | Using I/O Size 2 Kbytes for data pages.
| | | | | With LRU Buffer Replacement Strategy for data pages.
| |
| | |SCAN Operator (VA = 3)
| | | FROM TABLE
| | | ID_XACT_CASH
| | | Index : X1_ID_XACT_CASH
| | | Forward Scan.
| | | Positioning by key.
| | | XACTC_NUM ASC
| | | Using I/O Size 2 Kbytes for index leaf pages.
| | | With LRU Buffer Replacement Strategy for index leaf pages.
| | | Using I/O Size 2 Kbytes for data pages.
| | | With LRU Buffer Replacement Strategy for data pages.
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
==================== Lava Operator Tree ====================
Emit
(VA = 5)
r:0 er:2
cpu: 0
/
Sequencer
(VA = 4)
r:0 er:2
/ \
ScalarAgg IndexScan
Max X1_ID_XACT_CASH
(VA = 2) (VA = 3)
r:1 er:1 r:0 er:2
cpu: 88400 l:0 el:6
p:0 ep:6
/
Restrict
(3)(0)(0)(0)(0)
(VA = 1)
r:0 er:1.943e+06
/
IndexScan
X1_ID_XACT_CASH
(VA = 0)
r:0 er:1.943e+06
l:7.184e+06 el:6
p:685388 ep:6
============================================================
(regular=159918 apf=525470 total=685388), apf IOs used=520206
Table: ID_XACT_CASH scan count 1, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0),
apf IOs used=0
Total writes for this command: 5
Execution Time 884.
Adaptive Server cpu time: 88400 ms. Adaptive Server elapsed time: 1170406 ms.
SETTLE_DAT
--------------------------
(0 rows affected)
Execution Time 0.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 1170406 ms.
*****************************************************************************************************************************************
*****************************************************************************************************************************************
*****************************************************************************************************************************************
HERE'S THE OPTDIAG FOR THIS TABLE
OptDiag/15.0.3/EBF 16548 ESD#1/P/Sun_svr4/OS 5.8/ase1503/2680/64-bit/OPT/Thu Mar 5 00:49:21 2009
Adaptive Server Enterprise/15.0.3/EBF 16548 ESD#1/P/Sun_svr4/OS 5.8/ase1503/2680/64-bit/FBO/Thu Mar 5 09:39:28 2009
Server name: "CLIENT1500"
Specified database: "xxx"
Specified table owner: not specified
Specified table: "ID_XACT_CASH"
Specified column: not specified
Table owner: "dbo"
Table name: "ID_XACT_CASH"
Statistics for table: "ID_XACT_CASH"
Data page count: 1372969
Empty data page count: 460
Data row count: 8224010.0000000000000000
Forwarded row count: 7.0000000000000000
Deleted row count: 15254.0000000000000000
Data page CR count: 142389.0000000000000000
OAM + allocation page count: 21451
First extent data pages: 2523
Data row size: 285.9223783223507667
Parallel join degree: 0.0000000000000000
Unused page count: 7865
OAM page count: 86
Data page cluster ratio: 0.9999900000000000
Space utilization: 0.8554743111926301
Large I/O efficiency: 0.9999300048996574
Statistics for index: "X9_ID_XACT_CASH" (nonclustered)
Index column list: "XACT_DAT"
Leaf count: 28603
Empty leaf page count: 0
Data page CR count: 408677.0000000000000000
Index page CR count: 2355.0000000000000000
Data row CR count: 733626.0000000000000000
First extent leaf pages: 880
Leaf row size: 6.0378037737678119
Index height: 3
Data page cluster ratio: 0.5781362587467953
Index page cluster ratio: 0.9999900000000000
Data row cluster ratio: 0.9999900000000000
Space utilization: 0.8671354973297203
Large I/O efficiency: 0.9999300048996574
Statistics for index: "X8_ID_XACT_CASH" (nonclustered)
Index column list: "SETTLE_DAT"
Leaf count: 28585
Empty leaf page count: 0
Data page CR count: 327911.0000000000000000
Index page CR count: 2423.0000000000000000
Data row CR count: 606500.0000000000000000
First extent leaf pages: 524
Leaf row size: 6.0378167480989546
Index height: 3
Data page cluster ratio: 0.6405272487739604
Index page cluster ratio: 0.9999900000000000
Data row cluster ratio: 0.9999900000000000
Space utilization: 0.8676833978429003
Large I/O efficiency: 0.9999300048996574
Statistics for index: "X7_ID_XACT_CASH" (nonclustered)
Index column list: "EXT_REF"
Leaf count: 43891
Empty leaf page count: 0
Data page CR count: 189222.0000000000000000
Index page CR count: 5237.0000000000000000
Data row CR count: 1175901.0000000000000000
First extent leaf pages: 1160
Leaf row size: 9.8068727969646918
Index height: 3
Data page cluster ratio: 0.9824172738659694
Index page cluster ratio: 0.9999900000000000
Data row cluster ratio: 0.9999900000000000
Space utilization: 0.9178561516286179
Large I/O efficiency: 0.9999300048996574
Statistics for index: "X6_ID_XACT_CASH" (nonclustered)
Index column list: "CONT_REF_NUM", "ID_ORDER", "BUS_STATE"
Leaf count: 30315
Empty leaf page count: 7
Data page CR count: 164003.0000000000000000
Index page CR count: 5955.0000000000000000
Data row CR count: 992804.0000000000000000
First extent leaf pages: 1768
Leaf row size: 6.6140383731755463
Index height: 3
Data page cluster ratio: 0.9999900000000000
Index page cluster ratio: 0.9183442660031667
Data row cluster ratio: 0.9999900000000000
Space utilization: 0.8962490210001021
Large I/O efficiency: 0.6362982153137592
Statistics for index: "X5_ID_XACT_CASH" (nonclustered)
Index column list: "EVT_REF_NUM"
Leaf count: 27536
Empty leaf page count: 0
Data page CR count: 155775.0000000000000000
Index page CR count: 5022.0000000000000000
Data row CR count: 1041164.0000000000000000
First extent leaf pages: 1155
Leaf row size: 6.2148671636569892
Index height: 3
Data page cluster ratio: 0.9999900000000000
Index page cluster ratio: 0.9344235079272848
Data row cluster ratio: 0.9999900000000000
Space utilization: 0.9271511772398635
Large I/O efficiency: 0.6853843090402230
Statistics for index: "X4_ID_XACT_CASH" (nonclustered)
Index column list: "XACT_NUM_REF"
Leaf count: 39589
Empty leaf page count: 0
Data page CR count: 172107.0000000000000000
Index page CR count: 3782.0000000000000000
Data row CR count: 1105876.0000000000000000
First extent leaf pages: 707
Leaf row size: 8.6771712237168810
Index height: 3
Data page cluster ratio: 0.9994177540074900
Index page cluster ratio: 0.9999900000000000
Data row cluster ratio: 0.9999900000000000
Space utilization: 0.9003745113857053
Large I/O efficiency: 0.9999300048996574
Statistics for index: "X3_ID_XACT_CASH" (nonclustered)
Index column list: "BUS_PROC_NUM", "BUS_NUM"
Leaf count: 74928
Empty leaf page count: 0
Data page CR count: 1385310.0000000000000000
Index page CR count: 3690.0000000000000000
Data row CR count: 1840980.0000000000000000
First extent leaf pages: 388
Leaf row size: 16.5611851096783873
Index height: 3
Data page cluster ratio: 0.2729515753104227
Index page cluster ratio: 0.9999900000000000
Data row cluster ratio: 0.9316241037739429
Space utilization: 0.9079602329248143
Large I/O efficiency: 0.9999300048996574
Statistics for index: "X2_ID_XACT_CASH" (clustered)
Index column list: "PF_COD", "CUR_COD", "SETTLE_DAT"
Leaf count: 50520
Empty data page count: 0
Data page CR count: 139638.0000000000000000
Index page CR count: 10886.0000000000000000
Data row CR count: 473206.0000000000000000
First extent leaf pages: 2177
Leaf row size: 9.3513063315470379
Index height: 3
Data page cluster ratio: 0.9999900000000000
Index page cluster ratio: 0.8965954077593032
Data row cluster ratio: 0.9999900000000000
Space utilization: 0.7603761715689907
Large I/O efficiency: 0.5801028844387286
Statistics for index: "X1_ID_XACT_CASH" (nonclustered)
Index column list: "XACTC_NUM"
Leaf count: 66144
Empty leaf page count: 0
Data page CR count: 1500494.0000000000000000
Index page CR count: 2139.0000000000000000
Data row CR count: 1813890.0000000000000000
First extent leaf pages: 56
Leaf row size: 14.0000000000000000
Index height: 3
Data page cluster ratio: 0.1908243608002547
Index page cluster ratio: 0.9999900000000000
Data row cluster ratio: 0.9355779778699813
Space utilization: 0.8694750762058454
Large I/O efficiency: 0.9999300048996574
Statistics for column: "BUS_NUM"
Last update of column statistics: Jun 8 2008 7:41:32:483PM
Range cell density: 0.0000036267645423
Total density: 0.0000036267645423
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000004)
Unique total values: default used (0.000004)
Average column width: default used (6.00)
Histogram for column: "BUS_NUM"
Column datatype: numeric(10,0)
Requested step count: 20
Actual step count: 17
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 <= 13679
2 0.07298563 <= 7207469
3 0.07297335 <= 7538055
4 0.07297335 <= 7890791
5 0.07297320 <= 8243459
6 0.07297320 <= 8614775
7 0.09728469 <= 9127041
8 0.05661477 <= 9469095
9 0.05661433 <= 9803968
10 0.05661463 <= 10107018
11 0.05661448 <= 10431174
12 0.05661433 <= 10754550
13 0.05661448 <= 11063295
14 0.05661448 <= 11377304
15 0.05661433 <= 11687135
16 0.02836652 <= 11836837
17 0.05655425 <= 12163815
Statistics for column: "BUS_PROC_NUM"
Last update of column statistics: Oct 11 2009 6:54:04:010PM
Range cell density: 0.0000002685374071
Total density: 0.0000002685374071
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000000)
Unique total values: default used (0.000000)
Average column width: default used (6.00)
Histogram for column: "BUS_PROC_NUM"
Column datatype: numeric(10,0)
Requested step count: 20
Actual step count: 20
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 <= 14527
2 0.05292683 <= 11639590
3 0.05292658 <= 12078716
4 0.05292670 <= 12453242
5 0.05292670 <= 12863506
6 0.05240834 <= 13256743
7 0.05085497 <= 13640307
8 0.05085485 <= 14059212
9 0.05085460 <= 14456989
10 0.05085460 <= 14834182
11 0.05341300 <= 15251984
12 0.05597384 <= 15693298
13 0.05597371 <= 16133678
14 0.05597384 <= 16545171
15 0.06996837 <= 17092670
16 0.05077101 <= 17500307
17 0.05077101 <= 17906642
18 0.05077101 <= 18336666
19 0.05077076 <= 18750076
20 0.03807930 <= 19054688
Statistics for column group: "BUS_PROC_NUM", "BUS_NUM"
Last update of column statistics: Oct 11 2009 6:54:04:010PM
Range cell density: 0.0000002685374071
Total density: 0.0000002685374071
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000000)
Unique total values: default used (0.000000)
Average column width: default used (6.00)
Statistics for column: "BUS_STATE"
Last update of column statistics: Jun 8 2008 7:40:40:426PM
Range cell density: 0.0000000000000000
Total density: 0.3210454526881976
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000000)
Unique total values: default used (0.321045)
Average column width: default used (3.00)
Histogram for column: "BUS_STATE"
Column datatype: varchar(3)
Requested step count: 20
Actual step count: 7
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 < "OP"
2 0.44702742 = "OP"
3 0.00000000 < "OPC"
4 0.27648506 = "OPC"
5 0.19545075 <= "OPD"
6 0.00000000 < "OPU"
7 0.08103678 = "OPU"
Statistics for column: "CONT_REF_NUM"
Last update of column statistics: Oct 11 2009 6:49:54:900PM
Range cell density: 0.0000004180041736
Total density: 0.3047613429921103
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000000)
Unique total values: default used (0.304761)
Average column width: default used (6.00)
Histogram for column: "CONT_REF_NUM"
Column datatype: numeric(10,0)
Requested step count: 20
Actual step count: 6
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 < 0
2 0.96720958 = 0
3 0.01008852 <= 390922
4 0.01008497 <= 441745
5 0.01008522 <= 495947
6 0.00253171 <= 510491
Statistics for column group: "CONT_REF_NUM", "ID_ORDER"
Last update of column statistics: Oct 11 2009 6:49:54:900PM
Range cell density: 0.0000002890580522
Total density: 0.3047613387639130
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000000)
Unique total values: default used (0.304761)
Average column width: default used (6.00)
Statistics for column group: "CONT_REF_NUM", "ID_ORDER", "BUS_STATE"
Last update of column statistics: Oct 11 2009 6:49:54:900PM
Range cell density: 0.0000001735188990
Total density: 0.3047613349753358
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000000)
Unique total values: default used (0.304761)
Average column width: default used (3.00)
Statistics for column: "CUR_COD"
Last update of column statistics: Jun 8 2008 7:43:14:536PM
Range cell density: 0.0115828804789111
Total density: 0.4402907520392507
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.011583)
Unique total values: default used (0.440291)
Average column width: default used (3.00)
Histogram for column: "CUR_COD"
Column datatype: varchar(3)
Requested step count: 20
Actual step count: 13
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 <= " ÿÿ"
2 0.00000117 <= " "
3 0.07281137 <= "CZK"
4 0.02081929 < "EUR"
5 0.64543837 = "EUR"
6 0.05919547 <= "JPY"
7 0.00580888 <= "NZ9"
8 0.02473281 <= "PLN"
9 0.01450210 <= "SKK"
10 0.01553698 <= "US9"
11 0.00000000 < "USD"
12 0.13908349 = "USD"
13 0.00207006 <= "ZAR"
Statistics for column: "EVT_REF_NUM"
Last update of column statistics: Oct 11 2009 6:51:08:910PM
Range cell density: 0.0000707704887416
Total density: 0.4394391045589158
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000071)
Unique total values: default used (0.439439)
Average column width: default used (6.00)
Histogram for column: "EVT_REF_NUM"
Column datatype: numeric(10,0)
Requested step count: 20
Actual step count: 16
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 < 0
2 0.66288406 = 0
3 0.01974253 <= 155052
4 0.02001927 <= 203652
5 0.02018353 <= 256361
6 0.01990862 <= 258395
7 0.02673373 <= 351015
8 0.02569444 <= 407719
9 0.02562602 <= 435411
10 0.02562418 <= 479433
11 0.02562394 <= 509601
12 0.02562687 <= 540052
13 0.02570570 <= 574278
14 0.02562736 <= 603719
15 0.02562382 <= 634062
16 0.02537595 <= 683586
Statistics for column: "EXT_REF"
Last update of column statistics: Oct 11 2009 6:48:20:913PM
Range cell density: 0.0000060452573327
Total density: 0.5131813538438641
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000006)
Unique total values: default used (0.513181)
Average column width: default used (30.00)
Histogram for column: "EXT_REF"
Column datatype: varchar(30)
Requested step count: 20
Actual step count: 13
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 < " "
2 0.71636558 = " "
3 0.01614545 <= "021130000854564"
4 0.02300129 <= "08161C004284"
5 0.02737523 <= "1"
6 0.02716531 <= "CP22553662_DIVCOR"
7 0.02716543 <= "OS21851266DIVREC"
8 0.02716543 <= "TEMPA_S_LU0003298733_20080318"
9 0.02716531 <= "TEMPDAT021130000803660"
10 0.02716531 <= "TEMPDAT_1463-103338-0002"
11 0.02716543 <= "TEMPDAT_2451-38979-0001"
12 0.02716531 <= "TEMPU 0021130000857723"
13 0.02695491 <= "YIDF00005 BEL"
Statistics for column: "ID_ORDER"
Last update of column statistics: Jun 8 2008 7:42:20:546PM
Range cell density: 0.0007211579226208
Total density: 0.9359033575710457
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000721)
Unique total values: default used (0.935903)
Average column width: default used (6.00)
Histogram for column: "ID_ORDER"
Column datatype: numeric(10,0)
Requested step count: 20
Actual step count: 6
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 < 0
2 0.96716577 = 0
3 0.00000000 < 1
4 0.01569575 = 1
5 0.01569619 <= 2
6 0.00144232 <= 4
Statistics for column: "PF_COD"
Last update of column statistics: Oct 11 2009 6:44:14:813PM
Range cell density: 0.0013343939688789
Total density: 0.0013343939688789
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.001334)
Unique total values: default used (0.001334)
Average column width: default used (12.00)
Histogram for column: "PF_COD"
Column datatype: varchar(12)
Requested step count: 20
Actual step count: 19
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 <= "000053ÿÿÿÿÿÿ"
2 0.05681435 <= "10059537"
3 0.05677176 <= "300092"
4 0.05019132 <= "300200"
5 0.05780162 <= "30060163"
6 0.05123625 <= "300812"
7 0.05124457 <= "30112409"
8 0.06030004 <= "482354"
9 0.06035133 <= "500344"
10 0.05090920 <= "600065"
11 0.06325598 <= "600932"
12 0.05861497 <= "700245"
13 0.05511496 <= "700623"
14 0.05527898 <= "700778"
15 0.05227162 <= "700849"
16 0.05864948 <= "900919"
17 0.05744972 <= "CSOBAMPRIV"
18 0.05756099 <= "MILAV"
19 0.04618287 <= "~SIMU00071"
Statistics for column group: "PF_COD", "CUR_COD"
Last update of column statistics: Oct 11 2009 6:44:14:813PM
Range cell density: 0.0006717721935544
Total density: 0.0006717721935544
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000672)
Unique total values: default used (0.000672)
Average column width: default used (3.00)
Statistics for column group: "PF_COD", "CUR_COD", "SETTLE_DAT"
Last update of column statistics: Oct 11 2009 6:44:14:813PM
Range cell density: 0.0000090754174290
Total density: 0.0000090754174290
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000009)
Unique total values: default used (0.000009)
Average column width: default used (8.00)
Statistics for column: "SETTLE_DAT"
Last update of column statistics: Oct 11 2009 6:46:51:910PM
Range cell density: 0.0029695237519826
Total density: 0.0035121512259891
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.002970)
Unique total values: default used (0.003512)
Average column width: default used (8.00)
Histogram for column: "SETTLE_DAT"
Column datatype: datetime
Requested step count: 20
Actual step count: 20
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 <= "Jul 29 2002 11:59:59:996PM"
2 0.06219992 <= "Nov 27 2007 12:00:00:000AM"
3 0.06168927 <= "Jan 10 2008 12:00:00:000AM"
4 0.05600284 <= "Feb 29 2008 12:00:00:000AM"
5 0.05663430 <= "Apr 14 2008 12:00:00:000AM"
6 0.05238313 <= "May 26 2008 12:00:00:000AM"
7 0.05521667 <= "Jul 2 2008 12:00:00:000AM"
8 0.06318512 <= "Aug 18 2008 12:00:00:000AM"
9 0.05147235 <= "Sep 23 2008 12:00:00:000AM"
10 0.04563660 <= "Oct 24 2008 12:00:00:000AM"
11 0.04529253 <= "Nov 25 2008 12:00:00:000AM"
12 0.05899832 <= "Jan 9 2009 12:00:00:000AM"
13 0.05678840 <= "Feb 28 2009 12:00:00:000AM"
14 0.05214322 <= "Apr 7 2009 12:00:00:000AM"
15 0.04678544 <= "May 8 2009 12:00:00:000AM"
16 0.05665829 <= "Jun 23 2009 12:00:00:000AM"
17 0.05791117 <= "Aug 3 2009 12:00:00:000AM"
18 0.05272830 <= "Sep 15 2009 12:00:00:000AM"
19 0.05136281 <= "Oct 23 2009 12:00:00:000AM"
20 0.01691131 <= "Aug 9 8930 12:00:00:000AM"
Statistics for column: "XACTC_NUM"
Last update of column statistics: Oct 11 2009 6:55:14:000PM
Range cell density: 0.0000001223993504
Total density: 0.0000001223993504
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000000)
Unique total values: default used (0.000000)
Average column width: default used (6.00)
Histogram for column: "XACTC_NUM"
Column datatype: numeric(10,0)
Requested step count: 20
Actual step count: 20
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 <= 20062
2 0.05210210 <= 17035148
3 0.05210198 <= 17680784
4 0.05210198 <= 18275660
5 0.05210198 <= 18869950
6 0.05189353 <= 19477977
7 0.05126427 <= 20052776
8 0.05126427 <= 20688614
9 0.05126427 <= 21301556
10 0.05126427 <= 21897237
11 0.05403430 <= 22563557
12 0.05680211 <= 23225034
13 0.05680211 <= 23860509
14 0.05680211 <= 24518212
15 0.07100411 <= 25312589
16 0.05035717 <= 25903400
17 0.05035705 <= 26486884
18 0.05035705 <= 27071780
19 0.05035705 <= 27659515
20 0.03776828 <= 28067060
Statistics for column: "XACT_DAT"
Last update of column statistics: Oct 11 2009 6:45:50:810PM
Range cell density: 0.0028317239478099
Total density: 0.0029619684151046
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.002832)
Unique total values: default used (0.002962)
Average column width: default used (8.00)
Histogram for column: "XACT_DAT"
Column datatype: datetime
Requested step count: 20
Actual step count: 20
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 <= "Jun 1 2000 11:59:59:996PM"
2 0.06044300 <= "Nov 15 2007 12:00:00:000AM"
3 0.05556417 <= "Jan 3 2008 12:00:00:000AM"
4 0.06027654 <= "Feb 15 2008 12:00:00:000AM"
5 0.05897715 <= "Mar 31 2008 12:00:00:000AM"
6 0.06129796 <= "May 15 2008 12:00:00:000AM"
7 0.05712427 <= "Jun 27 2008 12:00:00:000AM"
8 0.05601251 <= "Aug 6 2008 12:00:00:000AM"
9 0.06052770 <= "Sep 23 2008 12:00:00:000AM"
10 0.04661347 <= "Oct 27 2008 12:00:00:000AM"
11 0.05828730 <= "Dec 10 2008 12:00:00:000AM"
12 0.05790579 <= "Jan 23 2009 12:00:00:000AM"
13 0.04548898 <= "Mar 3 2009 12:00:00:000AM"
14 0.04638715 <= "Apr 2 2009 12:00:00:000AM"
15 0.05604018 <= "May 8 2009 12:00:00:000AM"
16 0.05116966 <= "Jun 22 2009 12:00:00:000AM"
17 0.04839805 <= "Jul 23 2009 12:00:00:000AM"
18 0.05202388 <= "Sep 5 2009 12:00:00:000AM"
19 0.04701592 <= "Oct 5 2009 12:00:00:000AM"
20 0.02044632 <= "Aug 9 8930 12:00:00:000AM"
Statistics for column: "XACT_NUM_REF"
Last update of column statistics: Oct 11 2009 6:52:32:006PM
Range cell density: 0.0000001226088895
Total density: 0.4457938590878456
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)
Unique range values: default used (0.000000)
Unique total values: default used (0.445794)
Average column width: default used (6.00)
Histogram for column: "XACT_NUM_REF"
Column datatype: numeric(10,0)
Requested step count: 20
Actual step count: 16
Sampling Percent: 0
Out of range Histogram Adjustment is DEFAULT.
Step Weight Value
1 0.00000000 < 0
2 0.66767794 = 0
3 0.02062882 <= 17316868
4 0.02062870 <= 18099445
5 0.02062870 <= 18796952
6 0.02062870 <= 19637181
7 0.02780705 <= 20603921
8 0.02466665 <= 21419752
9 0.02466665 <= 22225187
10 0.02466665 <= 23017404
11 0.02466665 <= 23966703
12 0.02466665 <= 24756060
13 0.02466665 <= 25670126
14 0.02466665 <= 26513721
15 0.02466665 <= 27281638
16 0.02466690 <= 28049323
No statistics for remaining columns: "AMT_CURRENT"
(default values used) "AMT_GROSS"
"BO_ACCOUNTING"
"COMMENT_1"
"INST_NUM"
"QTY"
"REC_CRE_DAT"
"REC_CRE_GMT_DAT"
"REC_CRE_GMT_TIM"
"REC_CRE_ORIG"
"REC_CRE_TZ"
"REC_CRE_USR"
"REC_MOD_DAT"
"REC_MOD_GMT_DAT"
"REC_MOD_GMT_TIM"
"REC_MOD_ORIG"
"REC_MOD_TZ"
"REC_MOD_USR"
"STRATEGY_COD"
"TAX_CREDIT"
"WHT_AMT"
"WHT_RATE"
"XACTC_FLG"
"XACTC_SIGN"
"XACTC_TYP"
Optdiag succeeded.