Venzi's Tech-Blog

22 October 2012

New job – Welcome Oracle!

Filed under: Oracle,Work — Venzi @ 11:02

It’s been a long time since my last post on this blog and there were very good reasons for this. Since the last 6 and a half months I did not only move from the US to the UK but also switched from my previous company to Oracle. Of course it took a while to get into the new job but also to get approval for continuing this blog. You will notice a disclaimer at the right hand side saying that this blog is my private blog and has nothing to do with Oracle. So obviously nothing that I post here reflects any official view or statement of Oracle or its affiliates! That being said: time to catch up on some posts!

6 October 2011

Indexing strategies with Jonathan Lewis

Filed under: Oracle,Performance,Work — Venzi @ 22:25

Today I was part of a NYOUG seminar about “Indexing Strategies” held by Mr. Jonathan Lewis himself. If somebody doesn’t know Jon Lewis, he is one of the top Oracle gurus on the entire planet with more than 25 years of Oracle experience. He probably used Oracle before I was even born (1985)! The very interesting fact however is that he actually never ever worked for Oracle but still has all the deep inside knowledge. Usually those gurus came out from Oracle or are still working for them. I guess the great amount of years dealing with Oracle helped him there.

However, Jonathan Lewis is also one of my top favorite because he shares his knowledge on his personal blog: http://jonathanlewis.wordpress.com In fact, Jons blog is the only one which I never can catch up with because he posts literally quicker than I got time to read his fabulous posts… reminds me that I should update my Blogroll.

The seminar itself was simply great. It was not free but those 230 bucks where totally worth it. Not only is Jon Lewis a great speaker – was the first time that I actually saw him – he also gave great detailed insights into indexes how they work, what is all possible with them and why Oracle is actually taking an index and much more important when it is not! . Furthermore he combined his sessions with some real world examples that he himself dealt with in the past which makes the whole thing much less theoretical.

Of course I won’t cover the whole seminar here, but I’ll end with some interesting things that I took out of it – some of which I got reminded again, some which were new for me:

  • Indexing means: Getting to data quickly
  • Indexes focus/compact data
  • There is always a Trade-Off between loading and querying performance
  • Index maintenance is expensive – it introduces costs
  • It’s all about precision
  • Non-unique indexes include the rowid automatically – for Oracle there are no non-unique indexes
  • Index compression can save a lots of space on repetitive columns
  • Index compression means elimination of duplicates
  • Primary key constraints don’t necessarily need unique key indexes but only indexes with the columns in the right order
  • Reverse indexes scatter entries accros the line, clustering factor can end up terrible
  • Don’t duplicate indexes – e.g. FK constraints can share the first columns of another index
  • Difference between tables and indexes: Index blocks have to be at the right place
  • Don’t trust Oracle – test everything!

 

26 April 2011

Exadata, Exadata, Exadata

Filed under: Oracle,Performance,Work — Venzi @ 18:15

I’m currently in Menlo Park, California at the old Sun lab from Oracle and do some Exadata testing for work. Everything TOP SECRET of course so I cannot tell anything about it. But what I can do is to share some nice pics from the facilities and the boxes! :)

19 January 2010

Oracle Seminar

Filed under: Oracle,Work — Venzi @ 21:55

Last Wednesday I had the chance to visit an Oracle seminar at the Marriot hotel in New York City. I was really happy since it was already a long time ago when I last visited an Oracle event (except the Oracle User Group Meeting but that’s not Oracle itself). There were four tracks to join: Database developer, .NET developer, Java developer and APEX developer. All of them sounded interesting but as you had to bring your own laptop and install a specific image for each track I had to decide for one. Well it wasn’t to hard for me and I took the Database developer track with Hands-On on SQL Developer, SQL Developer Data Modeler, TimesTen and last but not least XML database. I have to say, that I was really excited about the sessions. I use SQL Developer and the Data Modeler part already quite a long time so there wasn’t much new for me but still something to learn (there is always something to learn!) The TimesTen and the XML database part was totally new to me and I was surprised what Oracle can already do with all the other products the own. Funniest thing was that I won a book of SQL Developer 2.1 which is brand-new and still wet from the press! ;) This was because I was the first who finished all SQL Developer related hands-on – I wasn’t aware that I can win a prize for this, but hey, a book is always good! :)

So the best things out of this seminar were:

  • SQL Developer can debug PL/SQL (not really new)
  • SQL Developer can build, maintain and execute Unit tests for PL/SQL
  • SQL Developer can connect to TimesTen since 1.6
  • SQL Developer has a new Query Executor which makes executing stuff against different databases much more flexible
  • TimesTen is really fast if you use it right (made selfwritten tests on my laptop bringing me 8.6 times more performance)

Well at the end here it’s time for some pics:

And here the prize I won:

9 December 2009

NYOUG Meeting – 25th anniversary

Filed under: Oracle,Work — Venzi @ 04:48

It’s a long time since I have posted something here, I know. As you might remember from my last post I moved over to New York and well, there where a lot of things to do the last months. Anyway, I’m not writing here to make excuses. Fact is that I have been very busy lately. In my (new) role as “Performance Engineer” in the company I got new tasks beside my Oracle tuning stuff. Actually I spent most of the time in the last few months with tuning our Java application and not Oracle anymore. But I’m happy with that as this gave and still gives me the opportunity to learn more about the art of tuning! But this just as a short clarification why my Oracle related posts are so rare lately.

Anyway, today I had the chance to attend the New York Oracle User Group meeting together with my working colleagues. This was my first user group meeting at all so I was a little bit excited about what will be waiting for me there! As I took this serious I printed off the agenda already a few days back and marked the sessions which seemed to be interesting for me. One great thing I discovered was a session with Tanel Poder who I already know because of his blog (which I have also in my Blogroll of course!). Tanel is one of the few guys who shares his scripts with you. Most of the DBAs hide their secret, magic scripts for themselves to be the number 1 DBA. But Tanel provides his scripts to the world. You can download the whole catalog as a simple zip file from his website and well believe me he has a lot of scripts! Anyway, from the four sessions I have chosen three of them where performance related. Actually all of the sessions that I wanted to attend were performance related but unfortunately the first session about performance on SSD was canceled so I attended Arup Nandas session about upgrading to 11g and how database replay/standby database can help you minimize the downtime. Actually I had such a session already back in February in Vienna at Oracle so there weren’t really new things for me at the presentation. The second session was then the one from Tanel. It was about (his) scripts and tools that (can) make your life easier. Third session was from Dave Anderson about 11g Result-Set Cache. He did some life demos on it what was really cool. It gave you the chance to see result set cache really working and improving your performance at the response time. Fourth and last session was from Dean Richards  – “Advanced SQL tuning”. This was a very, very interesting session as he had some real world issues in the presentation. He also gave an overview on which steps are important for performance tuning (knowing the background, triggering down the issue, etc.) After the sessions there were some vendor raffles. I hoped to win the 32″ TV but of course I did not! Actually I didn’t win anything! ;(

If I have to rate the sessions I attended then I would say that the best presenter was Tanel Poder. He was agile and brought some nice “real world” jokes into the session. There was not a single second in his session where you got bored or did pay more attention to your Blackberry than to him! The best session rated by the content would have been Dean Richards “Advanced SQL tuning”. He showed up important things about how to tune and how important it is to understand the whole story around it!

Well time for pictures:














3 November 2008

Finally a good tool for Data Modeling: Oracle SqlDeveloper Data Modeling

Filed under: Oracle,Work — Venzi @ 12:42

How long did you search for a good tool to do Data Modeling. Well not long I guess, I always used Microsoft Visio for it. But how long did you search for a tool to create a Data Model and export the DDL statements or vice versa and also for one which is free? I did it now for around 4 year and then I found this blog entry: Finally – free tool for Oracle Data Modeling

It’s called Oracle SqlDeveloper Data Modeling

I just downloaded it and tried it out: It’s absolutely nice. You can create logical models like you know it from Visio but you can also create relational models where you can already define column data types, constraints and so.

But not even that, you can create relational models out from logical and vice versa!

Oracle SqlDeveloper Data Modeling can also export it to DDL (a feature I searched long for!). And even better: You can choose which DDL (Oracle 9i, 10g, 11g; SQL Server 2000, 2005; DB2 7, 7.1, 8, 8.1). The only bad thing: MySql isn’t supported right now :( (Already gave feedback on that! ;) )

Of course you’re also able to play it the other way round and import DDL and create a datamodel out of it – really cool stuff! And you can again import it for any databases listed above!

And now the best feature: If you’ve access to the DB catalog, you can even create a data model out of an existing schema!

Sorry for the grey shadows but a little bit of data protection must be! ;)

All in all Oracle SqlDeveloper Data Modeling is really a good tool. It has lots of more features which I didn’t try out so far (Design Rules, Multidimensional Models, Process Models, …) and the best thing: It’s free – at the moment. It seems that there were some rumors about a license for this tool. We’ll see!

20 October 2008

Back again – away again

Filed under: Work — Venzi @ 16:42

What’s the best thing when you’re back on the first day in the company after your vacation? When they tell you, that you should be already at the client! So it happened to me today. I’ll go to London tomorrow morning 6:00 AM and stay there at least for Thrusday doing performance tests and tuning. It’s not a bad thing – I’m currently be used to live out of the suitcase :) And it shows that at least someone missed me that three weeks – always a good sign! ;) So I’ll stay the next three days (or more, we’ll see) in London and be glad to see the city again (even when it’s just the street where the office of the client is located). The last time I was in London I was just 14. So let’s see how much the city changed – or how much I’ve forgot over the time…

16 July 2008

Imported statistics doesn’t get populated to table

Filed under: Oracle,PL/SQL,Work — Venzi @ 14:46

Oracle provides a PL/SQL package for handling statistics called DBMS_STATS. With this package you’re able to gather, delete, export and import statistics and some more stuff. So today a colleague came to me and told me that he just wanted to import the stats of a table from one schema to another one on a different database but the stats doesn’t get populated. I said him that he should provide the commands he executed and checked them – they were ok. The import routine of the DBMS_STATS package always gets successfully executed. After a short look into the stats table and on Metalink I was surprised that the package seems to be designed for saving and restoring statistics of a schema in case of crash or upgrades but not for importing statistics into another schema. And Metalinks only workaround (look at section “Different schema” in Doc 117203.1) is to modify the stats table before re-importing although the documentation explicit says not to modify anything in the stats table: (:D)
CREATE_STAT_TABLE Procedure
This procedure creates a table with name stattab in ownname‘s schema which is capable of holding statistics. The columns and types that compose this table are not relevant as it should be accessed solely through the procedures in this package.)

So let’s see what the problem actually is:

First of all we need two users where we want to transfer the statistics, so we create user test and test1:

SQL> create user test identified by test;
User created.

SQL> grant connect, resource to test;
Grant succeeded.

SQL> create user test1 identified by test1;
User created.

SQL> grant connect, resource to test1;
Grant succeeded.

Next thing is to create a table, populate it with data and gather table stats:

SQL> conn test/test@TEST1
Connected.

SQL> create table datatable (line number);
Table created.

SQL> insert into datatable select rownum from all_objects;
40779 rows created.

SQL> commit;
Commit complete.

SQL> exec dbms_stats.gather_table_stats (user, ‘DATATABLE’);
PL/SQL procedure successfully completed.

SQL> select num_rows from user_tables;
NUM_ROWS
———-
40779

As you can see stats showing 40,779 rows in the table.

Next thing now is to export the statistics into a stats table. Therefore we need to create the stats table and export the stats into it:

SQL> exec dbms_stats.create_stat_table(user, ‘STATS’);
PL/SQL procedure successfully completed.

SQL> select * from stats;
no rows selected

SQL> exec dbms_stats.export_table_stats(user, ‘DATATABLE’,null,’STATS’);
PL/SQL procedure successfully completed.

As you can see the table was empty before the export, now it’s already populated:

SQL> set heading off;
SQL> select * from stats;

T          4          2
DATATABLE

TEST                                40779         65          4      40779

16-JUL-08

C          4          2
DATATABLE
LINE

TEST                                40779 .000024522      40779      40779
0          4      40774          5
16-JUL-08
C105
C305084B

SQL> set heading on;

Looking at the C5 column which contains the owner of the object we can see that these stats belong to owner TEST:

SQL> select c5 from stats;

C5
——————————
TEST
TEST

Now let’s export the stats table and import it in the other schema (using create table as select statement for this, but this would be also the same with exp/imp and datapump:

SQL> conn sys@TEST1 as sysdba
Connected.
SQL> create table test1.stats as select * from test.stats;
Table created.

So now let’s create the same data table and import the statistics:

SQL> conn test1/test1@TEST1
Connected.
SQL> create table datatable (line number);
Table created.

SQL> exec dbms_stats.import_table_stats(user, ‘DATATABLE’, null, ‘STATS’);
PL/SQL procedure successfully completed.

Stats are successfully imported the procedure completed successfully. Looking at the statistics we should see now stat but instead of that we see following:

SQL> select num_rows from user_tables;

NUM_ROWS
———-

SQL> select table_name, num_rows from user_tables;

TABLE_NAME                       NUM_ROWS
—————————— ———-
STATS
DATATABLE

No stats there, looking at the column C5 we still see the old user of course:

SQL> select c5 from stats;

C5
——————————
TEST
TEST

So the workaround is: Update the C5 column with the correct owner (the new schema) and import it:

SQL> update stats set c5 = user;
2 rows updated.

SQL> commit;
Commit complete.

SQL> exec dbms_stats.import_table_stats(user, ‘DATATABLE’, null, ‘STATS’);
PL/SQL procedure successfully completed.

And there you go:

SQL> select table_name, num_rows from user_tables;

TABLE_NAME                       NUM_ROWS
—————————— ———-
STATS
DATATABLE                           40779

I’m a little bit confused that the package doesn’t support export and re-import into another schema. For me that’s a design issue. But I read on Metalink that an enhancement request has been opened by the Oracle support guy. Let’s see if it will get implemented.

14 July 2008

Feeling like the exterminator

Filed under: Work — Venzi @ 16:46

Sometimes I’m thinking I’m the exterminator in the Company. I always find the trickiest bugs in some components of the software and I’m afraid of that because I’m the end user in some cases. QA should have found them already all – ok nearly all. Anyway, it’s just annoying when you’re trying to do your work but you always fall from one problem into the next one….

11 July 2008

Going to Paris

Filed under: Work — Venzi @ 09:21

My boss just called me. We’ve possibly a new client who is currently doing a functional POC (proof of concept) and wants to do a performance POC afterwards in the second week of August. So someone of my group has to go there and my boss decided to send me to Paris and get that thing running if the POC is successful. (Un) fortunately I’m on vacation the first week but he just said that either we reschedule the performance POC to start in the second week of August or someone from New York is going there the first week and I go there in the second week. But he definitely wants me there (I’ve two other team mates here in Austria) so I’m very honored at the moment. And of course my motivation is also back to the top!

Next Page »

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.