Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Manjunath.

Asked: June 19, 2017 - 12:56 pm UTC

Last updated: August 22, 2019 - 2:29 pm UTC

Version: 11.2.0.1

Viewed 10K+ times! This question is

You Asked

Hello Tom & Team,

Can you please provide to get the list of sql ids along with elasped times and other metrics for a given sql statment. Once obtained, can i force oracle to use a particular sql id which was performing better ?

Regards,
Manjunath

and Chris said...

You can find the SQL ID for a statement by querying v$sql:

select /* this */* from dual;
select /* that */* from dual;

select sql_id, sql_text from v$sql
where  ( sql_text like '%this%' or sql_text like '%that%' )
and    sql_text not like '%not this%';

SQL_ID        SQL_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
------------- ----------------------------
gqrwquv0utrb7 select /* this */* from dual                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
cd8h6juhw5wkk select /* that */* from dual


This view also contains some metrics such as elapsed time, cpu time, buffer gets, etc. There are several other views that contain further performance stats such as v$sqlstats.

The SQL ID is a hash of the statement's text. So there will only ever be one ID for a given statement. But one statement can have two or more different plans. These will have different plan_hash_values. You can encourage/force Oracle Database to use a particular plan with SQL Plan Management (SPM). This uses features such as SQL profiles and baselines to do this.

Read more about this at:

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-management-11gr2-133099.pdf
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-12c-1963237.pdf

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Shweta, August 22, 2019 - 10:26 am UTC

Hi, is it possible that exactly same SQL ID gets generated in the other environment too on running the same statement ?
Chris Saxon
August 22, 2019 - 2:29 pm UTC

Yes. A SQL statement should get the same SQL ID for in every Oracle Database of the same version you run it on.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.