Subscriber Count

    459

Subscribe to Posts by Email

Pages

SQL Tuning: Handling nulls in indexes and get them used

Hello,

Thanks to my colleague, I have just buyed the following from my Friend Naga Satish from his email and sharing here.

"Edited Explain plan" —————————————————————————————— | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | —————————————————————————————— | 0 | SELECT STATEMENT | | 3626K| 390M| | 1217K (1)| 04:03:28 […]

SQL Plan Management:- How many days the sql plan baselines or management data can be retained?

If you want to understand more about SQL Plan Management, read this post before reading further,

https://blogs.oracle.com/optimizer/entry/sql_plan_management_part_1_of_4_creating_sql_plan_baselines

Debate- SPA vs. SPM vs. ACS, SPM vs. sql_profiles etc

As you see in the figure, Each and every sql baselines has multiple plans associated with it and stored in sql plan management base. Some of the statements […]

Oracle Execution Plan: Common Join Methods explained

In Oracle execution plan, you often see Nested loops/Hash Joins/Merge Joins etc. These are called Join methods between row sources (tables/result sets of query).

You are can find more about Rowsource Join Methods here in the documentation

There are many join methods, out of all the following were common and generic

Nested loop join   […]

Like operator and index usages

Typically we would have the following patterns when we use like operator.

SEARCH-STRING% %SEARCH-STRING %SEARCH-STRING% SEARCH%STRING

1. a)The SEARCH-STRING% will perform INDEX RANGE SCAN data in least possible time.  

    b)Also uses INDEX FULL SCAN when you have only indexed column list in the queries.

    c) If multiple predicates used, this may loose […]

Finding SQL Bind variables and its literal values

Hi,

In 10g, you can use data dictionary view v$sql_bind_capture or AWR view dba_hist_sqlbind (historical version of v$sql_bind_capture) to find bind variables’ values. However it has some significant limitations:

Captured periodically (_cursor_bind_capture_interval=900 seconds by default), not at real time. Captured under maximum size(_cursor_bind_capture_area_size=400) Only bind variables in WHERE clause are captured (e.g bind variables passed […]

Why my SQL Profile has not been picked up?

Somtimes you might be into a situation, that you have created an sql profile and unfortunately that has not been picked up?

Well as per my reads and understanding and limited knowledge, these may be the causes.

1) Signature mismatch – SQL statements as converted to signature (with possible extra space truncation, replace literals etc)

[…]

Performance Tuning: Oracle SPM Vs. Sql Profiles

The following is the real nice work from

http://intermediatesql.com/oracle/what-is-the-difference-between-sql-profile-and-spm-baseline/

This was very good post and awesome write up which help my blog readers too. End to end on sqlprofiles and spm, happy reading, thanks to Maxym.

 

SQL Profiles

Note —– – SQL profile "SYS_SQLPROF_012ad8267d9c0000" used FOR this statement

 

and

[…]

Oracle Cursor Sharing Examples

In the previous post we discussed about the Parent & Child Cursors in Oracle and how do they work or shared.

To get into deeper, Oracle controls the sharing of cursors with parameter called cursor_sharing. It has three values

EXACT SIMILAR FORCE

In this post we will create a table which has some records with […]

Shared Pool: Parent & Child Cursors in Shared Pool

What are PARENT AND CHILD CURSORS IN ORACLE

A cursor is a memory area in library cache allocated to a SQL statement which stores various info about the SQL statement like its text, execution plan, statistics etc.

Each SQL statement has

One Parent cursor One or more child cursors

PARENT CURSOR

It […]

Explain Plan: Different formats/options with dbms_xplan

The DBMS_XPLAN package provides an easy way to display the output of the EXPLAIN PLAN command in several, predefined formats. You can also use the DBMS_XPLAN package to display the plan of a statement stored in the Automatic Workload Repository (AWR) or stored in a SQL tuning set. It further provides a way to display […]