How to run SQL tuning advisor manually in Oracle Database
Step 1: Get the hash value of the SQL using the SQL ID. Enter the SQL ID select a.plan_hash_value from v$sql a where sql_id=’&a’; Step 2: Create a tuning task using SQL ID [...]
SQLTXTRACT and SQLTEXECUTE -Revealing Confusion
SQLT XTRACT is the easiest report to create because it does not require the execution of the SQL at the time of the report generation. The report can be collected after the statement has been [...]
Query to generate AWR report
set lines 500; set pages 500; set long 1000000; SELECT X.SQL_ID, X.CPU_TIME, X.EXECUTIONS, T.SQL_TEXT FROM DBA_HIST_SQLTEXT T, ( SELECT S.SQL_ID SQL_ID, SUM(S.CPU_TIME_DELTA/1000000) CPU_TIME, [...]
Script to generate statspack report
set lines 300; set pages 500; set long 1000000; select A.hash_value, A.text_subset, A.module, trunc((B.cpu_time-A.cpu_time)/1000) “CPU_TIME(ms)”, B.executions-A.executions executions, [...]
SGA TARGET ADVISORY IN ORACLE
The V$SGA_TARGET_ADVICE view provides information that helps us in deciding optimal value for SGA_TARGET. MMON background process gather statistics about sga_target usage and update the [...]
Scripts to identify performance related issues.
one script to Identify all performace issue related to sql query Set echo off set trimspool on set define on column filename new_val filename select to_char(sysdate, [...]
Tips to import the export and import datapump performance
1) Use parallel 2)While mentioning the dump file, use _%U.dmp (generates multiple dumps), which will be faster. 3)Increase the stream pool size alter system set streams_pool_size=2G; alter system [...]
Query to get SQL changed Performance
SPO sql_performance_changed.txt; DEF days_of_history_accessed = ’31’; DEF captured_at_least_x_times = ‘1’; DEF captured_at_least_x_days_apart = ‘1’; DEF [...]