(Oralce) Web page Optimization Instance

web| Page | Optimization example of Web page-turning optimization

Author: Wanghai





Environment:

Linux version 2.4.20-8custom (ROOT@WEB2) (gcc version 3.2.2 20030222 (Red Hat Linux 3.2.2-5)) #3 SMP Thu June 5 22:03:36 CS T 2003

mem:2113466368

swap:4194881536

CPU: Two Hyper-Threading Intel (R) Xeon (TM) CPU 2.40GHz



Optimization before the statement in the MySQL query about 15 seconds out, transfer to Oracle without adjusting the index and statements in the case of the execution time is about 4-5 seconds, adjusted after the execution time is less than 0.5 seconds.



Page Turn statement:

SELECT * FROM (select t1.*, rownum as LineNum from (

SELECT/*+ Index (a ind_old) * *

A.category from Auction_auctions a WHERE a.category = ' 170101 ' and a.closed= ' 0 ' and ends > Sysdate and (a.approve_stat us>=0) Order by A.ends) T1 where RowNum < 18681) where LineNum >= 18641



Tables queried: auction_auctions (Product table)

Table structure:

sql> desc auction_auctions;

Name Null? Type

----------------------------------------- -------- ----------------------------

ID not NULL VARCHAR2 (32)

USERNAME VARCHAR2 (32)

TITLE CLOB

Gmt_modified not NULL DATE

Starts not NULL DATE

DESCRIPTION CLOB

Pict_url CLOB

CATEGORY not NULL VARCHAR2 (11)

Minimum_bid number

Reserve_price number

Buy_now number

Auction_type CHAR (1)

DURATION VARCHAR2 (7)

Incrementnum not NULL number

City VARCHAR2 (30)

Prov VARCHAR2 (20)

LOCATION VARCHAR2 (40)

Location_zip VARCHAR2 (6)

SHIPPING CHAR (1)

PAYMENT CLOB

INTERNATIONAL CHAR (1)

ENDS not NULL DATE

Current_bid number

CLOSED CHAR (2)

photo_uploaded CHAR (1)

QUANTITY Number (11)

STORY CLOB

Have_invoice not NULL number (1)

Have_guarantee not NULL number (1)

Stuff_status not NULL number (1)

Approve_status not NULL number (1)

Old_starts not NULL DATE

ZOO VARCHAR2 (10)

Promoted_status not NULL number (1)

Repost_type CHAR (1)

Repost_times not NULL number (4)

Secure_trade_agree not NULL number (1)

Secure_trade_transaction_fee VARCHAR2 (16)

Secure_trade_ordinary_post_fee number

Secure_trade_fast_post_fee number



Number and size of table records

Sql> Select COUNT (*) from auction_auctions;



COUNT (*)

----------

537351



Sql> Select Segment_name,bytes,blocks from user_segments where segment_name = ' auction_auctions ';



Segment_name BYTES BLOCKS

Auction_auctions 1059061760 129280



The original index on the table

Create INDEX Ind_old on auction_auctions (closed,approve_status,category,ends) tablespace tbsindex compress 2;



Sql> Select Segment_name,bytes,blocks from user_segments where segment_name = ' ind_old ';



Segment_name BYTES BLOCKS

Ind_old 20971520 2560

Tables and indexes have been analyzed, let's take a look at the cost of SQL execution

sql> set Autotrace trace;

Sql> SELECT * FROM (select t1.*, rownum as LineNum from (select A.* to auction_auctions a WHERE a.category like ' 18% ') and a.closed= ' 0 ' and ends > Sysdate and (a.approve_status>=0) Order by A.ends) T1 where RowNum <18681) where Lin Enum >= 18641;



Rows selected.



Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT optimizer=choose (cost=19152 card=18347 byt

es=190698718)



1 0 VIEW (cost=19152 card=18347 bytes=190698718)

2 1 COUNT (Stopkey)

3 2 VIEW (cost=19152 card=18347 bytes=190460207)

4 3 TABLE ACCESS (by INDEX ROWID) of ' auction_auctions '

(cost=19152 card=18347 bytes=20860539)



5 4 INDEX (RANGE SCAN) of ' Ind_old ' (non-unique) (Cost

=810 card=186003)



Statistics

----------------------------------------------------------

0 Recursive calls

0 db Block gets

19437 consistent gets

18262 Physical Reads

0 Redo Size

114300 Bytes sent via sql*net to client

56356 bytes received via sql*net from client

435 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

Rows processed



We can see that the SQL statement finds the innermost result set through the index range scan, and then the data is finally drawn through two view operations. of which 18502 consistent gets,17901 physical reads



Let's take a look at whether this index is built in the end, and see the distinct value of each lookup column first.

Select COUNT (distinct ends) from auction_auctions;



COUNT (Distinctends)

-------------------

338965



Sql> Select COUNT (Distinct category) from Auction_auctions;



COUNT (Distinctcategory)

-----------------------

1148



Sql> Select COUNT (Distinct closed) from auction_auctions;



COUNT (distinctclosed)

---------------------

2

Sql> Select COUNT (Distinct approve_status) from auction_auctions;



COUNT (Distinctapprove_status)

-----------------------------

5



Average storage length of columns in a page index

Sql> Select AVG (vsize (ends)) from Auction_auctions;



AVG (Vsize (ENDS))

----------------

7



Sql> Select AVG (vsize (closed)) from Auction_auctions;



AVG (Vsize (CLOSED))

------------------

2



Sql> Select AVG (vsize (category)) from Auction_auctions;



AVG (Vsize (CATEGORY))

--------------------

5.52313106



Sql> Select AVG (vsize (approve_status)) from Auction_auctions;



AVG (Vsize (approve_status))

--------------------------

1.67639401



Let's estimate the size of the various combination indexes and see that the closed,approve_status,category are relatively lower set potential columns (more duplicates), and we'll probably calculate the space needed for the various page indexes below.



Column distinct num column len

Ends 338965 7

Category 1148 5.5

Closed 2 2

Approve_status 5 1.7



Index1: (ends,closed,category,approve_status) Compress 2

ENDS:DISTINCT Number---338965

CLOSED:DISTINCT Number---2

Index size=338965*2* (9+2) + 537351* (1.7+5.5+6) =14603998



Index2: (Closed,category,ends,approve_status)

CLOSED:DISTINCT Number---2

CATEGORY:DISTINCT Number---1148

Index size=2*1148* (2+5.5) +537351* (7+1.7+6) =7916279



INDEX3: (closed,approve_status,category,ends)

CLOSED:DISTINCT Number---2

Approve_status:distinct number―5

Index size=2*5* (2+1.7) +537351* (7+5.5+6) =9941030



The results came out, Index2: (closed,category,ends,approve_status) the smallest index



Let's look at the statement again.

SELECT * FROM (select t1.*, rownum as LineNum from (select A.* to auction_auctions a WHERE a.category like ' 18% ' and A.C Losed= ' 0 ' and ends > Sysdate and (a.approve_status>=0) Order by A.ends) T1 where rownum <18681) where linenum ; = 18641;

You can see that this SQL statement has great scope for optimization, first the innermost result set select A.* from Auction_auctions a WHERE a.category like ' 18% ' and a.closed= ' 0 ' and ends > Sysdate and (a.approve_status>=0) Order by A.ends, this will take the index range scan, and then the table scan by ROWID, so if the eligible data is much more resource-intensive, We could rewrite it as

SELECT A.rowid from Auction_auctions a WHERE a.category like ' 18% ' and a.closed= ' 0 ' and ends > Sysdate and (a.approve_s tatus>=0) Order by A.ends

In this case, the innermost result set requires only the index fast full scan to complete, and then rewrite to draw the following statement



SELECT * from Auction_auctions where rowid the Select Rid from (

SELECT T1.rowid RID, rownum as linenum from

(SELECT A.rowid from Auction_auctions a WHERE a.category like ' 18% ' and a.closed= ' 0 ' and ends > Sysdate and

(a.approve_status>=0) ORDER by A.ends) T1 where RowNum < 18681) where LineNum >= 18641)



Now let's test the query cost for this index



SELECT * from Auction_auctions where rowid the Select Rid from (

SELECT T1.rowid RID, rownum as linenum from

(SELECT A.rowid from Auction_auctions a WHERE a.category like ' 18% ' and a.closed= ' 0 ' and ends > Sysdate and

(a.approve_status>=0) ORDER by A.closed,a.ends) T1 where RowNum < 18681) where LineNum >= 18641)

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT optimizer=choose (cost=18698 card=18344 byt

es=21224008)



1 0 NESTED LOOPS (cost=18698 card=18344 bytes=21224008)

2 1 VIEW (cost=264 card=18344 bytes=366880)

3 2 SORT (UNIQUE)

4 3 COUNT (Stopkey)

5 4 VIEW (cost=264 card=18344 bytes=128408)

6 5 SORT (Order by Stopkey) (cost=264 card=18344 byt

ES=440256)



7 6 INDEX (FAST full SCAN) of ' Idx_auction_browse '

(Non-unique) (cost=159 card=18344 bytes=440256)



8 1 TABLE ACCESS (by USER ROWID) of ' auction_auctions ' (cost

=1 card=1 bytes=1137)



Statistics

----------------------------------------------------------

0 Recursive calls

0 db Block gets

2080 consistent gets

1516 physical Reads

0 Redo Size

114840 Bytes sent via sql*net to client

56779 bytes received via sql*net from client

438 sql*net roundtrips To/from Client

2 Sorts (memory)

0 Sorts (disk)

Rows processed



You can see consistent gets from 19437 to 2080,physical reads from 18262 to 1516, and the query time also drops to 0 for 4 seconds. 5 Seconds, you can say the SQL tuning has the desired effect.



And then I changed the statement,



Sql> SELECT * from Auction_auctions where rowid

2 (SELECT rid from (

3 SELECT t1.rowid RID, rownum as linenum from

4 (SELECT A.rowid from Auction_auctions a

5 WHERE a.category like ' 18% ' and a.closed= ' 0 ' and ends > Sysdate and

A.approve_status>=0

6 7 order by A.closed,a.category,a.ends) T1

8 where RowNum < 18600) where linenum >= 18560);



Rows selected.



Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT optimizer=choose (cost=17912 card=17604 byt

es=20367828)



1 0 NESTED LOOPS (cost=17912 card=17604 bytes=20367828)

2 1 VIEW (cost=221 card=17604 bytes=352080)

3 2 SORT (UNIQUE)

4 3 COUNT (Stopkey)

5 4 VIEW (cost=221 card=17604 bytes=123228)

6 5 INDEX (RANGE SCAN) of ' Idx_auction_browse ' (non-

UNIQUE) (cost=221 card=17604 bytes=422496)



7 1 TABLE ACCESS (by USER ROWID) of ' auction_auctions ' (cost

=1 card=1 bytes=1137)



Statistics

----------------------------------------------------------

0 Recursive calls

0 db Block gets

Consistent gets

Physical Reads

0 Redo Size

117106 Bytes sent via sql*net to client

56497 bytes received via sql*net from client

436 sql*net roundtrips To/from Client

1 Sorts (memory)

0 Sorts (disk)

Rows processed



The index leading column is added to the order by to eliminate the

6 5 SORT (Order by Stopkey) (cost=264 card=18344 byt

ES=440256)

And dropped the consistent gets from 2080 to 550.