Skip to main content

Posts

Showing posts from May, 2018

Work with Execution Plan stability, using baselines.

Use the below queries to see the available execution plans and see which plan was running fine.   1.       @?/rdbms/admin/awrsqrpi.sql  ---> This will generate the html page for the required query based on the SQLID and its awr history. 2.       dbms_xplan.display_awr() Ex: select * from TABLE(dbms_xplan.display_awr('47qjdv3ncanhr')); 3.        USING GRID Control 12c   To gather the history of a SQL execution and the plans used during those runs, obtain the SQL Id to be evaluated, connect to the GRID Control 12c: select the Targets/Databases -->Select the database -->Performance/SQL/Search SQL--> Check AWR Snapshots -->Enter the SQL ID in the SQL ID filed / Search--> Verify the executions and the different Hash Plans used.  The ones with the Smallest Elapsed Times are the best execution Plans for the SQL. If the HASH Plan...

List All Hidden Database Parameters

List All Hidden Database Parameters   -- -- List all hidden database parameters. -- SET PAUSE ON SET PAUSE ' Press Return to Continue ' SET PAGESIZE 60 SET LINESIZE 300 COLUMN ksppinm FORMAT A50 COLUMN ksppstvl FORMAT A50 SELECT ksppinm , ksppstvl FROM x $ ksppi a , x $ ksppsv b WHERE a . indx = b . indx AND substr ( ksppinm , 1 , 1 ) = ' _ ' ORDER BY ksppinm /