ORACLE performance optimization ideas, related commands and parameter modification

ORACLE performance optimization ideas, related commands and parameter modification

Summary of ORACLE performance troubleshooting reasons

Preface

This article is mainly for the guys who have nowhere to start with performance> With a direction for guidance, the real optimization process, meticulously analyzes each waiting event in Oracle, analyzes the execution plan of each SQL, and analyzes hot data. The content is quite a lot.

The performance investigation of oracle I summarized mainly from three directions

  • Server resource usage
  • Oracle parameter settings
  • awr report analysis

Server resource

For server resources, the most important thing is to use the overall situation under Linux (top), thread queue (vmstat), memory (free -m), etc. to check the resource occupancy of CPU, memory, disk, network, etc. I will not expand it in detail here.

Oracle parameter settings

In the Oracle database, processes, sessions, and transactions respectively represent the number of processes, the number of sessions, and the current maximum allowable transaction volume. The adjustment of these three parameters is used the most. Set these three values reasonably according to your actual business concurrency.

According to the description in the official document, they are related.

The following is the 11.2.0.4 database version

sessions=(1.5*processes)+22

transactions=1.1*sessions

In the 10g version

sessions=(1.1*processes) +5

transactions=1.1*sessions

View processes and sessions parameters

SQL> show parameter processes
NAME                                  TYPE         VALUE
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
job_queue_processes                  integer     10
log_archive_max_processes            integer     2
processes                            integer     50
 
SQL> show parameter sessions
NAME                                  TYPE         VALUE
license_max_sessions                 integer     0
license_sessions_warning             integer     0
logmnr_max_persistent_sessions       integer     1
sessions                             integer     60
shared_server_sessions               integer     10
 

Modify the values of processes and sessions

SQL> alter system set processes=300 scope=spfile;
 
SQL> alter system set sessions=335 scope=spfile;
 
 

Note: Modifying the values of processes and sessions must restart the oracle server to take effect

awr report analysis

Here are the steps to export the oracle awr report. The analysis process of the specific awr report will be described separately in the following chapter> how to analyze and view the awr report.

  • 1. Enter the database sqlplus/as sysdba

  • 2. View user show parameter db_name

  • 3. After starting the pressure test, execute exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

  • 4. After finishing the pressure test, execute exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

  • 5. Enter the command @?/rdbms/admin/awrrpt

Note: Steps 4 and 5 are not executed, and the time node specified by the system will be generated by default (the default person generates a report once an hour)

  • 6. Enter the carriage return and the default format is html

  • 7. Enter 1 to query the most recent day

  • 8. Enter the start node and end node

  • 9. Enter the name of the report you want to generate, ending in html format

  • 10. The generated awr defaults to the path to log in to the database (test machine: C:\Users\wangxh.PANACRO)

Other commonly used commands

Query the number of connections in the current process of the database:

select count(*) from v$process;
 

View the number of connections in the current session of the database:

select count(*) from v$session;
 

View the number of concurrent connections to the database:

select count(*) from v$session where status='ACTIVE';
 

View the current session established by the database:

select sid,serial#,username,program,machine,status from v$session;
 

Query the maximum number of connections allowed in the database:

select value from v$parameter where name = 'processes';
 

or

show parameter processes;
 

Check if there is a lock table

SELECT a.username,decode(b.type,'TM','TABLE LOCK','TX','ROW LOCK',NULL) LOCK_LEVEL,
c.owner,c.object_name,c.object_type,
a.sid,a.serial#,a.terminal,a.machine,a.program,a.osuser
FROM v$session a,v$lock b,dba_objects c
WHERE b.sid = a.sid
AND b.id1 = c.object_id(+)
AND a.username is NOT Null;
 

Query table space usage

Log in with DBA authority

SELECT UPPER(F.TABLESPACE_NAME) " ",
D.TOT_GROOTTE_MB " (M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES " (M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES)/D.TOT_GROOTTE_MB * 100,2),'990.99') 
" ",
F.TOTAL_BYTES " (M)",
F.MAX_BYTES " (M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES)/(1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES)/(1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES)/(1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
 

result:

Tablespace name Table space size (M) Used space (M) Use ratio Free space (M) Largest block (M)
CSP 30000 29,947.37 99.82 52.63 9
SYSTEM 500 491.19 98.24 8.81 7.94
SYSAUX 440 424.31 96.43 15.69 7.94
USERS 5 0.44 8.80 4.56 4.56
UNDOTBS1 22415 190.19 0.85 22,224.81 3968

It is found that the table space is only 52.63 M free. It is guessed that the automatic expansion of the table space may fail (the growth of the table space is too high, ORACLE defaults to 50%),

Modify the table space file extension method:

SQL>ALTER DATABASE 
    DATAFILE '/u01/oracle/oradata/orcl/ccen01.dbf' AUTOEXTEND 
    ON NEXT 50M MAXSIZE UNLIMITED
 

summary

Oracle provides a wealth of statistical information, including statistical views such as session, sql execution, and events, through which the oracle execution status can be carried out. This article is aimed at small partners who are unable to start with oracle performance problems.