Search

OakieTags

Who's online

There are currently 0 users and 36 guests online.

Recent comments

Oakies Blog Aggregator

Public / private cursors

As we all know (hopefully), we should always deploy named program units (procedures/functions) within packages. Its a great method of encapsulation of logic.

However, not many are aware that its not just procedures and functions that can have a public (package spec) and private (package body) component.

Cursors can also be defined in the same way as the following example shows:




CREATE PACKAGE my_pkg AS
   CURSOR c_recent_hires RETURN emp%ROWTYPE;
   ...
END emp_stuff;
/

CREATE PACKAGE BODY my_pkg AS
   CURSOR c_recent_hires RETURN emp%ROWTYPE IS
      SELECT * FROM emp 
      WHERE hiredate > sysdate-30;
   ...
END emp_stuff;
/


New Round of Let’s Talk Database Events

I’ll be presenting a new round of “Let’s Talk Database” events around Australia and NZ next month. These are free events but have often “sold out” in the past so booking early is recommended to avoid disappointment. All events run between 9:00am – 12:30pm and are followed by a networking lunch. Currently, the confirmed events are: Sydney – Tuesday, […]

Implementing Index Compression (and other Physical Storage Options) via Application Designer

There are some performance improvements that require physical storage options to be set on tables or indexes.
One particular technique that I will take as an example for this article is index compression.  A good example in PeopleSoft is the tree node table, PSTREENODE.  It drives many security and hierarchical queries.  It is not updated very frequently, only as new trees are brought on.  Many of the indexes are good candidates for compression.  Oracle can calculate the optimal prefix length using

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">ANALYZE INDEX … VALIDATE STRUCTURE

I have written script to make it slightly easier, calc_opt_comp.sql.  This is the output on my demo database, but I get similar results on production systems.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">                                          Optimal
Compression Weighted
Prefix Current Average
Table Name Index Name Length FREQ PARTS Blocks Saving %
------------------ ------------------ ----------- ---- ----- ---------- --------
PSTREENODE PSAPSTREENODE 4 1 0 280 39.0
PSBPSTREENODE 3 1 0 264 30.0
PSCPSTREENODE 1 1 0 120 7.0
PSDPSTREENODE 4 1 0 256 61.0
PSFPSTREENODE 2 1 0 256 67.0
PSGPSTREENODE 3 1 0 400 49.0
PS_PSTREENODE 4 1 0 256 44.0 

However, I want to make sure that should the table need to rebuilt in the future, PeopleTools will generate the DDL with the appropriate settings.  The same principle would also apply to any other physical storage option.  I would always recommend that the compression prefix lengths be incorporated into the PeopleTools DDL override in Application Designer (figure 1).  While you could extend the DDL model and add another override for compression, I just append it to the PCTFREE setting.

Index DDL Overrides
Figure 1. Index DDL Overide

However, there is catch.  PeopleTools has never examined DDL overrides when determining whether there is a difference between the PeopleSoft and database data dictionaries, even though that comparison must be platform specific.  DDL overrides and DDL models are just strings held in the PeopleTools tables.  They can be extended (or even removed) by customers.  I assume this is the reason; it was not felt possible to reliably check them,
So, if the build settings (figure 2) are 'recreate index only if modified', which is the default, Application Designer will not generate a DDL script, nor execute any DDL.

Build Settings
Figure 2. Build Settings

The workaround has always been to set the index creation option in the build settings to 'recreate index if it already exists'.  However, we then discover the override doesn't appear in the DDL.  As Application Designer has not detected a difference between PeopleTools and the database, it has instead used the Oracle DBMS_METADATA package to generate the storage clause from from the index that exists in the database.  Hence the DDL contains additional keywords not in the PeopleSoft DDL model.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">CREATE UNIQUE  INDEX PS_PSTREENODE ON PSTREENODE (SETID,
SETCNTRLVALUE,
TREE_NAME,
EFFDT,
TREE_NODE_NUM,
TREE_NODE,
TREE_BRANCH)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 40960 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PSINDEX"
/
ALTER INDEX PS_PSTREENODE NOPARALLEL LOGGING
/

I have only checked this behaviour on PeopleTools 8.54, but use of DBMS_METADATA was introduced in PeopleTools 8.51, so this problem has probably existed since then.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECT dbms_metadata.get_ddl('INDEX','PS_PSTREENODE')
FROM dual

DBMS_METADATA.GET_DDL('INDEX','PS_PSTREENODE')
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX "SYSADM"."PS_PSTREENODE" ON "SYSADM"."PSTREENODE" ("SETID"
, "SETCNTRLVALUE", "TREE_NAME", "EFFDT", "TREE_NODE_NUM", "TREE_NODE", "TREE_BRANCH")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 40960 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PSINDEX"

