一个列别名引发的性能问题,兼谈产品设计等(2010-11-15)

其实数据库还是《从一次临时表空间不足错误的处理说起》提到的那个逻辑从库.
解决那个问题之后,还是比较关注这个数据库的,看看是否还会报TEMP表空间不足的错误.
之后,还是发现IO阻塞,TEMP表空间读写是最频繁的.查找相应时间段里直接写最频繁的SQL语句,又发现下面两个SQL语句:

sql1:
select b.*, getproducturls(productid) as producturls
  from (select rownum as rowno, a.*
          from (select p.id productid,
                       nvl(p.image1, '') image1,
                       nvl(p.image2, '') image2,
                       nvl(p.image3, '') image3,
                       nvl(p.image4, '') image4,
                       nvl(p.refprice, 0) refprice,
                       nvl(p.catalogname, '') catalogname,
                       nvl(p.brandid, 0) brandid,
                       nvl(brandname, '') brandname,
                       nvl(p.catalogid, 0) catalogid,
                       nvl(p.seriesname, '') seriesname,
                       nvl(p.name, '') name,
                       nvl(p.productname, '') productname,
                       nvl(p.name1, '') name1,
                       nvl(p.name2, '') name2,
                       nvl(dealernum, 0) dealernum,
                       nvl(articlenum, 0) articlenum,
                       nvl(Replace(Replace(p.maininfo, '<li>', ''),
                                   '</li>' || chr(10),
                                   '/'),
                           '') maininfo
                  from product p
                 where 1 = 1
                   and p.publishstatus = 3
                 order by p.priority desc, nvl(pageview, 0) desc) a
         where rownum <= :1) b
 where rowno >= :2;

sql2:
select b.*, getproducturls(productid) as producturls
  from (select rownum as rowno, a.*
          from (select p.id productid,
                       nvl(p.image1, '') image1,
                       nvl(p.image2, '') image2,
                       nvl(p.image3, '') image3,
                       nvl(p.image4, '') image4,
                       nvl(p.refprice, 0) refprice,
                       nvl(p.catalogname, '') catalogname,
                       nvl(p.brandid, 0) brandid,
                       nvl(brandname, '') brandname,
                       nvl(p.catalogid, 0) catalogid,
                       nvl(p.seriesname, '') seriesname,
                       nvl(p.name, '') name,
                       nvl(p.productname, '') productname,
                       nvl(p.name1, '') name1,
                       nvl(p.name2, '') name2,
                       nvl(dealernum, 0) dealernum,
                       nvl(articlenum, 0) articlenum,
                       nvl(Replace(Replace(p.maininfo, '<li>', ''),
                                   '</li>' || chr(10),
                                   '/'),
                           '') maininfo
                  from product p
                 where 1 = 1
                   and p.publishstatus = 3
                 order by p.priority desc, nvl(refprice,0) asc) a
         where rownum <= :1) b
 where rowno >= :2;

这里不说nvl(p.image1, '') image1 根本就没有任何的意义,因为它和p.image1 image1就是一回事儿的问题.

查看当前实际的执行计划,发现是找不到的,不在共享池中,当时也没有细想这个问题.
查看出现问题时刻的执行计划,发现它们都是走全表扫描,而后排序完成的.
查看表结构,发现确实是缺少相应的索引结构的.
因为主库端的日志应用推迟推迟到了7天,所以没有在主库端添加相应的索引结构(因为主库端并不执行这样的语句).而是直接在逻辑从库端添加了如下的索引结构:
sys> alter session set workarea_size_policy=manual;
sys> alter session set sort_area_size=409600000;
sys> create index productuser.ind60_product on productuser.product(PUBLISHSTATUS,PRIORITY,NVL(PAGEVIEW,0)) online;
sys> create index productuser.ind61_product on productuser.product(publishstatus,priority desc,nvl(refprice,0)) online;

