Latest Post

How do you check the User failed login attempts without auditing theuser?

Written By Unknown on Tuesday, December 29, 2009 | 4:36 PM

How do you check the User failed login attempts without auditing the user?


A user created with default profile and the profile is modified to have the resource name FAILED_LOGIN_ATTEMPTS set to 10. Now the user account will lock when the attempts to connect to the user is more than 10.

How do we verify that there are so many number of failed login attempts earlier.
Is auditing the user the only solution ?
No

You can find the FAILED_LOGIN_ATTEMPTS value from the lcount column of user$ table.
SQL> select * from dba_profiles where resource_name = 'FAILED_LOGIN_ATTEMPTS' and limit=10;

PROFILE                        RESOURCE_NAME                    RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD
10

First i queried the failed login attemps and it shows the value "0".

SQL> select USER#,NAME,LCOUNT from user$ where NAME='RAC_ACCNT';

USER# NAME                               LCOUNT
---------- ------------------------------ ----------
303 RAC_ACCNT                               0

Then i tried with one failed login attempt from another session and the value increased to 1.

SQL> select USER#,NAME,LCOUNT from user$ where NAME='RAC_ACCNT';

USER# NAME                               LCOUNT
---------- ------------------------------ ----------
303 RAC_ACCNT                               1

Again tried with  failed login attempt from another session and the value increased to 2.

SQL> select USER#,NAME,LCOUNT from user$ where NAME='RAC_ACCNT';

USER# NAME                               LCOUNT
---------- ------------------------------ ----------
303 RAC_ACCNT                               2

Now i tried to connect to the user successfully without fail and the failed login attempt parameter is set to "0" agian.

SQL> select USER#,NAME,LCOUNT from user$ where NAME='RAC_ACCNT';

USER# NAME                               LCOUNT
---------- ------------------------------ ----------
303 RAC_ACCNT                               0


Conclusions :
============
In order to check if the FAILED_LOGIN_ATTEMPTS is working or not , we need to try giving wrong passwords 10 times continuously without any successful attempts. Then 11th attempt will lock the user account. But if there is atleast one single successful attempt to connect to the user , the value resets to "0" again.

Purging LOBs

Written By Unknown on Friday, November 27, 2009 | 6:54 PM

Purging LOBs:


If we purge a table which contains LOBs , we may not reclaim space. BLOBs are stored out of line once they exceed 4,000 bytes (you can also specify that they are always stored out of line as well).

If the BLOBs were all inline, each taking 4000 or less bytes, then you did free space in your table.  The table will not shrink in size (tables NEVER shrink) but it will have more blocks on its free list for subsequent inserts.


SQL> desc applsys.fnd_lobs
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
FILE_ID                                   NOT NULL NUMBER
FILE_NAME                                          VARCHAR2(256)
FILE_CONTENT_TYPE                         NOT NULL VARCHAR2(256)
FILE_DATA                                          BLOB
UPLOAD_DATE                                        DATE
EXPIRATION_DATE                                    DATE
PROGRAM_NAME                                       VARCHAR2(32)
PROGRAM_TAG                                        VARCHAR2(32)
LANGUAGE                                           VARCHAR2(4)
ORACLE_CHARSET                                     VARCHAR2(30)
FILE_FORMAT                               NOT NULL VARCHAR2(10)



SQL> select round(sum(bytes)/1024/1024) Mb from dba_segments where segment_name='FND_LOBS';

MB
----------
179

SQL> select round(sum(bytes)/1024/1024) Mb from dba_segments where segment_name='SYS_LOB0000034032C00004$$';

MB
----------
20699

SQL> select OBJECT_NAME,OBJECT_ID,OWNER,OBJECT_TYPE,STATUS from dba_objects where object_name='SYS_LOB0000034032C00004$$' and object_type='LOB';

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_ID OWNER                          OBJECT_TYPE         STATUS
---------- ------------------------------ ------------------- -------
SYS_LOB0000034032C00004$$
34033 APPLSYS                        LOB                 VALID

SQL> select OWNER,TABLE_NAME,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME from dba_lobs where SEGMENT_NAME='SYS_LOB0000034032C00004$$';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------
APPLSYS                        FND_LOBS
FILE_DATA
SYS_LOB0000034032C00004$$      APPS_TS_MEDIA


Action Plan
===========

1) Take hotbackup for the instance
2) Execute the following commands as sysdba



SQL> delete from APPLSYS.FND_LOBS
SQL> alter table applsys.FND_LOBS modify lob (FILE_DATA) (shrink space);

3) Then Execute the following commands to confirm the space gained.

SQL> select round(sum(bytes)/1024/1024) Mb from dba_segments where segment_name='FND_LOBS';
SQL> select round(sum(bytes)/1024/1024) Mb from dba_segments where segment_name='SYS_LOB0000034032C00004$$';

NOTE: It should return "0" for both the commands.

Or

Update the LOB column value with NULL and then execute the following command.

SQL> alter table applsys.FND_LOBS modify lob (FILE_DATA) (shrink space);

OR

Use the DBMS_LOB.ERASE   package and erase the LOBs. Then execute the following command ..

SQL> alter table applsys.FND_LOBS modify lob (FILE_DATA) (shrink space);

In all the case it is compulsory to use the " alter table .." command  claim the space.

R12 Patching Enhancement

Written By Unknown on Thursday, October 15, 2009 | 8:10 PM

R12 Patching Enhancement:


Patching R12 Application Oracle Homes

  1. Opatch is used to apply patches to the 10.1.3 and 10.1.2 Oracle Homes.

  2. Requires access to the Oracle Homes inventory

  3. List of patches already applied through  Ex : Opatch lsinventory - detail


Application Patching Enhancements

  1. Codelines(12.0:A, 12.1:B) and Codelevels(R12.AD.A.1, R12.AD.A.2)

  2. Patch prereq checking: only require a codelevel as a prerequisite

  3. Registering flagged files to understand a patch's impact on customizations



  • Replaces the applcust.txt file in 11i

  • Customized files are now maintained in database format

  • Display flagged files affected by a patch through impact analysis


Patch Application Assistant (admsi.pl)

  1. PAA (Patch Application Assistant) is tool/Perl script to generate customized installation instructions for a patch in Oracle Applications R12 which helps user to track and perform manual steps during patching.

  2. cd $AD_TOP/bin ; perl admsi.pl -patch_top=<patch-top-directory> -appspass=<apps-password>


Patch Wizard functionality improvement

Easy to identify applied patch, file history and patch timing and action summary
Screen Shots :

Code Level and CodeLine :

code level

Codeline and codelevel


Patch Wizard :

patch wizard

Applied Patches :

Applied Patches

Register Flagged Patches :

Register Flagged Files

Patch Impact Analysis Summary :

Patch Impact Analysis Summary

R12 - Codelines , Codelevels and Types of Patches

R12 - Codelines , Codelevels and Types of Patches:


Types of Application Patches :

There are several different types of Oracle Applications patches. These are the more common patches:
One-off patch: This is the simplest type of patch. It is created to resolve a specific bug.
Minipack patch: This is a collection of one-off patches and enhancements related to a particular module. Alphabetic characters denote the Minipack version for the module; for example, the product code for the Application DBA utilities is AD, and version Minipack I of this product would be called AD.I.
Family Pack patch: This is a collection of Minipack patches for a particular family group of application modules. Alphabetic characters denote the Family Pack version; for example, the J version of the Human Resources Suite Product Family would be HR_PF.J.
Maintenance Pack patch: This is a collection of Family Packs that serves as a point-level release upgrade; Oracle Applications Release 11.5.10 is an example of a Maintenance Pack.

There are also other special types of patches:
Consolidated patch: This is a collection of one-off fixes for a Family Pack or Maintenance Pack; Oracle Applications 11.5.10 Consolidated Update 2 (CU2) is an example of a consolidated patch.
Interoperability patch: This is a patch that is required for Oracle Applications to function with a newer version of a technology stack component; for example, you would apply an interoperability patch when upgrading the database to version 10g.
NLS patch: This is a patch that updates language-specific information for multi-language installations.
Rollup patch: This is a collection of one-off patches that update code levels for particular products.
Legislative patch: This is a special patch for HR Payroll customers; it contains legislative data for multiple countries.

As the patch group size increases from one-off patches to Maintenance Packs, the complexity of the patch application process also increases. More research is required for Family Packs than is required for a Minipack. Due to the increased complexity, there is more planning required for Maintenance Packs and Family Packs than other patches.

Codelines(12.0:A, 12.1:B)
In release 12, Oracle Applications introduces codelines and codelevels to ease tracking of patch prerequisites, dependencies, and compatibilities. Patches are associated with a codeline, which not only identifies the set of product features, but also the order of the various patches released to provide fixes to that set of features.
A codeline begins with a base point that consists of a unique set of product features, and progresses to include all the patches created to provide fixes to that base point. For example, Oracle Financials and Oracle Human Resources are active in your system.
The initial set of features or base point of Oracle Financials and Oracle Human Resources are FIN.A and HR.A, respectively. When fixes are required for the base point, a patch (or a set of patches) is released, and the order in which the patch created is indicated by a number appended to the base point. Each new patch is called a codelevel. For example, codelevel FIN.A.1 is the first set of fixes to base point, FIN.A, and FIN.A.2 is the second set of fixes, and so on.

