Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Data Pump LOGTIME, DUMPFILE, PARFILE, DATA_PUMP_DIR in 12c

Data Pump is a powerful way to save data or metadata, move it, migrate, etc. Here is an example showing few new features in 12cR1 and 12cR2.

New parameters

Here is the result of a diff between 12.1 and 12.2 ‘imp help=y’
CaptureDataPump122

But for this post, I’ll show the parameters that existed in 12.1 but have been enhanced in 12.2

LOGTIME

This is a 12.1 feature. The parameter LOGFILE=ALL displays the system timestamp in front of the messages in at the screen and in the logfile. The default is NONE and you can also set it to STATUS for screen only and LOGFILE for logfile only.


[oracle@vmreforanf12c01 tmp]$ expdp system/manager@PDB01 parfile=impdp.par logfile=impdp.log
 
Export: Release 12.2.0.1.0 - Production on Sat Apr 22 22:20:22 2017
 
Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved.
 
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
22-APR-17 22:20:29.671: Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@PDB01 parfile=impdp.par logfile=impdp.log
22-APR-17 22:20:35.505: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
22-APR-17 22:20:36.032: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
22-APR-17 22:20:36.407: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
22-APR-17 22:20:43.586: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
22-APR-17 22:20:44.126: Processing object type SCHEMA_EXPORT/USER
22-APR-17 22:20:44.199: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
22-APR-17 22:20:44.243: Processing object type SCHEMA_EXPORT/ROLE_GRANT
22-APR-17 22:20:44.296: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
22-APR-17 22:20:44.760: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
22-APR-17 22:20:53.706: Processing object type SCHEMA_EXPORT/TABLE/TABLE
22-APR-17 22:20:59.699: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
22-APR-17 22:21:00.712: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
22-APR-17 22:21:03.494: . . exported "SCOTT"."DEMO" 8.789 KB 14 rows
22-APR-17 22:21:03.651: . . exported "SCOTT"."EMP" 8.781 KB 14 rows
22-APR-17 22:21:03.652: . . exported "SCOTT"."DEPT" 6.031 KB 4 rows
22-APR-17 22:21:03.654: . . exported "SCOTT"."SALGRADE" 5.960 KB 5 rows
22-APR-17 22:21:03.656: . . exported "SCOTT"."BONUS" 0 KB 0 rows
22-APR-17 22:21:04.532: Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
22-APR-17 22:21:04.558: ******************************************************************************
22-APR-17 22:21:04.559: Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
22-APR-17 22:21:04.569: /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/log/46EA7EF707457B4FE0531416A8C027F2/SCOTT_20170422.01.dmp
22-APR-17 22:21:04.622: Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Apr 22 22:21:04 2017 elapsed 0 00:00:41

You will always appreciate finding timestamps in the log file. But remember that your import/export is processed by multiple workers and it is difficult to estimate duration between the different lines. I explained this in https://blog.dbi-services.com/datapump-processing-object-type-misleading-messages/

DUMPFILE

You can see that my DUMPFILE contains also the timestamp in the file name. This is possible in 12.2 with the %T substitution variable. Here was my PARFILE where DUMPFILE mentions %U (in addition to %U if there are multiple files):

[oracle@vmreforanf12c01 tmp]$ cat impdp.par
schemas=SCOTT
logtime=all
dumpfile=SCOTT_%T.%U.dmp
reuse_dumpfiles=yes
filesize=1M

PARFILE parameters

I don’t usually use a PARFILE and prefer to pass all parameters on the command line, even if this requires escaping a lot of quotes, because I like to ship the log file with the DUMPFILE. And before 12.2 the LOGFILE mentions only the parameters passed on command line. In 12.2 the PARFILE parameters are mentioned into the LOGFILE (but not to the screen):


;;;
Export: Release 12.2.0.1.0 - Production on Sat Apr 22 22:20:22 2017

Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
22-APR-17 22:20:24.899: ;;; **************************************************************************
22-APR-17 22:20:24.901: ;;; Parfile values:
22-APR-17 22:20:24.903: ;;; parfile: filesize=1M
22-APR-17 22:20:24.905: ;;; parfile: reuse_dumpfiles=Y
22-APR-17 22:20:24.907: ;;; parfile: dumpfile=SCOTT_%T.%U.dmp
22-APR-17 22:20:24.909: ;;; parfile: logtime=all
22-APR-17 22:20:24.911: ;;; parfile: schemas=SCOTT
22-APR-17 22:20:24.913: ;;; **************************************************************************
22-APR-17 22:20:29.654: Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@PDB01 parfile=impdp.par logfile=impdp.log
22-APR-17 22:20:35.469: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
22-APR-17 22:20:36.032: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
22-APR-17 22:20:36.407: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
22-APR-17 22:20:43.535: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
22-APR-17 22:20:44.126: Processing object type SCHEMA_EXPORT/USER
22-APR-17 22:20:44.199: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
22-APR-17 22:20:44.243: Processing object type SCHEMA_EXPORT/ROLE_GRANT
22-APR-17 22:20:44.296: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
22-APR-17 22:20:44.760: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
22-APR-17 22:20:53.620: Processing object type SCHEMA_EXPORT/TABLE/TABLE
22-APR-17 22:20:59.699: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
22-APR-17 22:21:00.712: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
22-APR-17 22:21:03.494: . . exported "SCOTT"."DEMO" 8.789 KB 14 rows
22-APR-17 22:21:03.651: . . exported "SCOTT"."EMP" 8.781 KB 14 rows
22-APR-17 22:21:03.652: . . exported "SCOTT"."DEPT" 6.031 KB 4 rows
22-APR-17 22:21:03.654: . . exported "SCOTT"."SALGRADE" 5.960 KB 5 rows
22-APR-17 22:21:03.656: . . exported "SCOTT"."BONUS" 0 KB 0 rows
22-APR-17 22:21:04.532: Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
22-APR-17 22:21:04.558: ******************************************************************************
22-APR-17 22:21:04.559: Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
22-APR-17 22:21:04.569: /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/log/46EA7EF707457B4FE0531416A8C027F2/SCOTT_20170422.01.dmp
22-APR-17 22:21:04.621: Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Apr 22 22:21:04 2017 elapsed 0 00:00:41

Now the LOGFILE shows all export information. Only the password is hidden.

DATA_PUMP_DIR

In 12.1 multitenant, you cannot use the default DATA_PUMP_DIR. It is there, but you just cannot use it implicitly or explicitly. With my PARFILE above when DIRECTORY is not mentioned I would have the following error:

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIR is invalid

This means that there is no default possible and we need to mention DIRECTORY.

But in 12.2 it worked, going to /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/log/46EA7EF707457B4FE0531416A8C027F2/ which is the default DATA_PUMP_DIR:

SYSTEM@PDB01 SQL> select * from dba_directories;
 
OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID
----- -------------- -------------- -------------
SYS TSPITR_DIROBJ_DPDIR /u90/tmp_data_restore 3
SYS PREUPGRADE_DIR /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin 1
SYS XMLDIR /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/xml 1
SYS ORA_DBMS_FCP_LOGDIR /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs 1
SYS ORA_DBMS_FCP_ADMINDIR /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin 1
SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/12.2.0/dbhome_1/ccr/state 1
SYS ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/12.2.0/dbhome_1/ccr/state 1
SYS XSDDIR /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/xml/schema 1
SYS DATA_PUMP_DIR /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/log/46EA7EF707457B4FE0531416A8C027F2 1
SYS OPATCH_INST_DIR /u01/app/oracle/product/12.2.0/dbhome_1/OPatch 1
SYS OPATCH_SCRIPT_DIR /u01/app/oracle/product/12.2.0/dbhome_1/QOpatch 1
SYS OPATCH_LOG_DIR /u01/app/oracle/product/12.2.0/dbhome_1/QOpatch 1
SYS ORACLE_BASE / 1
SYS ORACLE_HOME / 1

Of course, don’t leave it under ORACLE_HOME which is on a filesystem for binaries where you don’t want to put variable size files. But it is good to have a default.

 

Cet article Data Pump LOGTIME, DUMPFILE, PARFILE, DATA_PUMP_DIR in 12c est apparu en premier sur Blog dbi services.

SecureFiles on multi-datafiles tablespaces

When we have a tablespace with multiple datafiles, we are used to seeing the datafiles filled evenly, the extents being allocated in a round-robin fashion. In the old time, we used that to maximize performance, distributing the tables to all disks. Today, we use LVM striping, maximum Inter-Policy, ASM even distribution. And we may even use bigfile tablespaces, so that we don’t care about having multiple datafiles.

But recently, during test phase of migration, I came upon something like this:
SecureFile003

To reproduce the case, I’ve created a tablespace with 8 datafiles:

SQL> create tablespace MYTABS datafile
2 '/tmp/MYTABS01.dbf' size 1M autoextend on maxsize 100M,
3 '/tmp/MYTABS02.dbf' size 1M autoextend on maxsize 100M,
4 '/tmp/MYTABS03.dbf' size 1M autoextend on maxsize 100M,
5 '/tmp/MYTABS04.dbf' size 1M autoextend on maxsize 100M,
6 '/tmp/MYTABS05.dbf' size 1M autoextend on maxsize 100M,
7 '/tmp/MYTABS06.dbf' size 1M autoextend on maxsize 100M,
8 '/tmp/MYTABS07.dbf' size 1M autoextend on maxsize 100M,
9 '/tmp/MYTABS08.dbf' size 1M autoextend on maxsize 100M
10 /
 
Tablespace created.

SecureFiles

This was a 11g to 12c migration, with Data Pump, and a good occasion to convert all LOB to SecureFiles with the transform=lob_storage:securefile parameter. And this tablespace is the one where the LOB segments are stored. I reproduced it with:

SQL> create table MYTABLE ( x clob ) tablespace USERS
2 LOB(x) store as securefile MYLOBSEG (tablespace MYTABS disable storage in row);
 
Table created.

Then I inserted about 80MB:

SQL> insert into MYTABLE select lpad('x',100000) from xmltable('1 to 8000') ;
8000 rows created.
 
SQL> select file_id,file_name,blocks,user_blocks from dba_data_files where tablespace_name='MYTABS' order by file_id;
 
FILE_ID FILE_NAME BLOCKS USER_BLOCKS
---------- ------------------------------ ---------- -----------
61 /tmp/MYTABS01.dbf 128 120
62 /tmp/MYTABS02.dbf 128 120
63 /tmp/MYTABS03.dbf 128 120
64 /tmp/MYTABS04.dbf 9344 9336
65 /tmp/MYTABS05.dbf 128 120
66 /tmp/MYTABS06.dbf 128 120
67 /tmp/MYTABS07.dbf 128 120
68 /tmp/MYTABS08.dbf 128 120
 
8 rows selected.
 
SQL> select file_id,segment_type,sum(blocks) from dba_extents where tablespace_name='MYTABS' group by file_id,segment_type order by file_id,segment_type;
 
FILE_ID SEGMENT_TYPE SUM(BLOCKS)
---------- ------------------ -----------
64 LOBSEGMENT 9216
65 LOBINDEX 8
65 LOBSEGMENT 24

And I continued to load rows, and observed the datafiles filled to their maxsize one after the other, without numeric or alphabetical order.

SQL> select file_id,file_name,blocks,user_blocks from dba_data_files where tablespace_name='MYTABS' order by file_id;
 
FILE_ID FILE_NAME BLOCKS USER_BLOCKS
---------- ------------------------------ ---------- -----------
61 /tmp/MYTABS01.dbf 8320 8312
62 /tmp/MYTABS02.dbf 12416 12408
63 /tmp/MYTABS03.dbf 12416 12408
64 /tmp/MYTABS04.dbf 12416 12408
65 /tmp/MYTABS05.dbf 128 120
66 /tmp/MYTABS06.dbf 128 120
67 /tmp/MYTABS07.dbf 128 120
68 /tmp/MYTABS08.dbf 128 120
 
8 rows selected.
 
SQL> select file_id,segment_type,sum(blocks) from dba_extents where tablespace_name='MYTABS' group by file_id,segment_type order by file_id,segment_type;
 
FILE_ID SEGMENT_TYPE SUM(BLOCKS)
---------- ------------------ -----------
61 LOBSEGMENT 8192
62 LOBSEGMENT 12288
63 LOBSEGMENT 12288
64 LOBSEGMENT 12288
65 LOBINDEX 8
65 LOBSEGMENT 24

Here are some screenshots during this load

SecureFile001

SecureFile002

SecureFile003

BasicFiles

This occurs only with SecureFiles. With the same load into LOB stored as BasicFile I got the following distribution:
SecureFile004

Note that the inserts to BasicFile were about two times slower than the same into SecureFiles.

So what?

I don’t see any bad consequences about that, and this may even be an improvement when concurrent sessions are loading the LOBs. When the datafile picked-up looks random, a guess is that it depends on a process ID to try to distribute the concurrent load. And if you want to distribute data over multiple disks, then you should do it at a lower level. However, it is 30 years behavior that changes and it’s better to be aware of it: datafiles may reach their maxsize even when the tablespace is not full. I order to be sure that it is the expected behavior, I opened an SR with easy to reproduce testcase. Status is ‘review update’ for 9 days (SR 3-14677784041) and I’ll update this post is I get an answer.

 

Cet article SecureFiles on multi-datafiles tablespaces est apparu en premier sur Blog dbi services.

Undo Understood

