Outline hints of the sql statement are the hints which are required to reproduce the same plan.This can be useful in many ways(a) You can enforce the same plan on the same query with different literal(b) we could use them between two slightly different queries also(c) These are the same hints used in the custom […]
How to implement custom Sql Profile in Oracle
We all know about SQL profiles in Oracle and How they add hints and scaling factors that help in the Performance of the sql in Oracle. We have two types of sql profile This is the standard one that we get from sql tuning advisor. It tunes the sql statement using hint and scaling factors. […]
how to find if the sql is using the sql profile
When we implement the sql profile for the statement, we will also like to confirm if the sql has started using the sql profile or not. we can use the below query to find out if the sql is using the sql profile how to find if the sql is using the sql profile col […]
How to verify stats for the table in EBS
Oracle has provided a procedure VERIFY_STATS in the FND_STATS package to verify stats for the table in EBS This procedure reports the statistics in the data dictionary tables for the tables, indexes, and histograms. Syntax FND_STATS.VERIFY_STATS ( schemaname VARCHAR2 DEFAULT NULL, tablelist VARCHAR2 DEFAULT NULL, days_old NUMBER DEFAULT NULL, column_stat BOOLEAN DEFAULT FALSE); To verify […]
STATISTICS_LEVEL in Oracle
The STATISTICS_LEVEL parameter was introduced in Oracle version 9 (9.2) to control allmajor statistics collections or advisories in the database. The level of the setting affects a number of statistics and advisories that are enabled. These listed below are for 10g and above The TYPICAL level is recommended for most production databases to enable the […]
Move SQL Profiles from One Database to Another in Oracle
In this post, we will check out how to move SQL Profiles from One Database to Another How to move the sql profiles in Oracle Suppose you have created a sql profile in System A and Now you want to transfer that sql profile to System B. Step 1On System A: Creating a staging table […]