Codelevels(R12.AD.A.1, R12.AD.A.2)
Codelevels are cumulative -- each one contains the initial set of features plus all the fixes created to date (except those replaced by subsequent patches) for that product family.
Some patches may contain new features. These patches create new base points or start new codelines. For example, when Oracle Financials releases new features in a patch (instead of being part of a complete upgrade of Oracle Applications), the patch starts a new codeline, FIN.B. Then, the subsequent release of patches (or codelevels) with fixes to the expanded set of features are named accordingly: FIN.B.1, FIN.B.2, FIN.B.3, and so on.
As a user, you can choose to accept a patch to fix your existing codeline or you can accept a patch on a more recent codeline, which will not only provide fixes to your products, but will also add feature enhancements to your system.

What is pre-requisite patch ?
A prerequisite patch fulfills a dependency for another patch.  In Release 12, only codelevels can be prereq patches. Strictly speaking, they are co-requisites and can be applied in any order before using the system. We recommend that you merge a patch with its required prerequisites, with the exception of prerequisite patches for the AD product.

Oracle Patch Application Assistant (PAA) helps you track and perform manual steps during patching.
How do I determine what baseline I’m on ?
Following select will tell you the R12 version:
SQL>select 'RELEASE '||RELEASE_NAME from APPLSYS.FND_PRODUCT_GROUPS;

Following select will tell you the products and their corresponding baselines:
SQL>set pages 999
SQL>set long 9999
SQL>column BASELINE format a8
SQL>break on baseline
SQL>select BASELINE,upper(abbreviation),name from AD_TRACKABLE_ENTITIES order by baseline,abbreviation;


.

R12 - PAA ( Patch Application Assistant )

R12 - PAA  ( Patch Application Assistant ):


Patch Application Assistant (admsi.pl)
PAA (Patch Application Assistant) is tool/Perl script to generate customized installation instructions for a patch in Oracle Applications R12 which helps user to track and perform manual steps during patching.

- Oracle Patch Application Assistant (PAA) helps you track and perform manual steps during patching
- For patches with manual steps, PAA generates a customized set of instructions specific to your installation and displays the relevant manual steps
- For merged patches, PAA automatically merges the contents of the individual patch readme files.
- If you need to use PAA, the patch readme will ask you to run admsi.pl

This utility can be run in either CLI mode or GUI mode

Run the admsi.pl script to generate customized installation instructions for applying the patch. You will need to provide the location of your patch top directory and the applmgr password.
$ cd $AD_TOP/bin
$ perl admsi.pl -patch_top=<patch-top-directory> -appspass=<apps-password>
For details, run "perl admsi.pl --help"

The steps are contained in the customized installation instructions generated by the admsi.pl script. Additional steps are also detailed in the customized installation instructions depending on the patch, the state of your system, and the products you have installed.

Important tables for ADPATCH

Written By Unknown on Wednesday, October 14, 2009 | 10:13 AM

Important Tables For ADPATCH :


AD_APPL_TOPS
AD_APPLIED_PATCHES
AD_BUGS
AD_PATCH_DRIVERS
AD_FILE_VERSIONS
AD_FILES
AD_PATCH_DRIVER_LANGS
AD_PATCH_DRIVER_MINIPKS
AD_PATCH_RUN_BUG_ACTIONS
AD_PATCH_RUN_BUGS
AD_PATCH_RUNS
AD_RELEASES
AD_PATCH_COMMON_ACTIONS

Applying hrglobal driver

Applying hrglobal  driver:


Ref Notes :   796167.1  and  140511.1

Check Note 145837.1 "Latest HRMS (HR Global) Legislative Data Patch Available" to determine which legislative patches are needed.
Run datainstall utility to determine which legislations are installed
$AFJVAPRG oracle.apps.per.DataInstall apps <apps passwd> thin <db server>:<listener port>:<SID>
Select option 1. This will show you which language legislations are currently installed. Use this list to determine which legislation patches are required according to the note above.
To exit the application, type <return>, select option 4, and choose 'N'.
Apply all recommended LEGISLATIVE PATCHES (NLS: Required if applicable)

Run datainstall utility
$AFJVAPRG oracle.apps.per.DataInstall apps <apps passwd> thin <db server>:<listener port>:<SID>
Choose to FORCE install already installed legislations

Apply $PER_TOP/patch/115/driver/hrglobal.drv - using adpatch
adpatch defaultsfile=$APPL_TOP/admin/<SID>/onlinedef.txt \
patchtop=$PER_TOP/patch/115/driver  \
logfile=HRGLOBAL.log driver=hrglobal.drv \
workers=24  \
flags=hidepw

Country codes
==========

http://www.theodora.com/country_digraphs.html
SELECT DECODE(hli.legislation_code
,null,'Global'
,hli.legislation_code)                legCode
, hli.application_short_name                  asn
, hli.status status, last_update_date
FROM hr_legislation_installations hli
where hli.status = 'I';

Explaining Patch c,d,g and Unified Driver

Explaining Patch c,d,g and Unified Driver:


The unzipped patch contains three  drivers "c,d,g" or "u". These are called copy,database,generate drivers or Unified drivers. The unified driver is the combination of all the three c,d,g drivers.

Now we will see these drivers one by one ....

Copy Driver : (cXXXXX.drv )
-- Copies all files to the appropriate directory ( ex:  .fmb , .pll files)
-- Relinks executable

Ex :
copy    ap     forms/US      APXIISIM.fmb     110.8
Driver will check the versions of APXIISIM.fmb on your system. If it is less than 110.8, then it will copy this file to the $AU_TOP/forms/US directory.

copy    ap     patch/110/sql apaiithb.pls     110.2
Driver will check the versions of apaiithb.pls on your system. If it is less than 110.2, then it will copy this file to the $AP_TOP/patch/110/sql directory.

copy    fnd    resource      JE.pll           110.8
Driver will check the versions of JE.pll on your system. If it is less than 110.8, then it will copy this file to the $AU_TOP/resource directory.

Database Driver : ( dXXXXXX.drv )
This is the driver that runs .sql, .pls, .odf and other files that update the database. As mentioned previously, some common ways the database is updated by the this driver are:
-- Create packages
-- Create new error messages
-- Add a new table or view to the database
-- Add a new column to a table
-- Add new seed data to a table

The database driver uses the same command structure:
<command>  <product>  <subdirectory>  <file>  <other arguments....>
However, as you will see, it uses the <other arguments> section much more. There are numerous different arguments that can be used, but following are some more common examples:
- sql Run the script directly from the worker
- sqlplus Spawn a new sqlplus session and run the script
- package Same as sqlplus but performs package version checking

Another argument you may see at the end of the command string is a 'phase='’ command. Before performing any actions, adpatch divides all actions contained in the patch driver file into phases based on information specified in the patch driver. Adpatch performs all actions grouped in one phase in parallel before proceeding to the next.
Many 'd' drivers in patches contain phase definitions that determine what order the files in the driver will
run. If the driver does not contain any phase definitions, then the files are executed in the order they appear in the driver.
There are currently around 19 different phases that can be defined, but some examples are:
Phase name      Action taken
seq             Create sequences
tab             Create tables and indexes
pls             Create package specifications (specs)
vw              Create Views
plb             Create package bodies

Following are some examples of common commands found in a dXXXXXX.drv driver, and a brief explanation.
sql  ar   patchsc/107/sql b512706a.sql  !AR_PERIOD_TYPES  &un_ar  &pw_ar sql  &phase=tab
Run the sql script $AR_TOP/patchsc/107/sql/b512706a.sql using the ar username and password. The !AR_PERIOD_TYPES is another parameter expected by the .sql script. As discussed previously the &phase argument specifies what type of action this script is doing, in this case creating a table, and when the script will be executed by the driver.

sql  ar   patchsc/107/sql    ARTEHPCS.pls    none none none package &phase=pls
Run the script $AR_TOP/patchsc/107/sql/ARTEHPCS.pls which creates a package (phase=pls).

sql ar patchsc/107/sql artcall5.sql none none none sqlplus &phase=tbm+5 &un_ar &pw_ar
Run the script $AR_TOP/patchsc/107/sql/artcall5.sql. The phase ‘tbm’ tells you that it is altering a table.
The +5 is a way of determining the order a script will run within a phase. For example, within a phase, commands would be run in the following order:
&phase=tbm
&phase=tbm+5
&phase=tbm+99

Generate Driver : (gXXXXXX.drv )
Following are some examples of commands in a 'g' driver. Once again, remember that
-- The .fmb files are stored in $AU_TOP, and when generated the executable (.fmx) is stored under the product.
-- The .pll is stored under $AU_TOP, and when generated the executable (.plx) is also stored under $AU_TOP.
genform    ap     forms/US     APXIISIM.fmb
Generate the form $AU_TOP/forms/US/APXIISIM.fmb and store the executable in $AP_TOP/forms/US/APXIISIM.fmx
genrep     ap     reports      APXIIADV.rdf
Generate the report $AP_TOP/reports/APXIIADV.rdf, and store the resulting file APXIIADV.rdf in the same directory.
genfpll    fnd    resource     JE.pll
Generate the PL/SQL library $AU_TOP/resource/JE.pll, and store the executable JE.plx in $AU_TOP/resource.

Unified Driver : ( uXXXXXXX.drv )
The u driver is a merged driver that is a combined c, d, and/or g driver. Oracle is beginning to release a majority of its patches as unified driver patches. If a patch is a unified driver patch, then only the u driver is applied.

Applying Applications Patch Using adpatch