It’s hard to understand all the ramifications of Oracle’s undo handling, and it’s not hard to find cases where the resulting effects are very confusing. In a recent post on the OTN database forum resulted in one response insisting that the OP was obviously updating a table with frequent commits from one session while querying it from another thereby generating a large number of undo reads in the querying session.

It’s a possible cause of the symptoms that had been described – although not the only possible cause, especially since the symptoms hadn’t been described completely. It’s actually possible to see this type of activity when there are no updates and no outstanding commits taking place at all on the target table. Unfortunately it’s quite hard to demonstrate this with a quick, simple, script in recent versions of Oracle unless you do some insanely stupid things to make the problem appear – but I know how to do “insanely stupid” in Oracle, so here we go; first some data creation:

rem
rem     Script:         undo_rec_apply_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          March 2017
rem

create table t2(v1 varchar2(100));
insert into t2 values(rpad('x',100));
commit;

create table t1
nologging
pctfree 99 pctused 1
as
with generator as (
        select
                rownum id
        from dual
        connect by
                level <= 1e4
)
select
        cast(rownum as number(8,0))                     id,
        cast(lpad(rownum,10,'0') as varchar2(10))       v1,
        cast(lpad('x',100,'x') as varchar2(100))        padding
from
        generator       v1,
        generator       v2
where
        rownum <= 8e4 -- > comment to bypass WordPress formatting issue
;

alter table t1 add constraint t1_pk primary key(id)
;

begin
        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'T1',
                method_opt       => 'for all columns size 1'
        );
end;
/

The t2 table is there as a target for a large of updates from a session other than the one demonstrating the problem. The t1 table has been defined and populated in a way that puts one row into each of 80,000 blocks (though, with ASSM and my specific tablespace definition of uniform 1MB extents, the total space is about 80,400 blocks). I’ve got a primary key declaration that allows me to pick single rows/blocks from the table if I want to.

At this point I’m going to do a lot of updates to the main table using a very inefficient strategy to emulate the type of thing that can happen on a very large table with lots of random updates and many indexes to maintain:


begin
        for i in 1..800 loop
                update t1 set v1 = upper(v1) where id = 100 * i;
                execute immediate 'alter system switch logfile';
                execute immediate 'alter system flush buffer_cache';
                commit;
                dbms_lock.sleep(0.01);
        end loop;
end;
/

set transaction read only;

I’m updating every 100th row/block in the table with single row commits, but before each commit I’m switching log files and flushing the buffer cache.

This is NOT an experiment to try on a production system, or even a development system if there are lots of busy developers or testers around – and if you’re running your dev/test in archivelog mode (which, for some of your systems you should be) you’re going to end up with a lot of archived redo logs. I have to do this switch to ensure that the updated blocks are unpinned so that they will be written to disc and flushed from the cache by the flush buffer cache. (This extreme approach would not have been necessary in earlier versions of Oracle, but the clever developers at Oracle Corp. keep adding “damage limitation” touches to the code that I have to work around to create small tests.) Because the block has been flushed from memory before the commit the session will record a “commit cleanout failures: block lost” on each commit. By the time this loop has run to completion there will be 800 blocks from the table on disc needing a “delayed block cleanout”.

Despite the extreme brute force I use in this loop, there is a further very important detail that has to be set before this test will work (at least in 11.2.0.4, which is what I’ve used in my test runs). I had to start the database with the hidden parameter _db_cache_pre_warm set to false. If I don’t have the database started with this feature disabled Oracle would notice that the buffer cache had a lot of empty space and would “pre-warm” the cache by loading a few thousand blocks from t1 as I updated one row – with the side effect that the update from the previous cycle of the loop would be cleaned out on the current cycle of the loop. If you do run this experiment, remember to reset the parameter and restart the instance when you’ve finished.

I’ve finished this chunk of code with a call to “set transaction read only” – this emulates the start of a long-running query: it captures a point in time (through the current SCN) and any queries that run in the session from now on have to be read-consistent with that point in time. After doing this I need to use a second session to do a bit of hard work – in my case the following:

execute snap_rollstats.start_snap

begin
        for i in 1..10000 loop
                update t2 set v1 = upper(v1);
                update t2 set v1 = lower(v1);
                commit;
        end loop;
end;
/

execute snap_rollstats.end_snap

The calls to the snap_rollstats package simply read v$rollstat and give me a report of the changes in the undo segment statistics over the period of the loop. I’ve executed 10,000 transactions in the interval, which was sufficient on my system to use each undo segment header at least 1,000 times and (since there are 34 transaction table slots in each undo segment header) overwrite each transaction table slot about 30 times. You can infer from these comments that I have only 10 undo segments active at the time, your system may have many more (check the number of rows in v$rollstat) so you may want to scale up that 10,000 loop count accordingly.

At this point, then, the only thing I’ve done since the start of my “long running query” is to update another table from another session. What happens when I do a simple count() from t1 that requires a full tablescan ?

alter system flush buffer_cache;

execute snap_filestat.start_snap
execute snap_my_stats.start_snap

select count(v1) from t1;

execute snap_my_stats.end_snap
execute snap_filestat.end_snap

I’ve flushed the buffer cache to get rid of any buffered undo blocks – again an unreasonable thing to do in production but a valid way of emulating the aging out of undo blocks that would take place in a production system – and surrounded my count() with a couple of packaged call to report the session stats and file I/O stats due to my query. (If you’re sharing your database then the file I/O stats will be affected by the activity of other users, of course, but in my case I had a private database.)

Here are the file stats:


--------------
Datafile Stats
--------------
file#       Reads      Blocks    Avg Size   Avg Csecs     S_Reads   Avg Csecs     M_Reads   Avg Csecs    Max      Writes      Blocks   Avg Csecs    Max
File name
-----       -----      ------    --------   ---------     -------   ---------     -------   ---------    ---      ------      ------   ---------    ---
    1          17          17       1.000        .065          17        .065           0        .000      6           0           0        .000     15
/u01/app/oracle/oradata/TEST/datafile/o1_mf_system_938s4mr3_.dbf
    3         665         665       1.000        .020         665        .020           0        .000      6           0           0        .000     15
/u01/app/oracle/oradata/TEST/datafile/o1_mf_undotbs1_938s5n46_.dbf
    5         631      80,002     126.786        .000           2        .045         629        .000      6           0           0        .000     17
/u01/app/oracle/oradata/TEST/datafile/o1_mf_test_8k__cz1w7tz1_.dbf

As expected I’ve done a number of multiblock reads of my data tablespace for a total of roughly 80,000 blocks read. What you may not have expected is that I’ve done 665 single block reads of the undo tablespace.

What have I been doing with all those undo blocks ? Check the session stats:


Session stats
-------------
Name                                                                     Value
----                                                                     -----
transaction tables consistent reads - undo records applied              10,014
transaction tables consistent read rollbacks                                10

