Showing posts with label Tuning. Show all posts
Showing posts with label Tuning. Show all posts
Friday, December 4, 2020

How to mark an existing SQL Plan baseline as fixed

›
Thanks a lot to Tim Hall for providing this simple example: SET SERVEROUTPUT ON DECLARE l_plans_altered PLS_INTEGER; BEGIN l_plans_...
Friday, August 9, 2019

How to find the number of executions for a specific SQL ID during a day

›
This query will gather data from DBA_HIST_ACTIVE_SESS_HISTORY for a specific day, and count the number of executions within that day: sel...
Wednesday, April 3, 2019

How to gather histograms on a column of a table

›
Use the METHOD_OPT directive in DBMS_STATS.GATHER_TABLE_STATS to collect histograms for column ARTICLE_NAME in table SALES_Q1_RESULTS. In...
Thursday, January 31, 2019

How to solve SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

›
Logged in as the user owning the objects, you are getting the following error when trying to use the autotrace feature: SP2-0618: Cannot ...
Thursday, September 20, 2018

How to load a plan from the AWR into the SMB

›
To load a plan found in an AWR snapshot into the SMB, you need to add it to an STS first, then load it into the SMB. Here is how: 1. cre...

How to drop an STS

›
exec dbms_sqltune.drop_sqlset ('mysts');

Yet another example on how to create and populate an STS

›
Here is how to create an STS and populate it with SQL statements matching a particular SQL ID: exec dbms_sqltune.create_sqlset(sqlset_name...
Monday, June 11, 2018

How to find the number of times a query has been executed

›
The following query was given to me by an experienced colleague. It shows the number of times different variations of a particular query ha...
Monday, June 4, 2018

What are 'TCP Socket (Kgas)' Waits?

›
Doc ID 416451.1 "What are 'TCP Socket (Kgas)' Waits?" from Oracle support explains: A session is waiting for an external...
Thursday, March 15, 2018

How to load a plan from the cursor cache into the SMB

›
set serveroutput on VARIABLE cnt NUMBER -- Specifying both the SQL ID and the plan hash value: EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_C...
Wednesday, January 10, 2018

What is "Automatic Reoptimization" introduced in Oracle 12cR1?

›
Some definitions here, mostly taken directly from Oracls documentation, or quoted from Tim Hall's www.oracle-base.com Automatic Reopt...
Monday, December 18, 2017

How to make use of the GATHER_PLAN_STATISTICS hint

›
Use the gather_plan_statistics hint to view the optimizer's estimation of rows vs. the actual returned number of rows. Notice the cas...
1 comment:

How to use dbms_spm.load_plans_from_sqlset

›
To load a specific SQL ID with a specific plan hash value from an STS into your SQL Plan Management Base, use: set serveroutput on declare...
1 comment:
Wednesday, November 15, 2017

How to find the number of full table scans

›
From the Oracle Documentation : The view DBA_HIST_SEG_STAT view displays historical information about segment-level statistics. This vie...
Friday, November 3, 2017

What is the difference between a bind-sensitive and bind-aware cursor?

›
The terms "bind-sensitive" and "bind-aware" are used when explaining Oracles Adaptive Cursor Sharing (ACS) feature. Ad...

Query to search for details about a specific SQL ID in the cursor cache

›
I have plenty of examples that Oracle 12cR1 selects poor execution plans over good ones. This is a recent example from one of my databases...
Wednesday, September 27, 2017

How to use the dbms_explan.display_cursor function to find the execution plan of a query in the Cursor Cache

›
set linesize 200 set pagesize 0 select * from table (dbms_xplan.display_cursor( '0syc4upspr638',3,'TYPICAL')); exit Where ...
Thursday, August 31, 2017

How to explain a SQL statement using bind variables

›
Sometimes I am asked to analyze an SQL statement picked up from Enterprise Manager Cloud Control. These SQL statements only reveal the bind...
Monday, August 21, 2017

Some important tuning parameters in Oracle 12c

›
Here are some parameters that you will have to deal with when tuning an Oracle 12c database. Execute the following SQL to view their curr...
Thursday, August 17, 2017

What does 1048575 in the Pstop column in the explain plan indicate?

›
The answer lies in the dictionary view ALL_PART_TABLES (or DBA_PART_TABLES). The column partition_count is the total number of partitions...