April 13th, 2017 by dbakevlar

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.

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:

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.)

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… 🙂 Along with my picture, bio and session information, there are links to my social media connections, allowing people to connect with me:

Yes, the Bizzabo software, (same software package that RMOUG will be using for our 2018 conference, along with a few other Oracle events this coming year) is aesthetically appealing, but more importantly, it incorporates important networking features that in the past just weren’t as essential as they are in today’s business world.

I first learned the networking tactic of connecting with people I was speaking with from Jeff Smith and I think its a great skill that everyone should take advantage of, no matter if you’re speaking or just attending.  For women, I think it’s essential to your career to take advantage of opportunities to network outside of the traditional ways we’ve been taught in the past and this is just one more way to work around that glass ceiling.

 

 

Posted in DBA Life, Oracle, SQLServer Tagged with: , , ,

April 12th, 2017 by dbakevlar

I recently switched to a Mac after decades use with PCs.  I loved my Surface Pro 4 and still do, but that I was providing content for those I thought would be on Macs, it seemed like a good idea at the time.  I didn’t realize at the time I’d be doing as many SQL Server conferences as Oracle in my next role with the Delphix… 🙂

With this change, I found myself limited to VMs running on my Mac with SQL Server, then I was working with Azure and it seemed like a lot of extra “weight” to just have access to a few tools.  I figured I wasn’t the only one and did some research, locating SQLPro for MSSQL from Hankinsoft.  Its an easy to configure and use, 12Mb query interface, (also available from the App Store) that was created for SQL Server users that find themselves on Mac.

If you’re using Azure, you simply need to update our firewall rules to allow access for your local IP address and connection to the Azure SQL database is simple after this update, (and will need to be updated each time, if you’re like me and change locations, (and IP addresses each time.))

You can collect the information for your Azure database from the Azure administration Console, under Database and Overview:

This offers a very robust, full featured and comparable tool to Oracle’s SQL Developer for those that want to work with SQL Server databases but are on a Mac.  I didn’t go into the features, but those, I leave to you right now to discover… 🙂

 

Posted in SQLServer Tagged with: ,

April 11th, 2017 by dbakevlar

I’m itching to dig more into the SQL Server 2016 optimizer enhancements, but I’m going to complete my comparison of indices between the two platforms before I get myself into further trouble with my favorite area of database technology.

<–This is sooo me.

Index Organized Tables

Index Organized Tables, (IOT) are just another variation of a primary b-tree index, but unlike a standard table with an index simply enforcing uniqueness, the index IS the table.  The data is arranged in order to improve performance and in a clustered primary key state.

This is the closest to a clustered index in SQL Server that Oracle will ever get, so it makes sense that a comparison in performance and fragmentation is the next step after I’ve performed standard index and primary key index comparisons to Oracle.

Let’s create a new copy of our Oracle objects, but this time, update to an Index Organized Table:

CREATE TABLE ora_tst_iot(
        c1 NUMBER, 
        c2 varchar2(255),
        CREATEDATE timestamp DEFAULT CURRENT_TIMESTAMP,
        CONSTRAINT pk_ora_iot PRIMARY KEY (c1))
    ORGANIZATION INDEX 
    TABLESPACE users
    PCTTHRESHOLD 20
    OVERFLOW TABLESPACE users;

CREATE SEQUENCE C1_iot_SEQ START WITH 1;

CREATE OR REPLACE TRIGGER C1_iot_BIR 
BEFORE INSERT ON ORA_TST_IOT
FOR EACH ROW
BEGIN
  SELECT C1_iot_SEQ.NEXTVAL
  INTO   :new.C1
  FROM   DUAL;
END;
/

The PCTThreshold can be anywhere between 0-50, but I chose 20 for this example.  I didn’t add any compression, as C1 is a simple sequence which won’t have the ability to take advantage of compression and I also added the additional support objects of a sequence and a trigger, just as I did for the previous test on the Oracle table.

First Data Load

Now we’ll insert the rows from ORA_INDEX_TST into ORA_TST_IOT

SQL> insert into ora_tst_iot(c2) select c2 from ora_index_tst;

995830 rows created.

Elapsed: 00:00:04:01

There won’t be any fragmentation in the current table-  it was directly loaded-  no deletes, no updates.  Although it won’t be shown in the examples, I will collect stats at regular intervals and flush the cache to ensure I’m not impacted in any of my tests.

SQL> ANALYZE INDEX PK_INDEXPS VALIDATE STRUCTURE;
Index analyzed.

Elapsed: 00:00:00.37

SQL> select index_name from dba_indexes
  2  where table_name='ORA_TST_IOT';

INDEX_NAME
------------------------------
PK_ORA_IOT
SQL> analyze index pk_ora_iot validate structure;

Index analyzed.

SQL> analyze index pk_ora_iot compute statistics;

Index analyzed.

SQL> SELECT LF_BLKS, LF_BLK_LEN, DEL_LF_ROWS,USED_SPACE, PCT_USED FROM INDEX_STATS where NAME='PK_ORA_IOT';

     32115 7996 0  224394262     88

Well, the table IS THE INDEX.  We collect stats on the table and now let’s remove some data, rebuild and then see what we can do to this IOT-

SQL> select * from ora_tst_iot
  2  where c1=994830;

    994830

SBTF02LYEQDFGG2522Q3N3EA2N8IV7SML1MU1IMEG2KLZA6SICGLAVGVY2XWADLZSZAHZOJI5BONDL2L
0O4638IK3JQBW7D92V2ZYQBON49NHJHZR12DM3JWJ1SVWXS76RMBBE9OTDUKRZJVLTPIBX5LWVUUO3VU
VWZTXROKFWYD33R4UID7VXT2NG5ZH5IP9TDOQ8G0

10-APR-17 03.09.52.115290 PM

SQL> delete from ora_tst_iot
  2  where c1 >=994820           
  3  and c1 <=994830;

11 rows deleted.

SQL> commit;

Now we see what the data originally looked like-  C2 is a large column data that was consuming significant space.

What if we now disable our trigger for our sequence and reinsert the rows with smaller values for c2, rebuild and then update with larger values again?

ALTER TRIGGER C1_IOT_BIR DISABLE;

INSERT INTO ORA_TST_IOT(C1,C2) VALUES (994820, 'A');
INSERT INTO ORA_TST_IOT(C1,C2) VALUES (994821, 'B');
INSERT INTO ORA_TST_IOT(C1,C2) VALUES (994822, 'C');
INSERT INTO ORA_TST_IOT(C1,C2) VALUES (994823, 'D');
INSERT INTO ORA_TST_IOT(C1,C2) VALUES (994824, 'E');
INSERT INTO ORA_TST_IOT(C1,C2) VALUES (994825, 'F');
INSERT INTO ORA_TST_IOT(C1,C2) VALUES (994826, 'G');
INSERT INTO ORA_TST_IOT(C1,C2) VALUES (994827, 'H');

so on and so forth till we reach 994830…

COMMIT and then let’s rebuild our table…

Rebuild or Move

ALTER TABLE ORA_IOT_TST REBUILD;

What happens to the table, (IOT) when we’ve issued this command?  It’s moving all the rows back to fill up each block up to the pct free.  For an IOT, we can’t simply rebuild the index, as the index IS THE TABLE.

Now we’ve re-organized our IOT so the blocks are only taking up the space that it would have when it was first inserted into.  So let’s see what happens now that we issue an UPDATE to those rows-

SQL> update ora_tst_iot set c2=dbms_random.string('B',200)
  2  where c1 >=994820
  3  and c1 <=994830;

11 rows updated.

So how vulnerable are IOTs to different storage issues?

Chained Rows

Chained Rows after updating, moving data and then updating to larger data values than the first with 10% free on each block?

Just 11 rows shows the pressure:

SQL> SELECT 'Chained or Migrated Rows = '||value
 FROM v$sysstat
 WHERE name = 'table fetch continued row';  

Chained or Migrated Rows = 73730

Data Unload

Let’s delete and update more rows using DML like the following:

SQL> delete from ora_tst_iot
  2  where c2 like '%300%';

4193 rows deleted.

Insert rows for 300 with varying degree of lengths, delete more, rinse and repeat and update and delete…

So what has this done to our table as we insert, update, delete and then insert again?

SQL> SELECT table_name, iot_type, iot_name FROM USER_TABLES
     WHERE iot_type IS NOT NULL;  

TABLE_NAME               IOT_TYPE           IOT_NAME
------------------------------ ------------ ------------------------------
SYS_IOT_OVER_88595       IOT_OVERFLOW       ORA_TST_IOT
ORA_TST_IOT              IOT

This is where a clustered index and an IOT is very different.  There is a secondary management object involved when there is overflow.  If you look up at my creation, yes, I chose to create an overflow.  Even if I drop the IOT properly, the overflow table will go into the recycle bin, (unless I’ve configured the database without it.)

SQL> select index_name from dba_indexes
  2  where table_name='ORA_TST_IOT';

INDEX_NAME
------------------------------
PK_ORA_IOT

SQL> analyze index pk_ora_iot validate structure;
Index analyzed.