发现SQL1可以走index_desc(p ind60_product)从而避免排序,但sql2就是避免不了排序,默认是全表扫描而后排序完成的,即使加提示:index(p ind61_product)也是需要排序操作的.
因为已经很晚了,就回家了.
第二天上班的时候,发现昨天晚上的IO还是很高,而且还是TEMP读写是最频繁的,抓取相应的sql,发现还是这两条SQL.
对于sql2,这里先不讨论.
对于sql1,就让人有些搞不明白了,昨天是走索引从而避免排序的呀,可查看出现问题时间段的执行计划,发现居然是走全表扫描,而后排序完成的.当前呢?也是走索引避免排序操作的呀.
查看问题时间段的绑定变量值,发现如下的值:

NAME       POSITION VALUE_STRING
---------- -------- --------------------
:1                1 291720
:2                2 291701
:1                1 300520
:2                2 300501
:1                1 294500
:2                2 294481
:1                1 20
:2                2 1

之后,我再查看当前的执行计划,这个sql1又找不到了,不在共享池中了.
可以判断这个sql1重复的硬分析,在出现问题之前,发生硬分析,peeking到的值刚好是291720这样很靠后的数据行,这时便走了全表扫描,而后排序的操作,实际上对于很靠后的数据行,不走索引是对的.

现在的问题是:为什么重复硬分析,为什么绑定变量值是这么大的值呢?
其实查看当前的共享池:
select plan_hash_value,count(1) cnt from v$sql group by plan_hash_value having count(1)>=100;
之后查看使用某一个执行计划的具体的sql语句,发现所有的语句都是使用字面值的,不仅仅是字面值有所不同,其它的地方也是不完全相同的.明显这些sql都是拼装出来的.
其实看看应用页面,这一点是最清楚的。应用页面上就是让用户选定限定条件,而后执行查询的.所以应用里是根据用户选定的条件拼装的sql语句,除了页数这里使用了绑定变量之外,其它的地方都没有使用绑定变量.
问题时间段的awr报表,因为数据库阻塞,数据库几乎不提供服务了,所以硬分析不是很高,在正常时间段,每秒钟的硬分析都是几十的.
这个问题算是定位了:因为拼装SQL,共享池中充满了没有使用绑定变量的sql语句,这样总体执行不是很频繁(但某个时间段很频繁)的sql1很快被挤出了共享池,下次执行的时候,reload,重新硬分析,peeking到的刚好是很靠后的页面,这时就选择了全表扫描而后排序的方式来完成操作.

那为什么会绑定这么大的数据值呢?也就是说为什么会选择这么靠后的页面呢?
其实看看应用页面也是很明显的,提供了最后一页,跳转到某个页面的功能,还有确切的搜索结果的数据行数.
我个人感觉如果搜索返回的结果集很大的话,最后一页,跳转到某个页面这样的功能其实是不应该提供的,其实你看看baidu,google的搜索页面,顶多让你10个页面10个页面的翻转,根本就没有最后一页,跳转到某个指定的页面的功能.如果搜索到的页面过多的话,客户跳转到某个页面,或者是最后一页,其实也是在碰运气,看看能不能找到想要的东西,但这样找到的概率有多大,几乎是微乎其微的,所以如果搜索到的页面过多,就应该设定更精确的搜索条件而后重新搜索的.我要找价格最低的,我却按价格降序排序,然后从最后一个页面开始往前翻,这不是很愚蠢吗?
其实说到搜索结果的数据行数,百度根本就没有提供这样的数据,google倒是提供了,但明显不是确切的结果,因为提供确切的结果数据行数是没有意义的,因为数据更新太快了,可能等你翻转到第二页的时候,数据行数已经改变了.因为实际上提供确切的数据行数的执行代价可能不亚于你查看某个页面的代价,特别是如果数据更新速度很快的话提供确切的数据行数是没有意义的,所以我感觉如果要提供搜索结果的数据行数的话,也应该是一个执行代价很低的估算的方法,至于如何估算倒是没有细想过.
当然这些东西,我可能更多的还是从性能的角度来考虑的,所以也许这些观点有待商榷.

和开发人员交流,才明白其实不仅仅是拼装sql的问题,这个问题确实是不好解决,而且最要命的是这个应用的访问被嵌入到了几百甚至上千的其它网站的页面代码里,很可能是当初写死了(很可能取了很靠后的页面).而具体是哪些网站,嵌入的具体代码是什么更加不好确定,其实想想这个应用在最初设计的时候存在的问题太多了.

