Category: SQL Scripts For DBA

How to Create SQL Plan Baseline from AWR

In another post How to Create SQL Plan Baseline from Cursor Cache. Here we will demonstrate how to create SQL plan baseline from AWR.

By using the SQL in Find Oracle SQL Execution History Details, we get the best execution plan for sql_id=’6fqb4wf9sgfyv’ and plan_hash_value= 815656277.

Create STS (  SQL Tuning Set )

 BEGIN  
  DBMS_SQLTUNE.CREATE_SQLSET (  
     sqlset_name  => 'STS_6fqb4wf9sgfyv',  
     description  => 'SQL Tuning Set for 6fqb4wf9sgfyv');  
 END;

Load the Good Plan into STS

DECLARE  
  cur sys_refcursor;  
 BEGIN  
  OPEN cur FOR  
   SELECT VALUE(P)  
   FROM TABLE(   dbms_sqltune.select_workload_repository(begin_snap=>101300,end_snap=>101304,basic_filter=>'sql_id = ''6fqb4wf9sgfyv''',attribute_list=>'ALL')  
        ) p;  
    DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS_6fqb4wf9sgfyv', populate_cursor=>cur);  
  CLOSE cur;  
 END;  

  /
PL/SQL procedure successfully completed.

Check the Good SQL Loaded into STS

SQL>  select OWNER,NAME,DESCRIPTION,STATEMENT_COUNT from DBA_SQLSET where OWNER='SYS' and NAME='STS_6fqb4wf9sgfyv';

OWNER                          NAME
------------------------------ ------------------------------
DESCRIPTION                                                                                                                                                                                                                                  STATEMENT_COUNT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------
SYS                            STS_6fqb4wf9sgfyv
SQL Tuning Set for 6fqb4wf9sgfyv                                                                                                                                                                                                                   1

SQL> select * from table(dbms_xplan.display_sqlset('STS_6fqb4wf9sgfyv','6fqb4wf9sgfyv'));
...
..
.

Load the Plan From STS

SQL> DECLARE
 good_plans pls_integer;
 BEGIN
  good_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
   sqlset_name => 'STS_6fqb4wf9sgfyv',
   basic_filter=>'plan_hash_value = ''815656277'''
   );
 END;   

 /

PL/SQL procedure successfully completed.

Check the Plan Loaded Successfully

SQL> select SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED,REPRODUCED,AUTOPURGE,OPTIMIZER_COST , EXECUTIONS,ELAPSED_TIME,CPU_TIME from DBA_SQL_PLAN_BASELINES where SQL_TEXT like 'SELECT O.ORDER_ID, O.ORDER_RE%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX REP AUT OPTIMIZER_COST EXECUTIONS ELAPSED_TIME   CPU_TIME
------------------------------ ------------------------------ --- --- --- --- --- -------------- ---------- ------------ ----------
SQL_4e7889c88773e98f           SQL_PLAN_4wy49t23r7ucgbe7808e2 YES YES NO  YES YES         229777          1     61311779   53542413

SQL>  SELECT * FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_4wy49t23r7ucgbe7808e2'));

SQL>  SELECT * FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(sql_handle=>'SQL_4e7889c88773e98f'));
Advertisement

How to Move LOBSEGMENT LOBINDEX to Another Tablespace

Check LOB current tablespace

SQL> select owner, segment_name, segment_type, tablespace_name , bytes from dba_segments where owner='USER1';

OWNER    SEGMENT_NAME                SEGMENT_TYPE       TABLESPACE_NAME    BYTES
-------- --------------------------- ------------------ ----------------- ----------
USER1    SYS_IL0000057639C00010$$    LOBINDEX           TEMPDATA	       65536
USER1    SYS_LOB0000057639C00010$$   LOBSEGMENT         TEMPDATA	    12713984
USER1    SYS_IL0000057685C00008$$    LOBINDEX           TEMPDATA	       65536
USER1    SYS_LOB0000057685C00008$$   LOBSEGMENT         TEMPDATA	     2228224
USER1    SYS_IL0000057693C00008$$    LOBINDEX           TEMPDATA	       65536
USER1    SYS_LOB0000057693C00008$$   LOBSEGMENT         TEMPDATA	      131072

6 rows selected.

Generate moving LObs sql 

SQL> select 'alter table  '||owner||'.'||table_name||' move lob ('||column_name||') store as (tablespace USER1) ;' from dba_lobs where  owner='USER1';

