How to check that your KEEP cache is right-sized.

I recently wanted to reduce the KEEP cache on one of the databases I am working on since evidence indicated that increased PGA RAM would be more useful (slow hash joins aplenty and no easy way to avoid them, but that’s a story for a different day).

I ran the following query that I got from Oracle support:

select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
        4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
         7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
         bh.object_name,bh.blocks
from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,
         o.name object_name,count(*) BLOCKS
         from obj$ o, x$bh x where o.dataobj# = x.obj
         and x.state !=0 and o.owner# !=0
         group by set_ds,o.name) bh 
where ds.set_id >= pd.bp_lo_sid
and ds.set_id <= pd.bp_hi_sid
and pd.bp_size != 0
and ds.addr=bh.set_ds;

And got result looking like this:

KEEP         DIM_AGREEMENT_C                12840                 

DEFAULT      DIM_PERS_DETAIL                31491                 

DEFAULT      TXPKR_PERSON_CONTRACT          19969                 

DEFAULT      TXPKE_CONTRACT                 8181                  

DEFAULT      MA_CLIENT_CONTACT_DETS_UC_C    2860                   

KEEP         DIM_AGREEMENT_C                6854                  

DEFAULT      SQL060602224851480             11333                 

I pasted the results into MS Excel and used the text to columns function in the data menu to format it nicely. Then I used the SUMIF conditional aggregation function to total all the blocks of objects in the KEEP cache and multiplied the result with the db block size to get the size of my actual KEEP cache.

This worked out to 770M with the specified db_keep_cache_size being 800M, so all was good in the land of KEEP.

A way, by the way (so to speak) to observe this is to employ SQL Plus as follows:

SQL> show parameters keep

NAME                                 TYPE        VALUE

------------------------------------ ----------- ----------

buffer_pool_keep                     string

control_file_record_keep_time        integer     32

db_keep_cache_size                   big integer 800M

Herman Scheepers signing  of from Cape Town

I was looking around the Web for a simple way to measure throughput at the instance  level, and by the time I was all Googled out I had not find anything suitable yet. I thus decided to roll my own.

It consists of using the difference in bytes shifted between to points in time to calculate the throughput during the interval so delineated.

Script to create the required tables:

--
-- IO_SAMPLES  (Table) 
--
CREATE TABLE XY90260.IO_SAMPLES
(
  ID                          NUMBER                NULL,
  IO_TIMESTAMP                DATE                  NULL,
  PHYSICAL_READ_IO_REQUESTS   NUMBER                NULL,
  PHYSICAL_WRITE_IO_REQUESTS  NUMBER                NULL,
  PHYSICAL_READ_TOTAL_MBR     NUMBER                NULL,
  PHYSICAL_WRITE_TOTAL_MBR    NUMBER                NULL,
  PHYSICAL_READ_TOTAL_BYTES   NUMBER                NULL,
  PHYSICAL_WRITE_TOTAL_BYTES  NUMBER                NULL
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

--
-- IO_SAMPLES_PK  (Index)
--
--  Dependencies:
--   IO_SAMPLES (Table)
--
CREATE UNIQUE INDEX XY90260.IO_SAMPLES_PK ON XY90260.IO_SAMPLES
(ID)
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

--
-- IO_SAMPLES_UK  (Index)
--
--  Dependencies:
--   IO_SAMPLES (Table)
--
CREATE UNIQUE INDEX XY90260.IO_SAMPLES_UK ON XY90260.IO_SAMPLES
(IO_TIMESTAMP)
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

--
-- Non Foreign Key Constraints for Table IO_SAMPLES
--
ALTER TABLE XY90260.IO_SAMPLES ADD (
  CONSTRAINT IO_SAMPLES_PK
 PRIMARY KEY
 (ID)
    USING INDEX
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

ALTER TABLE XY90260.IO_SAMPLES ADD (
  CONSTRAINT IO_SAMPLES_UK
 UNIQUE (IO_TIMESTAMP)
    USING INDEX
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

ALTER TABLE XY90260.IO_SAMPLE_DELTAS ADD (
  CONSTRAINT IO_SAMPLE_DELTAS_FROM_FK
 FOREIGN KEY (ID_FROM)
 REFERENCES XY90260.IO_SAMPLES (ID),
  CONSTRAINT IO_SAMPLE_DELTAS_TO_FK
 FOREIGN KEY (ID_TO)
 REFERENCES XY90260.IO_SAMPLES (ID));

-
-- IO_SAMPLE_DELTAS  (Table)
--
--  Dependencies:
--   IO_SAMPLES (Table)
--
CREATE TABLE XY90260.IO_SAMPLE_DELTAS
(
  ID_FROM       NUMBER                              NULL,
  ID_TO         NUMBER                              NULL,
  IO_SPEED_MBS  NUMBER                              NULL
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

--
-- IO_SAMPLE_DELTAS_UK  (Index)
--
--  Dependencies:
--   IO_SAMPLE_DELTAS (Table)
--
CREATE UNIQUE INDEX XY90260.IO_SAMPLE_DELTAS_UK ON XY90260.IO_SAMPLE_DELTAS
(ID_FROM, ID_TO)
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

--
-- Non Foreign Key Constraints for Table IO_SAMPLE_DELTAS
--
ALTER TABLE XY90260.IO_SAMPLE_DELTAS ADD (
  CONSTRAINT IO_SAMPLE_DELTAS_UK
 UNIQUE (ID_FROM, ID_TO)
    USING INDEX
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

--
-- Foreign Key Constraints for Table IO_SAMPLE_DELTAS
--
ALTER TABLE XY90260.IO_SAMPLE_DELTAS ADD (
  CONSTRAINT IO_SAMPLE_DELTAS_FROM_FK
 FOREIGN KEY (ID_FROM)
 REFERENCES XY90260.IO_SAMPLES (ID));

ALTER TABLE XY90260.IO_SAMPLE_DELTAS ADD (
  CONSTRAINT IO_SAMPLE_DELTAS_TO_FK
 FOREIGN KEY (ID_TO)
 REFERENCES XY90260.IO_SAMPLES (ID));

The script to take a sample:

CREATE OR REPLACE procedure XY90260.take_io_sample as

  i       number;
  mx      number;
  s       number;
begin

select max(id) into mx from io_samples;

select nvl(max(id),1)+1 into i from io_samples;

INSERT INTO io_samples (
 id
 ,io_timestamp
,physical_read_io_requests
,physical_write_io_requests
,physical_read_total_mbr
,physical_write_total_mbr
,physical_read_total_bytes
,physical_write_total_bytes)
SELECT
i
,
sysdate
,
sum(decode(name,'physical read total IO requests',value,0)
  - decode(name,'physical read total multi block requests',value,0))
  ,
sum(decode(name,'physical write total IO requests',value,0)
  - decode(name,'physical write total multi block requests',value,0)) sw2
  ,
sum(decode(name,'physical read total multi block requests',value,0)) lr2
, sum(decode(name,'physical write total multi block requests',value,0)) lw2
, sum(decode(name,'physical read total bytes',value,0)) tbr2
, sum(decode(name,'physical write total bytes',value,0)) tbw2
FROM v$sysstat;

-- automatically calculate delta with most recent other sample

calc_io_sample_delta(mx,i,s);

dbms_output.put_line(s);

end;
/

The script to do the required scheduling:

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'XY90260.TAKE_IO_SAMPLE_JOB'
      ,start_date      => TO_TIMESTAMP_TZ('2009/12/20 14:12:19.340308 +02:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
      ,repeat_interval => 'freq=hourly; byminute=0'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'BEGIN take_io_sample; END;'
      ,comments        => 'Instance level IO sampling.'
    );
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
     ,attribute => 'RESTARTABLE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
     ,attribute => 'LOGGING_LEVEL'
     ,value     => SYS.DBMS_SCHEDULER.LOGGING_RUNS);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
     ,attribute => 'MAX_FAILURES');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
     ,attribute => 'MAX_RUNS');
  BEGIN
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
      ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
       ,attribute => 'STOP_ON_WINDOW_CLOSE'
       ,value     => FALSE);
  EXCEPTION
    -- could fail if program is of type EXECUTABLE...
    WHEN OTHERS THEN
      NULL;
  END;
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
     ,attribute => 'JOB_PRIORITY'
     ,value     => 3);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
     ,attribute => 'SCHEDULE_LIMIT');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
     ,attribute => 'AUTO_DROP'
     ,value     => TRUE);

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'XY90260.TAKE_IO_SAMPLE_JOB');
END;
/

From which it obviously follows that the following query will give you instance throughput:

select
  to_char(n.io_timestamp,'YYYY-MM-DD:HH24:MI:SS') AS TS, IO_SPEED_MBS
  ,n.PHYSICAL_READ_IO_REQUESTS-prev.PHYSICAL_READ_IO_REQUESTS+n.PHYSICAL_WRITE_IO_REQUESTS-prev.PHYSICAL_WRITE_IO_REQUESTS
    as total_io_requests  
from
  io_samples       n,
  io_samples       prev, 
  io_sample_deltas d
where
  n.id = d.ID_TO
  AND
  prev.id = (select max(id) from io_samples where id < n.id)
order by
  n.io_timestamp;

With a bit of Excel magic you can produce a daily load profile such as:

For the last few years, my focus has been on custom ASP.NET web application, I have been developing custom solution's since Classic ASP, my team has been developing since ASP.NET 1.1 to ASP.NET 3.5.  over the years we have formed code libraries, well structured processes, tried and tested business logic that we can re-use for other clients, and we now have great relationships with partners that help us do what we do better and fastet.

 So, over the year, we have finally reach a good place on fast delivery with great quality.  but our focus is on custom development.  web applications built according to one clients unique requirements.  Cant this great skillset be used for product development?  Product development is a great way to build something once, and sell it to many people.  Product development allows you to sell the solution for a much cheaper price, and the developers focus is now on improving the product which is alot better than building full solutions from scratch.

 well, it is a great idea, and something i wanted to focus on for a while, but is it possible?  Firstly, the thing about a product is that you want to build it for a large audience, and its best to do this with a subject matter expert, building something that you think someone else wants, bill be something no one wants, with a subject matter expert, you can identify the requirements straight from the end users mouth.

