Automatic SQL Tuning in Oracle Database 11g

g automates the SQL Tuning process by identifying problematic SQLg:

Oracle Database 11

statements, running SQL Tuning Advisor on them, and implementing the resulting SQL profile

recommendations to tune the statement without requiring user intervention. Automatic SQL Tuning

uses the AUTOTASK framework through a new task called “Automatic SQL Tuning” that runs

every night by default. Here is a brief description of the automated SQL tuning process in Oracle

Database 11

Step 1: Based on the AWR Top SQL identification (SQLs that were top in four different time

periods: the past week, any day in the past week, any hour in the past week, or single response time),

Automatic SQL Tuning targets for automatic tuning.

Steps 2 and 3: While the Automatic SQL Tuning task is executing during the maintenance window,

the previously identified SQL statements are automatically tuned by invoking SQL Tuning Advisor.

As a result, SQL profiles will be created for them if needed. However, before making any decision,

the new profile is carefully tested.

Step 4: At any point in time, you can request a report about these automatic tuning activities.

You then have the option of checking the tuned SQL statements to validate or remove the automatic

SQL profiles that were generated.

How to setup 11g active dataguard

Main advantage of 11g dataguard is

■Ability to open the standby database in read-only mode while the database is still in managed recovery mode. This means 11g active dataguard database is bi-functional, provides disaster recovery and at the same time can be used for reporting purpose.

■Able to open standby database in read-write. This is very useful to turn our standby database into application testing / development purposes. This is achieved through snapshot standby database.

Steps

Setting up active dataguard is very easy and straight forward. Initial steps to create active dataguard are as same as setting up 10g dataguard. The steps are almost similar with only few different commands. In this scenario, I have named my Niti_Production database as ‘Niti_Prod’ and standby database as ‘Niti_Stdby’ :

1. First of all, parameter file (with appropriate dataguard parameters) and standby control file must be created from the Niti_Production database.

2.Setup listener.ora and tnsnames.ora for connectivity between Niti_Production and standby.

3. Connect to rman

rman target sys/oracle@Niti_Prod auxiliary sys/oracle@Niti_Stdby

4. With 11g active dataguard, 1 simple command will create standby without even backing up Niti_Prod database and placing it in a staging area.

RMAN>duplicate target database for standby from active database;

5. Standby database successfully created with the db is in mount state.

Active Dataguard

1. Use dataguard broker to enable active dataguard.

2. If you try to open database during this time, you will get error .

3. In order to open the standby database, connect to dgmrl and change the apply state.

4. Open the standby database now.



5. Re-enable the apply state so that redo transferred and applied to standby database while the database is open for querying.

6. To disable the active dataguard, the steps are the same :

I. DGMGRL> edit database Niti_Stdby set state=apply-off;

II. Shutdown the Niti_Stdby database

III. Startup mount the Niti_Stdby database

IV. DGMGRL> edit database Niti_Stdby set state=apply-on;

Snapshot standby database

1. In order to create snapshot standby database :

■standby database must be a physical standby database
■Flashback logging must be enabled on both Niti_Production and standby database.

2. After enabling flashback mode, connect to dgmrl utility in the primary database :



3. Convert the physical standby to snapshot standby with a single command .

4. From now on, we can do any testing (like creating new schemas, tables and so on) on our standby database. Please note that at this point of time, all the redo generated in our Niti_Production database will be still shipped to standby database. But it is just that not going to be applied until the database is converted into physical standby mode.

5. Once the testing done, you can convert the snapshot standby database to physical standby with just 1 command.

DGMGRL>convert database Niti_Stdby to physical standby;

6. Note that when the above command executed

■All the changed made to the snapshot standby database ( such as creating new schemas, tables) has been terminated
■Previous physical standby database state is initiated
■Physical standby database is mounted and MRP process is initiated. MRP will apply all the logs which was shipped yet applied during the snapshot standby database state.