'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'MOVELOB('||COLUMN_NAME||')STOREAS(TABLESPACE USER1;'
---------------------------------------------------------------------------------------------
alter table  USER1.TABLE1 move lob (DATA) store as (tablespace USER1);
alter table  USER1.TABLE2 move lob (DATA) store as (tablespace USER1);
alter table  USER1.TABLE3 move lob (DATA) store as (tablespace USER1);

move lobs into new tablespace

SQL>alter table  USER1.TABLE1 move lob (DATA) store as (tablespace USER1);
Table altered.

SQL>alter table  USER1.TABLE2 move lob (DATA) store as (tablespace USER1);
Table altered.

SQL>alter table  USER1.TABLE3 move lob (DATA) store as (tablespace USER1);
Table altered.

check lobs moved into new tablespace

SQL> select owner, segment_name, segment_type, tablespace_name , bytes from dba_segments where owner='USER1';

OWNER    SEGMENT_NAME                SEGMENT_TYPE       TABLESPACE_NAME    BYTES
-------- --------------------------- ------------------ ----------------- ----------
USER1    SYS_IL0000057639C00010$$    LOBINDEX           USER1	       65536
USER1    SYS_LOB0000057639C00010$$   LOBSEGMENT         USER1	    12713984
USER1    SYS_IL0000057685C00008$$    LOBINDEX           USER1	       65536
USER1    SYS_LOB0000057685C00008$$   LOBSEGMENT         USER1	     2228224
USER1    SYS_IL0000057693C00008$$    LOBINDEX           USER1	       65536
USER1    SYS_LOB0000057693C00008$$   LOBSEGMENT         USER1	      131072

6 rows selected.

rebuild UNUSABLE indxes

SQL>  select 'alter index '||owner||'.'||INDEX_NAME||' rebuild;' from dba_indexes where owner='USER1' and STATUS='UNUSABLE';

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;'
------------------------------------------------------
alter index USER1.idx_customers rebuild;
alter index USER1.idx_prods rebuild;
alter index USER1.idx_orders rebuild;
....
..
.

SQL>alter index USER1.idx_customers rebuild;
Index altered.

SQL>alter index USER1.idx_prods rebuild;
Index altered.

SQL>alter index USER1.idx_orders rebuild;
Index altered.

Please note LOBINDEX will be automatically relocated onto the same new tablespace as LOBSEGMENT, so there is no need to run SQL to relocate LOBINDEX . Otherwise the error will occur.

How to Check Oracle Database Table and Index Fragmentation

The following posts demonstrated how to check fragmentations in PostgreSQL:
How to Identify Fragmentation in PostgreSQL RDS
Code to Report Table Index Schema or Database Fragmentation in PostgreSQL

This post will show how to check table and index fragmentations in Oracle database.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

dbms_metadata to Generate DDL

The following command is generally used to extract object DDL:

SQL>SELECT DBMS_METADATA.GET_DDL('object_type','object_name','object owner') FROM DUAL;

Generate a Table DDL

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','TABLE_NAME','OWNER') FROM DUAL;

Generate All Tables DDL

SQL> select dbms_metadata.get_ddl('TABLE',t.table_name, t.owner) from dba_tables t where owner='USER_NAME' ;

Generate an Index DDL

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','INDEX_NAME','OWNER') FROM DUAL;

Generate All Indexes DDL

SQL> select dbms_metadata.get_ddl('INDEX',i.index_name, i.owner) from dba_indexes i where table_owner='USER_NAME' ;

Generate a View DDL

SQL> Select Dbms_metadata.get_ddl ('VIEW', 'VIEW_NAME', 'OWNER') from dual;

Generate All Views DDL

SQL> SELECT dbms_metadata.GET_DDL ('VIEW', v.view_name,v.owner) from dba_views v;

Generate a Materialized View DDL

SQL> SELECT query FROM  DBA_MVIEWS WHERE  MVIEW_NAME='MV_NAME';

SQL> Select Dbms_metadata.get_ddl ('MATERIALIZED_VIEW', 'MVIEW_NAME', 'MV_OWNER') from dual;

Generate All Materialized Views DDL

SQL> SELECT dbms_metadata.GET_DDL ('MATERIALIZED_VIEW', mv.mview_name,mv.owner) from dba_mviews mv;

Generate a Procedure DDL

SQL>select dbms_metadata.get_ddl ('PROCEDURE','PROCEDURE_NAME','OWNER') from dual;

Generate All Procedures DDL

SQL>select dbms_metadata.get_ddl ('PROCEDURE', o.object_name, o.owner,) from dba_objects o where o.object_type = 'PROCEDURE' and o.owner='USER';

Generate a Function DDL

SQL>select dbms_metadata.get_ddl ('FUNCTION', 'FUNCTION_NAME', 'OWNER') from dual;

Generate All Functions DDL

SQL>select dbms_metadata.get_ddl ('FUNCTION', f.object_name, f.owner,) from dba_objects f where f.object_type = 'FUNCTION' and f.owner='USER';

Generate a Package DDL

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE','PACKAGE_NAME','OWNER') FROM DUAL;

Generate a Package Body DDL

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','PACKAGE_NAME','OWNER') FROM DUAL;

Generate a Tablespace DDL

SQL>  SELECT DBMS_METADATA.GET_DDL('TABLESPACE','TABLESPACE_NAME') FROM dual;

Generate All Tablespace DDL

SQL> SELECT dbms_metadata.GET_DDL('TABLESPACE', ts.tablespace_name) from dba_tablespaces ts;

Generate Foreign Key Constraints DDL

SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','TABLE_NAME','OWNER') from dual;

System Privileges Granted to User

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','USER') from dual;

Generate Roles Granted DDL

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','USER') from dual;

Generate Objects Granted to User DDL

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','USER') from dual;

Oracle Enterprise Manager Corrective Action (CA) Execution Stats

How many times Corrective Action (CA) has been executed one year so far ?

SQL> select CA_NAME,  count(*) 
       from sysman.MGMT$CA_EXECUTIONS 
      where START_TIME>=sysdate-365  
   group by CA_NAME order by 1

CA_NAME                                  COUNT(*)
---------------------------------------- ----------
...
..
.
EXTEND_TABLESPACE_NORAC                         3
EXTEND_TABLESPACE_PDB                          16
EXTEND_TABLESPACE_RAC                          70
NON-RAC_RMAN_AUTO_BACKUP_ARCHIVELOGS           56
RMAN_AUTO_BACKUP_ARCHIVELOGS                  533
...
..
.

10 rows selected.

About view MGMT$CA_EXECUTIONS , Oracle official documentation is here or just click the following link.

https://docs.oracle.com/en/enterprise-manager/cloud-control/enterprise-manager-cloud-control/13.5/emvws/job-views.html#GUID-CBDDC556-498B-4458-BCB4-CF28CAB3A834