Find Communities by: Category | Product

Eddy Yang

How to calculate Oracle Lic

Posted by Eddy Yang Apr 26, 2012

Today I read the Oracle software licensing requirement, which is posted by Nick on https://community.emc.com/docs/DOC-16354. This document is very helpful.

 

After reading, I like to summarize the Oracle lic calculation, as we know, it is based on # of CPU cores, # of lIc = core factor * # of CPU cores. To various CPU families, you can find its related core factors here, http://www.oracle.com/us/corporate/contracts/processor-core-factor-table-070634.pdf

 

First, we need to know the CPU type on your Oracle systems.

$>psrinfo -pv

The physical processor has 64 virtual processors (0-63)

UltraSPARC-T2+ (chipid 0, clock 1415 MHz)

The physical processor has 64 virtual processors (64-127)

UltraSPARC-T2+ (chipid 1, clock 1415 MHz)

We can tell, it is UltraSPARC-T2+

 

Second, we check with the core factor table with searching UltraSPARC-T2+, we can find the according factor is 0.5

 

Third, we go to figure out # of CPU Core.

Use kstat -m cpu_info or cat /proc/cpuinfo under linux.

If you have a huge multi-core system, you can use the script to calculate them. The one is here, http://blogs.oracle.com/mandalika/entry/solaris_show_me_the_cpu

to understand how much redo log / archive redo log is gnerated with instance per hour is very important to any online backup solution design.

 

I find script, like share with you guys.

 

WITH times AS

(SELECT /*+ MATERIALIZE */

   hour_end_time

    FROM (SELECT (TRUNC(SYSDATE, 'HH') + (2 / 24)) - (ROWNUM / 24) hour_end_time

            FROM DUAL

          CONNECT BY ROWNUM <= (1 * 24) + 3),

         v$database

   WHERE log_mode = 'ARCHIVELOG')