Applying Applications Patch Using adpatch:


Adpatch is a utility we use to apply application patches.Patches are used either to fix some issue or to intoroduce a new feature. There are different types of patches and we can explain different types of patches in some other article. Let us know focus on how to apply the patch to applications.

Following are the basic steps we follow to apply the patch
1) prepatch analysis
2) pre health checks
3) shutdown MT services
4) Enable Maintenance mode
5) Apply patch using adpatch
6) Disable Maintenance mode
7) Start all MT services
8 ) Perform any post patch steps
9) Post health checks

Prepatch analysis :
First download correct patch for your platform to local machine and unzip the patch. Review the readme file provided with patch and check that all the pre-requisite patches are applied to the system. If there is any pre-required patches to be applied , we will list those patches also to apply to the system. And finally we come to the conclusion with the list of all the patches to be applied.

Pre health checks :
It is important to do pre-health checks to cross verify the impact of patch comparing the post health checks with pre health checks.
Check the status of the following components on all MT nodes :
- Conc Manager status
- Forms status
- URLs check
- Opmn Processes status
- MWA status if configured
- Discoverer status
- Record invalids
- Record workflow status
- Check if there are any NLS Lang available for the patch

Use the following queries ...
SQL> create table apps.invalids_askm_bak tablespace bkpd as (select owner,object_name,object_type from dba_objects where status='INVALID');
SQL> select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+) order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;
SQL> select nls_language, language_code, installed_flag from apps.fnd_languages where installed_flag in ('I','B');
SQL> select * from apps.ad_bugs where bug_number='&bug';

Shutdown MT services :
Use the adstpall.sh script to stop all MT services. Wait for some time to make sure that all the processes are down. Kill if there are any defunct processes and runaway processes.

Enable Maintenance mode :
SQL> @$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE
SQL> select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual; --> to check

Apply patch using adpatch:
Now upload the patch zip file to any temporary directory and unzip the patch. Move into the unzipped directory. And then apply the patch ...
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/default.txt logfile=patch.log \
patchtop=$APPL_TOP/patches/xxxxxxx options=noautoconfig,nocompilejsp,nocompiledb workers=8 \
driver=xxxxxxxx.drv

Answer to the prompts given by the adpatch and wait till you get the message that "autopatch is complete". Then review the log files to check if there are any error in the logfiles. Logfiles are located in  $APPL_TOP/admin/<SID>/log.
Apply NLS patches if any in the same way.

Disable Maintenance mode :
SQL> @$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE
SQL> select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual; --> to check

Start all MT services :

Start all the MT services using adstrtal.sh script.

Perform any post patch steps :
Patch readme file contains the details if there are any post patch steps to be performed. Complete all the post patch steps.

Post health checks :
Perform all the post health checks and compare it with pre patch health checks. Compile new invalids if any. Contact Oracle Support if you have any problem in applying the patch and provide all the patch logfiles to the support.

Do we have mod_plsql,jserv,reports server in R12 ?

Written By Unknown on Monday, October 12, 2009 | 8:19 PM

Do we have mod_plsql,jserv,reports server in R12 ?


mod_plsql :

Mod_plsql is an Apache web server extension that can be used to develop web application pages using Server PL/SQL.
Unlike Oracle E-Business Suite Release 11i, Release 12 does not include mod_plsql as part of its technology stack.
Modplsql component of Apache is removed in R12 .you have to see the alternate for custom developed programs which will be using this.
If you have developed mod_plsql extensions to Oracle E-Business Suite Release 11i, and are considering upgrading to Release 12, you will have to take some action to preserve that functionality.
mod_plsql is replaced by Oracle Application Framework in R12. The reasong for replacing the mod_plsql with Oracle Application Framework in R12 is that , mod_plsql does not provide solutions for a number of important problems that must be solved in a robust and secure web application.

The following components/modules were removed from Release 12
1. mod_plsql
2. Oracle Reports Server
3. Oracle Graphics Integration with Oracle Forms
4. Oracle Applications Framework pages in the AK Repository (AK mode)

JServ :

OC4J replaces the Jserv component which is there in the current release 11i of Oracle Applications. Also as a result the mod_jserv component would be replaced by the mod_oc4j component in release 12 of Oracle Applications. The mod_oc4j is used to communicate between different OC4J instances.

The default installation Release 12 of Oracle Applications creates 3 OC4J instances

-- Oacore: This runs the OA Framework -based applications.
-- Forms: This runs the Forms-based applications.
-- OAFM: This is responsible for running the web services.

The Jserv groups which are there current in Oracle Applications Release 11i are also planned be replaced by OC4J instances.

As mentioned earlier the OC4J properties are controlled using the XML files and OC4J.properties file. These files are managed by the standard Oracle Applications Autoconfig.

The Java code deployment in Oracle E-Business suite for OC4J is done at the time of install using rapid install and maintained by ad tools like adadmin and adpatch. New custom code deployment can be done by using the Application Server Control user interface.

The OC4J implementation In Oracle Applications Release 12 includes the following directory structure.

-- applications: Contains applications deployed
-- applications-deployment: Contains configuration settings for the applications deployed
-- config: Contains common configuration setting for the OC4J instance.

Remember there are no jserv.properties or jserv.conf or zone.properties in R12 (new techstack), Jserv is replaced by Oacore.

Finally ...
- Jserv component is removed and it is replaced in R12 by OC4J(oacore)
- mod_plsql is replaced by Oracle Application Framework
- The reports server has been removed in R12 and it runs as a spawned process called rwrun which is spawned by conc manager.


Ref Note : 726711.1

Killing runaway processes after terminating concurrent Request

Killing runaway processes after terminating concurrent Request:


Every concurrent Request uses some resources for running. If you feel that the concurrent request is taking long time and decided to terminate the concurrent request , the resources may not be released soon. These processes are called runaway processes. So we need to manually kill the processes at database and os level to have the resources released to the system.
Terminate the concurrent request from the front end. Then ...
SQL>select request_id,oracle_process_id,os_process_id from fnd_concurrent_requests where request_id='&Req_Id';

SQL>select p.spid , s.sid , s.serial# from v$session s , v$process p where s.paddr = p.addr and s.process = &os_process_id ;

SQL> alter system kill session 'session-id,session-serial'

$ kill -9 <server pid>


Complete details about the request can be found using the following query :
SELECT qt.user_concurrent_queue_name
, fcr.Request_Id Request_id
, fu.User_name
, p.spid
, s.sid ||’, ‘|| s.serial# SIDSERIAL
, substr( Fcpv.Concurrent_Program_Name ||’ - ‘|| Fcpv.User_Concurrent_Program_Name, 1,46) Program
, to_char( fcr.actual_start_date, ‘mm/dd hh24:mi’ ) actual_start_date
, phase_code, status_code
, to_char( trunc(sysdate) + ( sysdate - fcr.actual_start_date )
, ‘hh24:mi:ss’ ) duration
FROM apps.Fnd_Concurrent_Queues Fcq
, apps.fnd_concurrent_queues_tl qt
, apps.Fnd_Concurrent_Requests Fcr
, apps.Fnd_Concurrent_Programs Fcp
, apps.Fnd_User Fu
, apps.Fnd_Concurrent_Processes Fpro
, v$session s
, v$process p
, apps.Fnd_Concurrent_Programs_Vl Fcpv
WHERE phase_code = ‘C’
AND status_Code = ‘X’
AND s.paddr = p.addr
AND fcr.requested_by = user_id
AND fcq.application_id = qt.application_id
AND fcq.concurrent_queue_id = qt.concurrent_queue_id
AND userenv(’lang’) = qt.language
AND fcr.os_process_id = s.process
AND fcr.Controlling_Manager = Concurrent_Process_Id
AND (fcq.concurrent_queue_id = fpro.concurrent_queue_id
AND fcq.application_id = fpro.queue_application_id )
AND (fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id )
AND (fcr.concurrent_program_id = fcpv.concurrent_program_id
AND fcr.program_application_id = fcpv.application_id )
ORDER BY fcr.actual_start_date;

R12 - How to deploy form on a server

R12 - How to deploy form on a server:


We use f60gen in R11i for deploying forms , but f60gen is deprecated in R12. We use frmcmp_batch.sh for forms compilation in R12.

Steps
=====
1) Backup the existing form in location $AU_TOP/forms/US
2) Copy the new form ( *.fmb ) to the location $AU_TOP/forms/US
3) Set the forms env var
4) Compile the form using :
frmcmp_batch.sh userid=apps/xxxxxx Module=xxxxx.fmb Module_Type=form compile_all=special output_file=xxxxxx.fmx
5) Copy the generated form ( *.fmx) to the corresponding product top  ( $PROD_TOP/forms/US )
6) Check the permission on file *.fmx ( it should be 755 )

Deploying forms from graphical interface :

Use the "frmcmp" in vnc server to open the graphical interface. The screen shot will look like this ....

form_comp

R12 - Various LogFiles Locations

R12 - Various LogFiles Locations :


Startup/Shutdown Log files for Application Tier in R12

$LOG_HOME/appl/admin/log/






































ServiceLogfile Name
TNS Listener Start/Stop logadalnctl.txt
Fulfillment Server Start/Stop logjtffmctl.txt
Oracle HTTP Server start/stop logadapcctl.txt
Concurrent Managers and ICM start/stop logadcmctl.txt
Forms OC4J start/stop logadformsctl.txt
OACore OC4J start/stop logadoacorectl.txtq
OAFM OC4J start/stop logadoafmctl.txt
OPMN start/stop logadopmnctl.txt