We’ve been reading undo blocks so that we can create read-consistent copies of the 10 undo segment headers that were active in my instance. We haven’t (and you’ll have to trust me on this, I can’t show you the stats that aren’t there!) reported any “data blocks consistent reads – undo records applied”.

If you want to see a detailed explanation of what has happened you’ll need to read Oracle Core (UK source), chapter 3 (and possibly chapter 2 to warm yourself up for the topic). In outline the following type of thing happens:

  • Oracle gets to the first block updated in t1 and sees that there’s an ITL (interested transaction list) entry that hasn’t been marked as committed (we flushed the block from memory before the commit cleanout could take place so the relevant transaction is, apparently, still running and the row is still marked as locked).
  • Let’s say the ITL entry says the transaction was for undo segment 34, transaction table slot 11, sequence 999. Oracle reads the undo segment header block for undo segment 34 and checks transaction table slot 11, which is now at sequence 1032. Oracle can infer from this that the transaction that updated the table has committed – but can’t yet know whether it committed before or after the start of our “long running query”.
  • Somehow Oracle has to get slot 11 back to sequence 999 so that it can check the commit SCN recorded in the slot at that sequence number. This is where we see “undo records applied” to make the “transaction table read consistent”. It can do this because the undo segment header has a “transaction control” section in it that records some details of the most recent transaction started in that segment. When a transaction starts it updates this information, but saves the old version of the transaction control and the previous version of its transaction table slot in its first undo record, consequently Oracle can clone the undo segment header block, identify the most recent transaction, find its first undo record and apply it to unwind the transaction table information. As it does so it has also wound the transaction control section backwards one step, so it can use that (older) version to go back another step … and so on, until it takes the cloned undo segment header so far back that it takes our transaction table slot back to sequence 999 – and the job is done, we can now check the actual commit SCN.  (Or, if we’re unlucky, we might receive an ORA-01555 before we get there)

So – no changes to the t1 table during the query, but lots of undo records read because OTHER tables have been changing.

Footnote:

In my example the tablescan used direct path reads – so the blocks that went through delayed block cleanout were in private memory, which means they weren’t in the buffer cache and didn’t get written out to disc. When I flushed the buffer cache (again to emulate aging out of undo blocks etc.) and repeated the tablescan Oracle had to go through all that work of creating read consistent transaction tables all over again.

Footnote 2:

Despite the grand claim implied by the title I hope everyone realises that this is just one little detail of how undo handling can interfere with their expectations of performance.

 

 

The Chicken Before the Egg of Cloud Migrations

style="display:inline-block;width:320px;height:100px"
data-ad-client="ca-pub-5103295461547706"
data-ad-slot="5182487270">

For over a year I’ve been researching cloud migration best practices.  Consistently there was one red flag that trips me that I’m viewing recommended migration paths.  No matter what you read, just about all of them include the following high level steps:

https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/04/Screen-Shot-2... 300w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/04/Screen-Shot-2... 768w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/04/Screen-Shot-2... 1174w" sizes="(max-width: 477px) 100vw, 477px" data-recalc-dims="1" />

As we can see from above, the scope of the project is identified, requirements laid out and a project team is allocated.

The next step in the project is to choose one or more clouds, choose the first environments to test out in the cloud, along with security concerns and application limitations.  DBAs are tested repeatedly as they continue to try to keep up with the demand of refreshing or ensuring the cloud environments are able to keep in sync with on-prem and the cycle continues until a cutover date is issued.  The migration go or no-go occurs and the either non-production or all of the environment is migrated to the cloud.

As someone who works for Delphix, I focus on the point of failure where DBAs can’t keep up with full clones and data refreshes in cloud migrations or development and testing aren’t able to complete the necessary steps that could be if the company was using virtualization.  From a security standpoint, I am concerned with how few companies aren’t investing in masking with the sheer quantity of breeches in the news, but as a DBA, there is a whole different scenario that really makes me question the steps that many companies are using to migrate to the cloud.

Now here’s where they loose me every time- the last step in most cloud migration plans is to optimize.

I’m troubled by optimization being viewed as the step you take AFTER you migrate to the cloud.  Yes, I believe that there will undoubtedly be unknowns that no one can take into consideration before the physical migration to a cloud environment, but to take databases, “as is” when an abundance of performance data is already known about the database that could and will impact performance, seems to be inviting unwarranted risk and business impact.

So here’s my question to those investing in a cloud migration or have already migrated to the cloud-  Did you streamline and optimize your database/applications BEFORE migrating to the cloud or AFTER?

 

 



Tags:  , ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [The Chicken Before the Egg of Cloud Migrations], All Right Reserved. 2017.

The post The Chicken Before the Egg of Cloud Migrations appeared first on DBA Kevlar.

Transportable Tablespace–part 2

I did a little demo of sharing a tablespace between two databases a few days back – you can see the details here or by just scrolling down Smile if you’re on the home page.

To avoid clouding the demonstration I omitted something in the details, but I’ll share that now, because it could be critical depending on how you currently use transportable tablespaces.

Let me do the most basic of examples now, transporting a tablespace from one database to another:

First, we make our tablespace read only, and Datapump export out the metadata


SQL> alter tablespace DEMO read only;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

C:\>expdp transport_tablespaces=DEMO directory=TEMP dumpfile=tts.dmp

Export: Release 12.1.0.2.0 - Production on Tue Apr 18 14:16:06 2017

