SQL Tuning Advisor against sql_id's in AWR

We were in a situation very recently to run SQL Tuning Advisor against a bunch of SQL statements that appeared in the AWR's ADDM recommendations report. The initial effort to launch SQL Tuning Advisor against the SQL_ID couldn't go through as the SQL didn't exist in the shared pool.

Since the sql_id was present in the AWR report, thought of running the advisory against the AWR data, and found a very nice and precisely explained at the following blog:

http://www.redstk.com/running-sql-tuning-advisor-against-awr-data/


---- Example how to run SQL Tuning advisor against sql_id in AWR

variable stmt_task VARCHAR2(64);
SQL> exec :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK (begin_snap => 4118, end_snap => 4119, sql_id => 'caxcavmq6zkv9' , scope => 'COMPREHENSIVE', time_limit => 60, task_name => 'sql_tuning_task01' );

SQL> exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'sql_tuning_task01');

SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'sql_tuning_task01';

set long 50000
set longchunksize 500000
SET LINESIZE 150
Set pagesize 5000
 
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_task01') FROM DUAL;


SQL> exec DBMS_SQLTUNE.drop_tuning_task(task_name =>'sql_tuning_task01');


References:
https://docs.oracle.com/database/121/ARPLS/d_sqltun.htm#ARPLS220
https://uhesse.com/2013/10/11/oracle-sql-tuning-advisor-on-the-command-line/



Happy reading/learning.

Migrating data from on-primeses to cloud

No doubt everyone talks about cloud technologies and certainly could holds the future for various reasons. Oracle doesn't want to left behind in the competition and put the top gear towards cloud offerings. 

This blog explore various Oracle options to migrate on-premises data to cloud. Typically, when a database is created on cloud, the next challenging factor is loading the data to cloud. The good thing about data migration is that the methods and procedures remain the same as you were doing earlier. All data migration constraints still applied, like the following:

• OS versions of on-premises and cloud machine
• DB versions
• Character set
• DB Size
• data types
• Network bandwidth

 The very known and DBA friendly popular Oracle methods are still valid for cloud data migration too :

• Logical method (conventional data pumps)
• TTS
• Cross platform TTS
• Unplugging/Plugging/Cloning/Remote Cloning of PDBs
• SQL Developer and SQL Loader
• Golden Gate

Usually, you take the data backup, choosing the method which suits your requirements,  and upload the backup files to the cloud machine where the database is hosted. Please consider good network and internet speed to expedite the data migration process.

In the example below, data pump (dumpfile) is copied from the on-premises machine to the cloud host machine:


Once the backup files are transferred to the cloud host, you use the typically method to do the data restore.

For more options, read the URL below:

https://docs.oracle.com/en/cloud/paas/database-dbaas-cloud/csdbi/mig-12c-non-cdb-12c.html

For example using SQL Developer and SQL Loading, read the URLs below:

http://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/dbaas/OU/MigratingToDBaaS/LoadingData/LoadingData.html
http://docs.oracle.com/cloud/latest/dbcs_schema/CSDBU/GUID-3B14CF7A-637B-4019-AAA7-A6DC5FF3D2AE.htm#CSDBU179

http://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/schema/50/DataLoad_SQLDev/DataLoad_SQLDev.html

Golden Gate
http://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/ggcs/Replicate_On-Premises_Data_to_Cloud_with_OGGCS/Replicate_on_premises_data_to_cloud_with_Oracle_GoldenGate_cloud_service.html

Transforming a heap table to a partitioned table - how and whats new in 12c R1 & R2

As part of the daily operational job, one of the typical requests we DBAs get is to convert a regular (heap) table into a partitioned table. This can be achieved either offline or online. This blog will demonstrate some of the pre-12c methods and enhancements in Oracle 12c R1 and R2.

I remembered when I  had such requests in the past, I used the following offline/online methods to achieve the goals, whatever best fit my application needs.

The offline method involves the following action sequence:

1. Create empty interim partitioned table, indexes and etc
2. Stop the application services if the non-partitioned table involved in any operations
3. Migrate the data from the non-partitioned table to partitioned table
4. Swap the table names
5. Drop the non-partitioned table
6. Compile any invalid package/procedure/functions/triggers
7. Gather table stats

Note: If any integrity references, dependencies exists, the above procedure slightly defers with a couple of additional actions. The downside of this workaround is the service interruption during the course of transformation.

To avoid any service interruption, Oracle provides redefinition feature to perform the action online, without actually impacting the going DML operations on the table. The redefinition option involves the following action sequence:

1.  Validate if the table can use redefinition feature or not (DBMS_REDEFINITION.CAN_REDEF_TABLE procedure)
2.  Create interim partition table and all indexes
3. Start the online redefinition process (DBMS_REDEFINITION.START_REDEF_TABLE procedure)
4. Copy dependent objects (DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS procedure)
5. Perform data synchronization (DBMS_REDEFINITION.SYNC_INTERIM_TABLE procedure)
6. Stop the online redefinition process (DBMS_REDEFINITIONS.FINISH_REDEF_TABLE procedure)
7. Swap the table names
8. Compile any invalid package/procedure/functions/triggers
9. Gather table stats

 However, such sort of action is simplified in Oracle 12c R1 and made easier in R2. The following demonstraes12c R1 and R2 methods.

12cR1 EXCHANGE PARTITION
With EXCHANGE PARTITION feature, the data can be quickly loaded from a non-partitioned table to a partitioned table:

Once you have the partitioned table, use the following example to exchange the data of heap table to partitioned table. In this example, the existing data will be copied to a single partition in the partitioned table.

ALTER TABLE sales EXCHANGE PARTITION p1 WITH TABLE non_sales_part;

12cR2 MODIFY 
With 12cR2 ALTER TABLE MODIFY option, a non-partitioned table can be easily transformed into a partitioned table, either offline or online. The example below demonstrate creating daily interval partition:

offline procedure:
ALTER TABLE sales MODIFY
PARTITION BY RANGE (column_name) INTERVAL (1)
(partition p1 values less than (100),
partitionp2 values less than (1000)) ;

Online procedure:
ALTER TABLE sales MODIFY
PARTITION BY RANGE (column_name) INTERVAL (1)
(partition p1 values less than (100),
partitionp2 values less than (1000))ONLINE UPDATE INDEXES (index1, index2 LOCAL) ;


References:
https://uhesse.com/2010/02/15/partitioning-a-table-online-with-dbms_redefinition/
https://docs.oracle.com/database/122/VLDBG/evolve-nopartition-table.htm#VLDBG-GUID-5FDB7D59-DD05-40E4-8AB4-AF82EA0D0FE5
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:869096700346654484
https://oracle-base.com/articles/misc/partitioning-an-existing-table-using-exchange-partition