Tech Stack  10.1.3 (Web/HTTP Server) Logs




































Log File NameLog File Location
AD script log files (e.g.from adapcctl.sh)$INST_TOP/logs/appl/admin/log
CM Log Files ($APPLCSF/$APPLLOG)$INST_TOP/logs/appl/conc/log
AD tools log files (e.g. ADPATCH)$APPL_CONFIG_HOME/admin/$TWO_TASK/log
OPMN Log Files (text and ODL)$ORA_CONFIG_HOME/10.1.3/opmn/logs (may move to  $INST_TOP/logs/10.1.3/opmn)
Apache Log Files (text and ODL)$INST_TOP/logs/10.1.3/Apache/
OC4J Log Files (text)$INST_TOP/logs/10.1.3/j2ee/oacore/
OC4J Log Files (ODL)$INST_TOP/logs/10.1.3/j2ee/oacore/log/oacore_default_group_1/oc4j

Log files related to cloning in R12:
Preclone log files in source instance
-- Database Tier - /$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/(StageDBTier_MMDDHHMM.log)
-- Application Tier - $INST_TOP/apps/$CONTEXT_NAME/admin/log/ (StageAppsTier_MMDDHHMM.log)
Clone log files in target instance
-- Database Tier - $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDBTier_.log
-- Apps Tier - $INST_TOP/apps/$CONTEXT_NAME/admin/log/ApplyAppsTier_.log

Patching related log files in R12
-- Application Tier adpatch log - $APPL_TOP/admin/$SID/log/
-- Developer (Developer/Forms & Reports 10.1.2) Patch - $ORACLE_HOME/.patch_storage
-- Web Server (Apache) patch - $IAS_ORACLE_HOME/.patch_storage
-- Database Tier opatch log - $ORACLE_HOME/.patch_storage

Autoconfig related log files in R12
Database Tier Autoconfig log :
-- $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/MMDDHHMM/adconfig.log
-- $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/MMDDHHMM/NetServiceHandler.log
Application Tier Autoconfig log :
-- $INST_TOP/apps/$CONTEXT_NAME/admin/log/$MMDDHHMM/adconfig.log
Autoconfig context file location in R12
-- $INST_TOP/apps/$CONTEXT_NAME/appl/admin/$CONTEXT_NAME.xml

Other log files in R12
Database Tier
Relink Log files :
-- $ORACLE_HOME/appsutil/log/$CONTEXT_NAME /MMDDHHMM/ make_$MMDDHHMM.log

Alert Log Files:
-- $ORACLE_HOME/admin/$CONTEXT_NAME/bdump/alert_$SID.log

Network Logs:
-- $ORACLE_HOME/network/admin/$SID.log

OUI Logs
OUI Inventory Logs :
-- $ORACLE_HOME/admin/oui/$CONTEXT_NAME/oraInventory/logs

Application Tier
-- $ORACLE_HOME/j2ee/DevSuite/log
-- $ORACLE_HOME/opmn/logs
-- $ORACLE_HOME/network/logs

R12 - Login Issue after any maintenance activity

R12 - Login Issue after any maintenance activity


Sometime if you try to access home page after any maintenance activity, it displays a blank page. This may occure due to jsp/class corruptions in $COMMON_TOP/_pages.

how do we come to know that there is jsp/class corruption ?
Enable and Collect Debug for HTTP, OC4J and OPMN in R12 ,
we may see in:
$LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1/application.log
(for various class files:

Missing class: _OA
Missing class: _AppsLocalLogin
Missing class: _OAInfo
Missing class: _jsp._fnd._aoljtest
)

Solution
========

To implement the solution, please execute the following steps:
1. cd $FND_TOP/patch/115/bin
2. perl ojspCompile.pl --compile --flush -p 2
3. Bring up the services and test login

Locking Improvements for Index Rebuild

Written By Unknown on Sunday, October 4, 2009 | 7:39 AM

Locking Improvements for Index Rebuild :

Red for session 1

Green for session 2

Blue for session 3

********************

*** 10g

********************

***
In session (1)

SQL-1> CREATE TABLE test_ind_table AS SELECT rownum id, 'Krishna Murthy' name FROM dual CONNECT BY LEVEL <= 10000;

Table created.

SQL-1> CREATE INDEX test_ind_table_i ON test_ind_table(id);

Index created.

*** In other session (2)

SQL-2> INSERT INTO test_ind_table VALUES (10001, 'Mahesh');

1 row created.

*** In the orginal session (1)

SQL-1> ALTER INDEX test_ind_table_i REBUILD ONLINE;
*** session hangs (due to inability to get table lock due to session 2)

*** In yet another session (3)

SQL-3> INSERT INTO test_ind_table VALUES (10002, 'Srinivas');
*** It now hangs due to lock from session 1 , as would a transaction on the test_ind_table table in session 4 and 5 and 6 and   ...

*** Now commit in session (2)

SQL-2> COMMIT;

Commit complete.
*** releases the lock in session 3 and the index rebuild is free to proceed but it will eventually get stuck again as it now requires another lock to complete the rebuild process ...

*** In session 2, perform another insert before session 3 commits ...

SQL-2> INSERT INTO test_ind_table VALUES (10003, 'Satish');
*** and now it in turn hangs due to the rebuild needing the second table lock

*** perform the commit in session (3)

SQL-3> commit;

Commit complete.
and it allows the rebuild in session 1 to finally finish and in turn allows the update in session 2 to then be released and complete as well

*** So a rebuild requires a lock at the start and at the end of the index rebuild process, even if performed ONLINE

*** These locks in turn cause other concurrent transactions on the table to hang as well


********************
*** 11g
********************
*** In session (1)
SQL-1> CREATE TABLE test_ind_table AS SELECT rownum id, 'Krishna Murthy' name FROM dual CONNECT BY LEVEL <= 10000;

Table created.

SQL-1> CREATE INDEX test_ind_table_i ON test_ind_table(id);

Index created.

*** In other session (2)

SQL-2> INSERT INTO test_ind_table VALUES (10001, 'Mahesh');

1 row created.

*** In the orginal session (1)

SQL-1> ALTER INDEX test_ind_table_i REBUILD ONLINE;
session still hangs (due to inability to get table lock due to session 2)

*** In yet another session (3)

SQL-3> INSERT INTO test_ind_table VALUES (10002, 'Srinivas');

1 row created.
*** Big change. This session is no longer impacted by the rebuild trying to get it's table lock. It can carry on happily ..

*** Performing a Commit in session 2 will allow the rebuild to commence but it will be stuck again with the incomplete transaction in session 3.

SQL-2> commit;

Commit complete.
*** Performing another insert in session 2 will complete fine as again the rebuild does not impact other transactions

SQL-2> INSERT INTO test_ind_table VALUES (10003, 'Satish');

1 row created.
*** commiting the transactions in both session 2 and 3 will allow the rebuild to finally complete

SQL-2> commit; (session 2)

Commit complete.

Index altered.  (session 1).
*** So an online rebuild in 11g can still be impacted by concurrent transactions but it in turn will not cause locking issues for other concurrent transactions on the base table

SQL Plan Management(SPM) - Demo with SQL Interface

Written By Unknown on Friday, October 2, 2009 | 1:45 PM

SQL Plan Management(SPM) - Demo with SQL Interface:



SQL> sho user
USER is "SH"
SQL> desc spm_test_table_1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
PROD_ID                                   NOT NULL NUMBER
CUST_ID                                   NOT NULL NUMBER
TIME_ID                                   NOT NULL DATE
CHANNEL_ID                                NOT NULL NUMBER
PROMO_ID                                  NOT NULL NUMBER
QUANTITY_SOLD                             NOT NULL NUMBER(10,2)
AMOUNT_SOLD                               NOT NULL NUMBER(10,2)

SQL> desc spm_test_table_2
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
CHANNEL_ID                                NOT NULL NUMBER
CUST_ID                                   NOT NULL NUMBER
PROD_ID                                   NOT NULL NUMBER
PROMO_ID                                  NOT NULL NUMBER
TIME_ID                                   NOT NULL DATE
UNIT_COST                                 NOT NULL NUMBER(10,2)
UNIT_PRICE                                NOT NULL NUMBER(10,2)
AMOUNT_SOLD                               NOT NULL NUMBER(10,2)
QUANTITY_SOLD                             NOT NULL NUMBER(10,2)
TOTAL_COST                                         NUMBER

SQL> select count(1) from spm_test_table_1;

COUNT(1)
----------
1

SQL> select count(1) from spm_test_table_2;

COUNT(1)
----------
1
 we are using two tables for this demo

SQL> create index spm_index_table1 on spm_test_table_1(cust_id);

Index created.

SQL> exec dbms_stats.gather_table_stats('SH', 'SPM_TEST_TABLE_1', NULL, dbms_stats.auto_sample_size);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SH', 'SPM_TEST_TABLE_2', NULL, dbms_stats.auto_sample_size);

PL/SQL procedure successfully completed.

SQL> explain plan for select count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1633133659

----------------------------------------------------------------------------------------
| Id  | Operation           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                  |     1 |     8 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |                  |     1 |     8 |            |          |
|   2 |   NESTED LOOPS |                  |     1 |     8 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| SPM_TEST_TABLE_2 |     1 |     4 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN | SPM_INDEX_TABLE1 |     1 |     4 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("T1"."CUST_ID"="T2"."CUST_ID")

