As we know that DBMS_STATS takes a backup of the statistics every time dbms_stats is run on the table. So we can restore the statistics from the backup. In this article, we will see, How to restore optimizer statistics in Oracle, How to check the retention of the statistics How far back in time we […]
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 […]
Gather Schema Statistics Using FND_STATS in EBS 11i and R12
How to Gather schema statistics in oracle apps R12 In order to use Cost-Based Optimizer effectively, the schema statistics must be current. Fortunately, Oracle EBS has provided some scripts and concurrent programs(Gather Schema Statistics and Gather Table Statistics) to make this administrative task easier. FND_STATS is a PL/SQL package for the Applications that has numerous […]
Histograms in EBS
Histograms in Oracle are required for skewed data, so it is required in the Oracle EBS database also. Histograms tell the CBO when data is not evenly distributed and can help the CBO to estimate the number of rows returned from a table join (called “cardinality”). Having histograms on the skewed columns may aid the […]
How to delete the archive logs in Oracle
Archive logs keep accumulating and we need to put things in place to delete them on a regular basis. We should not be deleting the archivelog from the filesystem directly as the Oracle Database is not aware of that and space is not released in the Flash recovery area Let’s see a few commands on […]
SQL Tuning Set in Oracle
A SQL Tuning Set (STS) in Oracle is a database object that includes one or more SQL statements along with their execution statistics and context. It provides a means to store SQL statements for future use, such as input to the SQL Tuning Advisor or SQL Access Advisor, capturing workloads to move them from one […]