SQL statements for querying and locating database problems in Oracle

--1) SQL statement for querying and locating database problems
--oracle Common Performance Monitoring SQL statements. sql

--1 Query Lock table information

Select VP. SPID,
Vs. P1,
Vs. P1raw,
Vs. P2,
Vs. EVENT,
Vsql. Sql_text,
Vsql. Sql_fulltext,
Vsql. sql_id
From V$session vs, V$sql Vsql, v$process VP
Where vs. sql_id = Vsql. sql_id
and vs. PADDR = VP. ADDR
and vs. Wait_class <> ' Idle '
Order by vs. EVENT


Select Vp.spid,
LO.SESSION_ID,
Lo.oracle_username,
Lo.os_user_name,
Ao.object_name
From V$process VP, v$session vs, V$locked_object Lo, all_objects ao
where vp.addr = Vs.paddr
and vs.process = lo.process
and lo.object_id = ao.object_id

--2 Unlock locked Table (sid,serial#)
Alter system kill session ' 324,50855 ';

--3 Current Active Session History table
SELECT *
From Dba_hist_active_sess_history
where session_id = 324--sid
ORDER BY sample_time Desc;

--2) Index Optimization

--turn on the monitor session statement
Alter session Set Sql_trace = true;
Dbms_system.set_sql_trace_in_session (SID, Serial#, True);
Dbms_session.set_sql_trace (TRUE);

--Index Analysis
Analyze index act_idx_task_procinst validate structure;

--Index usage analysis lfpercent>20 need to rebuild index
Select (Idx.del_lf_rows_len/idx.lf_rows_len) * lfpercent
from Index_stats idx;

--Index reconstruction
Alter index ACT_IDX_TASK_PROCINST rebuild;

---act_hi_taskinst (TRUNC (start_time_))

--Get sql_id
Select vs.sql_id, Vs.sql_fulltext, Vs.runtime_mem
From V$sqlarea vs
where Vs.sql_fulltext like '%papc_bdqd_orders% '
ORDER BY vs.last_load_time Desc;

--To get the execution plan of SQL statements according to SQL_ID
Select Plan_table_output
From table (Dbms_xplan.display_cursor (' 0sb2f19wmm82u ', ' 0 ', ' all '));

--3) tables to be followed for analysis of SQL performance issues
--depending on the criteria field in the SQL statement, determine how many records are returned in each condition,
--When making a table connection, you should follow the principle of using a table with a small number of records (or intermediate result sets) as the driving table.

--Query whether the table information has been collected statistical information
--Table information
Select Dt.owner, Dt.table_name, dt.last_analyzed, Dt.num_rows, dt.blocks
From Dba_tables DT
where Dt.owner = ' papcdata '
and dt.table_name = ' papc_bse_city ';

--Query Index information
-Best Case Clustering_factor = total number of data blocks in the table BLOCKS
--index information of the table
Select Di.index_name,
Di.index_type,
Di.blevel,
Di.leaf_blocks,
Di.last_analyzed,
Di.distinct_keys,
Di.clustering_factor
From Dba_indexes di
where Di.owner = ' papcdata '
and di.table_name = ' papc_bse_city ';

--index information is used on columns of the query table
Select Dic.index_name, Dic.column_name, dic.column_position
From Dba_ind_columns DiC
where Dic.table_owner = ' papcdata '
and dic.table_name = ' papc_bse_city ';

--Information about using related columns in the query table 1
Select Dtcs.column_name,
Dtcs.num_distinct,
Dtcs.num_nulls,
Dtcs.density,
Dtcs.low_value,
Dtcs.high_value,
Dtcs.last_analyzed,
Dtcs.histogram
From Dba_tab_col_statistics DTCs
where Dtcs.owner = ' papcdata '
and dtcs.table_name = ' papc_bse_city ';

--Information about using related columns in the query table 2
Select Dtc.column_name,
Dtc.num_distinct,
Dtc.num_nulls,
Dtc.density,
Dtc.low_value,
Dtc.high_value,
Dtc.histogram,
Dtc.last_analyzed
From Dba_tab_columns DTC
where Dtc.owner = ' papcdata '
and dtc.table_name = ' papc_bse_city ';

/*+ INDEX (Image_for_policy, Ix_image_for_policy_code) */
EXEC dbms_stats. Gather_table_stats (ownname = ' Lifedata ',
tabname = ' Image_for_policy ',
estimate_percent = 100,
method_opt = ' for COLUMNS document_code SIZE 1 ');

Select sql_id,
Round (cpu_time/executions/1000000, 2) cpu_time_s,
Round (elapsed_time/executions/1000000, 2) elapsed_time_s,
Round (buffer_gets/executions, 2) buffer_gets,
Executions,
PARSING_USER_ID,
Round (rows_processed/executions, 2) rows_processed
From V$sql
where sql_id in (' C8t01f69awtw9 ', ' F0KR8H3DQ7DXM ');

--data buffer hit ratio buffer hit% < 95% db_cache_size need to be adjusted, appear db file sequential read memory sort ratio in-memory sort% <> 100% need to adjust PGA The size
---hit ratio of SQL resolution in shared pool Library hit% < 95% increase shared pool, modify cursor_shring with binding variables Soft parse% < 95% need to use bound variables < 80%sql do not reuse Number of times as a percentage of the number of analysis the Execute to Parse% value is small, indicating that the soft and hard parsing ratio is too large, fast soft resolution ratio is small

SQL statements for querying and locating database problems in Oracle