Oracle via AWR SQL ordered by gets and SQL ordered by Reads

I have seen a lot of database reports before, and until recently there have been two serious performance problems, very representative, so recorded.

One is to report the Oracle process to the CPU of the database server 100%

SQL ordered by Gets is the data in memory, unit of time, is the main source of CPU consumption, when debugging SQL, most of the time it is to measure performance. Here is the actual AWR report, pretty scary:

SQL ordered by Gets

• Resources reported for PL/SQL code includes the resources used by the "all" 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	2qrj96dwj2xx1	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	21st	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 ...

Once the database server IO load has reached its limit

SQL ordered by Reads go to disk to fetch data, in units of times, if too large, IO will cause the entire database to be slow and is a RAC environment that may cause the database to restart.

Oracle via AWR SQL ordered by gets and SQL ordered by Reads