16 rows selected.

SQL> sho parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     TRUE

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%SPM%spm_test_table%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';


no rows selected
 -- currently there is no baseline plan for this statement.

SQL> select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

COUNT(*)
----------
0

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%SPM%spm_test_table%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';


no rows selected

-- Still no baseline for this

SQL> select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

COUNT(*)
----------
0

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%SPM%spm_test_table%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';


SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT
------------------------------ ------------------------------ --- --- --- ---
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO  YES
 -- We now have the base line plan which was captured automatically.

We will now populate the two test tables with some huge data.

SQL> select count(1) from spm_test_table_1;

COUNT(1)
----------
918844

SQL> select count(1) from spm_test_table_2;

COUNT(1)
----------
916040

SQL> exec dbms_stats.gather_table_stats('SH', 'SPM_TEST_TABLE_1', NULL, dbms_stats.auto_sample_size);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SH', 'SPM_TEST_TABLE_2', NULL, dbms_stats.auto_sample_size);

PL/SQL procedure successfully completed.

SQL> explain plan for select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1633133659

----------------------------------------------------------------------------------------
| Id  | Operation           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                  |     1 |    10 |  1834K  (1)| 06:06:59 |
|   1 |  SORT AGGREGATE     |                  |     1 |    10 |            |          |
|   2 |   NESTED LOOPS |                  |   119M|  1137M|  1834K  (1)| 06:06:59 |
|   3 |    TABLE ACCESS FULL| SPM_TEST_TABLE_2 |   916K|  4472K|  1644   (1)| 00:00:20 |
|*  4 |    INDEX RANGE SCAN | SPM_INDEX_TABLE1 |   130 |   650 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("T1"."CUST_ID"="T2"."CUST_ID")

Note
-----
- SQL plan baseline "SQL_PLAN_53fxyhv7tzwqxbbf661e7" used for this statement

20 rows selected.
 -- We are still using the same baseline plan.

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%SPM%spm_test_table%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';


SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT
------------------------------ ------------------------------ --- --- --- ---
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO  YES

SQL> select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

COUNT(*)
----------
172868759

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%SPM%spm_test_table%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';


SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT
------------------------------ ------------------------------ --- --- --- ---
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqx91ccd494 YES NO NO  YES
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO  YES
 --- The new plan is captured but not inserted into the plan baseline.

We will now simulate the automatic SQL tuning by executing the task manually.

SQL> DECLARE
my_task_name varchar2(30);
sql_txt clob;
BEGIN
sql_txt := 'select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => sql_txt,
user_name => 'SH',
scope => 'COMPREHENSIVE',
time_limit => 300,
task_name => 'Task_askm',
description => 'Tune SPM Query Task');
END;
/


PL/SQL procedure successfully completed.

SQL> exec dbms_sqltune.execute_tuning_task('Task_askm');

PL/SQL procedure successfully completed.

SQL> select dbms_sqltune.report_tuning_task('Task_askm') FROM dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : Task_askm
Tuning Task Owner  : SH
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 300
Completion Status  : COMPLETED
Started at         : 10/02/2009 09:07:14
Completed at       : 10/02/2009 09:08:06

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SH
SQL ID     : cvfq6n3ngyzzy
SQL Text   : select /* SPM_AUTO */ count(*) from spm_test_table_1
t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
--------------------------------------------------------
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 99.77%)
------------------------------------------
- Consider accepting the recommended SQL profile. The SQL plan baseline
corresponding to the plan with the SQL profile will also be updated to an
accepted plan.
execute dbms_sqltune.accept_sql_profile(task_name => 'Task_askm',
task_owner => 'SH', replace => TRUE);


DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 1633133659

--------------------------------------------------------------------------------
--------
| Id  | Operation           | Name             | Rows  | Bytes | Cost (%CPU)| Ti

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
me     |
--------------------------------------------------------------------------------
--------
|   0 | SELECT STATEMENT    |                  |     1 |    10 |  1834K  (1)| 06
:06:59 |
|   1 |  SORT AGGREGATE     |                  |     1 |    10 |            |
|
|   2 |   NESTED LOOPS |                  |   119M|  1137M|  1834K  (1)| 06
:06:59 |
|   3 |    TABLE ACCESS FULL| SPM_TEST_TABLE_2 |   916K|  4472K|  1644   (1)| 00
:00:20 |

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
|*  4 |    INDEX RANGE SCAN | SPM_INDEX_TABLE1 |   130 |   650 |     2   (0)| 00
:00:01 |
--------------------------------------------------------------------------------
--------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("T1"."CUST_ID"="T2"."CUST_ID")

2- Using SQL Profile

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
--------------------
Plan hash value: 3821076920

--------------------------------------------------------------------------------
-------------------
| Id  | Operation              | Name             | Rows  | Bytes |TempSpc| Cost
(%CPU)| Time     |
--------------------------------------------------------------------------------
-------------------
|   0 | SELECT STATEMENT       |                  |     1 |    10 |       |  421
9  (10)| 00:00:51 |

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE        |                  |     1 |    10 |       |
|          |
|*  2 |   HASH JOIN |                  |   119M|  1137M|    14M|  421
9  (10)| 00:00:51 |
|   3 |    TABLE ACCESS FULL   | SPM_TEST_TABLE_2 |   916K|  4472K|       |  164
4   (1)| 00:00:20 |
|   4 |    INDEX FAST FULL SCAN| SPM_INDEX_TABLE1 |   918K|  4486K|       |   72
3   (1)| 00:00:09 |
--------------------------------------------------------------------------------
-------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T1"."CUST_ID"="T2"."CUST_ID")

-------------------------------------------------------------------------------

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%SPM%spm_test_table%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';


SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT
------------------------------ ------------------------------ --- --- --- ---
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqx91ccd494 YES NO NO  YES
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO  YES
 -- We now need to accept the recommendation before the new plan is integrated into the plan baseline. This would be done automatically by automatic SQL Tuning.

SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'Task_askm',task_owner => 'SH', replace => TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%SPM%spm_test_table%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';


SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT
------------------------------ ------------------------------ --- --- --- ---
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqx91ccd494 YES YES NO  YES
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO  YES

SQL> explain plan for select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'basic +note'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3821076920

---------------------------------------------------
| Id  | Operation              | Name             |
---------------------------------------------------
|   0 | SELECT STATEMENT       |                  |
|   1 |  SORT AGGREGATE        |                  |
|   2 |   HASH JOIN |                  |
|   3 |    TABLE ACCESS FULL   | SPM_TEST_TABLE_2 |
|   4 |    INDEX FAST FULL SCAN| SPM_INDEX_TABLE1 |
---------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
- SQL profile "SYS_SQLPROF_0124159dddfa0000" used for this statement
- SQL plan baseline "SQL_PLAN_53fxyhv7tzwqx91ccd494" used for this statement

16 rows selected.

SQL>  var my_var number
SQL> exec :my_var := dbms_spm.alter_sql_plan_baseline -
(sql_handle => 'SYS_SQL_51bbbe86cf9ff2dd', -
plan_name => ' SQL_PLAN_53fxyhv7tzwqx91ccd494', -
attribute_name => 'ACCEPTED', attribute_value => 'NO');


PL/SQL procedure successfully completed.

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%SPM%spm_test_table%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';


SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT
------------------------------ ------------------------------ --- --- --- ---
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqx91ccd494 YES NO NO  YES
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO  YES

SQL> explain plan for select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1633133659

----------------------------------------------------------------------------------------
| Id  | Operation           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                  |     1 |    10 |  1834K  (1)| 06:06:59 |
|   1 |  SORT AGGREGATE     |                  |     1 |    10 |            |          |
|   2 |   NESTED LOOPS |                  |   119M|  1137M|  1834K  (1)| 06:06:59 |
|   3 |    TABLE ACCESS FULL| SPM_TEST_TABLE_2 |   916K|  4472K|  1644   (1)| 00:00:20 |
|*  4 |    INDEX RANGE SCAN | SPM_INDEX_TABLE1 |   130 |   650 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("T1"."CUST_ID"="T2"."CUST_ID")

Note
-----
- SQL plan baseline "SQL_PLAN_53fxyhv7tzwqxbbf661e7" used for this statement

20 rows selected.

SQL>  var my_var number
SQL> exec :my_var := dbms_spm.alter_sql_plan_baseline -
(sql_handle => 'SYS_SQL_51bbbe86cf9ff2dd', -
plan_name => ' SQL_PLAN_53fxyhv7tzwqx91ccd494', -
attribute_name => 'ACCEPTED', attribute_value => 'YES');


PL/SQL procedure successfully completed.

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%SPM%spm_test_table%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';


SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT
------------------------------ ------------------------------ --- --- --- ---
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqx91ccd494 YES YES NO  YES
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO  YES

SQL> explain plan for select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'basic +note'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3821076920

---------------------------------------------------
| Id  | Operation              | Name             |
---------------------------------------------------
|   0 | SELECT STATEMENT       |                  |
|   1 |  SORT AGGREGATE        |                  |
|   2 |   HASH JOIN |                  |
|   3 |    TABLE ACCESS FULL   | SPM_TEST_TABLE_2 |
|   4 |    INDEX FAST FULL SCAN| SPM_INDEX_TABLE1 |
---------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
- SQL profile "SYS_SQLPROF_0124159dddfa0000" used for this statement
- SQL plan baseline "SQL_PLAN_53fxyhv7tzwqx91ccd494" used for this statement

