Posted in 2016

Private Cloud HOL

Important: https://blogs.oracle.com/scoter/entry/oow16_hol_private_cloud_demonstration

Advertisements
Posted in 2016

SQL Tuning Sets – fixing an Execution Plan – Tuning Pack

First of all, sqlt is super, it’s doing all the same I did in a simple way… see this post: sqlt script to force a good plan

The point of this blog entry is to show how you can work with tuning sets, profiles and SPM.. and that it uses a wrong plan..
Preparing the user is important:

SYS@TESTDB> create user psorger identified by psorger default tablespace users quota 
unlimited on users ;
SYS@TESTDB> grant connect, resource to psorger ;
SYS@TESTDB> grant select on v_$sql_plan to psorger ;
SYS@TESTDB> grant select on v_$session to psorger ;
SYS@TESTDB> connect psorger/psorger
PSORGER@TESTDB> create table test_spm (id number) ;
PSORGER@TESTDB> create index test_spm_i on test_spm(id) ;
PSORGER@TESTDB> insert into test_spm select 1 from dual connect by level<=1 ;
PSORGER@TESTDB> insert into test_spm select 100000 from dual connect by level<=100000 ;
PSORGER@TESTDB> insert into test_spm select 100 from dual connect by level<=100 ; commit ;

PSORGER@TESTDB> exec dbms_stats.gather_schema_stats(user) ;

Do some queries

PSORGER@TESTDB> var B1 number ;
PSORGER@TESTDB> exec :B1 := 1 ;

PL/SQL procedure successfully completed.

PSORGER@TESTDB> select count(*) from test_spm where id=:B1 ;

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

PSORGER@TESTDB> exec :B1 := 100000 ;

PL/SQL procedure successfully completed.

PSORGER@TESTDB> select count(*) from test_spm where id=:B1 ;

  COUNT(*)
----------
    100000

PSORGER@TESTDB> select count(*) from test_spm where id=:B1 ;

  COUNT(*)
----------
    100000

PSORGER@TESTDB> select count(*) from test_spm where id=:B1 ;

  COUNT(*)
----------
    100000

PSORGER@TESTDB> select count(*) from test_spm where id=:B1 ;

  COUNT(*)
----------
    100000

PSORGER@TESTDB> /

  COUNT(*)
----------
    100000

PSORGER@TESTDB> /

  COUNT(*)
----------
    100000

PSORGER@TESTDB> /

  COUNT(*)
----------
    100000

Let’s check the execution plans:

SYS> select * from table(dbms_xplan.display_cursor('0rucdkwhrqgzz',null,'ADVANCED')) ;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID  0rucdkwhrqgzz, child number 0
-------------------------------------
select count(*) from test_spm where id=:B1

Plan hash value: 1569137237

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |            |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| TEST_SPM_I |     1 |     3 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------


SQL_ID  0rucdkwhrqgzz, child number 1
-------------------------------------
select count(*) from test_spm where id=:B1

Plan hash value: 1438295085

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |    69 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_SPM |   100K|   292K|    69   (2)| 00:00:01 |
-------------------------------------------------------------------------------

We see that FTS is 1438295085 and IRS is 1569137237. So let’s do some sqlsets…

begin
  dbms_sqltune.create_sqlset(sqlset_name=>'SQLSET') ;
end ;
/

DECLARE
  c_sqlarea_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
 OPEN c_sqlarea_cursor FOR
   SELECT VALUE(p)
   FROM   TABLE( 
            DBMS_SQLTUNE.SELECT_CURSOR_CACHE(' plan_hash_value in (1438295085,1569137237) ', 
     null,null,null,null, null,null,'ALL')
          ) p;
-- load the tuning set
  DBMS_SQLTUNE.LOAD_SQLSET (  
    sqlset_name     => 'SQLSET'
,   populate_cursor =>  c_sqlarea_cursor 
);
END;
/

See how easy I delete all unnecessary sql_ids from the sqlset, there were arround also recursive sqls and something else, I don’t remember, you can try it and see…

exec dbms_sqltune.delete_sqlset(sqlset_name=>'TEST', basic_filter=> 'sql_id!=''0rucdkwhrqgzz'' ')

OK so let’s load the FTS hints from OTHER_XML to the sql_text… To make it static..

