What are the commonly used Oracle statements for performance testing?

Oracle's performance test mainly simulates a large number of SQL statement operations to pressurize the database server. Before testing, you need to prepare the following SQL statements to be simulated, test scripts, and prepare the test control machine, test pressure machine, and the database server under test.

Oracle database performance advantages and disadvantages
Advantages
1. Can run on all mainstream platforms (including windows). Fully supports all industry standards. Adopt a completely open strategy. This enables customers to choose the most suitable solution. Full support to developers.
2. Parallel servers extend the capabilities of windownt by enabling a group of nodes to share work in the same cluster, providing high availability and high scalability cluster solutions.
3. If windowsNT cannot meet the needs, users can move the database to UNIX.
4. In terms of security, the performance is the highest.
5. Client support and application mode , multi-level network computing, supports multiple industrial standards, can use ODBC, JDBC, OCI and other network customer connection requirements, and can construct a database according to actual system requirements.
6. Use standard SQL structured query language.
7. It has a wealth of development tools, covering all stages of the development cycle.
8. Support large databases. The data type supports numbers, characters, and binary data up to 2GB, providing data support for object-oriented storage of databases.
9. Development tools with fourth-generation languages ​​(SQL*FORMS, SQL*REPORTS, SQL*MENU, etc.).
10. It has character interface and graphical interface and is easy to develop.
11. Control user permissions through SQL*DBA, provide data protection functions, monitor the running status of the database, and adjust the size of the data buffer.
12. Distribution optimization query function.
13. It has data transparency and network transparency, and supports heterogeneous networks and heterogeneous database systems. Parallel processing uses dynamic data sharding technology.
14. Support client/server architecture and mixed architecture (centralized, distributed, client/server).
15. Two-stage submission and multi-clue query methods are implemented.
16. Data security protection measures: There is no read lock, and the snapshot SNAP method is used to completely eliminate distributed read and write conflicts. Deadlocks and conflicts are automatically detected and resolved.
17. The data security level is C2 (the highest level).

18. The database internal model supports multi-byte encoding and supports multiple language text encodings.
19. Have management information system and financial system application system for manufacturing systems.
20. WORKGROUP/2000 has ORACLE7WORKGROUP server, POWER OBJECTS (graphical development environment, supports OS/2, UNIX, WINDOWS/NT platforms.
21. Sales share in China accounts for more than 50%.
Disadvantages
1. Management and maintenance are more troublesome;
2. Replying after the database crashes is very troublesome because it puts a lot of things in the memory;
3. The database connection is slower. Easy to use connection pool;
4. Large objects are difficult to use, the vchar2 field is too short and not enough;
5. The administrator’s work is annoying, and experience is very important;
6. Hardware requirements are very high High;

Commonly used Oracle statements for performance testing

Display the current number of connections to the database:

selectcount(*) from v $process;

Display the maximum number of database connections:

selectvalue from v$parameter where name ='processes'

Modify the maximum Oracle Maximum number of connections:

alter systemset processes = 300 scope = spfile;

Display the current number of session connections:

selectcount(* ) fromv$session

Check which users are currently using data:

SELECT osuser,a.username,cpu_time/executions/1000000||'s', sql_fulltext ,machine fromv$session a, v$sqlarea b where a.sql_address =b.address order by cpu_time/executionsdesc;

View SGA in the database:

System globalarea (SGA),system global area(PGA);

View the number of connections of all machines connected to Oracle:

selectmachine,count(*) fromv$session groupbymachine;

View the number and status of connections to all machines connected to Oracle:

selectmachine,status,count(*) fromv$session groupbymachine,status orderbystatus;

Oracle 11g sets automatic memory management:

View the Top 5 SQLs that consume the most disk reads:

selectdisk_reads,sql_text,SQL_FULLTEXT

from (selectsql_text,disk_reads,SQL_FULLTEXT,

dense_rank() over

(order by disk_reads desc) disk_reads_rank

from v$sql)

wheredisk_reads_rank

The above is the detailed content of What are the commonly used Oracle statements for performance testing?. For more information, please follow other related articles on the PHP Chinese website!