Oracle Performance Optimization

ArticleDirectory

• I/O busy
• High CPU load
• Query Plan Analysis
• Optimization Method:
• Sqlplus settings:
• Query plan settings
• Online Performance Optimization

System performance I/O busy I/o hotspot files and SQL analysis and judgment of hotspot data files

SQL> @? /Rdbms/admin/awrrpt. SQL

Or

Column name format A40;

Set lines 500;

Select name, sum (S. phyrds), sum (phywrts), sum (readtim), sum (writetim ),

Sum (phyrds + phywrts)/(sum (readtim + writetim) avgio from V $ filestat S, V $ datafile F

Where S. File # = f. File #

Group by name

Order by 6;

1. Move data files and adjust hotspot data files to idle Disks

SQL> startup Mount

SQL> alter database rename 'source File 'to 'destination file'

$ MV 'source file' 'destination file'

SQL> alter database open

 

View the physical Io status of each process

Select se. Sid,

Se. Serial #,

Pr. spid,

Se. username,

Se. status,

Se. program,

Se. module,

St. event,

St. p1text,

St. P1,

St. P2,

St. P3,

St. State,

St. seconds_in_wait,

Si. physical_reads,

Si. block_changes

From v $ session se,

V $ session_wait St,

V $ sess_io Si,

V $ process PR

Where St. Sid = Se. Sid and St. Sid = Si. Sid

And se. paddr = Pr. ADDR and Se. Sid> 6

And st. wait_time = 0 and St. event not like '% SQL %'

Order by physical_reads

Queries SQL statements with high Io values;

Select P. spid, S. Sid, S. Machine, S. Program, Q. disk_reads, Q. SQL _text

From v $ PROCESS p, V $ session S, V $ SQL Q

Where p. ADDR = S. paddr and S. SQL _id = Q. SQL _id

Order by 5;

 

8799 612 cqcu-zyite-col109

Perl @ cqcu-zyite-col109 (TNS V1-V3) 1683932

 

Spid Sid Machine

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

Program disk_reads

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

SQL _text

Bytes ----------------------------------------------------------------------------------------------------

Delete resmonicurinfo RC where exists (select 1 from rescurinfo Ri where RC. probeid in

('Prs00008') and RC. probeid = Ri. probeid and RC. resid = Ri. resid and RC. dat

Aitemid = Ri. dataitemid)

 

8733 483 cqcu-zyite-col113

Perl @ cqcu-zyite-col113 (TNS V1-V3) 1998034

Delete resmonicurinfo RC where exists (select 1 from rescurinfo Ri where RC. probeid in

 

Spid Sid Machine

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

Program disk_reads

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

SQL _text

Bytes ----------------------------------------------------------------------------------------------------

('Prs00012') and RC. probeid = Ri. probeid and RC. resid = Ri. resid and RC. dat

Aitemid = Ri. dataitemid)

B) Delete from devicemacvlan where DeviceID =: p1

C) 31448 113 cqcu-zyite-col113 Perl @ cqcu-zyite-col113 (TNS V1-V3) 11009354

Select D. deviceID, D. ppdescr, D. upconspeed, D. downconspeed, D. upcfgspeed, D. downloadspeed, D. upmaxspeed, D. downmaxspeed, D. usnr, D. downsnr, D. upattenuation, D. downattenuation, p.ppt ype from dslamportparacur D, ppinfo P where D. deviceID = P. deviceID (+) and D. ppdescr = P. ppdescr (+)

High CPU load

1. Find the process number with an excessively high CPU;

PS-e-o PID, pcpu, user, argS | sort-n + 1"

1. View the SQL statement corresponding to the process number