However, if I drop the index and then regenerate the DDL script in Application Designer,

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">DROP INDEX ps_pstreenode
/

PeopleTools generates the create index with the compression specified in the PeopleTools table.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">
CREATE UNIQUE INDEX PS_PSTREENODE ON PSTREENODE (SETID,
SETCNTRLVALUE,
TREE_NAME,
EFFDT,
TREE_NODE_NUM,
TREE_NODE,
TREE_BRANCH) TABLESPACE PSINDEX STORAGE (INITIAL 40000 NEXT 100000
MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 0 COMPRESS 4 PARALLEL
NOLOGGING
/
ALTER INDEX PS_PSTREENODE NOPARALLEL LOGGING
/

Rather than go through the business of dropping the index so you can then generate the correct script to then recreate the index, I would suggest just implementing the change manually by rebuilding the indexes.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">ALTER INDEX PSAPSTREENODE REBUILD TABLESPACE PSINDEX COMPRESS 4;
ALTER INDEX PSBPSTREENODE REBUILD TABLESPACE PSINDEX COMPRESS 3;
ALTER INDEX PSCPSTREENODE REBUILD TABLESPACE PSINDEX COMPRESS 1;
ALTER INDEX PSDPSTREENODE REBUILD TABLESPACE PSINDEX COMPRESS 4;
ALTER INDEX PSFPSTREENODE REBUILD TABLESPACE PSINDEX COMPRESS 2;
ALTER INDEX PSGPSTREENODE REBUILD TABLESPACE PSINDEX COMPRESS 3;
ALTER INDEX PS_PSTREENODE REBUILD TABLESPACE PSINDEX COMPRESS 4;

Conclusion

This makes the business of implementing physical attributes through Application Designer much more complicated.  I would still recommend recording the settings in Application Designer, if only because it provides documentation, but then it may be easier to implement the changes manually.

Quiz Night

I was setting up a few tests on a copy of 12.1.0.2 recently when I made a mistake creating the table – I forgot to put in a couple of CAST() calls in the select list, so I just patched things up with a couple of “modify column” commands. Since I was planning to smash the table in all sorts of ways and it had taken me several minutes to create the data set (10 million rows) I decided to create a clean copy of the data so that I could just drop the original table and copy back the clean version – and after I’d done this I noticed something a little odd.

Here’s the code (cut down to just 10,000 rows), with a little output:

--
--      Script:  12c_vc32767_b.sql
--

create table t1 (
        id      not null,
        date_open,
        date_closed,
        deal_type,
        client_ref,
        small_vc,
        padding
)
nologging
as
select
        rownum                                          id,
        trunc(
                add_months(sysdate, -120) +
                        (rownum)* 3652 / 1e7
        )                                               date_open,
        trunc(
                add_months(
                        add_months(sysdate, -120) +
                                (rownum) * 3652 / 1e7,
                        12 * trunc(dbms_random.value(1,6))
                )
        )                                               date_closed,
        dbms_random.string('U',1)                       deal_type,
        dbms_random.string('U',4)                       client_ref,
        lpad(rownum,10)                                 vc_small,
        rpad('x',100,'x')                               vc_padding
from
        dual
connect by
        level <= 10000
;
alter table t1 modify deal_type varchar2(1);
alter table t1 modify client_ref varchar2(4);

create table t2 nologging as select * from t1;

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

select table_name, num_rows, blocks, avg_row_len from user_tables;

TABLE_NAME             NUM_ROWS     BLOCKS AVG_ROW_LEN
-------------------- ---------- ---------- -----------
T1                        10000        304         139
T2                        10000        218         139

There’s no hidden code – this is the code I ran, and the tables both went into the same tablespace- yet one table has used about 30% more blocks than the other on a simple “create as select”, even though the row lengths and row counts are the same!

When I first did this test it just so happened that the only place in the database I had to make the t2 copy was in a tablespace using freelist management, and I’d created the original table in a tablespace using ASSM, so I wasted a little time examining the ASSM space management (bitmap) blocks before I realised what had happened.

Suggestions about what has happened, and how to confirm your hypothesis, invited in the comments.

Update (following morning)

The explanation is in the comments: I had enabled extended (32,767 byte) varchar2().  My calls to dbms_random.string() had returned character values, and the return type defaults to the maximum character length, which meant they were declared as varchar2(32767) and those are implemented as (unhackable) CLOBs in 12.1.0.2.

When I modified the table to change the varchar2(32767) to shorter lengths Oracle let me get away with it because none of the actual stored values at that time was longer than my target declaration – but it didn’t physically change the stored column values to “short” varchar2(), it left them as CLOBs, and a “short” CLOB includes a LOB Locator which starts out at about 30 bytes.

When I created t2 from t1 Oracle first copied the (short) column definitions, and then copied the data, reading and converting the CLOBs to normal varchar2() storage, and that explains why t2 needed fewer blocks.  The avg_row_len for the two tables matched because the code to gather stats simply applies the sys_op_opnsize() function to the varchar2() defined in t1, and doesn’t give you any indication about the LOB locator.

(If you use the extremely deprecated analyze command to gather stats on the two tables you’ll find that the avg_row_len of table t1 then allows for the LOB locator size.)

“Workaround:”

I should have used:  cast(dbms_random.string(‘U’,4) as varchar2(4)) and the column definition would have been a proper varchar2(4) from the start.  (As noted in a comment from Ivica Arsov below, substr(…, 1, 4) would also have worked.  Of course I should have used cast() for the numerics as well so that I could declare them as things like number(8,0) rather than the slightly dangerous “number”.

Note:  I’ve put “workaround” in quotes because it’s not really a workaround to a problem – it’s just an example of doing it right.

Footnote:

Here’s a symbolic dump of a single character (‘T’) being stored as a varchar2(32767):


LOB
Locator:
  Length:        84(31)
  Version:        1
  Byte Length:    1
  LobID: 00.00.00.01.00.00.01.6b.ba.d7
  Flags[ 0x01 0x0c 0x00 0x80 ]:
    Type: BLOB
    Storage: SecureFile
    Characterset Format: IMPLICIT
    Partitioned Table: No
    Options: ReadWrite
  SecureFile Header:
    Length:   11
    Old Flag: 0x48 [ DataInRow SecureFile ]
    Flag 0:   0x90 [ INODE Valid ]
    Layers:
      Lengths Array: INODE:5
      INODE:
        00 00 01 01 54

Note the 84(31) at line 3: this is a reminder of how big a LOB locator could get for an out of line LOB, compared to the current size of the LOB locator. Rows that hold out of line LOBs can actually be much longer than avg_row_len tells you.

RMOUG 2016 – Controlling Execution Plans Workshop

Thanks to everyone that attended my session. Here’s a quick link to a zip file with the scripts I used in my Controlling Execution Plans Workshop presentation.

Controlling Execution Plans Workshop Zip File

Video: Amazon Web Services (AWS) : Relational Database Services (RDS) for SQL Server

Here’s another video on my YouTube channel. This one is a quick run through of RDS for SQL Server, a DBaaS offering from Amazon Web Services.

The video was based on this article.

The cameo for this video is Garth Harbach, a former colleague of mine. :)

I’ve been ill recently and my voice is pretty shot. The last three videos have all be on AWS RDS, which has very similar setup regardless of which database engine you use. This has been really handy, as I could pretty much reuse one vocal track for all three videos. Not sure if anyone would have noticed, but I felt guilty, so I thought I would confess up front. :)