这个问题先说到这里.
至于sql2无论如何都避免不了排序的问题.最初我感觉可能和这个索引中使用了nvl这样的函数有关系,因为以前确实遇到过这样的情形,在索引中使用了函数之后就是避免不了排序,可在其它数据库上使用dba_objects这样的表加载数据到测试表中,测试使用nvl,order by这样的要求几乎是一样的,是没有问题,可以使用索引避免排序的,在这台数据库上使用这样的表测试也是没有问题的,这就奇怪了,看来只能做一下10053事件了,
其实看看10053的跟踪文件,还是很明显的:
******* UNPARSED QUERY IS *******
 ORDER BY "P"."PRIORITY" DESC,NVL(NVL("P"."REFPRICE",0),0)

而原来的语句中是:
order by p.priority desc, nvl(refprice,0) asc

select部分中是:nvl(p.refprice, 0) refprice
所以,原来语句中的order by部分的refprice实际上引用的是这里的别名refprice,也就是nvl(p.refprice, 0),而不是product p的refprice列,所以最终替换后解析成了
order by p.priority desc,nvl(nvl(p.refprice,0),0)
而这里已经和索引定义中的nvl(refprice,0),也就是user_tab_cols中显示的隐藏列的定义不同了.(虽然说本质上还是一样的,但显然优化器还没有智能到那样的地步,它没有意识到这一点),所以这个索引避免不了排序操作.

明白了这些,其实解决起来就很简单了,将order by部分改写为:
order by p.priority desc, nvl(p.refprice,0) asc
或者是
order by p.priority desc, refprice asc
之后,就可以使用ind61_product避免排序操作了.
所以,写SQL的时候,你需要明白你引用的东西到底是什么,这一点是需要清楚的

这个使用索引避免不了排序的问题就算是解决了.当然如果它这里也出现sql1那样取很靠后的页面的情况的话,还是会使用全表扫描而后排序这样的操作来完成的,这是避免不了的,但实际上监控却没有发现这样的问题.

其实说到这个应用的真正的解决方案,和应用设计有关系,需要应用上做修改的。
这里不提应用设计上的改变,如果单从SQL修改的角度来看:
933>select /*+ ordered use_nl(a p)*/
       rowno,
       p.id productid,
       nvl(p.image1, '') image1,
       nvl(p.image2, '') image2,
       nvl(p.image3, '') image3,
       nvl(p.image4, '') image4,
       nvl(p.refprice, 0) refprice,
       nvl(p.catalogname, '') catalogname,
       nvl(p.brandid, 0) brandid,
       nvl(brandname, '') brandname,
       nvl(p.catalogid, 0) catalogid,
       nvl(p.seriesname, '') seriesname,
       nvl(p.name, '') name,
       nvl(p.productname, '') productname,
       nvl(p.name1, '') name1,
       nvl(p.name2, '') name2,
       nvl(dealernum, 0) dealernum,
       nvl(articlenum, 0) articlenum,
       getproducturls(p.id) as producturls
from
(
select rid,rowno
  from (select rownum as rowno, rid
          from (select  /*+ full(p) */rowid rid
                  from product p
                 where 1 = 1
                   and p.publishstatus = 3
                 order by p.priority desc, nvl(pageview, 0) desc) a
         where rownum <= 300000) b
 where rowno >= 299981
) a,product p
where a.rid=p.rowid
order by rowno;

Execution Plan
----------------------------------------------------------
Plan hash value: 3274237678

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |   103K|    47M|       |   121K  (1)| 00:24:18 |
|   1 |  SORT ORDER BY               |         |   103K|    47M|   107M|   121K  (1)| 00:24:18 |
|   2 |   NESTED LOOPS               |         |   103K|    47M|       |   110K  (1)| 00:22:11 |
|*  3 |    VIEW                      |         |   103K|  2517K|       |  7706   (1)| 00:01:33 |
|*  4 |     COUNT STOPKEY            |         |       |       |       |            |          |
|   5 |      VIEW                    |         |   103K|  1208K|       |  7706   (1)| 00:01:33 |
|*  6 |       SORT ORDER BY STOPKEY  |         |   103K|  2114K|  7320K|  7706   (1)| 00:01:33 |
|*  7 |        TABLE ACCESS FULL     | PRODUCT |   103K|  2114K|       |  7035   (1)| 00:01:25 |
|   8 |    TABLE ACCESS BY USER ROWID| PRODUCT |     1 |   460 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("ROWNO">=299981)
   4 - filter(ROWNUM<=300000)
   6 - filter(ROWNUM<=300000)
   7 - filter("P"."PUBLISHSTATUS"=3)

Statistics
----------------------------------------------------------
         43  recursive calls
          0  db block gets
      40311  consistent gets
          0  physical reads
          0  redo size
      23002  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         20  rows processed

996>select b.*, getproducturls(productid) as producturls
  from (select rownum as rowno, a.*
          from (select p.id productid,
                       nvl(p.image1, '') image1,
                       nvl(p.image2, '') image2,
                       nvl(p.image3, '') image3,
                       nvl(p.image4, '') image4,
                       nvl(p.refprice, 0) refprice,
                       nvl(p.catalogname, '') catalogname,
                       nvl(p.brandid, 0) brandid,
                       nvl(brandname, '') brandname,
                       nvl(p.catalogid, 0) catalogid,
                       nvl(p.seriesname, '') seriesname,
                       nvl(p.name, '') name,
                       nvl(p.productname, '') productname,
                       nvl(p.name1, '') name1,
                       nvl(p.name2, '') name2,
                       nvl(dealernum, 0) dealernum,
                       nvl(articlenum, 0) articlenum
                  from product p
                 where 1 = 1
                   and p.publishstatus = 3
                 order by p.priority desc, nvl(pageview, 0) desc) a
         where rownum <= 300000) b
 where rowno >= 299981;

Execution Plan
----------------------------------------------------------
Plan hash value: 4064044132

--------------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |         |   103K|    92M|       | 17038   (1)| 00:03:25 |
|*  1 |  VIEW                    |         |   103K|    92M|       | 17038   (1)| 00:03:25 |
|*  2 |   COUNT STOPKEY          |         |       |       |       |            |          |
|   3 |    VIEW                  |         |   103K|    90M|       | 17038   (1)| 00:03:25 |
|*  4 |     SORT ORDER BY STOPKEY|         |   103K|    44M|   100M| 17038   (1)| 00:03:25 |
|*  5 |      TABLE ACCESS FULL   | PRODUCT |   103K|    44M|       |  7035   (1)| 00:01:25 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ROWNO">=299981)
   2 - filter(ROWNUM<=300000)
   4 - filter(ROWNUM<=300000)
   5 - filter("P"."PUBLISHSTATUS"=3)

Statistics
----------------------------------------------------------
         43  recursive calls
          0  db block gets
      40291  consistent gets
          0  physical reads
          0  redo size
      23002  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         20  rows processed

我们查看一下这两个会话的PGA使用情况:
select s.sid,p.PGA_USED_MEM,p.PGA_ALLOC_MEM,p.PGA_FREEABLE_MEM,p.PGA_MAX_MEM
from v$process p,v$session s
where p.ADDR=s.PADDR and s.sid in(933,996);

       SID PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
---------- ------------ ------------- ---------------- -----------
       996      5520085      10150485                0   154346741
       933      5322477       9512181            65536    20915445

933的会话中,我为了和996的会话进行对比,我强制走了全表扫描,排序字段没有变,和996会话是一样的,但只取出了rowid字段,然后再关联product表取其它需要的字段.
933会话比996会话多出的20的逻辑IO是因为根据rowid关联取product表的其它字段的时候多出的逻辑IO,取20条记录,刚好就是20的逻辑IO.
933会话比996会话多出的1次内存排序是我最后对取出的20条记录按照rowno排序多出的一次排序,基本上没有什么排序量的.
但933比996在最大PGA内存使用上少出很多来,933只有20M,而996都达到了150M.排序字段是相同的,但933会话中引用到的其它字段比996少了很多,这里只引用到了rowid,没有取其它字段,所以对PGA内存要求少了很多.这样同样的业务调用,通过rowid关联再取数据的sql,就需要更大的并发量才会读写临时表空间来完成排序操作的.其实我这里就是想要表明不仅order by字段,select里的字段也影响到了workarea区域的使用的.

下面是通过rowid关联取数据,但不强制全表扫描,而是走索引避免排序的情形(是默认的选择):
965>select /*+ ordered use_nl(a p)*/
       rowno,
       p.id productid,
       nvl(p.image1, '') image1,
       nvl(p.image2, '') image2,
       nvl(p.image3, '') image3,
       nvl(p.image4, '') image4,
       nvl(p.refprice, 0) refprice,
       nvl(p.catalogname, '') catalogname,
       nvl(p.brandid, 0) brandid,
       nvl(brandname, '') brandname,
       nvl(p.catalogid, 0) catalogid,
       nvl(p.seriesname, '') seriesname,
       nvl(p.name, '') name,
       nvl(p.productname, '') productname,
       nvl(p.name1, '') name1,
       nvl(p.name2, '') name2,
       nvl(dealernum, 0) dealernum,
       nvl(articlenum, 0) articlenum,
       getproducturls(p.id) as producturls
from
(
select rid,rowno
  from (select rownum as rowno, rid
          from (select rowid rid
                  from product p
                 where 1 = 1
                   and p.publishstatus = 3
                 order by p.priority desc, nvl(pageview, 0) desc) a
         where rownum <= 300000) b
 where rowno >= 299981
) a,product p
where a.rid=p.rowid
order by rowno;

Execution Plan
----------------------------------------------------------
Plan hash value: 1874480058

----------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |               |   103K|    47M|       |   113K  (1)| 00:22:48 |
|   1 |  SORT ORDER BY                   |               |   103K|    47M|   107M|   113K  (1)| 00:22:48 |
|   2 |   NESTED LOOPS                   |               |   103K|    47M|       |   103K  (1)| 00:20:41 |
|*  3 |    VIEW                          |               |   103K|  2517K|       |   187   (0)| 00:00:03 |
|*  4 |     COUNT STOPKEY                |               |       |       |       |            |          |
|   5 |      VIEW                        |               |   103K|  1208K|       |   187   (0)| 00:00:03 |
|*  6 |       INDEX RANGE SCAN DESCENDING| IND60_PRODUCT |   103K|  2114K|       |   187   (0)| 00:00:03 |
|   7 |    TABLE ACCESS BY USER ROWID    | PRODUCT       |     1 |   460 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("ROWNO">=299981)
   4 - filter(ROWNUM<=300000)
   6 - access("P"."PUBLISHSTATUS"=3)

Statistics
----------------------------------------------------------
         66  recursive calls
          0  db block gets
       1252  consistent gets
          0  physical reads
          0  redo size
      25089  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         20  rows processed

这里的排序是最后order by rowno造成的操作,order by p.priority desc, nvl(pageview, 0) desc并没有排序操作,而是通过索引访问来完成的.

看一下这个会话的PGA使用:
select s.sid,p.PGA_USED_MEM,p.PGA_ALLOC_MEM,p.PGA_FREEABLE_MEM,p.PGA_MAX_MEM
from v$process p,v$session s
where p.ADDR=s.PADDR and s.sid in(965);

       SID PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
---------- ------------ ------------- ---------------- -----------
       965      5728773      10495221                0    10495221

这里的pga使用基本上就是uga的使用了,就是用来保存会话信息的,这里的使用是不可避免的.

1048>select b.*, getproducturls(productid) as producturls
  from (select rownum as rowno, a.*
          from (select /*+ index_desc(p ind60_product) */p.id productid,
                       nvl(p.image1, '') image1,
                       nvl(p.image2, '') image2,
                       nvl(p.image3, '') image3,
                       nvl(p.image4, '') image4,
                       nvl(p.refprice, 0) refprice,
                       nvl(p.catalogname, '') catalogname,
                       nvl(p.brandid, 0) brandid,
                       nvl(brandname, '') brandname,
                       nvl(p.catalogid, 0) catalogid,
                       nvl(p.seriesname, '') seriesname,
                       nvl(p.name, '') name,
                       nvl(p.productname, '') productname,
                       nvl(p.name1, '') name1,
                       nvl(p.name2, '') name2,
                       nvl(dealernum, 0) dealernum,
                       nvl(articlenum, 0) articlenum
                  from product p
                 where 1 = 1
                   and p.publishstatus = 3
                 order by p.priority desc, nvl(pageview, 0) desc) a
         where rownum <= 300000) b
 where rowno >= 299981;

Execution Plan
----------------------------------------------------------
Plan hash value: 2767923102

-------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |   103K|    92M| 24050   (1)| 00:04:49 |
|*  1 |  VIEW                           |               |   103K|    92M| 24050   (1)| 00:04:49 |
|*  2 |   COUNT STOPKEY                 |               |       |       |            |          |
|   3 |    VIEW                         |               |   103K|    90M| 24050   (1)| 00:04:49 |
|   4 |     TABLE ACCESS BY INDEX ROWID | PRODUCT       |   103K|    44M| 24050   (1)| 00:04:49 |
|*  5 |      INDEX RANGE SCAN DESCENDING| IND60_PRODUCT |   103K|       |   187   (0)| 00:00:03 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ROWNO">=299981)
   2 - filter(ROWNUM<=300000)
   5 - access("P"."PUBLISHSTATUS"=3)

Statistics
----------------------------------------------------------
         43  recursive calls
          0  db block gets
     143772  consistent gets
          0  physical reads
          0  redo size
      25089  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed

select s.sid,p.PGA_USED_MEM,p.PGA_ALLOC_MEM,p.PGA_FREEABLE_MEM,p.PGA_MAX_MEM
from v$process p,v$session s
where p.ADDR=s.PADDR and s.sid in(1048);

       SID PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
---------- ------------ ------------- ---------------- -----------
      1048      5134213       9905397           262144     9970933

原来的sql语句强制走索引避免排序操作的话,逻辑IO很高,这里很高的逻辑IO主要是因为不必要的回访表操作造成的.
确实没有排序操作,但逻辑IO太高,还是会消耗大量的cpu,大量并发的话,必然导致hash latch争用,不断的spin操作消耗CPU,如果sql语句改成这样的话,并发下的必然结果是CPU没有空闲的.

综合比对:
select /*+ ordered use_nl(a p)*/
       rowno,
       p.id productid,
       nvl(p.image1, '') image1,
       nvl(p.image2, '') image2,
       nvl(p.image3, '') image3,
       nvl(p.image4, '') image4,
       nvl(p.refprice, 0) refprice,
       nvl(p.catalogname, '') catalogname,
       nvl(p.brandid, 0) brandid,
       nvl(brandname, '') brandname,
       nvl(p.catalogid, 0) catalogid,
       nvl(p.seriesname, '') seriesname,
       nvl(p.name, '') name,
       nvl(p.productname, '') productname,
       nvl(p.name1, '') name1,
       nvl(p.name2, '') name2,
       nvl(dealernum, 0) dealernum,
       nvl(articlenum, 0) articlenum,
       getproducturls(p.id) as producturls
from
(
select rid,rowno
  from (select rownum as rowno, rid
          from (select rowid rid
                  from product p
                 where 1 = 1
                   and p.publishstatus = 3
                 order by p.priority desc, nvl(pageview, 0) desc) a
         where rownum <= 300000) b
 where rowno >= 299981
) a,product p
where a.rid=p.rowid
order by rowno;

这个是最优的,不需要排序,没有不必要的回访表,逻辑io也不大.

对于取靠前的数据行:
select /*+ ordered use_nl(a p)*/
       rowno,
       p.id productid,
       nvl(p.image1, '') image1,
       nvl(p.image2, '') image2,
       nvl(p.image3, '') image3,
       nvl(p.image4, '') image4,
       nvl(p.refprice, 0) refprice,
       nvl(p.catalogname, '') catalogname,
       nvl(p.brandid, 0) brandid,
       nvl(brandname, '') brandname,
       nvl(p.catalogid, 0) catalogid,
       nvl(p.seriesname, '') seriesname,
       nvl(p.name, '') name,
       nvl(p.productname, '') productname,
       nvl(p.name1, '') name1,
       nvl(p.name2, '') name2,
       nvl(dealernum, 0) dealernum,
       nvl(articlenum, 0) articlenum,
       getproducturls(p.id) as producturls
from
(
select rid,rowno
  from (select rownum as rowno, rid
          from (select rowid rid
                  from product p
                 where 1 = 1
                   and p.publishstatus = 3
                 order by p.priority desc, nvl(pageview, 0) desc) a
         where rownum <= 100) b
 where rowno >= 81
) a,product p
where a.rid=p.rowid
order by rowno;

 

select b.*, getproducturls(productid) as producturls
  from (select rownum as rowno, a.*
          from (select p.id productid,
                       nvl(p.image1, '') image1,
                       nvl(p.image2, '') image2,
                       nvl(p.image3, '') image3,
                       nvl(p.image4, '') image4,
                       nvl(p.refprice, 0) refprice,
                       nvl(p.catalogname, '') catalogname,
                       nvl(p.brandid, 0) brandid,
                       nvl(brandname, '') brandname,
                       nvl(p.catalogid, 0) catalogid,
                       nvl(p.seriesname, '') seriesname,
                       nvl(p.name, '') name,
                       nvl(p.productname, '') productname,
                       nvl(p.name1, '') name1,
                       nvl(p.name2, '') name2,
                       nvl(dealernum, 0) dealernum,
                       nvl(articlenum, 0) articlenum
                  from product p
                 where 1 = 1
                   and p.publishstatus = 3
                 order by p.priority desc, nvl(pageview, 0) desc) a
         where rownum <= 100) b
 where rowno >= 81;

两者都是通过index_desc(p ind60_product)来避免排序操作的,前者逻辑IO:164,后者逻辑io:245,因为后者还是不必要的回访了前80行的数据.
如果是1,20的话,两者的逻辑IO是相同的,都是164.

也就是说对于取靠前的数据行,/*+ ordered use_nl(a p)*/的方式也不输,甚至更优一些.
所以在目前不改变业务设计,还是要取很靠后的数据行的话,要修改sql的话,应该改写成这样:
select /*+ ordered use_nl(a p)*/
       rowno,
       p.id productid,
       nvl(p.image1, '') image1,
       nvl(p.image2, '') image2,
       nvl(p.image3, '') image3,
       nvl(p.image4, '') image4,
       nvl(p.refprice, 0) refprice,
       nvl(p.catalogname, '') catalogname,
       nvl(p.brandid, 0) brandid,
       nvl(brandname, '') brandname,
       nvl(p.catalogid, 0) catalogid,
       nvl(p.seriesname, '') seriesname,
       nvl(p.name, '') name,
       nvl(p.productname, '') productname,
       nvl(p.name1, '') name1,
       nvl(p.name2, '') name2,
       nvl(dealernum, 0) dealernum,
       nvl(articlenum, 0) articlenum,
       getproducturls(p.id) as producturls
from
(
select rid,rowno
  from (select rownum as rowno, rid
          from (select rowid rid
                  from product p
                 where 1 = 1
                   and p.publishstatus = 3
                 order by p.priority desc, nvl(pageview, 0) desc) a
         where rownum <= :1) b
 where rowno >= :2
) a,product p
where a.rid=p.rowid
order by rowno;

对于目前的现状,在应用修改之前,针对当前的内存使用情况:
sga:4300M,当前,db_cache_size=1600m,shared_pool_size=2500m,这是当前的值,数据库端并没有对它们做下限值的限制.
pga:2000m

做出如下的调整:
db_cache先不做修改,awr报表没有反映这个设置方面的问题,对于共享池,针对当前拼装sql,重用可能性不大的现状,调整为1500m(减小了1000M),也就是说db_cache_size=1600m,shared_pool_size=1500m,sga_target=sga_max_size=3300m(实际上做出了这样的下限值限定之后,共享池调整的余地已经很小了,基本上可以确定就是1500M可能稍微多一点了),把节省出来的1000M内存分配给了pga,pga_aggregate=3000m.
同时,shared_pool_size大小的减小,默认情况下会导致LCR CACHE的减小,如果它太小的话,容易报错ORA-04031,导致lsp进程终止的.
所以必须保证lcr cache的大小的:
EXECUTE DBMS_LOGSTDBY.APPLY_SET('MAX_SGA',350);
将lcr cache固定为350M.