Common SQL statements for oracle maintenance, monitoring, and optimization

Oracle maintenance, monitoring, and optimization of common SQL 1) SQL monitoring www.2cto.com SELECT SE. sid, schemaname, SE. serial #, PR. spid, SE. status, SUBSTR (SE. program, 1, 10) PROG, SUBSTR (SE. machine, 1, 10) MACH, SQ. SQL _text FROM v $ session SE, v $ sqlarea SQ, v $ process PR WHERE SE. paddr = PR. ADDR (+) and se. SQL _address = SQ. address (+) AND schemaname = 'King' order by se. sid --- select * from table (dbms_xplan.display_cursor ('ddzktdys2nz3w ') select count (*) from v $ session --- view SQL select se in each status. status, sq. SQL _text from v $ session se, v $ sqlarea sq where se. SQL _address = sq. address (+) -- select. username,. sid, B. SQL _TEXT, B. SQL _FULLTEXT from v $ session a, v $ sqlarea B where. SQL _address = B. address --- executed select B. SQL _TEXT, B. FIRST_LOAD_TIME, B. SQL _FULLTEXT from v $ sqlarea B where B. FIRST_LOAD_TIME between '2017-03-2013: 30: 47 'and '2017-03-01/13: 50: 47' order by B. FIRST_LOAD_TIME -- (This method allows you to view the SQL statements executed in a certain period of time, and SQL _FULLTEXT contains the complete SQL statement) -- other select OSUSER, PROGRAM, USERNAME, SCHEMANAME, B. cpu_Time, STATUS, B. SQL _TEXT from V $ session a left join v $ SQL B ON. SQL _ADDRESS = B. address and. SQL _HASH_VALUE = B. HASH_VALUE order by B. cpu_time desc select address, SQL _text, piece from v $ session, v $ sqltext where address = SQL _address -- and machine = <you machine name> order by address, piece --- Search for the top 10 SQL statements with poor performance. SELECT * FROM (select PARSING_USER_ID, EXECUTIONS, SORTS, COMMAND_TYPE, DISK_READS, SQL _text FROM v $ sqlarea order BY disk_reads DESC) where ROWNUM <10; --- view the running session SELECT se that occupies a large io. sid, se. serial #, pr. SPID, se. username, se. status, se. terminal, se. program, se. MODULE, se. SQL _address, st. event, st. p1text, si. physical_reads, si. block_changes FROM v $ session se, v $ session_wait st, v $ sess_io si, v $ process pr WHERE st. sid = se. sid AND st. sid = si. sid AND se. PADDR = pr. addr and se. sid> 6 AND st. wait_time = 0 AND st. event not like '% SQL %' ORDER BY physical_reads DESC -- alter system set processes = 400 scope = SPFILE show processes PARAMETERS ---- view instance name select instance_name from V $ instance 2) view select B. file_name physical file name, B. tablespace_name tablespace, B. bytes/1024/1024 size M, (B. bytes-sum (nvl (. bytes, 0)/1024/1024 used M, substr (B. bytes-sum (nvl (. bytes, 0)/(B. bytes) * 100,1, 5) utilization from dba_free_space a, dba_data_files B where. file_id = B. file_id group by B. tablespace_name, B. file_name, B. bytes order by B. tablespace_name 3) view and delete indexes --- query the INDEX of a table select * from user_indexes where TABLE_Name = 'case _ ACLINE_TEMP 'select * from user_indexes where TABLE_Name = 'case _ ACLINE_PARTITION' --- delete an INDEX DROP INDEX CASE_ACLINE_PARTITION_IDX drop index FK_CASE_ACLINE_TEM_CASE_ID -- select. segment_name,. tablespace_name, B. table_name,. bytes/1024/1024 mbytes,. blocks from user_segments a, user_indexes B where. segment_name = B. index_name and. segment_NAME = 'case _ acline_partition_idx' -- index and. tablespace_name = 'test _ TEMP '-- tablespace and B. table_name = 'case _ ACLINE_PARTITION '-- order by table_name,. bytes/1024/1024 desc --- view the Partition Table index SELECT * FROM USER_IND_PARTITIONS t where t. INDEX_NAME = 'case _ ACLINE_PARTITION_IDX '4) view and modify the number of connections -- select count (*) from v $ session -- Select count (*) of concurrent connections (*) from v $ session where status = 'active' --- maximum connection show parameter processes --- modify the number of connections and restart the system set processes = 400 scope = spfile;