I’m hoping I’ll get my voice back in the next few days so I’ll be able to do something different. :)

Cheers

Tim…


Video: Amazon Web Services (AWS) : Relational Database Services (RDS) for SQL Server was first posted on February 11, 2016 at 8:15 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Stolen Content – Again…

frowning-150840_640In my Writing Tips series I wrote about Copyright Theft. I had a quick look through my blog and the first time I wrote about my stuff getting stolen was in 2006. I’m sure it had happened before then and it has happened many times since. Most of the time I try to deal with it privately and give people a chance to sort their lives out without publicly branding them a thief, but sometimes circumstances bring the worst out of me.

If you had followed my series of rants on Twitter tonight you will know it happened to me again. The reason I went off the deep end this time was because approximately 10 months ago, this same person did exactly the same thing to me. When I contacted them the first time, they were very apologetic and removed the content, saying they had paid someone to produce some content for them and they didn’t know it was all stolen. Since it was all resolved quickly and pleasantly, I said nothing more. I did of course keep a record of the whole process, including my contact with the hosting company etc.

Fast forward to today and Martin Widlake contacted me to say he had found some of my stuff on another site. When I checked, it was this same person again! Some of the content that got removed last time had mysteriously returned, and there was a load more with it. Most of the time it was a straight copy. Sometimes the article names had been slightly altered, but the content was straight off my site. Occasionally there was one extra sentence at the start. In total I found 141 articles stolen from my site. There may have been more, but these were all I identified up to now.

I wrote an email to the individual in question, which ended with the rather melodramatic statement of,

“What you are doing is wrong and illegal. I will end you!”

I was putting together a DMCA takedown notice when Martin Widlake said the content had started to disappear. I checked and sure enough, I was getting 404 errors for most of the URLs. I’m promoting Martin to “Chief of the Content Police”! :)

Now I’m a rather petty individual and I have a very large readership, so I’m pretty sure that if this person ever does something like this again, I will be able to make sure everyone he has ever worked for, or ever will work for, will know he has been proven to be a thief twice over. Not exactly the sort of person you want working with your valuable data!

Just some words to the wise:

  • If you steal content from a popular source, people are going to notice and tell the original content producer about it. There is no maybe. It will definitely happen.
  • When you are caught stealing stuff it makes you look like scum. You know why? Because you are scum! If you are lucky, you will be able to deal with it quietly. If not, the world will find out you are scum!
  • If you pay someone to produce content for you, you better make sure they are not stealing it, because if they are, it is you that will end up looking like scum, not them!
  • If you are paying someone for content and they are producing several articles a day, it is highly likely the work is stolen, or incredibly derivative at best. If you do not realise this, you are a moron. Being a moron is not a defence, and kind-of affects your future job prospects!

Changing tack slightly…

We are all writing about the same stuff. When a new product is released, there is a flurry of new articles on the subject, many of which are covering the same content. There is nothing wrong with that. No one person has a claim on it. You won’t get an email from me asking you not to write about it. That would be ridiculous. Everyone’s take on the same subject matter is slightly different. If you ever see me in person you’ll know I’m always encouraging people to get involved. Having said that, if your idea of getting involved is stealing other people’s material, we will not be friends!

Cheers

Tim…

 


Stolen Content – Again… was first posted on February 10, 2016 at 9:04 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

AskTom–some thoughts on the future

The structure of an AskTom question contains four elements:

  • The original question
  • Our answer
  • A review, which can be posted by anyone
  • And then we can opt to add a single Followup to any of those reviews

 

image

We’re thinking of changing AskTom to allow greater flexibility, namely:

  • The original question (unchanged)
  • Our answer (unchanged)
  • A review, which can be posted by anyone (unchanged)
  • We will then allow anyone to add a Followup to a review, and also, there could be any number of Followups attached to a single review.

Let us know your thoughts via Comment on this blog.  If you prefer a private response, you can also send your thoughts to: asktom_us@oracle.com

What do you think ?

EM13c Upgrade Tips- Part II

There is a flurry of excitement about Enterprise Manager 13c and I’m blown away by how many customers are looking not just to install and test out the new release, but have plans already underway to upgrade to it.  At Oracle, we’re covering a lot of information that will help make that upgrade easier and I want to make sure I share what we learn with the public so you can be as cool about upgrades as we are.

ZQT94hx

Your Repository is Going to be a Single Tenant Database 12c

  • You MUST upgrade your repository, (OMR) to 12.1.0.2
  • Also plan on applying the latest Bundle patch to the 12.1.0.2 database, don’t just take the base installation and think you’re done.  There were important updates in the bundle patch.
  • Upgrade your software only vs. upgrading both software and database at once, then upgrade the repository later, which will decrease downtime.  This is done by running $OMR_HOME/sysman/install/ConfigureGC.sh at the time of actual repository upgrade.

I Have a LOT of Agents to Upgrade!

  • Consider retaining your agent at 12.1.0.4 and then upgrade them to 13c agent post your initial upgrade process.
  • This can save you downtime and you can even automate this if you have provisioning and patching with DBLM.
  • Utilize the Gold Agent Image to simplify and ensure you possess a unified agent environment.  It allows mass update of all agents to “gold image” which is very cool!

New 13c Plugins, Too

  • With the infrastructure changes to EM13c, a requirement for plugin rewrites were also required.  New plugins for 13c are being released almost daily, so verify the ones you need are available before upgrading.
  • Remember to remove any plugins that are decommissioned, (obsolete.)  This includes Exalogic Elastic Cloud Infrastructure and EMC Symmetrix Array.

I’m going to have a lot more tips and tricks, but these are three topics to add to your list as you plan your own Enterprise Manager 13c upgrade!

 



Tags:  ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [EM13c Upgrade Tips- Part II], All Right Reserved. 2016.

Video: Amazon Web Services (AWS) : Relational Database Services (RDS) for MySQL

Here’s another video on my YouTube channel. This one is a quick run through of RDS for MySQL, a DBaaS offering from Amazon Web Services.

The video was based on this article.

If you watch the little outtake at the end you will hear me cracking up with the goofiest while filming Brian ‘Bex’ Huff‘s clip. :)

Cheers

Tim…


Video: Amazon Web Services (AWS) : Relational Database Services (RDS) for MySQL was first posted on February 8, 2016 at 3:35 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.