使用oracle odbc缓慢的odbc oracle查询 [英] Slow odbc oracle query using oracle odbc
问题描述
你好，
我有一个大问题广告，我无法解决它。我们有一台运行oracle 10g 10.1.0版本的服务器
。我们通常使用access作为前端
并连接数据库表以进行数据提取。我们一直在使用
oracle客户端10.1.0.2和它的odbc一段时间没有问题。当我们决定重新连接所有表并保存
密码时，出现了
问题。一些查询突然变得很慢。然后我发现
使用odbc附带的表连接了
oracle的7.3版本。唯一明显的变化是连接字符串可见
在访问中传递鼠标在查询之间改变了什么
像ODBC; DSN = dnsname; DBQ = OdbcName ; ASY = OFF ;; TABLE = Tabl eName"
类似
" ODBC; DSN = dnsname; UID =用户名; DBQ = OdbcName; DBA = W; APA = F; EXC = F; FEN = T ; QTO = T; FRC = 10; FDL = 10; LOB = F; RST = T; BTD = F; BAM = IfAllSuccessful; NUM = NLS; DPM = F; MTS = T; MDI = F; CSR = F; FWC = T; FBS = 64000; TLO = O ;; TABLE = TableN ame
查询为：
SELECT QSHOC00.SHOCPORD， QSHOC00.SHOCITEM，Sum（（[SHOCQTRC] / 100））AS
QtaOk，QGDLN00.GDLNDESC
来自TabellaOrdini INNER JOIN（QSHOC00 INNER JOIN QGDLN00 ON
QSHOC00.SHOCITEM = QGDLN00.GDLNITEM）ON TabellaOrdini.Ordine =
QSHOC00.SHOCPORD
WHERE（（（QSHOC00.SHOCDTIV）< DateValue（" 31/12 / 4712"））和
（（QSHOC00.SHOCDTFV）= DateValue（" 31/12 / 4712"））和（（QGDLN00.GDLNFINI）="
"）AND（（QGDLN00.GDLNLANG）=" I"））
GROUP BY QSHOC00.SHOCPORD，QSHOC00.SHOCITEM，QGDLN00.GDLNDESC，
QSHOC00.SHOCFINI
HAVING（（（QSHOC00.SHOCITEM）<>""）AND（（QSHOC00.SHOCF INI）=" ））;
表格如下：
1）TabellaOrdini：它只包含两个记录并且具有Ordine和Ordine。作为
主键
2）QSHOC00：它包含大约1870000条记录并且具有由字段SOCIETA，SHOCPORD，SHOCFASE组成的复合
主键，SHOCITEM，
SHOCPORD，SHOCFINI，SHOCLRIN
3）QGDLN00 ::它包含大约75000条记录并且有一个复合的
主键由SOCIETA，GDLNITEM，GDLNFINI，GDLNLANG
查询大约需要2分钟才能运行，而且只需几秒钟就可以获得
秒。如果我更改查询，则将两个值设为ordini。在
a where子句再次非常快，所以问题必须在加入
与TabellaOrdini。
最后一件事：我已经也尝试使用客户端10.2.3，但它更改
没有，除非我使用旧的odbc重新连接表然后它运行
一切都很好。
很抱歉很长的解释并提前感谢答案
给出。
hello,
I''ve got a big problem ad i''m not able to resolve it. We have a server
running oracle 10g version 10.1.0. We usually use access as front end
and connect database tables for data extraction. We have been using
oracle client 10.1.0.2 with it''s odbc for a while without problem. The
problem arose when we decided to reconnect all the tables and save
password. Some query became suddenly very slow. Then I''ve discovered
that the tables were connected using odbc shipped with version 7.3 of
oracle. the only apparent change is that the connection string visible
in access passing the mouse on the query is changed between something
like "ODBC;DSN=dnsname;DBQ=OdbcName;ASY=OFF;;TABLE=Tabl eName" to
something like
"ODBC;DSN=dnsname;UID=username;DBQ=OdbcName ;DBA=W;APA=F;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=F ;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS =T;MDI=F;CSR=F;FWC=T;FBS=64000;TLO=O;;TABLE=TableN ame"
The query is:
SELECT QSHOC00.SHOCPORD, QSHOC00.SHOCITEM, Sum(([SHOCQTRC]/100)) AS
QtaOk, QGDLN00.GDLNDESC
FROM TabellaOrdini INNER JOIN (QSHOC00 INNER JOIN QGDLN00 ON
QSHOC00.SHOCITEM = QGDLN00.GDLNITEM) ON TabellaOrdini.Ordine =
QSHOC00.SHOCPORD
WHERE (((QSHOC00.SHOCDTIV)<DateValue("31/12/4712")) AND
((QSHOC00.SHOCDTFV)=DateValue("31/12/4712")) AND ((QGDLN00.GDLNFINI)="
") AND ((QGDLN00.GDLNLANG)="I"))
GROUP BY QSHOC00.SHOCPORD, QSHOC00.SHOCITEM, QGDLN00.GDLNDESC,
QSHOC00.SHOCFINI
HAVING (((QSHOC00.SHOCITEM)<>" ") AND ((QSHOC00.SHOCFINI)=" "));
The tables are the following:
1)TabellaOrdini: it contains only two records and has "Ordine" as
primary key
2) QSHOC00: it contains about 1870000 records and has a composite
primary key composed by fields SOCIETA, SHOCPORD, SHOCFASE, SHOCITEM,
SHOCPORD, SHOCFINI, SHOCLRIN
3) QGDLN00: : it contains about 75000 records and has a composite
primary key composed by fields SOCIETA, GDLNITEM, GDLNFINI, GDLNLANG
The query takes about 2 minutes to run and before it took only few
seconds. If I change the query putting the two values of "ordini" in
a where clause is again very fast so the problem must be in the join
with TabellaOrdini.
A last thing: I''ve also tried to use client 10.2.3 but it changes
nothing, unless I reconnect the tables using old odbc and then it runs
all fine.
Sorry for the long explanation and thanks in advance for the answers
given.
推荐答案
3月23日上午12:56，funky < marco.ronc ... @ gmail.comwrote：
On Mar 23, 12:56 am, "funky" <marco.ronc...@gmail.comwrote:
hello，
我有一个大问题广告我'我无法解决它。我们有一台运行oracle 10g 10.1.0版本的服务器
。我们通常使用access作为前端
并连接数据库表以进行数据提取。我们一直在使用
oracle客户端10.1.0.2和它的odbc一段时间没有问题。当我们决定重新连接所有表并保存
密码时，出现了
问题。一些查询突然变得很慢。然后我发现
使用odbc附带的表连接了
oracle的7.3版本。唯一明显的变化是连接字符串可见
在访问中传递鼠标在查询之间改变了什么
像ODBC; DSN = dnsname; DBQ = OdbcName ; ASY = OFF ;; TABLE = Tabl eName"
类似
" ODBC; DSN = dnsname; UID =用户名; DBQ = OdbcName; DBA = W; APA = F; EXC = F; FEN = T ; QTO = T; FRC = 10; FDL = 10; LOB = F; RST = T; BTD = F; BAM = IfAllSuc * cessful; NUM = NLS; DPM = F; MT S = T; MDI = F; CSR = F; FWC = T; FBS = 64000; TLO = O ;; TABLE =表N * ame
查询为：
SELECT QSHOC00.SHOCPORD，QSHOC00.SHOCITEM，Sum（（[SHOCQTRC] / 100））AS
QtaOk，QGDLN00.GDLNDESC
来自TabellaOrdini INNER JOIN（QSHOC00 INNER JOIN QGDLN00 ON
QSHOC00.SHOCITEM = QGDLN00.GDLNITEM）ON TabellaOrdini.Ordine =
QSHOC00.SHOCPORD
WHERE（（（QSHOC00.SHOCDTIV）< ; DateValue（" 31/12 / 4712"））AND
（（QSHOC00.SHOCDTFV）= DateValue（" 31/12 / 4712"））AND（（QGDLN00.GDLNFINI）=" ;
"）AND（（QGDLN00.GDLNLANG）=" I"））
GROUP BY QSHOC00.SHOCPORD，QSHOC00.SHOCITEM，QGDLN00.GDLNDESC，
QSHOC00.SHOCFINI
HAVING（（（QSHOC00.SHOCITEM）<>""）AND（（QSHOC00.SHOCF INI）=" ））;
表格如下：
1）TabellaOrdini：它只包含两个记录并且具有Ordine和Ordine。作为
主键
2）QSHOC00：它包含大约1870000条记录并且具有由字段SOCIETA，SHOCPORD，SHOCFASE组成的复合
主键，SHOCITEM，
SHOCPORD，SHOCFINI，SHOCLRIN
3）QGDLN00 ::它包含大约75000条记录并且有一个复合的
主键由SOCIETA，GDLNITEM，GDLNFINI，GDLNLANG
查询大约需要2分钟才能运行，而且只需几秒钟就可以获得
秒。如果我更改查询，则将两个值设为ordini。在
a where子句再次非常快，所以问题必须在加入
与TabellaOrdini。
最后一件事：我已经也尝试使用客户端10.2.3，但它更改
没有，除非我使用旧的odbc重新连接表然后它运行
一切都很好。
很抱歉很长的解释，并提前感谢答案
给出。
hello,
I''ve got a big problem ad i''m not able to resolve it. We have a server
running oracle 10g version 10.1.0. We usually use access as front end
and connect database tables for data extraction. We have been using
oracle client 10.1.0.2 with it''s odbc for a while without problem. The
problem arose when we decided to reconnect all the tables and save
password. Some query became suddenly very slow. Then I''ve discovered
that the tables were connected using odbc shipped with version 7.3 of
oracle. the only apparent change is that the connection string visible
in access passing the mouse on the query is changed between something
like "ODBC;DSN=dnsname;DBQ=OdbcName;ASY=OFF;;TABLE=Tabl eName" to
something like
"ODBC;DSN=dnsname;UID=username;DBQ=OdbcName ;DBA=W;APA=F;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=F ;RST=T;BTD=F;BAM=IfAllSuc*cessful;NUM=NLS;DPM=F;MT S=T;MDI=F;CSR=F;FWC=T;FBS=64000;TLO=O;;TABLE=Table N*ame"
The query is:
SELECT QSHOC00.SHOCPORD, QSHOC00.SHOCITEM, Sum(([SHOCQTRC]/100)) AS
QtaOk, QGDLN00.GDLNDESC
FROM TabellaOrdini INNER JOIN (QSHOC00 INNER JOIN QGDLN00 ON
QSHOC00.SHOCITEM = QGDLN00.GDLNITEM) ON TabellaOrdini.Ordine =
QSHOC00.SHOCPORD
WHERE (((QSHOC00.SHOCDTIV)<DateValue("31/12/4712")) AND
((QSHOC00.SHOCDTFV)=DateValue("31/12/4712")) AND ((QGDLN00.GDLNFINI)="
") AND ((QGDLN00.GDLNLANG)="I"))
GROUP BY QSHOC00.SHOCPORD, QSHOC00.SHOCITEM, QGDLN00.GDLNDESC,
QSHOC00.SHOCFINI
HAVING (((QSHOC00.SHOCITEM)<>" ") AND ((QSHOC00.SHOCFINI)=" "));
The tables are the following:
1)TabellaOrdini: it contains only two records and has "Ordine" as
primary key
2) QSHOC00: it contains about 1870000 records and has a composite
primary key composed by fields SOCIETA, SHOCPORD, SHOCFASE, SHOCITEM,
SHOCPORD, SHOCFINI, SHOCLRIN
3) QGDLN00: : it contains about 75000 records and has a composite
primary key composed by fields SOCIETA, GDLNITEM, GDLNFINI, GDLNLANG
The query takes about 2 minutes to run and before it took only few
seconds. If I change the query putting the two values of "ordini" in
a where clause is again very fast so the problem must be in the join
with TabellaOrdini.
A last thing: I''ve also tried to use client 10.2.3 but it changes
nothing, unless I reconnect the tables using old odbc and then it runs
all fine.
Sorry for the long explanation and thanks in advance for the answers
given.
您没有提及是否刷新了链接。
You did not mention whether you had refreshed the links or not.
3月23,6：21 pm，eng ... @ ridesoft.com写道：
On Mar 23, 6:21 pm, eng...@ridesoft.com wrote:
3月23日12:56 am，funky < marco.ronc ... @ gmail.comwrote：
On Mar 23, 12:56 am, "funky" <marco.ronc...@gmail.comwrote:
你好，
我''我有一个大问题，我无法解决它。我们有一台运行oracle 10g 10.1.0版本的服务器
。我们通常使用access作为前端
并连接数据库表以进行数据提取。我们一直在使用
oracle客户端10.1.0.2和它的odbc一段时间没有问题。当我们决定重新连接所有表并保存
密码时，出现了
问题。一些查询突然变得很慢。然后我发现
使用odbc附带的表连接了
oracle的7.3版本。唯一明显的变化是连接字符串可见
在访问中传递鼠标在查询之间改变了什么
像ODBC; DSN = dnsname; DBQ = OdbcName ; ASY = OFF ;; TABLE = Tabl eName"
类似
" ODBC; DSN = dnsname; UID =用户名; DBQ = OdbcName; DBA = W; APA = F; EXC = F; FEN = T ; QTO = T; FRC = 10; FDL = 10; LOB = F; RST = T; BTD = F; BAM = IfAllSuc **有效; NUM = NLS; DPM = F; M TS = T; MDI = F; CSR = F; FWC = T; FBS = 64000; TLO = O ;; TABLE = Tabl e * N * ame
hello,
I''ve got a big problem ad i''m not able to resolve it. We have a server
running oracle 10g version 10.1.0. We usually use access as front end
and connect database tables for data extraction. We have been using
oracle client 10.1.0.2 with it''s odbc for a while without problem. The
problem arose when we decided to reconnect all the tables and save
password. Some query became suddenly very slow. Then I''ve discovered
that the tables were connected using odbc shipped with version 7.3 of
oracle. the only apparent change is that the connection string visible
in access passing the mouse on the query is changed between something
like "ODBC;DSN=dnsname;DBQ=OdbcName;ASY=OFF;;TABLE=Tabl eName" to
something like
"ODBC;DSN=dnsname;UID=username;DBQ=OdbcName ;DBA=W;APA=F;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=F ;RST=T;BTD=F;BAM=IfAllSuc**cessful;NUM=NLS;DPM=F;M TS=T;MDI=F;CSR=F;FWC=T;FBS=64000;TLO=O;;TABLE=Tabl e*N*ame"
查询为：
The query is:
SELECT QSHOC00.SHOCPORD，QSHOC00 .SHOCITEM，Sum（（[SHOCQTRC] / 100））AS
QtaOk，QGDLN00.GDLNDESC
来自TabellaOrdini INNER JOIN（QSHOC00 INNER JOIN QGDLN00 ON
QSHOC00.SHOCITEM = QGDLN00.GDLNITEM）ON TabellaOrdini.Ordine =
QSHOC00.SHOCPORD
WHERE（（（QSHOC00.SHOCDTIV）< DateValue（" 31 / 12/4712"））和
（（QSHOC00.SHOCDTFV）= DateValue（" 31/12 / 4712"））和（（QGDLN00.GDLNFINI）="
"）AND（（QGDLN00.GDLNLANG）=" I"））
GROUP BY QSHOC00.SHOCPORD，QSHOC00.SHOCITEM，QGDLN00.GDLNDESC，
QSHOC00.SHOCFINI
HAVING（（（QSHOC00.SHOCITEM）<>""）AND（（QSHOC00.SHOCFINI）=""））;
SELECT QSHOC00.SHOCPORD, QSHOC00.SHOCITEM, Sum(([SHOCQTRC]/100)) AS
QtaOk, QGDLN00.GDLNDESC
FROM TabellaOrdini INNER JOIN (QSHOC00 INNER JOIN QGDLN00 ON
QSHOC00.SHOCITEM = QGDLN00.GDLNITEM) ON TabellaOrdini.Ordine =
QSHOC00.SHOCPORD
WHERE (((QSHOC00.SHOCDTIV)<DateValue("31/12/4712")) AND
((QSHOC00.SHOCDTFV)=DateValue("31/12/4712")) AND ((QGDLN00.GDLNFINI)="
") AND ((QGDLN00.GDLNLANG)="I"))
GROUP BY QSHOC00.SHOCPORD, QSHOC00.SHOCITEM, QGDLN00.GDLNDESC,
QSHOC00.SHOCFINI
HAVING (((QSHOC00.SHOCITEM)<>" ") AND ((QSHOC00.SHOCFINI)=" "));
表格如下：
1）TabellaOrdini：它只包含两个记录并且具有Ordine和Ordine。作为
主键
2）QSHOC00：它包含大约1870000条记录并且具有由字段SOCIETA，SHOCPORD，SHOCFASE组成的复合
主键，SHOCITEM，
SHOCPORD，SHOCFINI，SHOCLRIN
3）QGDLN00 ::它包含大约75000条记录并且有一个复合的
主键由字段SOCIETA，GDLNITEM，GDLNFINI，GDLNLANG
The tables are the following:
1)TabellaOrdini: it contains only two records and has "Ordine" as
primary key
2) QSHOC00: it contains about 1870000 records and has a composite
primary key composed by fields SOCIETA, SHOCPORD, SHOCFASE, SHOCITEM,
SHOCPORD, SHOCFINI, SHOCLRIN
3) QGDLN00: : it contains about 75000 records and has a composite
primary key composed by fields SOCIETA, GDLNITEM, GDLNFINI, GDLNLANG
查询大约需要2分钟才能运行，之后只需几个
秒。如果我更改查询，则将两个值设为ordini。在
a where子句再次非常快，所以问题必须在加入
与TabellaOrdini。
最后一件事：我已经也尝试使用客户端10.2.3，但它更改
没有，除非我使用旧的odbc重新连接表然后它运行
都没关系。
The query takes about 2 minutes to run and before it took only few
seconds. If I change the query putting the two values of "ordini" in
a where clause is again very fast so the problem must be in the join
with TabellaOrdini.
A last thing: I''ve also tried to use client 10.2.3 but it changes
nothing, unless I reconnect the tables using old odbc and then it runs
all fine.
很抱歉很长的解释并提前感谢答案
给出。
Sorry for the long explanation and thanks in advance for the answers
given.
您没有提及是否刷新了链接.-隐藏引用的文字 -
- 显示引用的文字 -
You did not mention whether you had refreshed the links or not.- Hide quoted text -
- Show quoted text -
是的，抱歉。我已刷新链接并重新连接所有表格
使用保存密码选项
Yes, sorry. I''ve refreshed the links and reconnected all the tables
using save password option
确定，启用JETSHOWPLAN标志后我发现了什么问题
，现在仍然可以理解为什么。这里是两个相同查询的两个不同的
行为，在同一台电脑上运行，与
相同的odbc但是与旧的连接连接的表格相同>
string"和新的。两个主要表格（QSHOC00和
QGDLN00）中的每一个都有两个索引，由于某些未知原因，odbc似乎需要
而不是QSHOC上的主要索引然后执行内部联接
与QGDLN以及带有Tabella_ordini的那个因此产生了巨大的b
净流量和减速。
这是查询计划
--- Qtaord_somma_codici_impiegati ---
- 查询输入 -
ODBC表'' QSHOC00''
使用索引''QSHOCIX1''
索引：
QSHOCIX1 1851162条目，2226页，462791值
有7列，固定
QSHOCIX 1851162条目，2226页，1851162值
有7列，固定，唯一，主键，没有 - nulls
ODBC表''QGDLN00''
表''Tabella_ordini''
- 查询结束输入 -
453469464）远程SQL
01）Inner Join res使用参数化分布式连接来表''00'''到表''QSHOC00''
连接表达式QGDLN00.GDLNITEM = QSHOC00.SHOCITEM
然后测试表达式DateValue（[SHOCDTIV]）<＃12/31/4712＃和
DateValue（[SHOCDTFV]）=＃12/31/4712＃"
02）''01'''内部加入结果''Tabella_ordini''
使用X-Prod加入
然后测试表达式QSHOC00.SHOCPORD = Tabella_ordini.Ordine
03）02的组结果''
任何人都知道为什么访问可能会出错指数。我忘记提到的一件事是，我们现在正在使用文件.mdw进行数据库
保护。进入我脑海的一件事是，这可能会破坏系统表（如MSysobjects）上的权限。我已经检查了
，事实上，使用新的
odbc连接的表在MSysObjects上有一个名为Lv的空字段，同时与
旧的那里有数据。
i不知道怎么摆脱这个烂摊子所以任何帮助都会很大
欣赏。
- 查询输入 -
表''Tabella_ordini''
ODBC表''QSHOC00''
使用索引''QSHOCIX''
索引：
QSHOCIX 10000条目，63页，10000个值
有7列，已修复，unique，primary-key，no-nulls
ODBC表''QGDLN00''
- 查询的结束输入 -
01）内部加入表''Tabella_ordini''到表''QSHOC00''
使用参数化分布式连接
连接表达式Tabella_ordini.Ordine = QSHOC00.SHOCPORD
然后测试表达式DateValue（[SHOCDTIV]）< ;＃12/31/4712＃和
DateValue（[SHOCDTFV]）=＃12/31/4712＃"
453469464）远程SQL
02）排序结果''01）''
03）内部加入结果''01）''结果为''02）''
使用临时索引
连接表达式QSHOC00.SHOCITEM = QGDLN00.GDLNITEM
04）'03'''组结果'
05）排序结果''04）''
Ok, after enabling the JETSHOWPLAN flag i found out what the problem
is, now it remains to understand why. here are the two differnet
behaviour of the two identical query, running on the same pc with the
same odbc but against tables connected with the old "connection
string" and the new one. Each of the two main tables (QSHOC00 and
QGDLN00) has two index, for some unknown reason the odbc seems to take
not the primary index on QSHOC and then perform first the inner join
with QGDLN and the the one with Tabella_ordini thus generating a huge
amount of net traffic and slowing down.
Here are the query plan
--- Qtaord_somma_codici_impiegati ---
- Inputs to Query -
ODBC table ''QSHOC00''
Using index ''QSHOCIX1''
Having Indexes:
QSHOCIX1 1851162 entries, 2226 pages, 462791 values
which has 7 columns, fixed
QSHOCIX 1851162 entries, 2226 pages, 1851162 values
which has 7 columns, fixed, unique, primary-key, no-nulls
ODBC table ''QGDLN00''
Table ''Tabella_ordini''
- End inputs to Query -
453469464) Remote SQL
01) Inner Join result of ''00)'' to table ''QSHOC00''
using parameterized distributed join
join expression "QGDLN00.GDLNITEM=QSHOC00.SHOCITEM"
then test expression "DateValue([SHOCDTIV])<#12/31/4712# And
DateValue([SHOCDTFV])=#12/31/4712#"
02) Inner Join result of ''01)'' to table ''Tabella_ordini''
using X-Prod join
then test expression "QSHOC00.SHOCPORD=Tabella_ordini.Ordine"
03) Group result of ''02)''
Anyone knows why acces might get the wrong index. A thing i''ve
forgotten to mention is that we work now with a file .mdw for database
protection. A thing that came into my head is that this might mess
with the rights on the system tables ( like MSysobjects ) . i''ve
checked and , as a matter of fact, the tables connected using the new
odbc have a null field named Lv on MSysObjects, while connected with
the old one there were datas in it.
i don''t know how to get out of this mess so any help would be greatly
appreciate.
- Inputs to Query -
Table ''Tabella_ordini''
ODBC table ''QSHOC00''
Using index ''QSHOCIX''
Having Indexes:
QSHOCIX 10000 entries, 63 pages, 10000 values
which has 7 columns, fixed, unique, primary-key, no-nulls
ODBC table ''QGDLN00''
- End inputs to Query -
01) Inner Join table ''Tabella_ordini'' to table ''QSHOC00''
using parameterized distributed join
join expression "Tabella_ordini.Ordine=QSHOC00.SHOCPORD"
then test expression "DateValue([SHOCDTIV])<#12/31/4712# And
DateValue([SHOCDTFV])=#12/31/4712#"
453469464) Remote SQL
02) Sort result of ''01)''
03) Inner Join result of ''01)'' to result of ''02)''
using temporary index
join expression "QSHOC00.SHOCITEM=QGDLN00.GDLNITEM"
04) Group result of ''03)''
05) Sort result of ''04)''
这篇关于使用oracle odbc缓慢的odbc oracle查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