16 rows selected.

SQL>

SQL Plan Manageability Demo.mp4



11g - Flashback Data Archive

Written By Unknown on Thursday, October 1, 2009 | 1:43 PM

11g - Flashback Data Archive (Oracle Total Recall) :


=====================================================================
DEMO               DEMO                DEMO              DEMO              DEMO                  DEMO                      DEMO
=====================================================================

SQL> sho user
USER is "SYS"
SQL> select name,user from v$database;

NAME      USER
--------- ------------------------------
ASKM      SYS

SQL> CREATE TABLESPACE fda_ts DATAFILE '/u01/app/oracle/oradata/askm/fda_ts01.dbf' SIZE 24M;

Tablespace created.

SQL> GRANT FLASHBACK ARCHIVE ADMINISTER TO sh;

Grant succeeded.

SQL> GRANT FLASHBACK ANY TABLE TO sh;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_FLASHBACK TO sh;

Grant succeeded.


SQL> conn sh/sh
Connected.
SQL> CREATE FLASHBACK ARCHIVE fda TABLESPACE fda_ts     QUOTA 1M RETENTION 7 DAY;

Flashback archive created.

SQL> create table rate_conv (currency varchar2(6),Rate number(15,4));

Table created.

SQL> ALTER TABLE sh.rate_conv FLASHBACK ARCHIVE  fda;

Table altered.

SQL> insert into rate_conv values ('IND-Rs',48.1012);

1 row created.

SQL> commit;

Commit complete.

SQL> update rate_conv set rate=49.0120;

1 row updated.

SQL> commit;

Commit complete.

SQL> update rate_conv set rate=48.2012;

1 row updated.

SQL> commit;

Commit complete.

SQL> delete rate_conv;

1 row deleted.

SQL> commit;

Commit complete.

SQL> insert into rate_conv values ('IND-Rs',50.0021);

1 row created.

SQL> commit;

Commit complete.

SQL> update rate_conv set rate=49.0120;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from rate_conv;

CURREN       RATE
------ ----------
IND-Rs     49.012

SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,rate from rate_conv versions between timestamp minvalue and maxvalue order by versions_starttime;

VERSIONS_STARTTIME        VERSIONS_ENDTIME          VERSIONS_XID     V       RATE
------------------------- ------------------------- ---------------- - ----------
01-OCT-09 08.56.25 AM     01-OCT-09 08.56.28 AM     0A0020009B020000 I    48.1012
01-OCT-09 08.56.28 AM     01-OCT-09 08.56.31 AM     060011002A030000 U     49.012
01-OCT-09 08.56.31 AM     01-OCT-09 08.56.40 AM     03000F003A030000 U    48.2012
01-OCT-09 08.56.40 AM                               050005006D030000 D    48.2012
01-OCT-09 08.56.44 AM     01-OCT-09 08.56.48 AM     0900090027030000 I    50.0021
01-OCT-09 08.56.48 AM                               0200010018030000 U     49.012

6 rows selected.

SQL> desc dba_FLASHBACK_ARCHIVE_TABLES;
Name                                                                                Null?    Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
TABLE_NAME                                                                          NOT NULL VARCHAR2(30)
OWNER_NAME                                                                          NOT NULL VARCHAR2(30)
FLASHBACK_ARCHIVE_NAME                                                              NOT NULL VARCHAR2(255)
ARCHIVE_TABLE_NAME                                                                           VARCHAR2(53)
STATUS                                                                                       VARCHAR2(8)

SQL> select TABLE_NAME, OWNER_NAME, FLASHBACK_ARCHIVE_NAME, ARCHIVE_TABLE_NAME from dba_FLASHBACK_ARCHIVE_TABLES;

TABLE_NAME                     OWNER_NAME
------------------------------ ------------------------------
FLASHBACK_ARCHIVE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
ARCHIVE_TABLE_NAME
-----------------------------------------------------
RATE_CONV                      SH
FDA
SYS_FBA_HIST_74714

SQL> select TABLE_NAME, OWNER_NAME, FLASHBACK_ARCHIVE_NAME, ARCHIVE_TABLE_NAME from dba_FLASHBACK_ARCHIVE_TABLES;

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME    ARCHIVE_TABLE_NAME
---------- ---------- ------------------------- --------------------
RATE_CONV  SH         FDA                       SYS_FBA_HIST_74714

SQL> desc SYS_FBA_HIST_74714
Name                                                  Null?    Type
----------------------------------------------------- -------- ------------------------------------
RID                                                            VARCHAR2(4000)
STARTSCN                                                       NUMBER
ENDSCN                                                         NUMBER
XID                                                            RAW(8)
OPERATION                                                      VARCHAR2(1)
CURRENCY                                                       VARCHAR2(6)
RATE                                                           NUMBER(15,4)

SQL> select count(1) from SYS_FBA_HIST_74714;

COUNT(1)
----------
4

SQL> desc rate_conv
Name                                                  Null?    Type
----------------------------------------------------- -------- ------------------------------------
CURRENCY                                                       VARCHAR2(6)
RATE                                                           NUMBER(15,4)

SQL> select * from SYS_FBA_HIST_74714;

RID                   STARTSCN    ENDSCN XID                       O CURREN       RATE
-------------------- --------- --------- ------------------------- - ------ ----------
AAASPaAAEAABhUvAAA     1234564   1234570 0A0020009B020000          I IND-Rs    48.1012
AAASPaAAEAABhUvAAA     1234570   1234573 060011002A030000          U IND-Rs     49.012
AAASPaAAEAABhUvAAA     1234573   1234577 03000F003A030000          U IND-Rs    48.2012
AAASPaAAEAABhUvAAB     1234588   1234591 0900090027030000          I IND-Rs    50.0021

SQL> select * from rate_conv;

CURREN       RATE
------ ----------
IND-Rs     49.012


SQL> conn /as sysdba
Connected.
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/u01/app/oracle/oradata/askm/undotbs2_01.dbf' SIZE 2M;

Tablespace created.

SQL> ALTER SYSTEM CHECKPOINT;

System altered.

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = 'UNDOTBS2' SCOPE=BOTH;

System altered.


SQL> conn sh/sh
Connected.
SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,rate from rate_conv versions between timestamp minvalue and maxvalue order by versions_starttime;

VERSIONS_STARTTIME                  VERSIONS_ENDTIME                    VERSIONS_XID     V       RATE
----------------------------------- ----------------------------------- ---------------- - ----------
01-OCT-09 08.56.25.000000000 AM     01-OCT-09 08.56.28.000000000 AM     0A0020009B020000 I    48.1012
01-OCT-09 08.56.28.000000000 AM     01-OCT-09 08.56.31.000000000 AM     060011002A030000 U     49.012
01-OCT-09 08.56.31.000000000 AM     01-OCT-09 08.56.40.000000000 AM     03000F003A030000 U    48.2012
01-OCT-09 08.56.44.000000000 AM     01-OCT-09 08.56.48.000000000 AM     0900090027030000 I    50.0021
01-OCT-09 08.56.48.000000000 AM                                         0200010018030000 U     49.012

SQL> select rate from rate_conv as of timestamp to_timestamp('2009-10-01 08:56:29','yyyy-mm-dd hh24:mi:ss');

RATE
----------
48.1012