Secondly, theres the finance, with a custom app, you have a client, client provides requirements, you build solution as per clients requirements, and you get paid, its simple, its safe, risk of not getting paid is minimised.  with products, you are building something, for an audience, not a client, the system must be flexible enough to handle the unique requirements of the audiences.  requiremetns that you may not know, and if not available, is a deal breaker, and it will not be considered.  so development time is longer and the risk of seeing any monies is greater.

So, you need subject matter expert, sufficient resource time that is basically doing work that may not see any revenue, making it a high risk implementation, now consider the opportunity cost, of safe, garanteed revenue inplementations of custom work that you are essentially giving up on.    Seems like something not worth going into.

So, lets get the best of both worlds, let get the resources to work with custom solutions, and during those time's between projects, get them to work on a product.  I direction I have tried for about 2 years now, and to date, I do not have one complete product.  the problem with this implementation is that paying clients take priority, meaning that product development is always last on the priority list.   meaning the breaks in the products are frequent and long.  the momentum of the product drops and 8 months later, you are only 20% into the development phase, and you say to yourself, this is just not worth it, and you scrap it all together and focus on keeping the paying projects on track.

Now what? does this mean there is no way for software development companies to build products and custom solutions?  well, there is, and right now, I changed my approach a bit and started focusing on hiring additional developers that will focus on product development full time, we also focus on hiring subject matter experts.  a high risk expense, but we are not loosing out on the opportunity cost of custom work, since that work is completed by a different set of developers.  We are currently focusing on the following products ...

Process Queue web application, a graphical representation of tasks assigned to a group of workers, managed by a process manager, and handles task prioritising and detailed reporting.

A support site, an applicaiton that helps clients add and monitor support ticket items, with great reports that take the SLA agreement into account

SharePoint applications, with the launch of our SharePoint Konsult department, and with the assistance of our SharePoint MVP, we have a great skill set in building products for SharePoint.  SAP, and project management has taken our focus.  but watch this space to see what eventually reaches a complete state.

I have recently evaluated a few code generators and (Object Relational Model) ORM frameworks in .NET, to my surprise, there were quite a few of them all with there own unique sets of pro’s and con’s and designed for various stages of development.

Why do we need an ORM/Code generator?

The main purpose here is to eliminate writing repetitive and static code; this will lead to a higher quality product, developed in a much shorter time. 

The repetitive/static code I am referring to is CRUD (Create, Read, Update and Delete), CRUD is common and is required by almost all applications, it is repetitive and error prone, Crud requires lots of testing time which can be reduced if code is automatically generated and it can take a lot of time (like weeks) if the database used is huge.

The Evaluation 

As mentioned, there are many ORM/code generators out there, each with there own pro’s and con’s, these are the features I looked for when selecting our ORM.

• Support for hierarchies and relationships,
• Support for grouping and aggregate functions,
• Support for transactions,
• Database independent,
• Concurrency,
• Performance and footprint,
• Learning curve and simplicity with which it can be integrated into code,
• Up to date documentation,
• Online support,
• Reasonable price.

End Result 

 
My evaluation has lead to an ORM and code generator that complement each other well, satisfy all of the above requirements, and has now been implemented as the core foundation of our development framework. 

 
Implementation has been successful and development time and quality of the product has been noticeably improved within the first week of implementation. This has a knock on effect that affects the clients directly, as greatly reduced development time means cheaper pricing and a happier client.

Nadir Kamdar

SMC Enterprise has many experts, we have our IT gurus such as developers, database administrators, project managers, network administrators and so on, we also have our non-IT experts such as sales, HR and recruitment and human relations.

After notifying everyone of this great new blog available for everyone to contribute as they see fit, many of the non-IT experts responded with a “Why do we need a blog?”. A fair question, I guess, in fact, it’s a “Blog worthy” question.

This video seems to describe the business blog advantages very well, have a look at it, it cleared things for many non-IT experts here.

Nadir Kamdar

Hello All, and welcome to my first Blog. 

I guess its only right to start off with an introduction.  My name is Nadir Kamdar,  I am a Microsoft Consultant for SMC Enterprise. 

I gratuated from the University of Cape Town back in 1999, with a B-Com degree, and majored in Infomation Systems, I soon started working for SMC Enterprise (then knowned as Shocked) as ASP Developer.  I have from then till know specialised in ASP, ASP.NET, SQL, Sharepoint and a few other Microsoft toys.  

I have worked in Vodacom, MEC-SA, City Of Cape Town,  Creda Communications and even in Saudi for a company called Saudi Aramco.

I am currently more a Project Manager/Consultant/Developer, but plan to use this blog to assist developers, as well as talk about things that i believe are of interest in the Microsoft IT space and also talk about the services offered in SMC Enterprise.

SMC Enterprise has many experts, and this blog will be used by them as well, so I am sure this space will provide a interesting source of information no matter whats your interest.

Nadir Kamdar