Select p. spid, S. status, substr (S. machine, 1, 10) machine, substr (S. program, 1, 10) program, substr (Q. SQL from V $ session S, V $ PROCESS p, V $ sqlarea Q where S. paddr = P. ADDR and S. SQL _id = Q. SQL _id (+)

1.  

Case: Database CPU usage is too high

3540, 84.25%, active, FJ-zyite-a, Perl @ FJ-zy, select adeviceid, p1.portdescr, C. transcircode from circuit C, p

28380, 83.24%, active, nmscol1, Perl @ nmsco, merge into resmonicurinfo A using (select resid, respara, dat

15713, 83.18%, active, nmscol2.qz, Perl @ nmsco, merge into resmonicurinfo A using (select resid, respara, dat

5880, 49.81%, active, FJ-zyite-a, Perl @ FJ-zy, select P. spid, S. status, substr (S. machine, 1, 10) machine, substr (S. program

112, 34.73%, inactive, FJ-zyite-a, select failureid, max (G. endlevel) from failurelist, (select failureupgr

100, 31.54%, active, nmscol2.fz, sqlldr @ NMS, insert into rescurinfo (probeid, resid, respara, dataitemid, value, recor

21706, 19.13%, active, nmscol1.ly, select distinct AA. circuitid, AA. fluxtime, AA. inavgvec, AA. outavgvec, c

12672, 14.34%, active, nmscol2.fz, Perl @ nmsco, select to_char (checktime, 'yyyymmddhh24mis') from rcheckreschecklog

28114, 13.75%, active, nmscol1.pt, Perl @ nmsco, select to_char (checktime, 'yyyymmddhh24mis') from rcheckreschecklog

SQL problem diagnosis and optimization query plan analysis

: Top right priority principle

 

SQL> set autotrace traceonly;

SQL> Delete resmonicurinfo RC where exists (select 1 from rescurinfo Ri where RC. probeid in ('prs00012') and RC. probeid = Ri. probeid and RC. resid = Ri. resid and RC. dataitemid = Ri. dataitemid );

 

10024 rows deleted.

 

 

Execution Plan

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

 

| ID | operation | Name | rows | bytes | cost (% CPU) |

Pstart | pstop |

 

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

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

 

| 0 | Delete statement | 1 | 31 | 1504 K (1) |

|

 

| 1 | Delete | resmonicurinfo |

|

 

| * 2 | filter |

|

 

| 3 | table access full | resmonicurinfo | 95404 | 2888k | 750 K (1) |

|

 

| * 4 | filter |

|

 

| 5 | partition list single | 1 | 34 | 8 (0) |

Key | key |

 

| * 6 | table access full | rescurinfo | 1 | 34 | 8 (0) |

Key | key |

 

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

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

 

 

Predicate information (identified by Operation ID ):

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

 

2-filter (exists (select 0 from "rescurinfo" "Ri" where: b1 = 'prs00012' and

"Ri". "dataitemid" =: B2 and "Ri". "resid" =: B3 ))

4-filter (: b1 = 'prs00012 ')

6-filter ("Ri". "dataitemid" =: B1 and "Ri". "resid" =: B2)

 

Note

-----

-'Plan _ table' is old version

 

 

Statistics

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

1 recursive cballs

101751 db block gets

3984004 consistent gets

670813 physical reads

9593840 redo size

667 bytes sent via SQL * Net to client

766 bytes encoded ed via SQL * Net From Client

4 SQL * Net roundtrips to/from client

1 sorts (memory)

0 sorts (Disk)

10024 rows processed

 

View Indexes

Select U. index_name, index_type, column_name

From user_indexes I, user_ind_columns u

Where I. index_name = U. index_name and I. table_name = 'rescurinfo ';

View object types,

Partition Table?

Select object_type from user_objects where object_name = 'rescurinfo ';

Optimization Method: Create an index

Are resources busy creating indexes for large tables?

Create a partition Index

Create index ind_rescurinfo_resid on rescurinfo (resid) tablespace indexcfg online local nologging;

Create a non-partition Index

Create index ind_devicemacvlan_deviceid on devicemacvlan (DeviceID) tablespace indexlist online nologging;

Create index ind_resmonicurinfo_resparadata on resmonicurinfo (resid, respara, dataitemid) tablespace indexlist online nologging;

Clear failurelist

Optional parameters for creating indexes for large online tables:

Parallel 4

Sort_area_size

Muti_block_read_count

 

Table Analysis

Analyze table tablename

 

 

Appendix: sqlplus settings:

Define editor = vi

Set serveroutput on size 1000000

Set trimspool on

Sets long 5000

Set linesize 100

Set pagesize 9999

Column plan_plus_exp format a80;

 

Query plan settings

SQL> alter user system identified by Oracle;

The user has changed.

 

SQL> conn system/Oracle

 

SQL> @? /Rdbms/admin/utlxplan. SQL

SQL> Create public synonym plan_table for plan_table;

SQL> grant all on plan_table to public;

 

SQL> Conn/As sysdba

SQL> @? /Sqlplus/admin/plustrce. SQL

SQL> grant plustrace to public

 

Set autotrace off | on | traceonly

Online Performance Optimization

SQL> alter system set timed_statistics true scope = both;

SQL> alter sytem set SQL _trace = true;

SQL> alter system set max_dump_file_size = '10m ';