没人知道吗?还是大家都以为这个问题不是问题,就是sql写错了……
顺便补充一下,根据TKPROF的结果来看,貌似是在ORDER BY的时候变成10条的,请朋友们在帮忙想想,谢谢啦!
顺便补充一下,根据TKPROF的结果来看,貌似是在ORDER BY的时候变成10条的,请朋友们在帮忙想想,谢谢啦!
解决方案 »
- 单元格计算过后汇总! (Excel or 润乾)
- expdp导出参数问题
- 能否有朋友将11g系统自身的定时计划解释一下?
- 仓库管理系统代码 B/S c# asp.net2.0 Oracle,那位同仁巧有相关的代码,给我发一封,谢谢!!
- VB.net能连接ORACLE;ASP.net却不能连接ORACLE。这是怎么回事????
- 请教一个问题,统计商品的最高价格的,sql语句
- 表空间'TEST'中无权限?
- 倾情奉送!-pro*c动态查询结果集写文件
- 请问GET_LINE结尾情况??
- 这样的SQL语句如何写????
- Oracle 删除重复数据(只留最新一条) 不管你行不行都过来看看
- 请问过程中表的别名可否重复使用
1、在query.list()大断点;
2、根据query对象,找到内部的Oracle Connection对象,找到当前sessionId和serialNumber;
3、执行如下SQL:
BEGIN
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sessionId,serialNumber,true);
END;
/注:query.list()是Hibernate里面留给开发者的相对“底层”的API了,返回的是SQL执行以后,Hibernate转成Java对象的结果集,一般是个List。同时我还特意留意了每次query.list()里面的这个sessionId,只要出现10条问题,我就对当前session执行上面的SQL,然后query.list()结果返回就从10条变成正常值,而且,只要sessionId不变,就一直都是正常值。但是过会儿sessionId就变了,然后又变成10条,我就继续重复,现象始终如上描述。感谢Inthirties的关注!
select investpool0_.ID as ID98_0_,
report1_.ID as ID86_1_,
investpool2_.ID as ID112_2_,
investpool0_.ADJUST_ACTION as ADJUST2_98_0_,
investpool0_.INNER_CODE as INNER3_98_0_,
investpool0_.INVEST_POOL_ID as INVEST8_98_0_,
investpool0_.REPORT_ID as REPORT9_98_0_,
investpool0_.SECURITY_CODE as SECURITY4_98_0_,
investpool0_.SECURITY_MARKET as SECURITY5_98_0_,
investpool0_.SECURITY_TYPE as SECURITY6_98_0_,
investpool0_.SUBMIT_USER_ID as SUBMIT10_98_0_,
investpool0_.UPDATE_TIME as UPDATE7_98_0_,
report1_.ARCHIVE_TIME as ARCHIVE2_86_1_,
report1_.ATTACHMENT_PAGE_SIZE as ATTACHMENT3_86_1_,
report1_.AUTHOR as AUTHOR86_1_,
report1_.BROKER_ID as BROKER43_86_1_,
report1_.BROKER_NAME as BROKER5_86_1_,
report1_.CLICK_COUNT as CLICK6_86_1_,
report1_.CONTENT as CONTENT86_1_,
report1_.ENTER_CENTER_TIME as ENTER8_86_1_,
report1_.FIRST_DIGEST as FIRST9_86_1_,
report1_.FIRST_RETURN_TIME as FIRST10_86_1_,
report1_.INDUSTRY_CODE as INDUSTRY11_86_1_,
report1_.INNER_JSID as INNER12_86_1_,
report1_.INNER_STOCK_CODE as INNER13_86_1_,
report1_.INVEST_RANKING as INVEST14_86_1_,
report1_.IS_RECOMMENDATION as IS15_86_1_,
report1_.JSID as JSID86_1_,
report1_.LANDING_TIME as LANDING17_86_1_,
report1_.LAST_INVEST_RANKING as LAST18_86_1_,
report1_.LAST_RISK_RANKING as LAST19_86_1_,
report1_.LAST_TARGET_PRICE as LAST20_86_1_,
report1_.MAIL_CONTENT as MAIL21_86_1_,
report1_.MAIL_SUBJECT as MAIL22_86_1_,
report1_.MAIL_TIME as MAIL23_86_1_,
report1_.MAIL_UID as MAIL24_86_1_,
report1_.PAGEVIEW as PAGEVIEW86_1_,
report1_.RANK_CHANGE as RANK26_86_1_,
report1_.RECEIVE_EMAIL as RECEIVE27_86_1_,
report1_.REMARK as REMARK86_1_,
report1_.REPORT_TYPE_ID as REPORT44_86_1_,
report1_.RISK_RANKING as RISK29_86_1_,
report1_.SECOND_RETURN_TIME as SECOND30_86_1_,
report1_.SECOND_TITLE as SECOND31_86_1_,
report1_.SECU_MARKET as SECU32_86_1_,
report1_.STATUS as STATUS86_1_,
report1_.STOCK_CODE as STOCK34_86_1_,
report1_.SUBMIT_TIME as SUBMIT35_86_1_,
report1_.SUMMARY as SUMMARY86_1_,
report1_.TARGET_DURATION as TARGET37_86_1_,
report1_.TARGET_PRICE as TARGET38_86_1_,
report1_.TITLE as TITLE86_1_,
report1_.UPDATE_TIME as UPDATE40_86_1_,
report1_.USER_ID as USER45_86_1_,
report1_.WORKFLOW_ID as WORKFLOW41_86_1_,
report1_.WRITE_TIME as WRITE42_86_1_,
investpool2_.IS_ABSTRACT as IS2_112_2_,
investpool2_.IS_ACTIVE as IS3_112_2_,
investpool2_.ADJUST_ASSIGN as ADJUST4_112_2_,
investpool2_.ADJUST_IN_AUDIT as ADJUST5_112_2_,
investpool2_.ADJUST_IN_REPORT_CONS as ADJUST6_112_2_,
investpool2_.ADJUST_OUT_AUDIT as ADJUST7_112_2_,
investpool2_.ADJUST_OUT_REPORT_CONS as ADJUST8_112_2_,
investpool2_.ADJUST_SOURCE as ADJUST9_112_2_,
investpool2_.ALLOW_SECURITY_OUT as ALLOW10_112_2_,
investpool2_.CREATE_TIME as CREATE11_112_2_,
investpool2_.CURRENT_NUM as CURRENT12_112_2_,
investpool2_.DESCRIPTION as DESCRIP13_112_2_,
investpool2_.EARNINGS_ESTIMATE as EARNINGS14_112_2_,
investpool2_.INVEST_POOL_ACCOUNT as INVEST31_112_2_,
investpool2_.INVEST_POOL_REPOSITORY as INVEST15_112_2_,
investpool2_.INVEST_POOL_TYPE as INVEST16_112_2_,
investpool2_.LAST_AUTO_UPDATE_TIME as LAST17_112_2_,
investpool2_.POOL_LEVEL as POOL18_112_2_,
investpool2_.MANUAL_ADJUST as MANUAL19_112_2_,
investpool2_.MAX_NUM as MAX20_112_2_,
investpool2_.NAME as NAME112_2_,
investpool2_.NEED_CONFIRM as NEED22_112_2_,
investpool2_.NEED_INCOME as NEED23_112_2_,
investpool2_.O3NAME as O24_112_2_,
investpool2_.PARENT_POOL_ID as PARENT32_112_2_,
investpool2_.RANKING_INFO as RANKING25_112_2_,
investpool2_.RELATED_TRADE as RELATED26_112_2_,
investpool2_.ROOT_POOL_ID as ROOT33_112_2_,
investpool2_.SECURITY_TYPE as SECURITY27_112_2_,
investpool2_.SHOW_ORDER as SHOW28_112_2_,
investpool2_.UPDATE_TIME as UPDATE29_112_2_,
investpool2_.WORK_FLOW_NAME as WORK30_112_2_
from irp.INVEST_POOL_ITEM investpool0_
left outer join irp.REPORT report1_ on investpool0_.REPORT_ID=report1_.ID
left outer join irp.INVEST_POOL investpool2_ on investpool0_.INVEST_POOL_ID=investpool2_.ID
where 1=1
and (investpool0_.INVEST_POOL_ID in (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 ,
:10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 ,
:22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 , :33 , :34
, :35 , :36 , :37 , :38 , :39 , :40 , :41 , :42 , :43 , :44 , :45 , :46 ,
:47 , :48 , :49 , :50 , :51 , :52 , :53 , :54 , :55 , :56 , :57 , :58 , :59
, :60 , :61 , :62 , :63 , :64 , :65 , :66 , :67 , :68 , :69 , :70 , :71 ,
:72 , :73 , :74 , :75 , :76 , :77 , :78 , :79 , :80 , :81 , :82 , :83 , :84
, :85 , :86 , :87 , :88 , :89 , :90 , :91 , :92 , :93 , :94 , :95 , :96 ,
:97 , :98 , :99 , :100 , :101 , :102 , :103 , :104 , :105 , :106 , :107 ,
:108 , :109 , :110 , :111 , :112 , :113 , :114 , :115 , :116 , :117 , :118 ,
:119 , :120 , :121 , :122 , :123 , :124 , :125 , :126 , :127 , :128 , :129
, :130 , :131 , :132 , :133 , :134 , :135 , :136 , :137 , :138 , :139 ,
:140 , :141 , :142 , :143 , :144 , :145 , :146 , :147 , :148 , :149 , :150 ,
:151 , :152 , :153 , :154 , :155 , :156 , :157 , :158 , :159 , :160 , :161
, :162 , :163 , :164 , :165 , :166 , :167 , :168 , :169 , :170 , :171 ,
:172 , :173 , :174)
)
and (investpool0_.INNER_CODE in (
select stockinfo4_.INNER_CODE from irp.STOCK_RESEARCHER_REL stockresea3_,
irp.STOCK_INFO stockinfo4_, irp.INVEST_POOL_ACCOUNT investpool7_
where stockresea3_.STOCK_INFO_ID=stockinfo4_.ID
and investpool2_.INVEST_POOL_ACCOUNT=investpool7_.ID
and stockinfo4_.ACCOUNT_TYPE=investpool7_.ACCOUNT_TYPE
and (stockresea3_.RESEARCHER_ID in (
select user8_.ID from irp.SECURITY_USER user8_
where user8_.LOGIN_NAME in (:175)))
))
order by investpool2_.INVEST_POOL_REPOSITORY asc, investpool2_.INVEST_POOL_ACCOUNT asc,
investpool2_.NAME asc, investpool0_.SECURITY_CODE asc
) where rownum <=:176因为大家一直怀疑是SQL的问题,所以我就把SQL给大家带出来。请大家帮忙也分析一下:
INVEST_POOL:投资池表;
INVEST_POOL_ITEM:池内证券表;
REPORT:研报表;INVEST_POOL和INVEST_POOL_ITEM是一对多关系,
INVEST_POOL_ITEM和REPORT是多对一关系上面有174个参数的in子句意思是在这些INVEST_POOL里面的ITEM。参数175是研究员用户名,select stockinfo4_.INNER_CODE from irp.STOCK_RESEARCHER_REL stockresea3_,
irp.STOCK_INFO stockinfo4_, irp.INVEST_POOL_ACCOUNT investpool7_
where stockresea3_.STOCK_INFO_ID=stockinfo4_.ID
and investpool2_.INVEST_POOL_ACCOUNT=investpool7_.ID
and stockinfo4_.ACCOUNT_TYPE=investpool7_.ACCOUNT_TYPE
and (stockresea3_.RESEARCHER_ID in (
select user8_.ID from irp.SECURITY_USER user8_
where user8_.LOGIN_NAME in (:175)))上面这句SQL的意思是,通过关联查询,找到当前INVEST_POOL对应的账户类型的研究员所分管的股票代码;如果没理解意思的话也不要紧,这句SQL返回的结果在TKPROF里面看是没有错误的。总觉得如果是SQL的问题,那么应该总是只有10条才对,不会大部分时候是正常的,偶尔出现10条问题。而且页大小变化以后,还是只有10条。另外,ALTER SYSTEM FLUSH SHARED_POOL以后能恢复正常一段时间请大家再帮我分析一下,谢谢!
------- ---------------------------------------------------
10 COUNT STOPKEY (cr=240 pr=0 pw=0 time=87616 us)
10 VIEW (cr=240 pr=0 pw=0 time=87598 us)
10 SORT ORDER BY STOPKEY (cr=240 pr=0 pw=0 time=87565 us)
229 NESTED LOOPS OUTER (cr=240 pr=0 pw=0 time=86246 us)
229 HASH JOIN (cr=185 pr=0 pw=0 time=84168 us)
7526 TABLE ACCESS FULL INVEST_POOL_ITEM (cr=122 pr=0 pw=0 time=7826 us)
5916 HASH JOIN (cr=63 pr=0 pw=0 time=43103 us)
612 VIEW VW_SQ_1 (cr=56 pr=0 pw=0 time=34077 us)
612 HASH UNIQUE (cr=56 pr=0 pw=0 time=32848 us)
612 HASH JOIN (cr=56 pr=0 pw=0 time=31017 us)
34 HASH JOIN (cr=49 pr=0 pw=0 time=29929 us)
1058 TABLE ACCESS FULL STOCK_RESEARCHER_REL (cr=16 pr=0 pw=0 time=51 us)
4619 MERGE JOIN CARTESIAN (cr=33 pr=0 pw=0 time=16498 us)
1 TABLE ACCESS BY INDEX ROWID SECURITY_USER (cr=2 pr=0 pw=0 time=45 us)
1 INDEX RANGE SCAN SECURITY_USER_INDEX_02 (cr=1 pr=0 pw=0 time=28 us)(object id 75405)
4619 BUFFER SORT (cr=31 pr=0 pw=0 time=7205 us)
4619 TABLE ACCESS FULL STOCK_INFO (cr=31 pr=0 pw=0 time=34 us)
19 TABLE ACCESS FULL INVEST_POOL_ACCOUNT (cr=7 pr=0 pw=0 time=65 us)
174 TABLE ACCESS FULL INVEST_POOL (cr=7 pr=0 pw=0 time=665 us)
18 TABLE ACCESS BY INDEX ROWID REPORT (cr=55 pr=0 pw=0 time=1408 us)
18 INDEX UNIQUE SCAN SYS_C006247 (cr=37 pr=0 pw=0 time=563 us)(object id 53537)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
10 COUNT (STOPKEY)
10 VIEW
10 SORT (ORDER BY STOPKEY)
229 NESTED LOOPS (OUTER)
229 HASH JOIN
7526 TABLE ACCESS MODE: ANALYZED (FULL) OF 'INVEST_POOL'
(TABLE)
5916 HASH JOIN
612 VIEW OF 'VW_SQ_1' (VIEW)
612 HASH (UNIQUE)
612 HASH JOIN
34 HASH JOIN
1058 TABLE ACCESS MODE: ANALYZED (FULL) OF
'STOCK_RESEARCHER_REL' (TABLE)
4619 MERGE JOIN (CARTESIAN)
1 TABLE ACCESS MODE: ANALYZED (BY INDEX
ROWID) OF 'SECURITY_USER' (TABLE)
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'SECURITY_USER_INDEX_02' (INDEX)
4619 BUFFER (SORT)
4619 TABLE ACCESS MODE: ANALYZED (FULL) OF
'STOCK_INFO' (TABLE)
19 TABLE ACCESS MODE: ANALYZED (FULL) OF
'INVEST_POOL_ACCOUNT' (TABLE)
174 TABLE ACCESS MODE: ANALYZED (FULL) OF
'INVEST_POOL_ITEM' (TABLE)
18 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'REPORT' (TABLE)
18 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'SYS_C006247'
(INDEX (UNIQUE))
[ui:22:23:13.292] DEBUG [http-8080-5] DefaultPage.reCalcPage(449) | TotalSize=793, PageSize=19, PageCount=42, PageIndex=1
[ui:22:23:13.293] DEBUG [http-8080-5] DefaultPage.fetchPageData(246) | Fetching page data [startIndex:0, fetchCount:19]上面这个TotalSize就是count的结果,PageSize是第一页的rownum,下面一行,startIndex是起始记录行,fetchCount是PageSize,也就是页大小。上面的SQL是Hibernate生成的,因为是第一页,所以只有一个rownum<:176。我看了TRC文件,第176个参数设置是正确的,是19,但是TKPROF的结果我上面已经贴出来了,是10条,而count的结果是793,SQL的条件部分是一模一样的。
每个order by 最后面都加上主键 。试一下 。
基本上 所有的驱动 换了个遍 不行隔一段时间就出现这个问题 而且只在绑定变量的情况下才出现
出现这个情况
只有 alter system flush shared_pool 就好了 但是隔一段时间就又会出现.
<property name="hibernate.jdbc.fetch_size" value="60" />
<property name="hibernate.jdbc.batch_size" value="40" /> 详细的就不说了