SQL> explain plan for select rate from rate_conv as of timestamp to_timestamp('2009-10-01 08:56:29','yyyy-mm-dd hh24:mi:ss');

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3276248349

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                    |     5 |    65 |     9  (12)| 00:00:01 |       |       |
|   1 |  VIEW                     |                    |     5 |    65 |     9  (12)| 00:00:01 |       |       |
|   2 |   UNION-ALL               |                    |       |       |            |          |       |       |
|*  3 |    FILTER                 |                    |       |       |            |          |       |       |
|   4 |     PARTITION RANGE SINGLE|                    |     1 |    39 |     3   (0)| 00:00:01 |   KEY |     1 |
|*  5 |      TABLE ACCESS FULL    | SYS_FBA_HIST_74714 | 1 |    39 |     3   (0)| 00:00:01 |   KEY |     1 |
|*  6 |    FILTER                 |                    |       |       |            |          |       |       |
|*  7 |     HASH JOIN OUTER       |                    |     4 |  8212 |     6  (17)| 00:00:01 |       |       |
|*  8 |      TABLE ACCESS FULL    | RATE_CONV          |     4 |   100 |     2   (0)| 00:00:01 |       |       |
|*  9 |      TABLE ACCESS FULL    | SYS_FBA_TCRV_74714 |     2 |  4056 |     3   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("TIMESTAMP_TO_SCN"(TIMESTAMP' 2009-10-01 08:56:29.000000000')<1242042)
5 - filter("ENDSCN"<=1242042 AND "ENDSCN">"TIMESTAMP_TO_SCN"(TIMESTAMP' 2009-10-01
08:56:29.000000000') AND ("STARTSCN" IS NULL OR "STARTSCN"<="TIMESTAMP_TO_SCN"(TIMESTAMP' 2009-10-01
08:56:29.000000000')))
6 - filter("STARTSCN"<="TIMESTAMP_TO_SCN"(TIMESTAMP' 2009-10-01 08:56:29.000000000') OR "STARTSCN"
IS NULL)
7 - access("T".ROWID=CHARTOROWID("RID"(+)))
8 - filter("T"."VERSIONS_STARTSCN" IS NULL)
9 - filter(("ENDSCN"(+) IS NULL OR "ENDSCN"(+)>1242042) AND ("STARTSCN"(+) IS NULL OR
"STARTSCN"(+)<1242042))

Note
-----
- dynamic sampling used for this statement (level=2)

34 rows selected.

SQL>

SQL> alter table rate_conv NO FLASHBACK ARCHIVE;

Table altered.


Flashback Data Archives information from Data Dictionary :


SQL> conn /as sysdba
Connected.
SQL> sho user
USER is "SYS"
SQL> SELECT table_name FROM dict WHERE  table_name LIKE '%FLASHBACK_ARCHIVE%';

TABLE_NAME
------------------------------
DBA_FLASHBACK_ARCHIVE
DBA_FLASHBACK_ARCHIVE_TABLES
DBA_FLASHBACK_ARCHIVE_TS
USER_FLASHBACK_ARCHIVE
USER_FLASHBACK_ARCHIVE_TABLES

SQL> SELECT flashback_archive_name, create_time, status FROM   dba_flashback_archive;

FLASHBACK_ CREATE_TIME                                   STATUS
---------- --------------------------------------------- ----------
FDA        01-OCT-09 08.55.34.000000000 AM

SQL> SELECT * FROM   dba_flashback_archive_ts;

FLASHBACK_ FLASHBACK_ARCHIVE# TABLESPACE_NAME
---------- ------------------ ------------------------------
QUOTA_IN_MB
----------------------------------------
FDA                         1 FDA_TS
1

SQL> SELECT * FROM   dba_flashback_archive_tables;

TABLE_NAME                     OWNER_NAME                     FLASHBACK_
------------------------------ ------------------------------ ----------
ARCHIVE_TABLE_NAME                                    STATUS
----------------------------------------------------- ----------
RATE_CONV                      SH                             FDA
SYS_FBA_HIST_74714                                    ENABLED


Maintaining Flashback Data Archive (FBDAs):
SQL> ALTER FLASHBACK ARCHIVE fda MODIFY TABLESPACE fda_ts QUOTA 2M;

Flashback archive altered.

SQL> ALTER FLASHBACK ARCHIVE fda MODIFY RETENTION 90 DAY;

Flashback archive altered.

SQL> CREATE TABLESPACE fda_ts2 DATAFILE '/u01/app/oracle/oradata/askm/fda_ts02.dbf' SIZE 16M;

Tablespace created.

SQL> ALTER FLASHBACK ARCHIVE fda ADD TABLESPACE fda_ts2;

Flashback archive altered.

SQL> ALTER FLASHBACK ARCHIVE fda PURGE BEFORE TIMESTAMP(SYSTIMESTAMP - INTERVAL '1' DAY);

Flashback archive altered.

SQL> DROP FLASHBACK ARCHIVE fda;

Flashback archive dropped.




11g - Flashback Data Archive (Oracle Total Recall)



11g - SQL Access Advisor

11g - SQL Access Advisor:


=====================================================================
DEMO               DEMO                DEMO              DEMO              DEMO                  DEMO                      DEMO
=====================================================================


Make sure that the user SH has been granted the following privileges.
SQL> sho user
USER is "SYS"
SQL> grant all on dbms_advisor to sh;

Grant succeeded.

SQL> grant advisor to sh;

Grant succeeded.

SQL> grant ADMINISTER SQL TUNING SET to sh;

Grant succeeded.

SQL> conn sh/sh
Connected


SQL> sho user
USER is "SH"
SQL> execute dbms_advisor.create_task ( 'SQL Access Advisor','SQL_ACC_TASK1','New SQLAccess  Task');

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.reset_task('SQL_ACC_TASK1');

PL/SQL procedure successfully completed.

SQL> create table temp_table AS SELECT * FROM SYS.WRI$_ADV_SQLW_STMTS WHERE NULL IS NOT NULL;

Table created.

SQL> create table sql_access_temp_table (c number, d varchar2(1000));

Table created.

SQL> begin
for i in 1..20000 loop
insert into sql_access_temp_table values(-i,'aoiejnflamnskdfjnsijndfklsjpoakneflkajsdfkjankdsnflkasjdnfkjasndjkfnjdklfsbkbsdnbaiuebdfiausdybfaouisdbflkjabdoiufbaklsdjfaksdiufakjsdofiuasdjfalkjsdfdjkhfakjshsjdfkjasksdfkjalkaksdjhfkjaakjdsfsdjfklasjdlkadfoiuaksmdnflkanmdlfknalkksdnflkansdflklksdnflkadsnflaknsdfoiweuroiamsdflkamsalksdmffalkklvmklakmdvlkamsdlkmvlksdmvlksdmvkllsdkfmasdfmalksdmfaksmdnflkanmdlfknalkksdnflkansdflklksdnflkadsnflaknsdfoiweuroiamsdflkamsalksdmffalkklvmklakmdvlkamsdlkmvlksdmvlksdmvkllsdkfmasdfmalksdmfaksmdnflkanmdlfknalkksdnflkansdflklk');
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.

SQL> create table customers_askm as select * from customers;

Table created.

SQL> @askm_sts.sql

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.add_sqlwkld_ref('SQL_ACC_TASK1','SQLSET_MY_SQLACCESS_WORKLOAD',1);

PL/SQL procedure successfully completed.

SQL> @askm_param.sql

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.execute_task('SQL_ACC_TASK1');

PL/SQL procedure successfully completed.

Scripts used :

$ cat askm_sts.sql
DECLARE
sql_stmt   varchar2(2000);
sqlsetname  VARCHAR2(30);
sqlsetcur   dbms_sqltune.sqlset_cursor;
refid    NUMBER;
k NUMBER := 0;
num_queries NUMBER := 500;
BEGIN

sql_stmt := 'SELECT /* QueryASKM 2 */ ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND c.cust_state_province = ''CA'' AND ch.channel_desc in (''Internet'',''Catalog'') AND t.calendar_quarter_desc IN (''1999-01'',''1999-02'') GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc';

insert into temp_table values(1,1,NULL,0,'SH','Access Advisor','Workload',0,0,0,0,1,100,2,to_date('02-FEB-2007'),3,0,sql_stmt,1);

sql_stmt := 'SELECT /* QueryASKM 3 */ ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND c.cust_state_province = ''CA'' AND ch.channel_desc in (''Internet'',''Catalog'') AND t.calendar_quarter_desc IN (''1999-03'',''1999-04'') GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc';

insert into temp_table values(1,1,NULL,0,'SH','Access Advisor','Workload',0,0,0,0,1,100,2,to_date('02-FEB-2007'),3,0,sql_stmt,1);

sql_stmt := 'SELECT /* QueryASKM 4 */ c.country_id, c.cust_city, c.cust_last_name FROM sh.customers c WHERE c.country_id in (52790, 52798) ORDER BY c.country_id, c.cust_city, c.cust_last_name';

insert into temp_table values(1,1,NULL,0,'SH','Access Advisor','Workload',0,0,0,0,1,100,2,to_date('02-FEB-2007'),3,0,sql_stmt,1);

sql_stmt := 'select /* func_indx */ count(*) from sql_access_temp_table where abs(c)=5';

insert into temp_table values(1,1,NULL,0,'SH','Access Advisor','Workload',0,0,0,0,1,100,2,to_date('02-FEB-2007'),3,0,sql_stmt,1);

sql_stmt := 'SELECT /* QueryASKM 5 */ * FROM sh.customersjfv WHERE cust_state_province = ''CA''';

insert into temp_table values(1,1,NULL,0,'SH','Access Advisor','Workload',0,0,0,0,1,100,2,to_date('02-FEB-2007'),3,0,sql_stmt,1);

sqlsetname := 'SQLSET_MY_SQLACCESS_WORKLOAD';

dbms_sqltune.create_sqlset(sqlsetname, 'Generated STS');

OPEN sqlsetcur FOR
SELECT
SQLSET_ROW(null,null, sql_text, null, null, username, module,
action, elapsed_time, cpu_time, buffer_gets, disk_reads,
0,rows_processed, 0, executions, 0, optimizer_cost, null,
priority, command_type,
to_char(last_execution_date,'yyyy-mm-dd/hh24:mi:ss'),
0,0,NULL,0,NULL,NULL
)
FROM temp_table;

dbms_sqltune.load_sqlset(sqlsetname, sqlsetcur);
END;
/

$


$ cat askm_param.sql
/* Set STS Workload Parameters */
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','VALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','VALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','SQL_LIMIT','25');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','VALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','VALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','INVALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','INVALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','INVALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','INVALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','VALID_SQLSTRING_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','INVALID_SQLSTRING_LIST','"@!"');

/* Set Task Parameters */
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','ANALYSIS_SCOPE','ALL');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','RANKING_MEASURE','PRIORITY,OPTIMIZER_COST');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DEF_PARTITION_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','TIME_LIMIT',10000);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','MODE','LIMITED');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','STORAGE_CHANGE',DBMS_ADVISOR.ADVISOR_UNLIMITED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DML_VOLATILITY','TRUE');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','WORKLOAD_SCOPE','PARTIAL');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DEF_INDEX_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DEF_INDEX_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DEF_MVIEW_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DEF_MVIEW_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DEF_MVLOG_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','CREATION_COST','TRUE');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','JOURNALING','4');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DAYS_TO_EXPIRE','30');