SELECT hour_end_time, NVL(ROUND(SUM(size_mb), 3), 0) size_mb, i.instance_name

  FROM(

SELECT hour_end_time, CASE WHEN(hour_end_time - (1 / 24)) > lag_next_time THEN(next_time + (1 / 24) - hour_end_time) * (size_mb / (next_time - lag_next_time)) ELSE 0 END + CASE WHEN hour_end_time < lead_next_time THEN(hour_end_time - next_time) * (lead_size_mb / (lead_next_time - next_time)) ELSE 0 END + CASE WHEN lag_next_time > (hour_end_time - (1 / 24)) THEN size_mb ELSE 0 END + CASE WHEN next_time IS NULL THEN(1 / 24) * LAST_VALUE(CASE WHEN next_time IS NOT NULL AND lag_next_time IS NULL THEN 0 ELSE(size_mb / (next_time - lag_next_time)) END IGNORE NULLS) OVER(

ORDER BY hour_end_time DESC, next_time DESC) ELSE 0 END size_mb

  FROM(

SELECT t.hour_end_time, arc.next_time, arc.lag_next_time, LEAD(arc.next_time) OVER(

ORDER BY arc.next_time ASC) lead_next_time, arc.size_mb, LEAD(arc.size_mb) OVER(

ORDER BY arc.next_time ASC) lead_size_mb

  FROM times t,(

SELECT next_time, size_mb, LAG(next_time) OVER(

ORDER BY next_time) lag_next_time

  FROM(

SELECT next_time, SUM(size_mb) size_mb

  FROM(

SELECT DISTINCT a.sequence#, a.next_time, ROUND(a.blocks * a.block_size / 1024 / 1024) size_mb

  FROM v$archived_log a,(

SELECT /*+ no_merge */

CASE WHEN TO_NUMBER(pt.VALUE) = 0 THEN 1 ELSE TO_NUMBER(pt.VALUE) END VALUE

  FROM v$parameter pt

WHERE pt.name = 'thread') pt

WHERE a.next_time > SYSDATE - 3 AND a.thread# = pt.VALUE AND ROUND(a.blocks * a.block_size / 1024 / 1024) > 0)

GROUP BY next_time)) arc

WHERE t.hour_end_time = (TRUNC(arc.next_time(+), 'HH') + (1 / 24)))

WHERE hour_end_time > TRUNC(SYSDATE, 'HH') - 1 - (1 / 24)), v$instance i

WHERE hour_end_time <= TRUNC(SYSDATE, 'HH')

GROUP BY hour_end_time, i.instance_name

ORDER BY hour_end_time

/

Sample Output:

HOUR_END_TIME    SIZE_MB INSTANCE_NAME
------------- ---------- ----------------
2011/9/29 1:0       2.92 VPROD1
2011/9/29 2:0       2.92 VPROD1
2011/9/29 3:0       2.92 VPROD1
2011/9/29 4:0       2.92 VPROD1
2011/9/29 5:0       2.92 VPROD1
2011/9/29 6:0       2.92 VPROD1
2011/9/29 7:0       2.92 VPROD1
2011/9/29 8:0       2.92 VPROD1
2011/9/29 9:0       2.92 VPROD1
2011/9/29 10:       2.92 VPROD1
2011/9/29 11:       2.92 VPROD1
2011/9/29 12:      3.537 VPROD1
2011/9/29 13:       3.55 VPROD1
2011/9/29 14:       3.55 VPROD1
2011/9/29 15:       3.55 VPROD1
2011/9/29 16:       3.55 VPROD1
2011/9/29 17:       3.55 VPROD1
2011/9/29 18:       3.55 VPROD1
2011/9/29 19:       3.55 VPROD1
2011/9/29 20:       3.55 VPROD1
Eddy Yang

Script: List Spfile on ASM

Posted by Eddy Yang Apr 25, 2012

As we know, loss of Spfile is trouble and it is hard to use asmcmd to look up.

 

Here is script shared with your guys, hope it could save your time.

 

--- listspfiles.sql

--- Purpose: Sample script to list spfiles kept in ASM instance

--- Usage: This should be run against an ASM instance,

--- not a database instance.

---

--- cut here --%<----%<----%<----%<----%<----%<--

 

--list all spfiles

 

set lines 120

col full_path for a110

SELECT full_path, dir, sys

FROM

(SELECT

CONCAT('+'||gname,SYS_CONNECT_BY_PATH(aname,'/')) full_path,

dir, sys FROM

(SELECT g.name gname,

a.parent_index pindex, a.name aname,

a.reference_index rindex, a.ALIAS_DIRECTORY dir,

a.SYSTEM_CREATED sys

FROM v$asm_alias a, v$asm_diskgroup g

WHERE a.group_number = g.group_number)

START WITH (MOD(pindex, POWER(2, 24))) = 0

CONNECT BY PRIOR rindex = pindex

ORDER BY dir desc, full_path asc)

WHERE UPPER(full_path) LIKE '%SPFILE%'

/

 

Sample output:

 

FULL_PATH                                                                                                      D S

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

+DATA/Aspfile.ora                                                                                              N N

+DATA/VPROD/PARAMETERFILE/spfile.273.766620265                         N Y

+DATA/VPROD/PARAMETERFILE/spfile.365.773976489                         N Y

+DATA/VPROD/spfileVPROD.ora                                                                   N N

Using IO Calibration to assess Oracle IO stack

 

Last weekend, I had a chat with my friend, Oracle DBA in Logistics Service Provider, which upgraded their Oracle system storage, on this conversation, the method leveraged to validated their storage upgrade plan inspire my interest.

 

They do not rely on AWR much to do 1st time validation, since it require real-world business load, any unexpected happened would be negative to business. They use IO Calibration to assent Oracle IO stack to tell if it satisfies planned target, which is able to calculate out IOPS and Bandwidth under certain latency @ SLA.

 

What is IO Calibration?

IO Calibration is a Oracle feature starting @ 11g, it is based on DBMS_RESOURCE_MANAGER.CALIBRATE_IO(). When Calibrate I/O is invoked it will generate I/O intensive read-only random I/O (db_block_size) and large-block (1MByte) sequential I/O workloads. Unlike various external I/O calibration tools, this tool uses the Oracle code stack and runs in the database, issuing I/O against blocks stored in the database. The results, therefore, much more closely match the actual database performance. Once the workload execution is completed, a summary of the results is provided.

 

How to use IO Calibration?

  • Pre-conditions:
  1. SYSDBA privilege is must
  2. Set TIME_STATISTICS=True
  3. Set ASYNC IO

 

  • Call CALIBRATE_IO

disk_count, max_latency are input field.

disk_count = how many spindle are used for these Oracle instances

max_latency = defined response time SLA.

 

SQL> set serveroutput on;

SQL> DECLARE

  2 lat  INTEGER;

  3 iops INTEGER;

  4 mbps INTEGER;

  5 BEGIN

  6  -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (disk_count,max_latency , iops, mbps, lat);

  7 DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 15, iops, mbps, lat);

  8

  9 DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);

10 DBMS_OUTPUT.PUT_LINE ('latency  = ' || lat);

11 dbms_output.put_line('max_mbps = ' || mbps);

12  end;

13  /

 

max_iops = 127

latency  = 15

max_mbps = 38

 

PL/SQL procedure successfully completed

 

SQL> select * from V$IO_CALIBRATION_STATUS;

 

STATUS        CALIBRATION_TIME

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

IN PROGRESS   1-8 -12 12.02.35.121 PM

 

SQL> alter session set nls_timestamp_format='HH24:MI';

 

Session altered

SQL> col start_time for a10;

SQL> col end_time for a10;

SQL> select * from DBA_RSRC_IO_CALIBRATE;

 

START_TIME END_TIME     MAX_IOPS   MAX_MBPS MAX_PMBPS    LATENCY NUM_PHYSICAL_DISKS

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

12:55      12:59             127         38         14         15                  2

 

Upon the output, we can find 127 IOPS and 38MBps able to reach under 15ms IO latency

Filter Blog

By date:
By tag: