Re: Paging Next/Prev for a result set in Oracle

From: chao_ping <chao_ping_at_163.com>
Date: 7 Apr 2002 19:03:51 GMT
Message-ID: <3cb09817$1@usenetgateway.com>

one step further: Tom,i got your idea, and want to further optimize it if possible, the following is my question: We have an 3-tier application and tuxedo connect to oracle.The database is small(sum(bytes) =600M) and we have a SGA of 1.6GB(with db buffer 1.4GB). This is a readonly database, with snapshot refresh from the maindb half an hour. But currently facing serious performance problem, all latch wait(cache buffer chain, cache buffer lru chain), CPU is 100% utilized, with only 6-8 connections open and active. I checked the system, tried modify parameter like db_block_lru_latch etc, but did not get much performance gains.I now refer to the SQLs, hoping to reduce the buffer gets. The following is the sample sql from the procedures(from statpack, the sql with most buffer_gets). I want to reduce buffer_gets of these sql, and need your help:

---

procedure get_goods_end_Desc(inp_goods_class_id in varchar2 default '0',inp_region_to in varchar2 default null,inp_sort_by in number default 1,inp_sort_type in number default 1, inp_page_no in number default 1,inp_page_size in number default 30,outp_ret out varchar2 ) as /*asc*/ CURSOR articlescatalog_cur00(cc_sort_by in number) is SELECT * FROM snp_articlescatalog a where a.goods_class_id like inp_goods_class_id||'%' and a.end_date_dtm> sysdate and a.end_date_dtm<=

sysdate+0.5 and a.on_shell_type <> 18 and a.status in(1,2) order by dec-
ode(cc_sort_by,0,(a.end_date_dtm-sysdate),1,a.bid_count,2,a.current_pri-
ce ,3,havepic_yn,4,fix_price,5,(a.start_date_dtm-sysdate),6,a.seller_tr-

ust_score,a.product_id) asc;

   articlescatalog_rec0 articlescatalog_cur00%rowtype;
   articlescatalog_rec1 articlescatalog_cur10%rowtype;
   articlescatalog_tab articlecatalog_tabtype; tv_ret varchar2(19860);

   next_row pls_integer :=0 ; tn_fetch_begin number :=0; tn_fetch_end    number :=0; tn_findnum number :=1; tn_region_to_length number :=0;    begin tn_region_to_length:=nvl(length(inp_region_to),0); if    inp_region_to ='%' or inp_region_to ='CN' THEN

   tn_region_to_length:=0; end if;
   tn_fetch_begin:=(inp_page_no-1)*inp_page_size;
   tn_fetch_end:=(inp_page_no)*inp_page_size; /*asc 0 desc 1*/ if

   inp_sort_type=0 then if tn_region_to_length=0 then FOR    articlescatalog_rec0 IN articlescatalog_cur00(inp_sort_by) LOOP if    tn_findnum>tn_fetch_begin and tn_findnum<=tn_fetch_end then next_row    := NVL (articlescatalog_tab.LAST, 0) + 1;    articlescatalog_tab(next_row) := articlescatalog_rec0;    tn_findnum:=tn_findnum+1; elsif tn_findnum<=tn_fetch_begin then    tn_findnum:=tn_findnum+1; elsif tn_findNum>tn_fetch_end then exit;    end if; END LOOP; else NULL;/*omit other statements here*/

                end if;--tn_region_to_length =0 IF next_row>0 THEN
                get_desc_string(articlescatalog_tab,tv_ret); END IF; --
                end if next_row>0 elsif inp_sort_type=1 then NULL;/*omit
                the other statements here*/ end if;-- end of
                tn_region_to_length=0; IF next_row>0 THEN
                get_desc_string(articlescatalog_tab,tv_ret); END IF; --
                end if next_row>0 end if; outp_ret:=
                nvl(tv_ret,tv_nofound); /*EXCEPTION WHEN OTHERS THEN
                RETURN '-1';/*error*/ end;--get_goods_end_Desc
----------------------------

This poor SQL generate too much buffer_gets when it ran. And I modified it like: procedure get_goods_end_Desc(inp_goods_class_id in varchar2 default '0',inp_region_to in varchar2 default null,inp_sort_by in number default 1,inp_sort_type in number default 1, inp_page_no in number default 1,inp_page_size in number default 30,outp_ret out varchar2 ) as /*asc*/ CURSOR articlescatalog_cur00(cc_sort_by in number,inp_page_no in number, inp_page_size in number) is SELECT col1,col2,....coln FROM (select col1,col2,...coln,rownum rowcount from snp_articlescatalog a where a.goods_class_id like inp_goods_class_id||'%' and a.end_date_dtm> sysdate and a.end_date_dtm<= sysdate+0.5 and a.on_shell_type <> 18 and a.status in(1,2) order by decode(cc_sort_by,0,(a.end_date_dtm-sysdate),- 1,a.bid_count,2,a.current_price ,3,havepic_yn,4,fix_price,5,(a.start_date _dtm-sysdate),6,a.seller_trust_score,a.product_id) asc ) where rowcount>inp_page_no-1 and rowcount<inp_page_no*inp_page_size;

   articlescatalog_rec0 articlescatalog_cur00%rowtype;
   articlescatalog_rec1 articlescatalog_cur10%rowtype; --
   articlescatalog_tab articlecatalog_tabtype; tv_ret varchar2(19860);

   next_row pls_integer :=0 ; tn_fetch_begin number :=0; tn_fetch_end    number :=0; tn_findnum number :=1; tn_region_to_length number :=0;    begin tn_region_to_length:=nvl(length(inp_region_to),0); if    inp_region_to ='%' or inp_region_to ='CN' THEN    tn_region_to_length:=0; end if; /*asc 0 desc 1*/ if inp_sort_type=0    then if tn_region_to_length=0 then open    articlescatalog_cur00(inp_sort_by,inp_page_no,inp_page_size); loop    fetch articlescatalog_cur00 into articlescatalog_rec0;

   get_desc_string_dba(articlescatalog_rec0,tv_ret); exit when
   articlescatalog_cur00%notfound; end loop; close
   articlescatalog_cur00; else open
   articlescatalog_cur01(inp_sort_by,inp_page_no,inp_page_size); loop

   fetch articlescatalog_cur01 into articlescatalog_rec0;

   get_desc_string_dba(articlescatalog_rec0,tv_ret); exit when
   articlescatalog_cur01%notfound; end loop; close
   articlescatalog_cur01; --null; end if;--tn_region_to_length =0

        elsif inp_sort_type=1 then NULL; end if; outp_ret:=
        nvl(tv_ret,tv_nofound); /*EXCEPTION WHEN OTHERS THEN RETURN
        '-1';/*³ِ´يت±*/ end;--get_goods_end_Desc

But i am not sure with my sql, whether it is better than the original sql, and whether it is still possible to improve it further more.

Thanks tom and your asktom:)

--
an oracle fan, 
an oracle beginner

Posted via dBforums
http://dbforums.com

Received on Sun Apr 07 2002 - 14:03:51 CDT