$

SQL Access Recommendations :

SQL> SET LONG 100000
SQL> SET PAGESIZE 50000
SQL> SELECT DBMS_ADVISOR.get_task_script('SQL_ACC_TASK1') AS script FROM   dual;

SCRIPT
--------------------------------------------------------------------------------
Rem  SQL Access Advisor: Version 11.2.0.1.0 - Production
Rem
Rem  Username:        SH
Rem  Task:            SQL_ACC_TASK1
Rem  Execution date:
Rem

Rem
Rem  Repartitioning table "SH"."CUSTOMERS"
Rem

SET SERVEROUTPUT ON
SET ECHO ON

Rem
Rem Creating new partitioned table
Rem
CREATE TABLE "SH"."CUSTOMERS1"
(    "CUST_ID" NUMBER,
"CUST_FIRST_NAME" VARCHAR2(20),
"CUST_LAST_NAME" VARCHAR2(40),
"CUST_GENDER" CHAR(1),
"CUST_YEAR_OF_BIRTH" NUMBER(4,0),
"CUST_MARITAL_STATUS" VARCHAR2(20),
"CUST_STREET_ADDRESS" VARCHAR2(40),
"CUST_POSTAL_CODE" VARCHAR2(10),
"CUST_CITY" VARCHAR2(30),
"CUST_CITY_ID" NUMBER,
"CUST_STATE_PROVINCE" VARCHAR2(40),
"CUST_STATE_PROVINCE_ID" NUMBER,
"COUNTRY_ID" NUMBER,
"CUST_MAIN_PHONE_NUMBER" VARCHAR2(25),
"CUST_INCOME_LEVEL" VARCHAR2(30),
"CUST_CREDIT_LIMIT" NUMBER,
"CUST_EMAIL" VARCHAR2(30),
"CUST_TOTAL" VARCHAR2(14),
"CUST_TOTAL_ID" NUMBER,
"CUST_SRC_ID" NUMBER,
"CUST_EFF_FROM" DATE,
"CUST_EFF_TO" DATE,
"CUST_VALID" VARCHAR2(1)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
TABLESPACE "EXAMPLE"
PARTITION BY RANGE ("CUST_ID") INTERVAL( 3000) ( PARTITION VALUES LESS THAN (3000));

Rem
Rem Copying comments to new partitioned table
Rem
COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_ID" IS 'primary key';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_FIRST_NAME" IS 'first name of the customer';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_LAST_NAME" IS 'last name of the customer';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_GENDER" IS 'gender; low cardinality attribute';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_YEAR_OF_BIRTH" IS 'customer year of birth';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_MARITAL_STATUS" IS 'customer maritalstatus; low cardinality attribute';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_STREET_ADDRESS" IS 'customer street address';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_POSTAL_CODE" IS 'postal code of the customer';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_CITY" IS 'city where the customer lives';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_STATE_PROVINCE" IS 'customer geography: state or province';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."COUNTRY_ID" IS 'foreign key to the countries table (snowflake)';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_MAIN_PHONE_NUMBER" IS 'customer mainphone number';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_INCOME_LEVEL" IS 'customer income level';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_CREDIT_LIMIT" IS 'customer credit limit';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_EMAIL" IS 'customer email id';

COMMENT ON TABLE "SH"."CUSTOMERS1"  IS 'dimension table';

Rem
Rem Copying constraints to new partitioned table
Rem
ALTER TABLE "SH"."CUSTOMERS1" ADD CONSTRAINT "CUSTOMERS_PK1" PRIMARY KEY ("CUST_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS  TABLESPACE "EXAMPLE"  ENABLE NOVALIDATE;

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_ID" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_FIRST_NAME" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_LAST_NAME" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_GENDER" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_YEAR_OF_BIRTH" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_STREET_ADDRESS" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_POSTAL_CODE" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_CITY" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_CITY_ID" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_STATE_PROVINCE" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_STATE_PROVINCE_ID" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("COUNTRY_ID" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_MAIN_PHONE_NUMBER" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_TOTAL" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_TOTAL_ID" NOT NULL ENABLE);

Rem
Rem Copying referential constraints to new partitioned table
Rem
ALTER TABLE "SH"."CUSTOMERS1" ADD CONSTRAINT "CUSTOMERS_COUNTRY_FK1" FOREIGN KEY ("COUNTRY_ID")
REFERENCES "SH"."COUNTRIES" ("COUNTRY_ID") ENABLE NOVALIDATE;

Rem
Rem Copying indexes to new partitioned table
Rem
CREATE UNIQUE INDEX "SH"."CUSTOMERS_PK1" ON "SH"."CUSTOMERS1" ("CUST_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" ;

CREATE BITMAP INDEX "SH"."CUSTOMERS_GENDER_BIX1" ON "SH"."CUSTOMERS1" ("CUST_GENDER")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" LOCAL;

CREATE BITMAP INDEX "SH"."CUSTOMERS_MARITAL_BIX1" ON "SH"."CUSTOMERS1" ("CUST_MARITAL_STATUS")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" LOCAL;

CREATE BITMAP INDEX "SH"."CUSTOMERS_YOB_BIX1" ON "SH"."CUSTOMERS1" ("CUST_YEAR_OF_BIRTH")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" LOCAL;

Rem
Rem Copying object grants to new partitioned table
Rem
GRANT SELECT ON "SH"."CUSTOMERS1" TO "BI";

Rem
Rem Populating new partitioned table with data from original table
Rem
INSERT /*+ APPEND */ INTO "SH"."CUSTOMERS1"
SELECT * FROM "SH"."CUSTOMERS";
COMMIT;

begin
dbms_stats.gather_table_stats('"SH"', '"CUSTOMERS1"', NULL, dbms_stats.auto_sample_size);
end;
/

Rem
Rem Renaming tables to give new partitioned table the original table name
Rem
ALTER TABLE "SH"."CUSTOMERS" RENAME TO "CUSTOMERS11";
ALTER TABLE "SH"."CUSTOMERS1" RENAME TO "CUSTOMERS";

Rem
Rem Revalidating dimensions for use with new partitioned table
Rem
ALTER DIMENSION "SH"."CUSTOMERS_DIM" COMPILE;

CREATE MATERIALIZED VIEW LOG ON
"SH"."CUSTOMERS"
WITH ROWID, SEQUENCE("CUST_ID","CUST_CITY","CUST_STATE_PROVINCE")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
"SH"."CHANNELS"
WITH ROWID, SEQUENCE("CHANNEL_ID","CHANNEL_DESC","CHANNEL_CLASS")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
"SH"."TIMES"
WITH ROWID, SEQUENCE("TIME_ID","CALENDAR_QUARTER_DESC")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
"SH"."SALES"
WITH ROWID, SEQUENCE("CUST_ID","TIME_ID","CHANNEL_ID","AMOUNT_SOLD")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW "SH"."MV$$_004D0000"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH.CUSTOMERS.CUST_STATE_PROVINCE C1, SH.CUSTOMERS.CUST_CITY C2, SH
.CHANNELS.CHANNEL_CLASS
C3, SH.CHANNELS.CHANNEL_DESC C4, SH.TIMES.CALENDAR_QUARTER_DESC C5, SUM("
SH"."SALES"."AMOUNT_SOLD")
M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.CUSTOMERS,
SH.CHANNELS, SH.TIMES, SH.SALES WHERE SH.SALES.CHANNEL_ID = SH.CHANNELS.C
HANNEL_ID
AND SH.SALES.TIME_ID = SH.TIMES.TIME_ID AND SH.SALES.CUST_ID = SH.CUSTOME
RS.CUST_ID
AND (SH.TIMES.CALENDAR_QUARTER_DESC IN ('1999-04', '1999-03', '1999-02'
, '1999-01')) AND (SH.CHANNELS.CHANNEL_DESC IN ('Internet', 'Catalog'
)) AND (SH.CUSTOMERS.CUST_STATE_PROVINCE = 'CA') GROUP BY SH.CUSTOMERS.CU
ST_STATE_PROVINCE,
SH.CUSTOMERS.CUST_CITY, SH.CHANNELS.CHANNEL_CLASS, SH.CHANNELS.CHANNEL_DESC,
SH.TIMES.CALENDAR_QUARTER_DESC;

begin
dbms_stats.gather_table_stats('"SH"','"MV$$_004D0000"',NULL,dbms_stats.auto_sa
mple_size);
end;
/

CREATE MATERIALIZED VIEW "SH"."MV$$_004D0001"
REFRESH FORCE WITH ROWID
ENABLE QUERY REWRITE
AS SELECT "SH"."CUSTOMERS"."COUNTRY_ID" M1, "SH"."CUSTOMERS"."CUST_CITY" M2,
"SH"."CUSTOMERS"."CUST_LAST_NAME"
M3 FROM SH.CUSTOMERS WHERE (SH.CUSTOMERS.COUNTRY_ID IN (52798, 52790));

begin
dbms_stats.gather_table_stats('"SH"','"MV$$_004D0001"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE INDEX "SH"."CUSTOMERS_IDX$$_004D0000" ON "SH"."CUSTOMERS" ("COUNTRY_ID","CUST_CITY","CUST_LAST_NAME") COMPUTE STATISTICS;

SQL>

11g - SQL Access Advisor



 
Support :
Copyright © 2013. askMLabs - All Rights Reserved
Proudly powered by Blogger