declare
  sql_t clob ;
  v_hints sys.sqlprof_attr;
  v_xml clob;

  begin
  select sql_fulltext into sql_t from v$sqlarea where sql_id='0rucdkwhrqgzz' ;

  -- to get the FULL TABLE SCAN
  select other_xml into v_xml from v_$sql_plan where sql_id='0rucdkwhrqgzz' and child_number=1 and other_xml is not null and rownum<=1 ; 
  
  select extractValue(value(t), '/hint') as hint bulk collect into v_hints from table(xmlsequence(extract(xmltype(v_xml),'/*/outline_data/hint'))) t;

  DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text=>sql_t, profile=>v_hints, name=>'SQLPROFILE_PETER', replace=>true, force_match=>true) ;
  end;
  /

select name from dba_sql_profiles ;

NAME
-------------------------
SQLPROFILE_PETER


SELECT SQL_ID, ELAPSED_TIME, FETCHES, EXECUTIONS, PRIORITY FROM   TABLE(DBMS_SQLTUNE.SELECT_SQLSET('SQLSET')) ;


SQL_ID        ELAPSED_TIME    FETCHES EXECUTIONS   PRIORITY
------------- ------------ ---------- ---------- ----------
0rucdkwhrqgzz         4616          1          1
0rucdkwhrqgzz        36616         12         12

var id number ;
exec :id := DBMS_SPM.LOAD_PLANS_FROM_SQLSET('SQLSET')


@list_sql_handle.sql

SQL_HANDLE                SQL_TEXT                       ENA ACC FIX EXECUTIONS PLAN_NAME
------------------------- ------------------------------ --- --- --- ---------- --------------------------------------------------
SQL_a53e31f51500d522      select count(*) from test_spm  YES YES NO           1 SQL_PLAN_aagjjynah1p92087a3704
                          where id=:B1

SQL_a53e31f51500d522      select count(*) from test_spm  YES YES NO          12 SQL_PLAN_aagjjynah1p92166588d9
                          where id=:B1

var id number ;
exec :id := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle=>'SQL_a53e31f51500d522',plan_name=>'SQL_PLAN_aagjjynah1p92087a3704', 
attribute_name=>'enabled',attribute_value=>'NO')


@list_sql_handle.sql

SQL_HANDLE                SQL_TEXT                       ENA ACC FIX EXECUTIONS PLAN_NAME
------------------------- ------------------------------ --- --- --- ---------- --------------------------------------------------
SQL_a53e31f51500d522      select count(*) from test_spm  NO  YES NO           1 SQL_PLAN_aagjjynah1p92087a3704
                          where id=:B1

SQL_a53e31f51500d522      select count(*) from test_spm  YES YES NO          12 SQL_PLAN_aagjjynah1p92166588d9
                          where id=:B1


PSORGER@TESTDB> set autotrace on explain
PSORGER@TESTDB> select count(*) from test_spm where id=:B1 ;

  COUNT(*)
----------
    100000


Execution Plan
----------------------------------------------------------
Plan hash value: 1569137237

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |     3 |    91   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |            |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| TEST_SPM_I | 33367 |    97K|    91   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   2 - access("ID"=TO_NUMBER(:B1))

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

PSORGER@TESTDB> exec :B1 := 1;
PSORGER@TESTDB> select count(*) from test_spm where id=:B1 ;

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


Execution Plan
----------------------------------------------------------
Plan hash value: 1569137237

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |     3 |    91   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |            |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| TEST_SPM_I | 33367 |    97K|    91   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   2 - access("ID"=TO_NUMBER(:B1))

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

It’s working, but somehow wrong I think, or what do you think? Shouldn’t it be FTS???

Posted in 2016

Migrating a Recovery Catalog is not easy with datapump

How to transfer data over network links:
Preparation:

connect sys@oemrepo as sysdba
create tablespace rcat datafile 1000m ;
create user rcat identified by xxx default tablespace rcat quota unlimited on rcat ;
grant recovery_catalog_owner to rcat ;
create public database link rcat connect to system identified by xxx using 'RCAT' ;

impdp job:
impdp rcat@oemrepo directory=dp_dir logfile=impdp.log network_link=RCAT table_exists_action=replace schemas=rcat flashback_time=systimestamp exclude=statistics

First problem:
ORA-39113 When Running A DataPump Job Through NETWORK_LINK (Doc ID 2100177.1)
– you have to create a public database link
Second problem:
ORA-39181: Only partial table data may be exported due to fine grain access control on “RCAT”.”BP”
. . imported “RCAT”.”BP” 565783 rows
– even when I grant grant exempt access policy to system ; it throws these errors
Third Problem:
There is a table with LONG column and that’s not supported by Oracle

Well, it looks like I have to read how to do it correctly because I become sad mad and I found this: https://docs.oracle.com/database/121/BRADV/rcmcatdb.htm#BRADV189
https://docs.oracle.com/database/121/RCMRF/rcmsynta026.htm#RCMRF198