SQL> select blocks, height, br_blks, lf_blks from index_stats;

    BLOCKS     HEIGHT BR_BLKS    LF_BLKS
---------- ---------- ---------- ----------
     32768     3       45        32115

We can see, for the blocks, the rows per leaf blocks aren’t too many-  this is a new table without a lot of DML, but we still see that with the current configuration, there aren’t a lot of rows returned per leaf block.

When we select from the IOT, the index is in full use and we can see that with the proper pct free/pct used, the index is still in pretty good shape:

SQL> select * from table(dbms_xplan.display_awr('fbhfmn88tq99z'));


select c1, c2, createdate from ora_tst_iot
Plan hash value: 3208808379

--------------------------------------------------------------------------------
| Id  | Operation     | Name   | Rows  | Bytes | Cost (%CPU)| Time

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |   |   |   |  8709 (100)|
|   1 |  INDEX FAST FULL SCAN| PK_ORA_IOT |   995K|   206M|  8709   (1)| 00:01:4
5 |

13 rows selected.

SQL> analyze index pk_ora_iot validate structure;
Index analyzed.

SQL> SELECT blocks, height, br_blks, lf_blks FROM index_stats;

    BLOCKS     HEIGHT  BR_BLKS    LF_BLKS
---------- ---------- ---------- ----------
     32768     3       45         32058

SQL> select pct_used from index_stats;

  PCT_USED
----------
 88

I Did a Bad

So now what happens, if like our original test, we shrink down the percentage of what can be used and reorganize, (and please don’t do this in production…or test….or dev….or ever! 🙂)?

SQL> alter table ora_tst_iot move pctfree 90;
Table altered.

SQL> analyze index pk_ora_iot validate structure;
Index analyzed.

SQL> SELECT blocks, height, br_blks, lf_blks FROM index_stats;

    BLOCKS     HEIGHT  BR_BLKS    LF_BLKS
---------- ---------- ---------- ----------
    172928     3      228         165630

Well, that’s a few more leaf blocks, eh?  Insert after enabling trigger again-

SQL> BEGIN
   FOR i in 1..1000000 LOOP
    INSERT INTO ORA_TST_IOT(c2) VALUES(i);
   END LOOP;
 COMMIT;
END;
 /

Now we have our elapsed time for Azure inserts of 1 million records with 100% and 10%.  Let’s compare it to our IOT.  The IOT move command to fill the blocks to 100% was quite fast.  Of course, the reverse, only allowing for 10%, (90% free) took F.O.R.E.V.E.R…, (OK, it sure felt like it…why didn’t I just truncate it?  Oh, yeah, I wanted it to be a real test, not simply an easy test..)

Note: For this test, we’ll rebuild after updating the pctfree each time.

 

10% Fill Factor in SQL Server and 1 million insert: Elapsed time: 23 minutes, 18 seconds

90% PCTFree in Oracle and 1 million insert:  7 min, 12 seconds

100% Fill Factor in SQL Server and 1 million insert: Elapsed Time: 4 minutes, 43 seconds

0% PCTFree in Oracle and 1 million insert: 1 min, 8 seconds

REBUILD of the Oracle IOT to make it 90% free in each block?  Elapsed Time:  8 hrs, 21 minutes, 12 seconds 

…along with four backups of archive logs it generated that filled up the archive dest… 🙂  Now the AWS Trial is to be used to test out the Delphix product, not to test out index performance in a high insert/delete/update scenario, so I’ve been asking for some of these challenges, but it was still a great way to build this out quickly and then compare.

 

Results

In this test, this was the overall results:

  1. Less overhead during transactional processing when it comes to inserts, updates and deletes to the IOT.
  2. Improved performance on any selects that require the data to be sorted in sequential order.
  3. Similar performance to SQL Server clustered indexes on complex queries and less sort temp usage.
  4. Limited use case than SQL Server clustered indexes, as these are quite common and IOTs are less used in the Oracle space.
  5. More maintenance upkeep as we will need to reorganize the IOT if its used with processing that includes a lot of inserts, updates and deletes.
  6. DBCC rebuilds of a clustered index uses less resources and doesn’t impact the transaction log as it does Oracle’s rollback and archive log.  It was easier to build the table with a high pct free storage configuration and then do an insert of the data, then drop the old table than to do an “alter move” command.

Now there’s more to do comparisons on, so I’m going to dig in more on the SQL Server side, but here’s to Oracle Index Organized Tables, (IOTs)!

Posted in Oracle, SQLServer Tagged with: , , ,

  • Facebook
  • Google+
  • LinkedIn
  • Twitter