Copyright (c) 1982, 2016, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  /******** AS SYSDBA transport_tablespaces=DEMO directory=TEMP dumpfile=tts.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
  C:\TEMP\TTS.DMP
******************************************************************************
Datafiles required for transportable tablespace DEMO:
  C:\ORACLE\ORADATA\NP12\DEMO.DBF
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Tue Apr 18 14:17:03 2017 elapsed 0 00:00:50

Then, I copy the datafile(s) to the target location and Datapump import the metadata.


C:\>copy C:\oracle\oradata\np12\DEMO.DBF C:\oracle\oradata\db122\DEMO.DBF
        1 file(s) copied.

C:\>impdp transport_datafiles=C:\oracle\oradata\db122\DEMO.DBF directory=TEMP dumpfile=tts.dmp

Import: Release 12.2.0.1.0 - Production on Tue Apr 18 14:17:27 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  /******** AS SYSDBA transport_datafiles=C:\oracle\oradata\db122\DEMO.DBF directory=TEMP dumpfile=tts.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Tue Apr 18 14:17:46 2017 elapsed 0 00:00:15

And voila, there is my tablespace in the target database…


C:\>sql122

SQL*Plus: Release 12.2.0.1.0 Production on Tue Apr 18 14:19:08 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Tue Apr 18 2017 14:14:19 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production



SQL> select * from dba_tablespaces
  2  where tablespace_name = 'DEMO'
  3  @pr
==============================
TABLESPACE_NAME               : DEMO
BLOCK_SIZE                    : 8192
INITIAL_EXTENT                : 1048576
NEXT_EXTENT                   : 1048576
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
MAX_SIZE                      : 2147483645
PCT_INCREASE                  : 0
MIN_EXTLEN                    : 1048576
STATUS                        : READ ONLY
CONTENTS                      : PERMANENT
LOGGING                       : LOGGING
FORCE_LOGGING                 : NO
EXTENT_MANAGEMENT             : LOCAL
ALLOCATION_TYPE               : UNIFORM
PLUGGED_IN                    : YES
SEGMENT_SPACE_MANAGEMENT      : AUTO
DEF_TAB_COMPRESSION           : DISABLED
RETENTION                     : NOT APPLY
BIGFILE                       : NO
PREDICATE_EVALUATION          : HOST
ENCRYPTED                     : NO
COMPRESS_FOR                  :
DEF_INMEMORY                  : DISABLED
DEF_INMEMORY_PRIORITY         :
DEF_INMEMORY_DISTRIBUTE       :
DEF_INMEMORY_COMPRESSION      :
DEF_INMEMORY_DUPLICATE        :
SHARED                        : SHARED
DEF_INDEX_COMPRESSION         : DISABLED
INDEX_COMPRESS_FOR            :
DEF_CELLMEMORY                :
DEF_INMEMORY_SERVICE          :
DEF_INMEMORY_SERVICE_NAME     :
LOST_WRITE_PROTECT            : OFF
CHUNK_TABLESPACE              : N

PL/SQL procedure successfully completed.

“Gee thanks, Connor” I can hear you muttering. A demonstration of the flippin’ obvious!

But there is one thing that is not apparent from the export or import logs. Let’s take a squizz at the database alert log for the target, that is, the database we imported the tablespace into.


DW00 started with pid=69, OS id=632, wid=1, job SYS.SYS_IMPORT_TRANSPORTABLE_01
2017-04-18T14:17:34.208631+08:00
Plug in tablespace DEMO with datafile
  'C:\oracle\oradata\db122\DEMO.DBF'
2017-04-18T14:17:46.199645+08:00
ALTER TABLESPACE "DEMO" READ WRITE
Completed: ALTER TABLESPACE "DEMO" READ WRITE
2017-04-18T14:17:46.665512+08:00
ALTER TABLESPACE "DEMO" READ ONLY
Completed: ALTER TABLESPACE "DEMO" READ ONLY

That is a change in 12c. Whilst our imported tablespace ends up as read only as it has always done, during the import process, there was a small window where the tablespace was READ WRITE. This is needed to make some metadata corrections to the tablespace on the way in.

So if you do intend to share tablespaces between databases, that is, share a single copy of the file, make sure take some precautions. On my Windows laptop, standard Windows file locking prohibited me from causing any damage to my source datafile, but on other platforms you might to set those files to read only at the OS level just in case. Of course, you’ll then see a warning during the Datapump import saying that the momentary change to read/write could not be done, but that is not a critical problem.  The transport will still complete.

SQLcl on Bash on Ubuntu on Windows

I’m running my laptop on Windows, which may sound weird, but Linux is unfortunately not an option when you exchange Microsoft Word documents, manage your e-mails and calendar with Outlook and present with Powerpoint using dual screen (I want to share on the beamer only the slides or demo screen, not my whole desktop). However, I have 3 ways to enjoy GNU/Linux: Cygwin to operate on my laptop, VirtualBox to run Linux hosts, and Cloud services when free trials are available.

Now that Windows 10 has a Linux subsystem, I’ll try it to see if I still need Cygwin.
In a summary, I’ll still use Cygwin, but may prefer this Linux subsystem to run SQLcl, the SQL Developer command line, from my laptop.

Bash on Ubuntu on Windows

In this post I’ll detail what I had to setup to get the following:
CaptureWin10bash000
Bash on Windows 10 is available for several months, but with no interaction with the Windows system except accessing to the filesystems. I didn’t try that. This month, Microsoft has released a new update, called ‘Creator Update’ for whatever reason.

Creator Update

You will probably have no choice to update to ‘Creator Update’ soon but for the moment you have to download Windows10Upgrade9252.exe from https://www.microsoft.com/en-us/software-download/windows10

Windows Subsystem for Linux

You enable the feature from Control Panel -> Programs and Features -> Turn Windows features on and off:
CaptureWin10bash002

This requires a reboot. Windows is not yet an OS where you can install or enable features without closing everything. But at least in Windows 10 the reboot is very fast.

Developer mode

This is a beta feature and requires to enable developer mode:
CaptureWin10bash003

You do that on the Setup -> Update and Security -> For developers:

CaptureWin10bash001

Bash

Now, when you run it (type Bash in the start menu) it installs a subset of Ubuntu (downloaded from the web):
CaptureWin10bash005
It asks for a user and password. You will need the password to sudo to root.
You are in Windows/System32 here, which is ugly, so better exit and run again ‘Bash on Ubuntu on Windows’.

HOME

All my customization (.bash_profile .bashrc .vimrc .tmux.conf .ssh/config … ) is in my cygwin environment and I want to share it for the time I’ll run both Cygwin and Bash on Ubuntu on Windows. For this, I sudo and change the entry in /etc/passwd to have my home where I have my cygwin.home:
fpa:x:1000:1000:"",,,:/mnt/d/Dropbox/cygwin-home/:/bin/bash

Mount

Here are the mount points I have on Cygwin
$ mount
C:/cygwin64/bin on /usr/bin type ntfs (binary,auto)
C:/cygwin64/lib on /usr/lib type ntfs (binary,auto)
C:/cygwin64 on / type ntfs (binary,auto)
C: on /cygdrive/c type ntfs (binary,posix=0,user,noumount,auto)
D: on /cygdrive/d type ntfs (binary,posix=0,user,noumount,auto)

My C: and D: windows drives are mounted in /cygdrive

Here are the mounts I have on the Windows Subsystem for Linux:
root@dell-fpa:/mnt# mount
rootfs on / type lxfs (rw,noatime)
data on /data type lxfs (rw,noatime)
cache on /cache type lxfs (rw,noatime)
mnt on /mnt type lxfs (rw,noatime)
sysfs on /sys type sysfs (rw,nosuid,nodev,noexec,noatime)
proc on /proc type proc (rw,nosuid,nodev,noexec,noatime)
none on /dev type tmpfs (rw,noatime,mode=755)
devpts on /dev/pts type devpts (rw,nosuid,noexec,noatime)
none on /run type tmpfs (rw,nosuid,noexec,noatime,mode=755)
none on /run/lock type tmpfs (rw,nosuid,nodev,noexec,noatime)
none on /run/shm type tmpfs (rw,nosuid,nodev,noatime)
none on /run/user type tmpfs (rw,nosuid,nodev,noexec,noatime,mode=755)
C: on /mnt/c type drvfs (rw,noatime)
D: on /mnt/d type drvfs (rw,noatime)
root on /root type lxfs (rw,noatime)
home on /home type lxfs (rw,noatime)
binfmt_misc on /proc/sys/fs/binfmt_misc type binfmt_misc (rw,noatime)

Because I have scripts and configuration files that mention /cygdrive, I’ve created symbolic links for them:

fpa@dell-fpa:/mnt$ sudo su
[sudo] password for fpa:
root@dell-fpa:/mnt# mkdir /cygdrive
root@dell-fpa:/# ln -s /mnt/c /cygdrive/c
root@dell-fpa:/# ln -s /mnt/d /cygdrive/D

chmod

The first thin I do from my bash shell is to ssh to other hosts:


fpa@dell-fpa:/mnt/c/Users/fpa$ ssh 192.168.78.104
Bad owner or permissions on /mnt/d/Dropbox/cygwin-home//.ssh/config

Ok, permissions of .ssh was set from cygwin, let’s try it from Bash On Ubuntu on Linux:

fpa@dell-fpa:/mnt/c/Users/fpa$ chmod 644 /mnt/d/Dropbox/cygwin-home//.ssh/config
fpa@dell-fpa:/mnt/c/Users/fpa$ ls -ld /mnt/d/Dropbox/cygwin-home//.ssh/config
-rw-rw-rw- 1 root root 5181 Mar 5 16:56 /mnt/d/Dropbox/cygwin-home//.ssh/config

This is not what I want. With 644 I expect -rw-r–r–

Let’s try 444:

fpa@dell-fpa:/mnt/c/Users/fpa$ chmod 444 /mnt/d/Dropbox/cygwin-home//.ssh/config
fpa@dell-fpa:/mnt/c/Users/fpa$ ls -ld /mnt/d/Dropbox/cygwin-home//.ssh/config
-r--r--r-- 1 root root 5181 Mar 5 16:56 /mnt/d/Dropbox/cygwin-home//.ssh/config
fpa@dell-fpa:/mnt/c/Users/fpa$ ssh 192.168.78.104
Last login: Sun Apr 16 15:18:07 2017 from 192.168.78.1
...

Ok, this works but there’s a problem. It seems that the Bash On Ubuntu on Linux doesn’t allow to set permissions differently for user, group and others.

SQLcl

The second thing I do from bash in my laptop is to connect to databases with SQLcl. For Cygwin I had an alias that run the sql.bat script because Cygwin can run .bat files. When I run SQLcl from Cygwin, I run the Windows JDK. This doesn’t work in Bash on Ubuntu on Windows because we are in a Linux subsystem. But we don’t need to because SQLcl can be run directly from the sql bash script, calling the Linux JDK from the Linux subsystem. There’s only one thing to do: download the Linux JDK and set JAVA_HOME to the directory.

In my .bashrc I have the following to set the ‘sql’ alias depending on which environment I am


if [[ $(uname -a) =~ CYGWIN ]]
then
alias sql='/cygdrive/D/Soft/sqlcl/bin/sql.bat'
else
alias sql='JAVA_HOME=/mnt/d/Soft/jdk1.8.0-Linux /cygdrive/D/Soft/sqlcl/bin/sql'
fi

What I observe here is that it is much faster (or less slower…) to start the JVM from the Linux subsystem.
Here 4 seconds to start SQLcl, connect and exit:

fpa@dell-fpa:/tmp$ time sql sys/oracle@//192.168.78.104/pdb1 as sysdba <<
 
real 0m4.684s
user 0m3.750s
sys 0m2.484s
 
fpa@dell-fpa:/tmp$ uname -a
Linux dell-fpa 4.4.0-43-Microsoft #1-Microsoft Wed Dec 31 14:42:53 PST 2014 x86_64 x86_64 x86_64 GNU/Linux

Here the same from Windows (Cygwin to time – but it’s running on Windows):

$ time sql sys/oracle@//192.168.78.104/pdb1 as sysdba <<
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
real 0m16.261s
user 0m0.000s
sys 0m0.015s
 
fpa@dell-fpa ~
$ uname -a
CYGWIN_NT-10.0 dell-fpa 2.7.0(0.306/5/3) 2017-02-12 13:18 x86_64 Cygwin

So what?

The Linux subsystem on Windows is not yet ready. The only thing I proved here is that it is faster to start a Java application from Linux, but for this I always have a VirtualBox VM started on my laptop, and this is where it is faster to run it, and have a real Linux system.

 

Cet article SQLcl on Bash on Ubuntu on Windows est apparu en premier sur Blog dbi services.

SQL Monitoring, Flamegraph and Execution Plan Temperature 2.0

Two of the things that I like the most about SQL Monitoring reports are the ability to quickly spot where in the execution plan the time is spent (Activity% column, thank you ASH) and the fact you can collapse part of the plan. Too bad the two don’t “work” together meaning if you collapse a part of the plan the Activity% is not rolled up at the collapsed level. I understand why it works that way (it might be confusing otherwise) but I’d still like to be able to collapsed a node and get a “subtree Activity%” so I know if that subtree is something I should be worry about or not (kind of…).

Pause that for a second, Flamegraph on the other hand is really good at showing  how much (in %) a “subtree” (function + its callees) accounts overall, Luca Canali did an (as usual) awesome job here with something similar, but a bit more sophisticated than just execution plan. Unfortunately the fact the elements are not sorted on time (I think most “Oracle people” tend to expect time on the X-axis) combined with the kind of random color used for each bar make this representation a little less intuitive than I’d like it to be for execution plans (don’t get me wrong, I still love this representation a lot!).

SQLd360 represents execution plans as tree since one of the first releases and for over a year the color of the nodes represent a heatmap of where time has been spent (same as Activity% in SQL Monitoring), starting from a couple releases ago there are a couple improvements I hope (like to hear you feedback on this) fixed the shortcomings of both SQL Monitoring and Flamegraph when it comes to digest execution plans.
Each step in the execution plan can be collapsed (this was since day 1) and now its color will change to represent the color the node would have had if all the time the SQL spent on the node itself plus its child steps was spent on the node itself, on expand the colors go back to original.
Also when mouse overing the node a new piece of info will show up in the popup, a “subtree impact” that represent exactly what % of time the node plus its children account for.

node.pnghttps://mauropaganodotcom.files.wordpress.com/2017/04/node.png?w=150 150w, https://mauropaganodotcom.files.wordpress.com/2017/04/node.png?w=300 300w" sizes="(max-width: 461px) 100vw, 461px" />

For example from the picture above step 16 accounted for 15.52% of the time, while all the subtree under the node (including it too) accounted for 39.6% of the time. Expanding the node the color will go back to a “lighter shade orange”.

I think one of the main advantage of this approach will be the ability to consume large execution plans quickly, collapsing large parts of them and focusing on just what matters, how those parts of the plan interacts with each others, etc etc.

As usual feedbacks, corrections, ideas are MORE than welcome! </p />
</p></div>

    	  	<div class=

Character selectivity

A recent OTN posting asked how the optimizer dealt with “like” predicates for character types quoting the DDL and a query that I had published some time ago in a presentation I had done with Kyle Hailey. I thought that I had already given a detailed answer somewhere on my blog (or even in the presentation) but found that I couldn’t track down the necessary working, so here’s a repeat of the question and a full explanation of the working.

The query is very simple, and the optimizer’s arithmetic takes an “obvious” strategy in the arithmetic. Here’s the sample query, with the equiavalent query that we can use to do the calculation:


select * from t1 where alpha_06 like 'mm%';

select * from t1 where alpha_06 >= 'mm' and alpha_06 < 'mn';

Ignoring the possible pain of the EBCDIC character set and multi-byte national-language character sets with “strange” collation orders, it should be reasonably easy to see that ‘mn’ is the first string in alphabetical order that fails to match ‘mm%’. With that thought in mind we can apply the standard arithmetic for range-based predicates assuming, to stick with the easy example, that there are no histograms involved. For a range closed at one end and and open at the other the selectivity is:


( ( 'mn' - 'mm') / (high_value - low_value) ) + 1/num_distinct

The tricky bits, of course, are how you subtract ‘mm’ from ‘mn’ and how you use the values stored in the low_value and high_value columns of view user_tab_cols. So let’s generate the orginal data set and see where we go (running on 12c, and eliminating redundant bits from the original presentation):


rem
rem     Script:         selectivity_like_char.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Sep 2013
rem

execute dbms_random.seed(0)

create table t1 nologging as
with generator as (
        select rownum id
        from dual
        connect by rownum <= 1000
)
select
        cast(dbms_random.string('l',6) as char(6))      alpha_06
from
        generator,
        generator
where
        rownum <= 1e6 -- > comment to avoid WordPress formatting issue
;

execute dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1')

column low_value  format a32
column high_value format a32

select
        column_name,
        num_distinct,
        density,
        low_value,
        high_value
from
        user_tab_cols
where
        table_name = 'T1'
order by
        column_name
;

select min(alpha_06), max(alpha_06) from t1;

set autotrace traceonly explain

select
        *
from
        t1
where
        alpha_06 like 'mm%'
;

set autotrace off

It will probably take a couple of minutes to generate the data – it’s 1M random strings, lower-case, 6 characters fixed – and will take up about 12MB of space. Here are the results from the stats and min/max queries, with the execution plan for the query we are testing:


COLUMN_NAME          NUM_DISTINCT    DENSITY LOW_VALUE                  HIGH_VALUE
-------------------- ------------ ---------- -------------------------- --------------------------
ALPHA_06                  1000000    .000001 616161616E72               7A7A7A78747A


MIN(AL MAX(AL
------ ------
aaaanr zzzxtz


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   157 |  1099 |   265  (20)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   157 |  1099 |   265  (20)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ALPHA_06" LIKE 'mm%')


Given that there are power(26,6) = 308,915,776 different combinations available for lower-case strings of 6 charactgers it’s not too surprising that Oracle generated 1M different strings, nor is it particularly surprising that the lowest value string started with ‘aaa’ and the highest with ‘zzz’.

So how do we get 157 as the cardinality for the query or, to put it another way, how do we get 0.000157 as the selectivity of the predicate. We need to refer to a note I wrote a few years ago to help us on our way (with a little caveat due to a change that appeared in 11.2.0.4) – what number would Oracle use to represent ‘mm’ and the other three strings we need to work with ?

According to the rules supplied (and adjusted in later versions) we have to:

  1. pad the strings with ASCII nulls (zeros) up to 15 bytes
  2. treat the results as a hexadecimal number and convert to decimal
  3. round off the last 21 decimal digits

We can model this in SQL with a statement like:


SQL> column dec_value format 999,999,999,999,999,999,999,999,999,999,999,999
SQL> select round(to_number(utl_raw.cast_to_raw(rpad('aaaanr',15,chr(0))),'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'),-21) dec_val from dual;

DEC_VAL
------------------------------------------------
505,627,904,294,763,000,000,000,000,000,000,000

1 row selected.

As an alternative, or possibly a cross-check, I created a table with a varchar2(6) column, inserted the four values I was interested in and created a histogram of 4 buckets on the column (there’s a suitable little demo at this URL) and got the following endpoint values:


ENDPOINT_NUMBER                                   ENDPOINT_VALUE
--------------- ------------------------------------------------
              1  505,627,904,294,763,000,000,000,000,000,000,000
              2  568,171,140,227,094,000,000,000,000,000,000,000
              3  568,191,422,636,698,000,000,000,000,000,000,000
              4  635,944,373,827,734,000,000,000,000,000,000,000

Once we’ve got these numbers we can slot them into the standard formula (not forgetting the 1/1,000,000 for the closed end of the predicate) – and to save typing I’m going to factor out 10^21 across the board in the division:

Selectivity = (568,191,422,636,698 – 568,171,140,227,094) / (635,944,373,827,734 – 505,627,904,294,763) + 1/1,000,000

Selectivity = 20,282,409,604 / 130,316,469,532,971 + 1/1,000,000

Selectivity = 0.00015564 + 0.000001 = 0.00015664

From which the cardinality = (selectivity * num_rows) = 156.64, which rounds up to 157. Q.E.D.

Sharing a tablespace between 2 databases

I was reading an interesting discussion today about multiple databases each containing large amounts of read-only data.  If that read-only data is common, then it would make sense to have a single copy of that data and have both databases share it.

Well, as long as you can isolate that data into its own tablespace, then you can do that easily with Oracle by transporting the metadata between two databases and leaving the files in place.

Here’s an example

Source database


SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> create tablespace i_am_on_121 datafile 'C:\oracle\oradata\tts\my_tspace' size 50m;

Tablespace created.

SQL> create table t tablespace i_am_on_121 as select * from dba_objects;

Table created.

SQL> alter tablespace i_am_on_121 read only;

Tablespace altered.

C:\>expdp transport_tablespaces=i_am_on_121 directory=TEMP dumpfile=tts.dmp

Export: Release 12.1.0.2.0 - Production on Fri Apr 14 08:50:24 2017

Copyright (c) 1982, 2016, Oracle and/or its affiliates.  All rights reserved.

Username: mcdonac/*****

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "MCDONAC"."SYS_EXPORT_TRANSPORTABLE_01":  mcdonac/******** transport_tablespaces=i_am_on_121 directory=TEMP dumpfile=tts.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "MCDONAC"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MCDONAC.SYS_EXPORT_TRANSPORTABLE_01 is:
  C:\TEMP\TTS.DMP
******************************************************************************
Datafiles required for transportable tablespace I_AM_ON_121:
  C:\ORACLE\ORADATA\TTS\MY_TSPACE
Job "MCDONAC"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Fri Apr 14 08:51:16 2017 elapsed 0 00:00:47

and then we import it into a different database (and this one even is a different version!).

Target database


C:\Users\hamcdc>impdp transport_datafiles=C:\oracle\oradata\tts\my_tspace directory=TEMP dumpfile=tts.dmp

Import: Release 12.2.0.1.0 - Production on Fri Apr 14 08:51:28 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Username: mcdonac/*****

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "MCDONAC"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "MCDONAC"."SYS_IMPORT_TRANSPORTABLE_01":  mcdonac/******** transport_datafiles=C:\oracle\oradata\tts\my_tspace directory=TEMP dumpfile=tts.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER


SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE    12.2.0.1.0      Production
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

SQL> select count(*) from t;

  COUNT(*)
----------
     92934

SQL> select * from dba_tablespaces
  2  where tablespace_name = 'I_AM_ON_121'
  3  @pr
==============================
TABLESPACE_NAME               : I_AM_ON_121
BLOCK_SIZE                    : 8192
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   :
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
MAX_SIZE                      : 2147483645
PCT_INCREASE                  :
MIN_EXTLEN                    : 65536
STATUS                        : READ ONLY
CONTENTS                      : PERMANENT
LOGGING                       : LOGGING
FORCE_LOGGING                 : NO
EXTENT_MANAGEMENT             : LOCAL
ALLOCATION_TYPE               : SYSTEM
PLUGGED_IN                    : YES
SEGMENT_SPACE_MANAGEMENT      : AUTO
DEF_TAB_COMPRESSION           : DISABLED
RETENTION                     : NOT APPLY
BIGFILE                       : NO
PREDICATE_EVALUATION          : HOST
ENCRYPTED                     : NO
COMPRESS_FOR                  :
DEF_INMEMORY                  : DISABLED
DEF_INMEMORY_PRIORITY         :
DEF_INMEMORY_DISTRIBUTE       :
DEF_INMEMORY_COMPRESSION      :
DEF_INMEMORY_DUPLICATE        :
SHARED                        : SHARED
DEF_INDEX_COMPRESSION         : DISABLED
INDEX_COMPRESS_FOR            :
DEF_CELLMEMORY                :
DEF_INMEMORY_SERVICE          :
DEF_INMEMORY_SERVICE_NAME     :
LOST_WRITE_PROTECT            : OFF
CHUNK_TABLESPACE              : N

PL/SQL procedure successfully completed.

Conference Networking- Tips to Doing it Right

I was in a COE, (Center of Excellence) meeting yesterday and someone asked me, “Kellyn, is your blog correct?  Are you really speaking at a Blockchain event??”  Yeah, I’m all over the technical map these days and you know what?

I love the variety of technology, the diversity of attendance and the differences in how the conferences are managed.  Now that last one might seem odd and you might think that they’d all be similar, but its surprising how different they really are.

Getting to Know You

Today I’m going to talk about an aspect of conferences that’s very near to my heart, which is networking via events.  For women in technology, there are some unique challenges for us when it comes to networking.  Men have concerns about approaching women to network- such as fearful of accusations of inappropriate interaction and women have the challenge that a lot of networking opportunities occur outside of the workplace and in social situations that we may not be comfortable in.  No matter who you are, no matter what your intentions, there’s a lot of wariness and in the end, women often just lose out when it comes to building their network.  I’ve been able to breach this pretty successfully, but I have seen where it’s backfired and have found myself on more than one occasion defending both genders who’ve ended up on the losing side of the situation.

With that said, conferences and other professional events can assist with helping us geeks build our networks and it’s not all about networking events.  I noticed a while back that the SQL Server community appeared to be more networked among their members.  I believe part of this is due to the long history of their event software and some of its features.

Using the SQL Pass website, specifically the local user group event management software-  notice that its all centralized.  Unlike the significantly independent Oracle user groups, SQL Server user groups are able to use a centralized repository for their event management, speaker portal, scheduling, etc.  It’s not to say that there aren’t any events outside of Pass Summit and SQL Saturdays, there’s actually a ton, but this was the portal for the regional user groups, creating the spoke that bridged out to the larger community.

Centralized System

Outside of submitting my abstract proposals to as many SQL Saturdays worldwide from one portal, I also can maintain one speaker biography, information about my blog, Twitter, Linkedin and other social media in this one location.

The second benefit of this simplicity, is that these biographies and profiles “feed” the conference schedules and event sites.  You have a central location for management, but hundreds of event sites where different members can connect.  After abstracts have been approved and the schedule built, I can easily go into an event’s schedule and click on each speaker biography and choose to connect with anyone listed who has entered their social media information in their global profile.

https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/04/Screen-Shot-2... 300w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/04/Screen-Shot-2... 768w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/04/Screen-Shot-2... 1200w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/04/Screen-Shot-2... 1800w" sizes="(max-width: 600px) 100vw, 600px" data-recalc-dims="1" />

Using my profile as an example, you’ll notice the social media icons under my title are available with a simple click of the mouse:

https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/04/Screen-Shot-2... 300w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/04/Screen-Shot-2... 768w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/04/Screen-Shot-2... 1200w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/04/Screen-Shot-2... 1800w" sizes="(max-width: 600px) 100vw, 600px" data-recalc-dims="1" />

This gives me both an easy way to network with my fellow speakers, but also an excuse to network with them!  I can click on each one of the social media buttons and choose to follow each of the speakers on Twitter and connect with them on Linkedin.  I send a note with the Linkedin connection telling the speaker that we’re both speaking at the event and due to this, I’d like to add them to my network.

As you can join as many regional and virtual user groups as you like, (and your Pass membership is free) I joined the three in Colorado, (Denver, Boulder and Colorado Springs.) Each one of those offers the ability to also connect with the board members using a similar method, (now going to use Todd and David as my examples from the Denver SQL Server user group.)

https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/04/Screen-Shot-2... 300w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/04/Screen-Shot-2... 768w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/04/Screen-Shot-2... 1596w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/04/Screen-Shot-2... 1200w" sizes="(max-width: 378px) 100vw, 378px" data-recalc-dims="1" />

The Oracle user groups have embraced adding twitter links to most speaker bios and some board groups, but I know for RMOUG, many still hesitated or aren’t using social media to the extent they could.  I can’t stress enough how impressed I am when I see events incorporate Linkedin and Twitter into their speaker and management profiles, knowing the value they bring to technical careers, networks and the community.

New Kids on the Block

Although the SQL Server community is a good example, they aren’t the only ones.  I’m also speaking at new events on emergent technologies, like Data Platforms 2017.  I’ll be polite and expose my own profile page, but I’m told I’m easy to find in the sea of male speakers… </p />
</p></div></div>

    	  	<div class=