7. Duration for this process is depends on few factors :

■The amount of changes made to the database during the snapshot standby database state. More changes will lead to more time to rewind the changes via flashback database option.
■The amount of archived logs generated during the snapshot standby database state. More archive logs will cause more time to apply it when the database is converted to physical standby database.

Oracle Concurrent Request Error Script

Do you need a script to check on the error on your Oracle Concurrent Request? Sometimes a script to check on the Oracle Concurrent Request is more useful than navigating into the Oracle Applications to check.

The script below will return any error in Oracle Concurrent Request for 7 days and for all the request. With the error information output from the script below, you may find the frequency of the error in your Oracle Concurrent Request.

SELECT a.request_id "Req Id"
,a.phase_code,a.status_code
, actual_start_date
, actual_completion_date
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.status_code = 'E'
AND a.phase_code = 'C'
AND actual_start_date > sysdate - 7
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE = 'US'
ORDER BY 5 DESC;

Long Running Concurrent Request In Oracle

Looking on how to check long running concurrent request in Oracle Apps 11i or R12? Here’s the overview of the SQL query script to detect the session information of each program.

First you need to get the listing of running concurrent request in Oracle Apps 11i or R12. You can use the SQL query script as below to obtain the list of running request.


SELECT a.request_id
,a.oracle_process_id "SPID"
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
,a.description
,a.ARGUMENT_TEXT
,b.node_name
,b.db_instance
,a.logfile_name
,a.logfile_node_name
,a.outfile_name
,q.concurrent_queue_name
,a.phase_code,a.status_code, a.completion_text
, actual_start_date
, actual_completion_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.phase_code = 'R'
AND a.status_code = 'R'
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
ORDER BY a.actual_start_date DESC


You can see the request id and other relevant information from the result.

Based on the SPID associated to each running request, query the v$session or v$session_longops table to see what is the request id doing in the backend.



SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c
WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = 'ACTIVE'
AND a.spid = '11696'
ORDER BY a.spid, c.piece


Replace v$session with gv$session if the database is running on RAC environment. Enable or set trace if you wish to know more details on the session.

Find SID from Concurrent Program request ID

SELECT sid
FROM v$session
WHERE paddr LIKE
(SELECT addr
FROM v$process
WHERE spid =
(SELECT oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER()
)
);

Oracle 11g SQL Plan Management – SQL Plan Baselines

Baselines are the latest evolution in Oracle’s efforts to allow a plan to be locked. The previous efforts were Outlines and SQL Profiles. Both of those efforts were based on the idea that hints could be applied to limit the optimizers choices down to one. That approach seems a little flawed. Why not just save the plan and be done with it? I believe that’s what Baselines are intended to do. Unfortunately, they don’t appear to do it yet. But they do have an advantage over Outlines and SQL Profiles in that they at least save the plan_hash_value, so they know if they are reproducing the correct plan or not.

Oracle SQL plan management (SPM) relieves the problem of environmental changes causing thousands of SQL statements to change their explain plan steps.

By default, the parameters optimizer_capture_sql_plan_baselines is set to FALSE and optimizer_use_sql_plan_baselines is set to TRUE.

Oracle notes that when SQL plan management is enabled, only known and verified plans are used, and all plan changes are automatically verified. During execution plan verification, only "better" execution plans will be implemented.

Oracle SQL plan management also offers a new package called dbms_spm and a new DBA view dba_sql_plan_baseline to allow the DBA to manage their SQL plans.

dbms_spm.evolve_sql_plan_baseline - This SPM procedure tests a new execution plan against a "verified" plan to determine if the new plan has comparable (or better) execution performance.

dbms_spm.load_plans_from_cursor_cache - This SPM procedure will extract revised SQL explain plans directly from the library cache.

dbms_spm.load_plans_from_sqlset - This SPM procedure allow the DBA to take pre-tested execution plans from a SQL tuning Set (STS), after running a workload test, and load them for production use.