Summary General SQL optimization steps

1. Use top SQL to find the problematic SQL statement select * from (select * from v $ sqlstats order by DISK_READS desc) where rownum <= 10; you can also use ELAPSED_TIME, AVG_HARD_PARSE_TIME and other sorting 2. Specify a jdbc connection to find out the problematic SQL statement. We connect jdbc to the database. In general, it is a web interface connection query. Select sid, serial #, username, SQL _id, prev_ SQL _id, logon_time from v $ session www.2cto.com where program = 'jdbc Thin client' and username = upper ('jscnbi ') order by logon_time desc; in general, if the connection pool is configured, the connection time should be the same. In this case, we can use SQL _id and v $ SQL to determine the current problematic SQL, in this case, it is best to add a SQL _id is not null. Select. SQL _text,. SQL _fulltext, B. sid, B. event from v $ SQL a, v $ session B where. SQL _id = B. SQL _id and B. program = 'jdbc Thin client' and B. SQL _id is not null order by B. logon_time desc; after finding out the specific SQL, we can view the SQL Execution Plan. 3. When viewing the execution plan, we need to scan some tables in the full table, scan tables with extremely large data volumes, and check whether there is a storage problem with statistics. 1) Check whether to create an index select * from all_ind_columns where table_name = upper ('table name'); 2) view the statistical information in the table www.2cto.com select OWNER, TABLE_NAME, NUM_ROWS, to_char (LAST_ANALYZED, 'yyyy-mm-dd hh24: mi: ss') from all_tables where table_name = upper ('product'); Pay special attention to the NUM_ROWS and LAST_ANALYZED fields. If LAST_ANALYZED is too old, it may also cause performance problems. If it is too old, I want to collect statistics for BEGIN SYS. DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'jsonbi', TabName => 'product', Estimate_Percent => 0, Method_Opt => 'for all indexed columns size 1', Degree => 4, cascade => TRUE, No_Invalidate => FALSE); END;/or use this method to collect Analyze Table JSCNBI. PRODUCT Estimate Statistics Sample 33 Percent; 4. View SQL Execution Plan 4.1 view SQL Execution Plan Information in sga select * from table (dbms_xplan.display_cursor (SQL _id); e G: select * from table (partition ('cbj0d7thtn00q '); 4.2 perform 10046 events and SQL _trace 1) identify this session www.2cto.com SQL> alter session set tracefile_identifier = 'jscntest'; 2) SQL tracle SQL> alter session set SQL _trace = true; SQL> run SQL> alter session set SQL _trace = false; enter the/udump directory and format the trc file tkprof jscn_ora_20448_jscntest.trc jscntest.txt sys = no 3) 10046 Level 1: equivalent to SQL _TRACE function Level 4: at Level Level 8: Level 1, Level 12: Level 4 + Level 8, that is, the Bind Variable information and wait event information are collected simultaneously. SQL> alter session set events '2017 trace name context forever, level 4'; SQL> Execute SQL SQL> alter session set events '2017 trace name context off ';