Oracle uses AWR's SQL ordered by Gets and SQL ordered by Reads to diagnose problems

Oracle uses AWR's SQL ordered by Gets and SQL ordered by Reads to diagnose problems

I have read many database reports before, and have encountered two serious performance problems recently. They are very representative, so I recorded them.

Once, the Oracle process is reported to get the CPU of the database server to 100%.

SQL ordered by Gets is used to retrieve data in the memory. The unit is time and is the main source of CPU consumption. It is used to measure performance in most cases when debugging SQL statements. The actual AWR report is as shocking as follows:

 

SQL ordered by Gets

• Resources reported for PL/SQL code when des the resources used by all SQL statements called by the code.
• Total Buffer Gets :###############
• Captured SQL account for 5.0% of Total

  Buffer Gets	Executions	Gets per Exec	% Total	CPU Time (s)	Elapsed Time (s)	SQL Id	SQL Module	SQL Text
  1,606,380,390	84	19,123,576.07	-0.84	37923.62	207407.54	78tpfukr4m00p	JDBC Thin Client	Select sum (temp. MidUser) total...
  1,479,957,445	0		-0.77	26647.48	43778.62	2mxgaus0t6pxz	JDBC Thin Client	SELECT device id sbid, device id sbbm, set...
  1,280,365,092	73	17,539,247.84	-0.67	30263.02	159789.71	2q1_96dwj2xx1	JDBC Thin Client	Select sum (temp. MidUser) total...
  1,163,229,240	518	2,245,616.29	-0.61	11775.72	30158.95	Cq0adyp396538	JDBC Thin Client	Select count (*) FROM DM_FL_OBJ...
  465,618,308	261	1,783,978.19	-0.24	11004.49	27015.23	Acqqmk9b7vc4a	JDBC Thin Client	Select count (*) FROM DM_FL_OBJ...
  448,728,301	21	21,368,014.33	-0.23	10698.43	43934.26	24gvfb62gt1yd	JDBC Thin Client	Select sum (temp. MidUser) total...
  386,047,329	24	16,085,305.38	-0.20	9286.27	45034.84	Fx8tp01tpwgkq	JDBC Thin Client	Select sum (temp. MidUser) total...
  294,571,906	13	22,659,377.38	-0.15	7002.73	26477.59	383ut78zg00hq	JDBC Thin Client	Select sum (temp. MidUser) total...
  274,341,527	14	19,595,823.36	-0.14	6415.83	26625.21	Cqhbuuz14x7gh	JDBC Thin Client	Select sum (temp. MidUser) total...
  176,633,305	10	17,663,330.50	-0.09	4160.82	26360.28	9xc9701y82st9	JDBC Thin Client	Select sum (temp. MidUser) total...

   

  Solution: optimize SQL

  At one time, the I/O load of the database server has reached the limit.

  SQL ordered by Reads retrieves data from the disk. The unit is the same. If it is too large, IO will slow the whole database, which is the RAC environment and may cause the database to restart.

  In the top 5 wait events of the database, we can see that the direct path read is very large.
  In Oracle 11g, The _ small_table_threshold parameter is defined as a large table. When the table size is smaller than this value, the table is cached. Its value is dynamically determined when the instance is started. Generally, it is 2% * DB_CACHE_SIZE, and the Unit is block. So it can be fixed according to the system situation.

  Solution: optimize SQL, increase SGA, and increase _ small_table_threshold