| T.R | Title | User | Personal
Name | Date | Lines |
|---|
| 1065.1 | more info | ORAREP::USDEV::JBONIN | Technical Surgeon, AYS | Fri Jun 07 1996 12:23 | 9 |
| I should mention that the exact same query run against the appropriate
link table taht is part of the view also takes forever and eats up
memory.
But, if I run the same query against the underlying physical database,
the results are absolutely instantaneous.
Thanks for any help,
John
|
| 1065.2 | still more info | ORAREP::USDEV::JBONIN | Technical Surgeon, AYS | Fri Jun 07 1996 12:41 | 9 |
| Some more interesting observations..
When I define DBI_OPTIMIZATION_LEVEL to be "fast first"
the query against the single link table that is part of the view also
causes the query to return results immediately, just about matching
the performance when run against the physical database.
John
|
| 1065.3 | Can you "explain" it? | BROKE::ABUGOV | | Fri Jun 07 1996 15:54 | 13 |
|
Hi John,
Can you turn on explain tracing and send us or post the resulting output?
$define dbi_trace_flags "EXPLAIN"
$define dbi_trace_output john.trace
It would also be helpful if you traced the queries in replies 1 and 2.
Thanks very much,
dan
|
| 1065.4 | why is dbi sorting without any order clause? | ORAREP::USDEV::JBONIN | Technical Surgeon, AYS | Fri Jun 07 1996 16:24 | 11 |
| Hi Dan,
I am running with requested dbi flags now, and will post the dbi trace
when finished, but I just noticed something strange.
This process is creating 2 sortwork files in my login directory, but
I am not sorting anything!, it's just a plain select just like in .0.
I'll keep you posted.
John
|
| 1065.5 | | ORAREP::USDEV::JBONIN | Technical Surgeon, AYS | Fri Jun 07 1996 16:38 | 6 |
| The next 2 replies will contain the log and the dbi trace output.
Like I said in the previous reply, it's strange that sortwork files
were created, and no dbi temp files were created yet before it failed.
John
|
| 1065.6 | here's the log | ORAREP::USDEV::JBONIN | Technical Surgeon, AYS | Fri Jun 07 1996 16:39 | 233 |
| $define rdms$debug_flags "SO"
$define dbi_trace_flags "explain"
$define dbi_trace_output dbi_trace.out
$SQL
ATTACH 'FILE TEST_DBI';
Solutions tried 1
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 5.3333335E+00
Cardinality of chosen solution 1.0000000E+00
Firstn Get Retrieval sequentially of relation RDB$DATABASE
Solutions tried 1
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 3.0000000E+00
Cardinality of chosen solution 1.0000000E+00
Firstn Get Retrieval sequentially of relation DBI_DATABASE
Solutions tried 1
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 3.0000000E+00
Cardinality of chosen solution 1.0000000E+00
Get Retrieval sequentially of relation DBI_DATABASE
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 6.9499884E+00
Cardinality of chosen solution 5.0000000E+00
Get Retrieval by index of relation DBI_DATABASE_LINKS
Index name DBI_LINK_NAME_NDX [0:0]
Solutions tried 3
Solutions blocks created 2
Created solutions pruned 0
Cost of the chosen solution 6.5340080E+00
Cardinality of chosen solution 3.1250000E-01
Conjunct Get Retrieval by index of relation DBI_LINK_PROXIES
Index name DBI_USER_NAME_NDX [0:0] Bool
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 7.2881889E+01
Cardinality of chosen solution 1.6200000E+02
Get Retrieval by index of relation DBI_FIELDS
Index name DBI_FIELDS_NAME_NDX [0:0]
Solutions tried 3
Solutions blocks created 2
Created solutions pruned 1
Cost of the chosen solution 3.4830490E+01
Cardinality of chosen solution 8.5000000E+01
Get Retrieval by index of relation DBI_RELATIONS
Index name DBI_REL_REL_NAME_NDX [0:0]
Solutions tried 3
Solutions blocks created 2
Created solutions pruned 1
Cost of the chosen solution 2.2115061E+00
Cardinality of chosen solution 1.0000000E+01
Get Retrieval by index of relation DBI_MODULES
Index name DBI_MOD_ID_NDX [0:0]
Solutions tried 3
Solutions blocks created 2
Created solutions pruned 1
Cost of the chosen solution 2.4159481E+01
Cardinality of chosen solution 0.0000000E+00
Sort Conjunct Get Retrieval by index of relation DBI_ROUTINES
Index name DBI_RTN_ID_NDX [0:0]
Solutions tried 1
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 3.0000000E+00
Cardinality of chosen solution 1.0000000E+00
Firstn Retrieval sequentially of relation DBI_DATABASE
Solutions tried 3
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 2.1269038E+00
Cardinality of chosen solution 6.0000000E+00
Get Retrieval by index of relation DBI_USERS
Index name DBI_USERS_ID_NDX [0:0]
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 2.1899979E+00
Cardinality of chosen solution 3.1250000E-02
Aggregate Conjunct Get
Retrieval by index of relation DBI_DATABASE_LINKS
Index name DBI_LINK_NAME_NDX [1:1] Direct lookup
Solutions tried 3
Solutions blocks created 2
Created solutions pruned 0
Cost of the chosen solution 1.0423013E+00
Cardinality of chosen solution 1.0000000E+00
Firstn Aggregate Index only retrieval of relation DBI_DATABASE_LINKS
Index name DBI_LINK_NAME_NDX [1:1] Direct lookup
Solutions tried 3
Solutions blocks created 2
Created solutions pruned 0
Cost of the chosen solution 3.9467785E+00
Cardinality of chosen solution 2.8125000E-01
Leaf#01 FFirst DBI_LINK_PROXIES Card=0
BgrNdx1 DBI_USER_NAME_NDX [1:1] Bool Fan=4
SET TRANSACTION READ ONLY;
@DISK$ERCDEV_USR01:[BONIN]sum.sql
SQL>
SQL> select org_unit,
cont> dtl_xplod_indcr,
cont> fyr_fqtr_fwk,
cont> child_prodt_id,
cont> cpu_id,
cont> sls_bu_segmt_cd,
cont> rvnue_bu_segmt_cd,
cont> prodt_type_ultmt_grp_cd,
cont> dec_order_no,
cont> line_segmt_cd,
cont> prodt_agrgt_cd,
cont> custmr_addr_id,
cont> partner_type,
cont> prodt_qty,
cont> iss_addon_qty,
cont> ibp_rvnue_qty,
cont> bu_ttl_qty,
cont> slp_amt,
cont> warnty_amt,
cont> gross_amt,
cont> uplift_amt,
cont> mlp_amt,
cont> dscnt_amt,
cont> allwnc_amt,
cont> trnsfr_amt,
cont> ibp_rvnue_amt,
cont> ibp_rvnue_trnsfr_amt,
cont> ibp_cost_amt,
cont> dlp_amt,
cont> nor_amt,
cont> bu_net_trnsfr_amt,
cont> bu_ttl_cost_amt,
cont> bu_ttl_rvnue_amt,
cont> bu_var_margin_amt,
cont> trad_allwnc_amt,
cont> trad_dscnt_amt,
cont> trad_nor_amt
cont> from dtl_xplod_or_2
cont> where fyr_fqtr_fwk between 1996000 AND 1996999;
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 2.4306905E+00
Cardinality of chosen solution 3.1250000E-02
Aggregate Conjunct Get
Retrieval by index of relation DBI_RELATIONS
Index name DBI_REL_REL_NAME_NDX [1:1] Direct lookup
Solutions tried 3
Solutions blocks created 2
Created solutions pruned 0
Cost of the chosen solution 1.4306904E+00
Cardinality of chosen solution 1.0000000E+00
Firstn Aggregate Index only retrieval of relation DBI_RELATIONS
Index name DBI_REL_REL_NAME_NDX [1:1] Direct lookup
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 4.9204202E+02
Cardinality of chosen solution 0.0000000E+00
Sort
Leaf#01 BgrOnly DBI_RELATION_FIELDS Card=922
BgrNdx1 DBI_RFR_REL_NAME_FLD_ID_NDX [0:0] Bool Fan=8
Solutions tried 3
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 2.7614224E+00
Cardinality of chosen solution 0.0000000E+00
Conjunct Get Retrieval by index of relation DBI_VIEW_RELATIONS
Index name DBI_VIEW_VIEW_NAME_NDX [0:0] Bool
Solutions tried 3
Solutions blocks created 3
Created solutions pruned 2
Cost of the chosen solution 1.0382317E+02
Cardinality of chosen solution 0.0000000E+00
Sort Conjunct Get
Retrieval sequentially of relation DBI_INTERRELATIONS
Solutions tried 2
Solutions blocks created 2
Created solutions pruned 1
Cost of the chosen solution 3.9826561E+01
Cardinality of chosen solution 0.0000000E+00
Sort Conjunct Get Retrieval sequentially of relation DBI_INDICES
Solutions tried 3
Solutions blocks created 2
Created solutions pruned 1
Cost of the chosen solution 2.2063452E+01
Cardinality of chosen solution 0.0000000E+00
Sort
Leaf#01 BgrOnly DBI_VIEW_PARTITIONS Card=3
BgrNdx1 DBI_VIEW_PARTN_ID_NDX [0:0] Fan=17
BgrNdx2 DBI_VIEW_PARTN_NDX [0:0] Bool Fan=5
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 3.2134743E+01
Cardinality of chosen solution 0.0000000E+00
Conjunct Get Retrieval by index of relation DBI_VIEW_PARTITION_FIELDS
Index name DBI_VIEW_PARTN_FLD_NDX [0:0] Bool
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 4.5522938E+01
Cardinality of chosen solution 0.0000000E+00
Conjunct Get Retrieval by index of relation DBI_INDEX_SEGMENTS
Index name DBI_NDX_SEG_NAM_FLD_POS_NDX [0:0] Bool
Solutions tried 1
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 2.1319008E+05
Cardinality of chosen solution 1.0000000E+00
Firstn Get Retrieval sequentially of relation DTL_XPLOD_OR
Solutions tried 13
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 1.1606041E+03
Cardinality of chosen solution 6.3722672E+04
Leaf#01 FFirst DTL_XPLOD_OR Card=4246409
BgrNdx1 DTL_XPLOD_OR_IDX_1 [1:1] Bool Fan=29
%RDB-F-SYS_REQUEST, error from system services request
-DBI-E-INSVMEM, Virtual memory exceeded
disconnect all;
%RDB-E-OPEN_TRANS, detach failed because 1 transaction are active
EXIT
$exit
BONIN job terminated at 7-JUN-1996 15:33:12.39
Accounting information:
Buffered I/O count: 1346 Peak working set size: 251760
Direct I/O count: 26695 Peak page file size: 426720
Page faults: 301875 Mounted volumes: 0
Charged CPU time: 0 00:05:36.03 Elapsed time: 0 00:17:58.20
|
| 1065.7 | here's the dbi trace | ORAREP::USDEV::JBONIN | Technical Surgeon, AYS | Fri Jun 07 1996 16:40 | 116 |
| ---EVENT BEG: EXPLAIN -------------------------- Fri Jun 7 15:15:25.179 1996---
Step 1 At DBI
Cardinality 1 Tuple Length 20 Cost 10.12 +Per Tuple 0
Left Operand 2
RESULT
Step 2 At DBI$MDD_LINK_NAME
Cardinality 1 Tuple Length 20 Cost 10.12 +Per Tuple 0
STREAM FROM DBI$MDD_LINK_NAME TO DBI
SELECT RDB$RELATIONS.RDB$DBKEY_LENGTH, RDB$RELATIONS.RDB$RELATION_ID,
RDB$RELATIONS.RDB$VIEW_BLR, RDB$RELATIONS.RDB$SYSTEM_FLAG
FROM RDB$RELATIONS T001
WHERE (RDB$RELATIONS.RDB$RELATION_NAME = <PARAM>)
---EVENT END: EXPLAIN -------------------------- Fri Jun 7 15:15:25.194 1996---
---EVENT BEG: EXPLAIN -------------------------- Fri Jun 7 15:15:25.390 1996---
Step 1 At DBI
Cardinality 1 Tuple Length 675 Cost 149.004 +Per Tuple 0
Left Operand 2
RESULT
Step 2 At DBI
Cardinality 1 Tuple Length 675 Cost 149.004 +Per Tuple 0
Left Operand 3
ORDER BY 002 ASC
Step 3 At DBI
Cardinality 1 Tuple Length 675 Cost 147.004 +Per Tuple 0
Left Operand 4
SELECT RDB$RELATION_FIELDS.RDB$FIELD_NAME,
RDB$RELATION_FIELDS.RDB$FIELD_POSITION,
RDB$RELATION_FIELDS.RDB$FIELD_ID, RDB$RELATION_FIELDS.RDB$QUERY_HEADER,
RDB$FIELDS.RDB$QUERY_HEADER, RDB$RELATION_FIELDS.RDB$EDIT_STRING,
RDB$FIELDS.RDB$EDIT_STRING, RDB$FIELDS.RDB$FIELD_TYPE,
RDB$FIELDS.RDB$FIELD_LENGTH, RDB$FIELDS.RDB$FIELD_SCALE,
RDB$FIELDS.RDB$COMPUTED_BLR, RDB$FIELDS.RDB$SEGMENT_LENGTH,
RDB$FIELDS.RDB$FIELD_SUB_TYPE, RDB$RELATION_FIELDS.RDB$QUERY_NAME,
RDB$FIELDS.RDB$QUERY_NAME, RDB$RELATION_FIELDS.RDB$DEFAULT_VALUE,
RDB$FIELDS.RDB$DEFAULT_VALUE
Step 4 At DBI
Cardinality 1 Tuple Length 675 Cost 146.004 +Per Tuple 0
Left Operand 5 Right Operand 6
HASH JOIN RDB$FIELDS.RDB$FIELD_NAME = RDB$RELATION_FIELDS.RDB$FIELD_SOURCE
Step 5 At DBI$MDD_LINK_NAME
Cardinality 1 Tuple Length 374 Cost 66.374 +Per Tuple 0
STREAM FROM DBI$MDD_LINK_NAME TO DBI
SELECT RDB$RELATION_FIELDS.RDB$DEFAULT_VALUE,
RDB$RELATION_FIELDS.RDB$QUERY_NAME,
RDB$RELATION_FIELDS.RDB$EDIT_STRING,
RDB$RELATION_FIELDS.RDB$QUERY_HEADER,
RDB$RELATION_FIELDS.RDB$FIELD_ID,
RDB$RELATION_FIELDS.RDB$FIELD_NAME,
RDB$RELATION_FIELDS.RDB$FIELD_POSITION,
RDB$RELATION_FIELDS.RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS T001
WHERE (RDB$RELATION_FIELDS.RDB$RELATION_NAME = <PARAM>)
Step 6 At DBI$MDD_LINK_NAME
Cardinality 10 Tuple Length 363 Cost 68.63 +Per Tuple 0
STREAM FROM DBI$MDD_LINK_NAME TO DBI
SELECT RDB$FIELDS.RDB$DEFAULT_VALUE, RDB$FIELDS.RDB$QUERY_NAME,
RDB$FIELDS.RDB$FIELD_SUB_TYPE, RDB$FIELDS.RDB$SEGMENT_LENGTH,
RDB$FIELDS.RDB$COMPUTED_BLR, RDB$FIELDS.RDB$FIELD_SCALE,
RDB$FIELDS.RDB$FIELD_LENGTH, RDB$FIELDS.RDB$FIELD_TYPE,
RDB$FIELDS.RDB$EDIT_STRING, RDB$FIELDS.RDB$QUERY_HEADER,
RDB$FIELDS.RDB$FIELD_NAME
FROM RDB$FIELDS
---EVENT END: EXPLAIN -------------------------- Fri Jun 7 15:15:25.399 1996---
---EVENT BEG: EXPLAIN -------------------------- Fri Jun 7 15:15:30.367 1996---
Step 1 At DBI
Cardinality 50359.06 Tuple Length 259 Cost 18065091 +Per Tuple 0
Left Operand 2
RESULT
Step 2 At REV_RPT_CFY
Cardinality 50359.06 Tuple Length 259 Cost 18065091 +Per Tuple 0
STREAM FROM REV_RPT_CFY TO DBI
SELECT DTL_XPLOD_OR_CFY.ORG_UNIT, DTL_XPLOD_OR_CFY.DTL_XPLOD_INDCR,
DTL_XPLOD_OR_CFY.FYR_FQTR_FWK, DTL_XPLOD_OR_CFY.CHILD_PRODT_ID,
DTL_XPLOD_OR_CFY.CPU_ID, DTL_XPLOD_OR_CFY.SLS_BU_SEGMT_CD,
DTL_XPLOD_OR_CFY.RVNUE_BU_SEGMT_CD,
DTL_XPLOD_OR_CFY.PRODT_TYPE_ULTMT_GRP_CD,
DTL_XPLOD_OR_CFY.DEC_ORDER_NO, DTL_XPLOD_OR_CFY.LINE_SEGMT_CD,
DTL_XPLOD_OR_CFY.PRODT_AGRGT_CD, DTL_XPLOD_OR_CFY.CUSTMR_ADDR_ID,
DTL_XPLOD_OR_CFY.PARTNER_TYPE, DTL_XPLOD_OR_CFY.PRODT_QTY,
DTL_XPLOD_OR_CFY.ISS_ADDON_QTY, DTL_XPLOD_OR_CFY.IBP_RVNUE_QTY,
DTL_XPLOD_OR_CFY.BU_TTL_QTY, DTL_XPLOD_OR_CFY.SLP_AMT,
DTL_XPLOD_OR_CFY.WARNTY_AMT, DTL_XPLOD_OR_CFY.GROSS_AMT,
DTL_XPLOD_OR_CFY.UPLIFT_AMT, DTL_XPLOD_OR_CFY.MLP_AMT,
DTL_XPLOD_OR_CFY.DSCNT_AMT, DTL_XPLOD_OR_CFY.ALLWNC_AMT,
DTL_XPLOD_OR_CFY.TRNSFR_AMT, DTL_XPLOD_OR_CFY.IBP_RVNUE_AMT,
DTL_XPLOD_OR_CFY.IBP_RVNUE_TRNSFR_AMT,
DTL_XPLOD_OR_CFY.IBP_COST_AMT, DTL_XPLOD_OR_CFY.DLP_AMT,
DTL_XPLOD_OR_CFY.NOR_AMT, DTL_XPLOD_OR_CFY.BU_NET_TRNSFR_AMT,
DTL_XPLOD_OR_CFY.BU_TTL_COST_AMT, DTL_XPLOD_OR_CFY.BU_TTL_RVNUE_AMT,
DTL_XPLOD_OR_CFY.BU_VAR_MARGIN_AMT,
DTL_XPLOD_OR_CFY.TRAD_ALLWNC_AMT, DTL_XPLOD_OR_CFY.TRAD_DSCNT_AMT,
DTL_XPLOD_OR_CFY.TRAD_NOR_AMT
FROM DTL_XPLOD_OR_CFY T001
WHERE (((DTL_XPLOD_OR_CFY.FYR_FQTR_FWK >= 1996000
AND DTL_XPLOD_OR_CFY.FYR_FQTR_FWK <= 1996999)
AND (DTL_XPLOD_OR_CFY.FYR_FQTR_FWK >= 1996101
AND DTL_XPLOD_OR_CFY.FYR_FQTR_FWK <= 1996414)))
---EVENT END: EXPLAIN -------------------------- Fri Jun 7 15:15:30.370 1996---
|
| 1065.8 | lots of questions for you... | BROKE::ABUGOV | | Fri Jun 07 1996 17:54 | 22 |
|
Hi John,
We think we understand what is happening - DBI is building a data
collection that probably consists of the entire table. How many rows
are in the table, and what do the columns look like? If you put a
limit to x rows, does this run faster? When you enable fast first, all
dbi is doing is materializing the resulting rows to the user as they
become available from the Rdb database, but eventually it will run out
of memory in the same way you are seeing if you let it run to
completion (we believe). Is there some restriction you can do on the
amount of data coming back (i.e. put in a where clause that causes
something other then the entire table contents to be streamed up to
DBI).
Please let us know if this impacts your anticipated use of DBI (i.e. is
this query really what you want to do, or would there normally be less
data moved around).
Thanks,
dan
|
| 1065.9 | some answers | ORAREP::USDEV::JBONIN | Technical Surgeon, AYS | Mon Jun 10 1996 14:01 | 99 |
| re: .last comments below
We think we understand what is happening - DBI is building a data
collection that probably consists of the entire table. How many rows
are in the table, and what do the columns look like? If you put a
limit to x rows, does this run faster? When you enable fast first, all
dbi is doing is materializing the resulting rows to the user as they
become available from the Rdb database, but eventually it will run out
of memory in the same way you are seeing if you let it run to
completion (we believe). Is there some restriction you can do on the
amount of data coming back (i.e. put in a where clause that causes
something other then the entire table contents to be streamed up to
DBI).
>> Yes, a "limit to 100 rows" produces instant results. I was
>> expecting "fast first" to buffer the materialized data to the
>> user like RDB and free up memory instead of running out.
>>
>> Is it normal to use the amount of memory that the log shows
>> (peak ws = 251760, peak page file = 426720)? Does it have
>> anything to do with the page size difference between vax
>> and alpha? Because an alpha page size is 16 times the vax,
>> does it use 16 time the memory values you normally see in
>> a log?
>>
>> The horizontally partitioned view dtl_xplod_or_2 is defined
>> below where 'FIELDS' can be substituted for all the fields
>> in the table (around 50 fields total).
>>
>> The underlying tables record count is...
>>
>> DTL_XPLOD_OR_CFY = 4,246,409
>> DTL_XPLOD_OR_PFY1 = 3,125,270
>> DTL_XPLOD_OR_PFY2 = 2,512,016
>>
>> for a total of around 10 million records, but the query should
>> only access the partition 'cfy'. My concern is that it is trying
>> to load this entire 4+ million rows into memory. The particular
>> query taht I entered the log and dbi trace for is just a simple,
>> extract program where that select statement is a cursor in a
>> sql module called by 3gl, where it simply fetches from the cursor
>> each record and write it to an ascii file.
>>
>> We were finally able to make this query run to completion by
>> playing around with logicals and increasing account quotas
>> (wsquo = 95,000, wsextent = 524288, pagefil = 350000).
>> After increasing just the account quotas, it ran longer than
>> it did before, but eventually failed with "virtual memory error",
>> but this time caused by "insufficient disk space", trying to
>> write out DBI_WM_TEMP files, which was pointing to sys$login.
>> I then defined all temp and sort files to large work area,
>> RDMS$BIND_WORK_FILE, SORTWORK0-3, DBI_WM_DIRECTORY thru
>> DBI_WM_DIRECTORY_3. Now back to catch-all "virtual memory error".
>> This time I define the logical DBI_WM_BYTE_LIMIT = 5000000
>> and it works! I don't get the error, but it still takes forever
>> before it starts writing output. If I define the logical
>> DBI_OPTIMIZATION_LEVEL = "Fast First", then it starts writing
>> output right away. One thing I noticed is that it used about
>> 4 million blocks of space for 4 different dbi temp files,
>> INNER_DB.TMP, OUTER_DC.TMP, and 2 different DBI_WM*.TMP files.
>> Also, I'll have to verify this, but I believe when I turn
>> on the "Fast First", it doesn't use the INNER_DC or OUTER_DC
>> temp files, but insteadd used 4 DBI_WM*.TMP files.
>> So, I'm still wondering why it was creating sortwork files
>> when the query didn't call for any sort (or is this internal
>> DBI sorting going on?). Also, why so much memory used?
>> Thanks, John
create view dtl_xplod_or_2 (FIELDS,,,...)
using horizontal partitioning on fyr_fqtr_fwk
partition cfy
where fyr_fqtr_fwk between 1996101 and 1996414
compose as
select FIELDS,,,...
from DTL_XPLOD_OR_cfy
partition pfy1
where fyr_fqtr_fwk between 1995101 and 1995414
compose as
select FIELDS,,,...
from DTL_XPLOD_OR_PFY1
partition pfy2
where fyr_fqtr_fwk between 1994101 and 1994414
compose as
select FIELDS,,,...
from DTL_XPLOD_OR_PFY2;
Please let us know if this impacts your anticipated use of DBI (i.e. is
this query really what you want to do, or would there normally be less
data moved around).
>> Yes, these are production year-to-date extracts, and was only
>> 6 months of data, so could become potentially worse.
|
| 1065.10 | We could behave better in this situation | BROKE::ABUGOV | | Mon Jun 10 1996 22:25 | 92 |
|
Hi John,
Please look for ## below.
Thanks,
dan
>> Yes, a "limit to 100 rows" produces instant results. I was
>> expecting "fast first" to buffer the materialized data to the
>> user like RDB and free up memory instead of running out.
>>
>> Is it normal to use the amount of memory that the log shows
>> (peak ws = 251760, peak page file = 426720)? Does it have
>> anything to do with the page size difference between vax
>> and alpha? Because an alpha page size is 16 times the vax,
>> does it use 16 time the memory values you normally see in
>> a log?
These are "normal" 512 bytes pages. The alpha doesn't use 16 times the memory
of the VAX, although there is some expansion of our image sizes due to 64 bits.
>>
>> The horizontally partitioned view dtl_xplod_or_2 is defined
>> below where 'FIELDS' can be substituted for all the fields
>> in the table (around 50 fields total).
>>
>> The underlying tables record count is...
>>
>> DTL_XPLOD_OR_CFY = 4,246,409
>> DTL_XPLOD_OR_PFY1 = 3,125,270
>> DTL_XPLOD_OR_PFY2 = 2,512,016
>>
>> for a total of around 10 million records, but the query should
>> only access the partition 'cfy'. My concern is that it is trying
>> to load this entire 4+ million rows into memory. The particular
##The explain tracing showed that indeed we only accessed that partition. Your
##concern is not without cause - we are loading the entire 4+ million rows in
##memory and/or workspace.
>> query taht I entered the log and dbi trace for is just a simple,
>> extract program where that select statement is a cursor in a
>> sql module called by 3gl, where it simply fetches from the cursor
>> each record and write it to an ascii file.
>>
>> We were finally able to make this query run to completion by
>> playing around with logicals and increasing account quotas
>> (wsquo = 95,000, wsextent = 524288, pagefil = 350000).
>> After increasing just the account quotas, it ran longer than
>> it did before, but eventually failed with "virtual memory error",
>> but this time caused by "insufficient disk space", trying to
>> write out DBI_WM_TEMP files, which was pointing to sys$login.
>> I then defined all temp and sort files to large work area,
>> RDMS$BIND_WORK_FILE, SORTWORK0-3, DBI_WM_DIRECTORY thru
>> DBI_WM_DIRECTORY_3. Now back to catch-all "virtual memory error".
>> This time I define the logical DBI_WM_BYTE_LIMIT = 5000000
>> and it works! I don't get the error, but it still takes forever
>> before it starts writing output. If I define the logical
>> DBI_OPTIMIZATION_LEVEL = "Fast First", then it starts writing
>> output right away. One thing I noticed is that it used about
>> 4 million blocks of space for 4 different dbi temp files,
>> INNER_DB.TMP, OUTER_DC.TMP, and 2 different DBI_WM*.TMP files.
>> Also, I'll have to verify this, but I believe when I turn
>> on the "Fast First", it doesn't use the INNER_DC or OUTER_DC
>> temp files, but insteadd used 4 DBI_WM*.TMP files.
##I'd be curious to know what it did create, and the sizes of the files.
>> So, I'm still wondering why it was creating sortwork files
>> when the query didn't call for any sort (or is this internal
>> DBI sorting going on?). Also, why so much memory used?
>> Thanks, John
##These aren't sort files - at the time of execution we have no mechanism in
##place to look ahead for our engine and tell it that it only needs to buffer
##the data it is passing up, then release the memory and not save the data
##because it is done with it. It saves the data in workspaces that it thinks
##it might use at a later time for joins.
##We'll look at this behaviour and see if there is anything we can do to
##reduce the memory usage/workspace building. At one point we looked at this
##and someone prototyped a solution which didn't pan out. Maybe this time...
>> Yes, these are production year-to-date extracts, and was only
>> 6 months of data, so could become potentially worse.
##You did say you had 4 gig of memory...
##Sorry!
|
| 1065.11 | temp file and memory space could become issue | ORAREP::USDEV::JBONIN | Technical Surgeon, AYS | Tue Jun 11 1996 13:40 | 10 |
| The Alpha 2100 only has 1 gig, not 4 gig, see .0.
I guess we'll have to live with the increased memory usage and temp
file space for now, but I think this can be expensive to the user.
We currently have a 48 million block sort disk, and we are concerned
about the number of concurrent users before exhausting this space.
Any improvements you may have or suggestions will be welcome.
Thanks,
John
|
| 1065.12 | another problem now | ORAREP::USDEV::JBONIN | Technical Surgeon, AYS | Wed Jun 12 1996 09:53 | 21 |
| I am adding this note because it already has my configuration and
related information.
I have a query which runs in current production VAX RDB V4.0-2 without DBI.
This database is identical in content to what the dbi logical database
has in .0, the only difference being that the tables are all local in
1 physical RDB database on the VAX (all fiscal detail data in one large
table), and on the alpha we created identical fiscal year databases,
and also put reference in a separate database (all in hopes that DBI
would work like a charm to bring all the data sources together, and
taking advantage of any parallel operations).
Anyway, this query runs on the vax between 4 and 5 hours elapsed, but
on the alpha through DBI it takes 15+ hours before it finally creates
a dbi bugcheck. I mistakenly deleted the dump file, but just started
the job again to reproduce it. In the meantime, the next 2 replies will
contain the log and the dbi trace output. It's a complex multi-join
query, but works fine on vax.
Thanks,
John
|
| 1065.13 | the 15+ hour log producing bugcheck | ORAREP::USDEV::JBONIN | Technical Surgeon, AYS | Wed Jun 12 1996 09:55 | 336 |
| $IF F$MODE() .EQS. "NETWORK" THEN GOTO PROXY_ACCESS
$IF F$MODE() .EQS. "BATCH" THEN GOTO PRIV
$PRIV:
$PRIV :== SET PROCESS/PRIVILEGES=ALL
$DEF :== SHOW DEFAULT
$SET PROT=(S:RWED,O:RWED,G,W)/DEFAULT
$V*DIR :== DIR/SIZE/DATE/PROT/WIDTH=(SIZE=7)
$ED*IT :== EDIT/EDT/command=DISK$ERCDEV_PRD01:[ERCPROD]edtini.edt
$LO*GOUT :== LOGOUT/FULL
$compas :== "print/queue=mso1_1d16_lps20/PARAM=(NUMBER_UP=2,SIDES=2)/prio=200"
$pr*int :== "print/queue=mso1_1d16_lps20/nofeed/prio=200"
$msgrdo :== "search/window=(0,10) sys$help:rd*.doc"
$msgcdo :== "search/window=(0,10) sys$help:cd*.doc"
$msgsql :== "search/window=(0,10) sys$help:sq*.doc"
$SUB*MIT :== SUBMIT/KEEP/NOTIFY/NOPRINT
$CPU :== MONITOR PROCESS/TOPCPU
$SPR :== SHOW PROCESS/CONT/ID=
$SPQ :== SHOW QUEUE/ALL/DEV
$bat*ch :== show system/batch
$DEL*ETE :== DELETE/CONFIRM
$PU*RGE :== PURGE/LOG
$COP*Y :== COPY/LOG
$DA*Y :== SHOW TIME
$EDT :== EDIT/EDT
$RA :== RECALL/ALL
$rec*all :== recall
$sql :== $sql$
$define home disk$ercdev_usr01:[bonin]
$home :== set def disk$ercdev_usr01:[bonin]
$define work disk$ercdev_wrk01:[work]
$define sort disk$ercdev_srt01:[sort],disk$ercdev_srt01:[sort1], -
disk$ercdev_srt01:[sort2],disk$ercdev_srt01:[sort3]
$define proto disk$ercdev_prd01:[cr_prototype]
$define test_dbi -
"/type=dbi/dbtype=rdb/dbname=DISK$ERCDEV_USR01:[BONIN.DBI]ERC_REV"
$DIR*ECTORY :== DIR/SIZE/DATE/PROT/WIDTH=(SIZE=7)
$node = f$getsyi("NODENAME")
$if node .eqs. "ERCDV1" then set prompt="[1mERCdv1 ==> [0m"
$SET PROCESS/PRIV=ALL
$PROXY_ACCESS:
$EXIT
$SET VERIFY
$SET OUTPUT_RATE=0:00:05
$define rdms$debug_flags "SO"
$define rdms$bind_buffers 500
$define rdms$bind_work_file disk$ercdev_wrk01:[work]
$define rdms$bind_sort_workfiles 4
$define rdms$bind_work_vm 1000000
$define rdm$bind_vm_segment 1
$define rdb$remote_multiplex_off "T"
$define dbi_wm_byte_limit 10000000
$define sys$scratch disk$ercdev_srt01:[sort]
$define sortwork0 disk$ercdev_srt01:[sort]
$define sortwork1 disk$ercdev_srt01:[sort1]
$define sortwork2 disk$ercdev_srt01:[sort2]
$define sortwork3 disk$ercdev_srt01:[sort3]
$define dbi_wm_file_directory disk$ercdev_srt01:[sort]
$define dbi_wm_file_directory_1 disk$ercdev_srt01:[sort1]
$define dbi_wm_file_directory_2 disk$ercdev_srt01:[sort2]
$define dbi_wm_file_directory_3 disk$ercdev_srt01:[sort3]
$define dbi_optimization_level "Fast First"
$define dbi_trace_flags "explain"
$define dbi_trace_output dbi_trace.out
$SQL
ATTACH 'FILE TEST_DBI';
Solutions tried 1
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 5.3333335E+00
Cardinality of chosen solution 1.0000000E+00
Firstn Get Retrieval sequentially of relation RDB$DATABASE
Solutions tried 1
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 3.0000000E+00
Cardinality of chosen solution 1.0000000E+00
Firstn Get Retrieval sequentially of relation DBI_DATABASE
Solutions tried 1
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 3.0000000E+00
Cardinality of chosen solution 1.0000000E+00
Get Retrieval sequentially of relation DBI_DATABASE
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 6.9499884E+00
Cardinality of chosen solution 5.0000000E+00
Get Retrieval by index of relation DBI_DATABASE_LINKS
Index name DBI_LINK_NAME_NDX [0:0]
Solutions tried 3
Solutions blocks created 2
Created solutions pruned 0
Cost of the chosen solution 6.5340080E+00
Cardinality of chosen solution 3.1250000E-01
Conjunct Get Retrieval by index of relation DBI_LINK_PROXIES
Index name DBI_USER_NAME_NDX [0:0] Bool
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 7.2881889E+01
Cardinality of chosen solution 1.6200000E+02
Get Retrieval by index of relation DBI_FIELDS
Index name DBI_FIELDS_NAME_NDX [0:0]
Solutions tried 3
Solutions blocks created 2
Created solutions pruned 1
Cost of the chosen solution 3.4830490E+01
Cardinality of chosen solution 8.5000000E+01
Get Retrieval by index of relation DBI_RELATIONS
Index name DBI_REL_REL_NAME_NDX [0:0]
Solutions tried 3
Solutions blocks created 2
Created solutions pruned 1
Cost of the chosen solution 2.2115061E+00
Cardinality of chosen solution 1.0000000E+01
Get Retrieval by index of relation DBI_MODULES
Index name DBI_MOD_ID_NDX [0:0]
Solutions tried 3
Solutions blocks created 2
Created solutions pruned 1
Cost of the chosen solution 2.4159481E+01
Cardinality of chosen solution 0.0000000E+00
Sort Conjunct Get Retrieval by index of relation DBI_ROUTINES
Index name DBI_RTN_ID_NDX [0:0]
Solutions tried 1
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 3.0000000E+00
Cardinality of chosen solution 1.0000000E+00
Firstn Retrieval sequentially of relation DBI_DATABASE
Solutions tried 3
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 2.1269038E+00
Cardinality of chosen solution 6.0000000E+00
Get Retrieval by index of relation DBI_USERS
Index name DBI_USERS_ID_NDX [0:0]
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 2.1899979E+00
Cardinality of chosen solution 3.1250000E-02
Aggregate Conjunct Get
Retrieval by index of relation DBI_DATABASE_LINKS
Index name DBI_LINK_NAME_NDX [1:1] Direct lookup
Solutions tried 3
Solutions blocks created 2
Created solutions pruned 0
Cost of the chosen solution 1.0423013E+00
Cardinality of chosen solution 1.0000000E+00
Firstn Aggregate Index only retrieval of relation DBI_DATABASE_LINKS
Index name DBI_LINK_NAME_NDX [1:1] Direct lookup
Solutions tried 3
Solutions blocks created 2
Created solutions pruned 0
Cost of the chosen solution 3.9467785E+00
Cardinality of chosen solution 2.8125000E-01
Leaf#01 FFirst DBI_LINK_PROXIES Card=0
BgrNdx1 DBI_USER_NAME_NDX [1:1] Bool Fan=4
SET TRANSACTION READ ONLY;
@DISK$ERCDEV_USR01:[BONIN.dbi]product.sql
SQL> select D6.ISO_CNTRY_CD,
cont> D6.ISO_CNTRY_NAME,
cont> G7.PRODT_ULTMT_GRP_CD,
cont> G7.PRODT_ULTMT_GRP_NAME,
cont> G1.PRODT_GRP_CD,
cont> G1.PRODT_GRP_NAME,
cont> F8.PRODT_AGRGT_CD,
cont> F8.PRODT_AGRGT_CD_DSCRPN,
cont> sum(PRODT_QTY)
cont> ,sum(WARNTY_AMT)
cont> ,sum(GROSS_AMT)
cont> ,sum(UPLIFT_AMT)
cont> ,sum(DLP_AMT)
cont> ,sum(DSCNT_AMT)
cont> ,sum(ALLWNC_AMT)
cont> ,sum(NOR_AMT)
cont> ,sum(C5.TRNSFR_AMT)
cont> from iso_cntry_rt D6
cont> ,PRODT_RT G2
cont> ,PRODT_AGRGT_RT F8
cont> ,PRODT_SET_RT G3
cont> ,PRODT_GRP_RT G1
cont> ,PRODT_ULTMT_GRP_RT G7
cont> , dtl_xplod_or_2 c5
cont> where (D6.ISO_CNTRY_CD = C5.ISO_CNTRY_CD)
cont> and ( D6.ISO_CNTRY_CD IN ('ES','EG','ER','FI','DZ','DJ','DE') )
cont> and (G2.PRODT_ID = C5.RVNUE_PRODT_ID)
cont> and (F8.PRODT_AGRGT_CD = G2.PRODT_AGRGT_CD)
cont> and (G3.PRODT_SET_CD = F8.PRODT_SET_CD)
cont> and (G1.PRODT_GRP_CD = G3.PRODT_GRP_CD)
cont> and (G7.PRODT_ULTMT_GRP_CD = G1.PRODT_ULTMT_GRP_CD)
cont> and FYR_FQTR_FWK between 1996201 and 1996213
cont> and DTL_XPLOD_INDCR In ( 'B' ,'D' , 'M')
cont> group by D6.ISO_CNTRY_NAME,D6.ISO_CNTRY_CD,
cont> G7.PRODT_ULTMT_GRP_NAME,G7.PRODT_ULTMT_GRP_CD,
cont> G1.PRODT_GRP_NAME,G1.PRODT_GRP_CD,
cont> F8.PRODT_AGRGT_CD_DSCRPN,F8.PRODT_AGRGT_CD
cont> order by D6.ISO_CNTRY_NAME,D6.ISO_CNTRY_CD,
cont> G7.PRODT_ULTMT_GRP_NAME,G7.PRODT_ULTMT_GRP_CD,
cont> G1.PRODT_GRP_NAME,G1.PRODT_GRP_CD,
cont> F8.PRODT_AGRGT_CD_DSCRPN,F8.PRODT_AGRGT_CD;
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 2.4306905E+00
Cardinality of chosen solution 3.1250000E-02
Aggregate Conjunct Get
Retrieval by index of relation DBI_RELATIONS
Index name DBI_REL_REL_NAME_NDX [1:1] Direct lookup
Solutions tried 3
Solutions blocks created 2
Created solutions pruned 0
Cost of the chosen solution 1.4306904E+00
Cardinality of chosen solution 1.0000000E+00
Firstn Aggregate Index only retrieval of relation DBI_RELATIONS
Index name DBI_REL_REL_NAME_NDX [1:1] Direct lookup
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 4.9204202E+02
Cardinality of chosen solution 0.0000000E+00
Sort
Leaf#01 BgrOnly DBI_RELATION_FIELDS Card=922
BgrNdx1 DBI_RFR_REL_NAME_FLD_ID_NDX [0:0] Bool Fan=8
Solutions tried 3
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 2.7614224E+00
Cardinality of chosen solution 0.0000000E+00
Conjunct Get Retrieval by index of relation DBI_VIEW_RELATIONS
Index name DBI_VIEW_VIEW_NAME_NDX [0:0] Bool
Solutions tried 3
Solutions blocks created 3
Created solutions pruned 2
Cost of the chosen solution 1.0382317E+02
Cardinality of chosen solution 0.0000000E+00
Sort Conjunct Get
Retrieval sequentially of relation DBI_INTERRELATIONS
Solutions tried 2
Solutions blocks created 2
Created solutions pruned 1
Cost of the chosen solution 3.9826561E+01
Cardinality of chosen solution 0.0000000E+00
Sort Conjunct Get Retrieval sequentially of relation DBI_INDICES
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 4.5522938E+01
Cardinality of chosen solution 0.0000000E+00
Conjunct Get Retrieval by index of relation DBI_INDEX_SEGMENTS
Index name DBI_NDX_SEG_NAM_FLD_POS_NDX [0:0] Bool
Solutions tried 3
Solutions blocks created 2
Created solutions pruned 1
Cost of the chosen solution 2.2063452E+01
Cardinality of chosen solution 0.0000000E+00
Sort
Leaf#01 BgrOnly DBI_VIEW_PARTITIONS Card=3
BgrNdx1 DBI_VIEW_PARTN_ID_NDX [0:0] Fan=17
BgrNdx2 DBI_VIEW_PARTN_NDX [0:0] Bool Fan=5
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 3.2134743E+01
Cardinality of chosen solution 0.0000000E+00
Conjunct Get Retrieval by index of relation DBI_VIEW_PARTITION_FIELDS
Index name DBI_VIEW_PARTN_FLD_NDX [0:0] Bool
Solutions tried 1
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 2.1319008E+05
Cardinality of chosen solution 1.0000000E+00
Firstn Get Retrieval sequentially of relation DTL_XPLOD_OR
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 3.5407174E+00
Cardinality of chosen solution 1.0000000E+00
Firstn Index only retrieval of relation ISO_CNTRY_RT
Index name ISO_CNTRY_RT_IDX_1 [0:0]
Solutions tried 2770
Solutions blocks created 233
Created solutions pruned 183
Cost of the chosen solution 3.1087580E+04
Cardinality of chosen solution 3.4884478E+05
Conjunct
Match
Outer loop
Sort Conjunct
Match
Outer loop
Sort Conjunct
Match
Outer loop
Cross block of 2 entries
Cross block entry 1
Get Retrieval by index of relation PRODT_GRP_RT
Index name PRODT_GRP_RT_IDX_1 [0:0]
Cross block entry 2
Leaf#01 BgrOnly PRODT_ULTMT_GRP_RT Card=7
BgrNdx1 PRODT_ULTMT_GRP_RT_IDX_1 [1:1] Fan=16
Inner loop (zig-zag)
Get Retrieval by index of relation PRODT_SET_RT
Index name PRODT_SET_RT_IDX_2 [0:0]
Inner loop (zig-zag)
Get Retrieval by index of relation PRODT_AGRGT_RT
Index name PRODT_AGRGT_RT_IDX_3 [0:0]
Inner loop (zig-zag)
Get Retrieval by index of relation PRODT_RT
Index name PRODT_RT_IDX_6 [0:0]
Solutions tried 3
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 4.0480156E+00
Cardinality of chosen solution 6.9141698E+00
Leaf#01 FFirst ISO_CNTRY_RT Card=243
BgrNdx1 ISO_CNTRY_RT_IDX_1 [1:1...]7 Fan=19
Solutions tried 7
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 1.1606041E+03
Cardinality of chosen solution 5.7892578E+03
Leaf#01 FFirst DTL_XPLOD_OR Card=4246409
BgrNdx1 DTL_XPLOD_OR_IDX_1 [1:1] Bool Fan=29
BgrNdx2 DTL_XPLOD_OR_IDX_17 [1:1...]3 Fan=32
%DBI-F-BUGCHECK: bugcheck dump will be written to DISK$ERCDEV_USR01:[BONIN]DBI_BUGCHECK.DMP;1
%DBI-F-INTERR, There is an internal software error - please submit an SPR
%DCL-W-SKPDAT, image data (records not beginning with "$") ignored
BONIN job terminated at 12-JUN-1996 00:37:38.80
Accounting information:
Buffered I/O count: 113397 Peak working set size: 164080
Direct I/O count: 2153223 Peak page file size: 230320
Page faults: 9830 Mounted volumes: 0
Charged CPU time: 0 02:34:23.74 Elapsed time: 0 15:55:52.98
|
| 1065.14 | dbi explain output | ORAREP::USDEV::JBONIN | Technical Surgeon, AYS | Wed Jun 12 1996 09:55 | 184 |
| ---EVENT BEG: EXPLAIN -------------------------- Tue Jun 11 08:41:52.414 1996---
Step 1 At DBI
Cardinality 1 Tuple Length 20 Cost 10.12 +Per Tuple 0
Left Operand 2
RESULT
Step 2 At DBI$MDD_LINK_NAME
Cardinality 1 Tuple Length 20 Cost 10.12 +Per Tuple 0
STREAM FROM DBI$MDD_LINK_NAME TO DBI
SELECT RDB$RELATIONS.RDB$DBKEY_LENGTH, RDB$RELATIONS.RDB$RELATION_ID,
RDB$RELATIONS.RDB$VIEW_BLR, RDB$RELATIONS.RDB$SYSTEM_FLAG
FROM RDB$RELATIONS T001
WHERE (RDB$RELATIONS.RDB$RELATION_NAME = <PARAM>)
---EVENT END: EXPLAIN -------------------------- Tue Jun 11 08:41:52.416 1996---
---EVENT BEG: EXPLAIN -------------------------- Tue Jun 11 08:41:52.066 1996---
Step 1 At DBI
Cardinality 1 Tuple Length 675 Cost 149.004 +Per Tuple 0
Left Operand 2
RESULT
Step 2 At DBI
Cardinality 1 Tuple Length 675 Cost 149.004 +Per Tuple 0
Left Operand 3
ORDER BY 002 ASC
Step 3 At DBI
Cardinality 1 Tuple Length 675 Cost 147.004 +Per Tuple 0
Left Operand 4
SELECT RDB$RELATION_FIELDS.RDB$FIELD_NAME,
RDB$RELATION_FIELDS.RDB$FIELD_POSITION,
RDB$RELATION_FIELDS.RDB$FIELD_ID, RDB$RELATION_FIELDS.RDB$QUERY_HEADER,
RDB$FIELDS.RDB$QUERY_HEADER, RDB$RELATION_FIELDS.RDB$EDIT_STRING,
RDB$FIELDS.RDB$EDIT_STRING, RDB$FIELDS.RDB$FIELD_TYPE,
RDB$FIELDS.RDB$FIELD_LENGTH, RDB$FIELDS.RDB$FIELD_SCALE,
RDB$FIELDS.RDB$COMPUTED_BLR, RDB$FIELDS.RDB$SEGMENT_LENGTH,
RDB$FIELDS.RDB$FIELD_SUB_TYPE, RDB$RELATION_FIELDS.RDB$QUERY_NAME,
RDB$FIELDS.RDB$QUERY_NAME, RDB$RELATION_FIELDS.RDB$DEFAULT_VALUE,
RDB$FIELDS.RDB$DEFAULT_VALUE
Step 4 At DBI
Cardinality 1 Tuple Length 675 Cost 146.004 +Per Tuple 0
Left Operand 5 Right Operand 6
HASH JOIN RDB$FIELDS.RDB$FIELD_NAME = RDB$RELATION_FIELDS.RDB$FIELD_SOURCE
Step 5 At DBI$MDD_LINK_NAME
Cardinality 1 Tuple Length 374 Cost 66.374 +Per Tuple 0
STREAM FROM DBI$MDD_LINK_NAME TO DBI
SELECT RDB$RELATION_FIELDS.RDB$DEFAULT_VALUE,
RDB$RELATION_FIELDS.RDB$QUERY_NAME,
RDB$RELATION_FIELDS.RDB$EDIT_STRING,
RDB$RELATION_FIELDS.RDB$QUERY_HEADER,
RDB$RELATION_FIELDS.RDB$FIELD_ID,
RDB$RELATION_FIELDS.RDB$FIELD_NAME,
RDB$RELATION_FIELDS.RDB$FIELD_POSITION,
RDB$RELATION_FIELDS.RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS T001
WHERE (RDB$RELATION_FIELDS.RDB$RELATION_NAME = <PARAM>)
Step 6 At DBI$MDD_LINK_NAME
Cardinality 10 Tuple Length 363 Cost 68.63 +Per Tuple 0
STREAM FROM DBI$MDD_LINK_NAME TO DBI
SELECT RDB$FIELDS.RDB$DEFAULT_VALUE, RDB$FIELDS.RDB$QUERY_NAME,
RDB$FIELDS.RDB$FIELD_SUB_TYPE, RDB$FIELDS.RDB$SEGMENT_LENGTH,
RDB$FIELDS.RDB$COMPUTED_BLR, RDB$FIELDS.RDB$FIELD_SCALE,
RDB$FIELDS.RDB$FIELD_LENGTH, RDB$FIELDS.RDB$FIELD_TYPE,
RDB$FIELDS.RDB$EDIT_STRING, RDB$FIELDS.RDB$QUERY_HEADER,
RDB$FIELDS.RDB$FIELD_NAME
FROM RDB$FIELDS
---EVENT END: EXPLAIN -------------------------- Tue Jun 11 08:41:52.068 1996---
---EVENT BEG: EXPLAIN -------------------------- Tue Jun 11 08:41:58.182 1996---
Step 1 At DBI
Cardinality 84.37915 Tuple Length 211 Cost 1.2815281e+12 +Per Tuple 0
Left Operand 2
RESULT
Step 2 At DBI
Cardinality 84.37915 Tuple Length 211 Cost 1.2815281e+12 +Per Tuple 0
Left Operand 3
ORDER BY 002 ASC, 001 ASC, 004 ASC, 003 ASC, 006 ASC, 005 ASC, 008 ASC, 007
ASC
Step 3 At DBI
Cardinality 84.37915 Tuple Length 211 Cost 1.2815281e+12 +Per Tuple 0
Left Operand 4
SELECT ISO_CNTRY_RT.ISO_CNTRY_CD, ISO_CNTRY_RT.ISO_CNTRY_NAME,
PRODT_ULTMT_GRP_RT.PRODT_ULTMT_GRP_CD,
PRODT_ULTMT_GRP_RT.PRODT_ULTMT_GRP_NAME, PRODT_GRP_RT.PRODT_GRP_CD,
PRODT_GRP_RT.PRODT_GRP_NAME, PRODT_AGRGT_RT.PRODT_AGRGT_CD,
PRODT_AGRGT_RT.PRODT_AGRGT_CD_DSCRPN, SUM (T001.C002), SUM (T001.C003),
SUM (T001.C004), SUM (T001.C005), SUM (T001.C006), SUM (T001.C007),
SUM (T001.C008), SUM (T001.C009), SUM (T001.C010)
Step 4 At DBI
Cardinality 84.37915 Tuple Length 211 Cost 1.2815281e+12 +Per Tuple 0
Left Operand 5
GROUP BY ISO_CNTRY_RT.ISO_CNTRY_NAME, ISO_CNTRY_RT.ISO_CNTRY_CD,
PRODT_ULTMT_GRP_RT.PRODT_ULTMT_GRP_NAME,
PRODT_ULTMT_GRP_RT.PRODT_ULTMT_GRP_CD, PRODT_GRP_RT.PRODT_GRP_NAME,
PRODT_GRP_RT.PRODT_GRP_CD, PRODT_AGRGT_RT.PRODT_AGRGT_CD_DSCRPN,
PRODT_AGRGT_RT.PRODT_AGRGT_CD
Step 5 At DBI
Cardinality 7119.841 Tuple Length 207 Cost 1.2815279e+12 +Per Tuple 0
Left Operand 6
PREDICATE PRODT_RT.PRODT_ID = T001.C011
Step 6 At DBI
Cardinality 2.631394e+09 Tuple Length 233
Cost 1.2788965e+12 +Per Tuple 0
Left Operand 7
PREDICATE ISO_CNTRY_RT.ISO_CNTRY_CD = T001.C012
Step 7 At DBI
Cardinality 6.394286e+11 Tuple Length 235
Cost 6.3946792e+11 +Per Tuple 0
Left Operand 8 Right Operand 9
CROSS PRODUCT
Step 8 At REV_RPT_REF
Cardinality 369586 Tuple Length 100 Cost 227791.4 +Per Tuple 0
STREAM FROM REV_RPT_REF TO DBI
SELECT PRODT_RT.PRODT_ID, PRODT_AGRGT_RT.PRODT_AGRGT_CD_DSCRPN,
PRODT_AGRGT_RT.PRODT_AGRGT_CD, PRODT_GRP_RT.PRODT_GRP_NAME,
PRODT_GRP_RT.PRODT_GRP_CD, PRODT_ULTMT_GRP_RT.PRODT_ULTMT_GRP_NAME,
PRODT_ULTMT_GRP_RT.PRODT_ULTMT_GRP_CD
FROM PRODT_RT T013, PRODT_AGRGT_RT T014, PRODT_SET_RT T015, PRODT_GRP_RT
T016, PRODT_ULTMT_GRP_RT T017
WHERE (PRODT_AGRGT_RT.PRODT_AGRGT_CD = PRODT_RT.PRODT_AGRGT_CD)
AND (PRODT_SET_RT.PRODT_SET_CD = PRODT_AGRGT_RT.PRODT_SET_CD)
AND (PRODT_GRP_RT.PRODT_GRP_CD = PRODT_SET_RT.PRODT_GRP_CD)
AND (PRODT_ULTMT_GRP_RT.PRODT_ULTMT_GRP_CD =
PRODT_GRP_RT.PRODT_ULTMT_GRP_CD)
Step 9 At DBI
Cardinality 1730121 Tuple Length 135 Cost 36965307 +Per Tuple 0
Left Operand 10 Right Operand 11
CROSS PRODUCT
Step 10 At REV_RPT_REF
Cardinality 126.7739 Tuple Length 52 Cost 255.19224 +Per Tuple 0
STREAM FROM REV_RPT_REF TO DBI
SELECT ISO_CNTRY_RT.ISO_CNTRY_NAME, ISO_CNTRY_RT.ISO_CNTRY_CD
FROM ISO_CNTRY_RT T018
WHERE ((ISO_CNTRY_RT.ISO_CNTRY_CD = 'ES'
OR (ISO_CNTRY_RT.ISO_CNTRY_CD = 'EG'
OR (ISO_CNTRY_RT.ISO_CNTRY_CD = 'ER'
OR (ISO_CNTRY_RT.ISO_CNTRY_CD = 'FI'
OR (ISO_CNTRY_RT.ISO_CNTRY_CD = 'DZ'
OR (ISO_CNTRY_RT.ISO_CNTRY_CD = 'DJ'
OR ISO_CNTRY_RT.ISO_CNTRY_CD = 'DE')))))))
Step 11 At REV_RPT_CFY
Cardinality 13647.3 Tuple Length 83 Cost 35221155 +Per Tuple 0
STREAM FROM REV_RPT_CFY TO DBI
SELECT DTL_XPLOD_OR_CFY.ISO_CNTRY_CD, DTL_XPLOD_OR_CFY.RVNUE_PRODT_ID,
DTL_XPLOD_OR_CFY.PRODT_QTY, DTL_XPLOD_OR_CFY.WARNTY_AMT,
DTL_XPLOD_OR_CFY.GROSS_AMT, DTL_XPLOD_OR_CFY.UPLIFT_AMT,
DTL_XPLOD_OR_CFY.DSCNT_AMT, DTL_XPLOD_OR_CFY.ALLWNC_AMT,
DTL_XPLOD_OR_CFY.TRNSFR_AMT, DTL_XPLOD_OR_CFY.DLP_AMT,
DTL_XPLOD_OR_CFY.NOR_AMT
FROM DTL_XPLOD_OR_CFY T019
WHERE (((DTL_XPLOD_OR_CFY.FYR_FQTR_FWK >= 1996101
AND DTL_XPLOD_OR_CFY.FYR_FQTR_FWK <= 1996414)
AND ((DTL_XPLOD_OR_CFY.FYR_FQTR_FWK >= 1996201
AND DTL_XPLOD_OR_CFY.FYR_FQTR_FWK <= 1996213)
AND (DTL_XPLOD_OR_CFY.DTL_XPLOD_INDCR = 'B'
OR (DTL_XPLOD_OR_CFY.DTL_XPLOD_INDCR = 'D'
OR DTL_XPLOD_OR_CFY.DTL_XPLOD_INDCR = 'M')))))
---EVENT END: EXPLAIN -------------------------- Tue Jun 11 08:41:58.188 1996---
|
| 1065.15 | Might be fixed... | BROKE::ABUGOV | | Wed Jun 12 1996 12:29 | 12 |
|
Hi John,
We have already started looking at this without the bugcheck (for now
we are assuming we ran out memory hence the bugcheck). We looked at
the trace it looks like we may have just fixed something in FT2 of DBI7
that is similar, and we'd like you to try it to see if this is indeed
fixed.
Thanks,
dan
|
| 1065.16 | Queries to single tables with fast first -> no dc files | BROKE::ABUGOV | | Wed Jun 12 1996 19:52 | 15 |
|
Hi John,
Also regarding the earlier problem - in our discussions here regarding
the query that uses all of the workspace files, if you are selecting
from a single partition or table and you have fast first enabled then
DBI will not build a data collection, and you should be OK regarding
disk and virtual memory usage. If you are getting data from multiple
partitions then we build the data colleciton. We are still looking at
optimizing our resource use in these situations, but it looks like
things are at least a little better than we had originally thought.
Hope this helps,
Dan
|
| 1065.17 | is this a bug? | ORAREP::USDEV::JBONIN | Technical Surgeon, AYS | Thu Jun 13 1996 12:31 | 8 |
| I am running into another problem. Part of the current system on the
VAX was built using selections from RDB system tables. Now on the
alpha the code is breaking because DBI is noT updating the statistics
information for the table RDB$FIELD_VERSIONS. This table is empty
and even 'alter table xx update statistics' doesn't fill this table.
Thanks,
John
|
| 1065.18 | | ORAREP::USDEV::JBONIN | Technical Surgeon, AYS | Thu Jun 13 1996 14:11 | 11 |
| re .16
Dan,
Which problem? :-) Which query are you referring to, in .0? As far as I
know, it still built the temp workspace files (4+ million blocks) even
after we enabled fast first. I have even enabled this logical in the
login file, so all dbi fetches are fast first. I know the query in .0
finds data only in 1 partition, and still produces dbi temp workfiles.
John
|
| 1065.19 | I have the dump file | ORAREP::USDEV::JBONIN | Technical Surgeon, AYS | Thu Jun 13 1996 14:44 | 7 |
| I now have the bugcheck dump file 200,000 blocks! if interested.
Can different versions of DBI be installed on the same system?
Does DBI V7 need RDB V7? I have the dbi7 software, but the only
system I am able to test it on is my vaxstation 4000, and I don't
have the disk space for any benchmarks.
John
|
| 1065.20 | rdb$field_versions support in DBI7 | PORTME::ABUGOV | | Thu Jun 13 1996 16:38 | 9 |
|
Hi John,
We added support for rdb$field_versions in DBI7. I think you might
like the FT version of the updated product!
Regards,
Dan
|
| 1065.21 | We'll check here, too. | PORTME::ABUGOV | | Thu Jun 13 1996 16:41 | 12 |
|
Hi John,
re .18
Yup, I was referring to the query in .0 that only touched one
partition. Could you double check (we'll make sure in the DBI7 code
stream here).
Thanks,
dan
|
| 1065.22 | The beginning of the dump file would be useful... | PORTME::ABUGOV | | Thu Jun 13 1996 17:01 | 26 |
|
<<< Note 1065.19 by ORAREP::USDEV::JBONIN "Technical Surgeon, AYS" >>>
Hi John,
Can you copy the first 100 blocks of the dump file (or so) to orarep""::
and send me mail? I'd like to have a look at it.
Thanks,
dan
=*=
-< I have the dump file >-
I now have the bugcheck dump file 200,000 blocks! if interested.
Can different versions of DBI be installed on the same system?
Does DBI V7 need RDB V7? I have the dbi7 software, but the only
system I am able to test it on is my vaxstation 4000, and I don't
have the disk space for any benchmarks.
John
|
| 1065.23 | | ORAREP::USDEV::JBONIN | Technical Surgeon, AYS | Fri Jun 14 1996 11:35 | 7 |
| I would really like to install DBI7 on the alpha, but we don't want to
do our development against field test software. Any release dates? Is
it close? Also, we are running RDB V6.1-03, and I'm not sure if it is
compatible with DBI7.
Thanks,
John (who's sure he will like DBI7)
|
| 1065.24 | more interesting things | ORAREP::USDEV::JBONIN | Technical Surgeon, AYS | Fri Jun 14 1996 12:03 | 22 |
| re .21
I may have just uncovered a bug.
No, the temporary files are not created when run in a batch job using
interactive SQL, because the log shows DBI only accessing the one
CFY parition.
This same query in a sql module procedure called by a 3gl shows that
all 3 partitions are accessed, and therefore producing the large
work files. It appears that horizontal partitioning is not working
correctly in callable sql, but is in interactive.
Another weird thing, The interactive version just puts output to
the log. After the log grew to around 2,000 blocks, there was no
output to the log for about 3-5 minutes while 4 sortwork files were
created, with 1 sort file growing to around 100,000 blocks. Then
the sortork files disappeared, and output resumed to the log. What
happened here? You were right though, when DBI accessed the 1
partition, it didn't create dbi temp files, just sort files.
John
|
| 1065.25 | dump file copied | ORAREP::USDEV::JBONIN | Technical Surgeon, AYS | Fri Jun 14 1996 12:08 | 9 |
| re: .22
How about the first 700+? It's hard to time that control-c
right! :-)
I just copied the dump file to orarep::testdbi.dmp
Thanks,
John
|
| 1065.26 | You'll love it... | BROKE::ABUGOV | | Mon Jun 17 1996 10:01 | 35 |
|
Hi John,
DBI7 is due approximately two months after Rdb7 (we have a relative
shipping date). Right now that looks to be in October. We'll ship
another field test late this month or very early July, and we'll send
a final "sanity" kit in late August/early September.
John, we feel our field test software is of very high quality, and we
are sure you will like what you see. Additionally, you have a chance
to influence development for the current version if you find
bugs and/or see behaviour that doesn't make sense. We've already made
improvements to the current field test version based on field test 1
feedback. It sounds like you are using the product to its full
capabilities (and then some) and hence would be a great field test
site. If you wait till we ship and then give us feedback on the new
version, you'll have to wait until DBI8.
DBI7 field test software works with RdbV4.1 -> RdbT7.0.
Thanks,
Dan
> <<< Note 1065.23 by ORAREP::USDEV::JBONIN "Technical Surgeon, AYS">>>
>
> I would really like to install DBI7 on the alpha, but we don't want to
> do our development against field test software. Any release dates? Is
> it close? Also, we are running RDB V6.1-03, and I'm not sure if it is
> compatible with DBI7.
>
> Thanks,
> John (who's sure he will like DBI7)
|
| 1065.27 | It's in there.... | BROKE::ABUGOV | | Mon Jun 17 1996 10:27 | 32 |
|
> I may have just uncovered a bug.
>
> No, the temporary files are not created when run in a batch job using
> interactive SQL, because the log shows DBI only accessing the one
> CFY parition.
>
> This same query in a sql module procedure called by a 3gl shows that
> all 3 partitions are accessed, and therefore producing the large
> work files. It appears that horizontal partitioning is not working
> correctly in callable sql, but is in interactive.
Actually, partition elimination from SQL/Mod and SQL/Pre applications
was added to DBI7 (you may see a feature called deferred compilation
which was added to DBI7 - this is the feature that allows us to until
we know the values of parameters before compiling so we know if
partition elimination can take place).
> Another weird thing, The interactive version just puts output to
> the log. After the log grew to around 2,000 blocks, there was no
> output to the log for about 3-5 minutes while 4 sortwork files were
> created, with 1 sort file growing to around 100,000 blocks. Then
> the sortork files disappeared, and output resumed to the log. What
> happened here? You were right though, when DBI accessed the 1
> partition, it didn't create dbi temp files, just sort files.
This is wierd. Do you know the names of the files that were created?
Thanks,
dan
|
| 1065.28 | Looking at the bugcheck. | BROKE::ABUGOV | | Mon Jun 17 1996 12:01 | 6 |
|
Hi John,
Thanks for forwarding the bugcheck along. We're looking at it.
Dan
|
| 1065.29 | 4 sortwork files | ORAREP::USDEV::JBONIN | Technical Surgeon, AYS | Mon Jun 17 1996 13:45 | 6 |
| re: .27
The files that were created were 4 SORTWORK.TMP files all pointing to
their corresponding sortwork logicals.
John
|
| 1065.30 | Can't wait to try DBI7! | ORAREP::USDEV::JBONIN | Technical Surgeon, AYS | Mon Jun 17 1996 13:56 | 14 |
| Dan,
Do you think the query which bugchecked is because of getting fields
from distantly related tables? If you look at the query, some fields
are derived from reference tables which have no direct key link to the
master. Look at the query and you'll see what I mean. The fields from
the table PRODT_ULTMT_GRP_RT G7 that are selected have to be found
through links in other tables.
Does this kind of "multi-reference derived join" (not having a better
name for it) perform badly because the database is so denormalized?
Thanks,
John
|
| 1065.31 | another poorly performing query vs vax | ORAREP::USDEV::JBONIN | Technical Surgeon, AYS | Tue Jun 18 1996 14:18 | 54 |
| I have another query qhich performs very poorly on Alpha RDB V6.1
with DBI, while the vax version accessing a single RDB database
without DBI works quite well.
Again, it seems to stem from queries which get fields from reference
tables which have to be derived by mapping to multiple tables where
the data is normalized.
This query runs in about 30 minutes on the vax, but takes hours in
DBI. Straight joins from the detail table to a reference table work
well in DBI, it's only when the query has to map through a few tables
that performance suffers.
Here's the query...
select D6.ISO_CNTRY_CD,
D6.ISO_CNTRY_NAME,
A7.BU_ACCT_ID,
A7.BU_ACCT_NAME,
C1.CUSTMR_ADDR_ID,
C1.CUSTMR_ADDR_NAME,
PRODT_QTY
,GROSS_AMT + WARNTY_AMT
,WARNTY_AMT
,GROSS_AMT
,UPLIFT_AMT
,DLP_AMT
,DSCNT_AMT
,ALLWNC_AMT
,NOR_AMT
,C5.TRNSFR_AMT
from BU_ACCT_RT A7
, BU_SEGMT_RT B1
, ISO_CNTRY_RT D6
, CUSTMR_ADDR_RT C1
, DTL_XPLOD_OR_CFY C5
where (C5.ISO_CNTRY_CD=D6.ISO_CNTRY_CD)
and ( C5.ISO_CNTRY_CD IN ('AU') )
and (C5.CUSTMR_ADDR_ID=C1.CUSTMR_ADDR_ID)
and (C1.BU_ACCT_ID=A7.BU_ACCT_ID)
and c5.rvnue_bu_segmt_cd = b1.bu_segmt_cd
and FYR_FQTR_FWK between 1996201 and 1996213
and b1.bu_cd = '800'
and DTL_XPLOD_INDCR In ( 'B' ,'D' , 'M')
order by D6.ISO_CNTRY_NAME, D6.ISO_CNTRY_CD,
A7.BU_ACCT_NAME, A7.BU_ACCT_ID,
C1.CUSTMR_ADDR_NAME, C1.CUSTMR_ADDR_ID
In DBI, it creates many temporary files of large sizes and takes
forever to run.
Thanks,
John
|
| 1065.32 | Empty Rdb database available? | BROKE::ABUGOV | | Tue Jun 18 1996 14:47 | 15 |
|
Hi John,
Is there any way you can make an empty Rdb database (or databases)
available to us as well as your catalog? The old explain trace you
made for us showed some funkiness that we think has been fixed in DBI7,
but I would like to make sure. The performance problem stems from the
cross products which showed up in the trace (I assume this new query
would show something similar - if you set trace flags to explain and
post the explain output that would show us for sure; there is no reason
to turn on Rdb tracing too).
Thanks,
dan
|
| 1065.33 | cross products = ?? | ORAREP::USDEV::JBONIN | Technical Surgeon, AYS | Wed Jun 19 1996 08:49 | 6 |
| Dan,
Are the cross products you refer to the same as the past few replies
where I talk about "derived fields" from distantly related tables?
John
|
| 1065.34 | You really could use DBI7 FT2... | BROKE::ABUGOV | | Wed Jun 19 1996 19:50 | 170 |
|
Hi John,
The cross products we saw in the trace were from DBI not noticing that
efficient joins could be done. Thanks for making the DBI Database and
the empty Rdb databases available to us. We have some data now that
shows:
DBI V3.1 did a poor job optimizing this request
DBI7 FT1 does about the same as DBI V3.1
DBI7 FT2 will do a much better job optimizing this request.
Attached is the new trace output from the query in note 13 (corresponds
to the tracing output in note 14). This in itself should be a major
performance improvement. I'm not sure if your link specifications are
remote, but potentially you can get even more speed if you aren't
already CPU or I/O bound by increasing concurrency for some of the
requests that get farmed off to the underlying rdb databases.
Anyway, if you want me to check how DBI behaves with other queries I'd
be happy to trace them through to make sure we are doing the right
things. We have just code frozen for our next field test though so
unless there are major problems any other enhancements won't be going
into FT2.
Thanks for working with us on these issues - we really do appreciate
it.
Regards,
Dan
select D6.ISO_CNTRY_CD,
D6.ISO_CNTRY_NAME,
G7.PRODT_ULTMT_GRP_CD,
G7.PRODT_ULTMT_GRP_NAME,
G1.PRODT_GRP_CD,
G1.PRODT_GRP_NAME,
F8.PRODT_AGRGT_CD,
F8.PRODT_AGRGT_CD_DSCRPN,
sum(PRODT_QTY)
,sum(WARNTY_AMT)
,sum(GROSS_AMT)
,sum(UPLIFT_AMT)
,sum(DLP_AMT)
,sum(DSCNT_AMT)
,sum(ALLWNC_AMT)
,sum(NOR_AMT)
,sum(C5.TRNSFR_AMT)
from iso_cntry_rt D6
,PRODT_RT G2
,PRODT_AGRGT_RT F8
,PRODT_SET_RT G3
,PRODT_GRP_RT G1
,PRODT_ULTMT_GRP_RT G7
, dtl_xplod_or_2 c5
where (D6.ISO_CNTRY_CD = C5.ISO_CNTRY_CD)
and ( D6.ISO_CNTRY_CD IN ('ES','EG','ER','FI','DZ','DJ','DE') )
and (G2.PRODT_ID = C5.RVNUE_PRODT_ID)
and (F8.PRODT_AGRGT_CD = G2.PRODT_AGRGT_CD)
and (G3.PRODT_SET_CD = F8.PRODT_SET_CD)
and (G1.PRODT_GRP_CD = G3.PRODT_GRP_CD)
and (G7.PRODT_ULTMT_GRP_CD = G1.PRODT_ULTMT_GRP_CD)
and FYR_FQTR_FWK between 1996201 and 1996213
and DTL_XPLOD_INDCR In ( 'B' ,'D' , 'M')
group by D6.ISO_CNTRY_NAME,D6.ISO_CNTRY_CD,
G7.PRODT_ULTMT_GRP_NAME,G7.PRODT_ULTMT_GRP_CD,
G1.PRODT_GRP_NAME,G1.PRODT_GRP_CD,
F8.PRODT_AGRGT_CD_DSCRPN,F8.PRODT_AGRGT_CD
order by D6.ISO_CNTRY_NAME,D6.ISO_CNTRY_CD,
G7.PRODT_ULTMT_GRP_NAME,G7.PRODT_ULTMT_GRP_CD,
G1.PRODT_GRP_NAME,G1.PRODT_GRP_CD,
F8.PRODT_AGRGT_CD_DSCRPN,F8.PRODT_AGRGT_CD;
was in fact not producing an optimal plan (see reply 14 to this note)
For DBI7 FT2 the plan looks much better, i.e.:
Step 1 At DBI
Cardinality 84.37915 Tuple Length 211 Cost 4.5639067e+07 +Per Tuple 0
Left Operand 2
RESULT
Step 2 At DBI
Cardinality 84.37915 Tuple Length 211 Cost 4.5639067e+07 +Per Tuple 0
Left Operand 3
ORDER BY 2 ASC, 1 ASC, 4 ASC, 3 ASC, 6 ASC, 5 ASC, 8 ASC, 7 ASC
Step 3 At DBI
Cardinality 84.37915 Tuple Length 211 Cost 4.5638319e+07 +Per Tuple 0
Left Operand 4
SELECT ISO_CNTRY_RT.ISO_CNTRY_CD, ISO_CNTRY_RT.ISO_CNTRY_NAME,
PRODT_ULTMT_GRP_RT.PRODT_ULTMT_GRP_CD,
PRODT_ULTMT_GRP_RT.PRODT_ULTMT_GRP_NAME, PRODT_GRP_RT.PRODT_GRP_CD,
PRODT_GRP_RT.PRODT_GRP_NAME, PRODT_AGRGT_RT.PRODT_AGRGT_CD,
PRODT_AGRGT_RT.PRODT_AGRGT_CD_DSCRPN, SUM (T2.C1), SUM (T2.C3), SUM (
T2.C4), SUM (T2.C5), SUM (T2.C6), SUM (T2.C7), SUM (T2.C8), SUM (T2.C9),
SUM (T2.C10)
Step 4 At DBI
Cardinality 84.37915 Tuple Length 211 Cost 4.5638234e+07 +Per Tuple 0
Left Operand 5
GROUP BY ISO_CNTRY_RT.ISO_CNTRY_NAME, ISO_CNTRY_RT.ISO_CNTRY_CD,
PRODT_ULTMT_GRP_RT.PRODT_ULTMT_GRP_NAME,
PRODT_ULTMT_GRP_RT.PRODT_ULTMT_GRP_CD, PRODT_GRP_RT.PRODT_GRP_NAME,
PRODT_GRP_RT.PRODT_GRP_CD, PRODT_AGRGT_RT.PRODT_AGRGT_CD_DSCRPN,
PRODT_AGRGT_RT.PRODT_AGRGT_CD
Step 5 At DBI
Cardinality 7119.841 Tuple Length 207 Cost 4.5511919e+07 +Per Tuple 0
Left Operand 6 Right Operand 7
HASH JOIN PRODT_RT.PRODT_ID = T2.C11
Step 6 At DBI
Cardinality 7119.841 Tuple Length 133 Cost 3.5667773e+07 +Per Tuple 0
Left Operand 8 Right Operand 9
HASH JOIN ISO_CNTRY_RT.ISO_CNTRY_CD = T2.C12
Step 8 At REV_RPT_REF
Cardinality 126.7739 Tuple Length 52 Cost 1903.252 +Per Tuple 0
STREAM FROM REV_RPT_REF TO DBI
SELECT ISO_CNTRY_RT.ISO_CNTRY_NAME, ISO_CNTRY_RT.ISO_CNTRY_CD
FROM ISO_CNTRY_RT T13
WHERE ((ISO_CNTRY_RT.ISO_CNTRY_CD = 'ES'
OR (ISO_CNTRY_RT.ISO_CNTRY_CD = 'EG'
OR (ISO_CNTRY_RT.ISO_CNTRY_CD = 'ER'
OR (ISO_CNTRY_RT.ISO_CNTRY_CD = 'FI'
OR (ISO_CNTRY_RT.ISO_CNTRY_CD = 'DZ'
OR (ISO_CNTRY_RT.ISO_CNTRY_CD = 'DJ'
OR ISO_CNTRY_RT.ISO_CNTRY_CD = 'DE')))))))
Step 9 At REV_RPT_CFY
Cardinality 13647.3 Tuple Length 83 Cost 3.5652095e+07 +Per Tuple 0
STREAM FROM REV_RPT_CFY TO DBI
SELECT T15.C14, T15.C16, T15.C17, T15.C18, T15.C19, T15.C20, T15.C21,
T15.C22, T15.C23, T15.C24, T15.C25
FROM (SELECT DTL_XPLOD_OR_CFY.DTL_XPLOD_INDCR,
DTL_XPLOD_OR_CFY.FYR_FQTR_FWK, DTL_XPLOD_OR_CFY.ISO_CNTRY_CD,
DTL_XPLOD_OR_CFY.RVNUE_PRODT_ID, DTL_XPLOD_OR_CFY.PRODT_QTY,
DTL_XPLOD_OR_CFY.WARNTY_AMT, DTL_XPLOD_OR_CFY.GROSS_AMT,
DTL_XPLOD_OR_CFY.UPLIFT_AMT, DTL_XPLOD_OR_CFY.DSCNT_AMT,
DTL_XPLOD_OR_CFY.ALLWNC_AMT, DTL_XPLOD_OR_CFY.TRNSFR_AMT,
DTL_XPLOD_OR_CFY.DLP_AMT, DTL_XPLOD_OR_CFY.NOR_AMT
FROM DTL_XPLOD_OR_CFY T26
WHERE ((DTL_XPLOD_OR_CFY.FYR_FQTR_FWK >= 1996101
AND DTL_XPLOD_OR_CFY.FYR_FQTR_FWK <= 1996414))) AS T15(C27,C28,
C14,C16,C17,C18,C19,C20,C21,C22,C23,C24,C25)
WHERE (((T15.C28 >= 1996201
AND T15.C28 <= 1996213)
AND (T15.C27 = 'B'
OR (T15.C27 = 'D'
OR T15.C27 = 'M'))))
Step 7 At REV_RPT_REF
Cardinality 369586 Tuple Length 100 Cost 9467441 +Per Tuple 0
STREAM FROM REV_RPT_REF TO DBI
SELECT PRODT_RT.PRODT_ID, PRODT_AGRGT_RT.PRODT_AGRGT_CD_DSCRPN,
PRODT_AGRGT_RT.PRODT_AGRGT_CD, PRODT_GRP_RT.PRODT_GRP_NAME,
PRODT_GRP_RT.PRODT_GRP_CD, PRODT_ULTMT_GRP_RT.PRODT_ULTMT_GRP_NAME,
PRODT_ULTMT_GRP_RT.PRODT_ULTMT_GRP_CD
FROM PRODT_RT T29, PRODT_AGRGT_RT T30, PRODT_SET_RT T31, PRODT_GRP_RT
T32, PRODT_ULTMT_GRP_RT T33
WHERE (PRODT_AGRGT_RT.PRODT_AGRGT_CD = PRODT_RT.PRODT_AGRGT_CD)
AND (PRODT_SET_RT.PRODT_SET_CD = PRODT_AGRGT_RT.PRODT_SET_CD)
AND (PRODT_GRP_RT.PRODT_GRP_CD = PRODT_SET_RT.PRODT_GRP_CD)
AND (PRODT_ULTMT_GRP_RT.PRODT_ULTMT_GRP_CD =
PRODT_GRP_RT.PRODT_ULTMT_GRP_CD)
|