Oracle :- Numbers Table

Background

Let us create and populate a numbers table.

Code

Outline

Creating a numbers table is a bit more complicated.

And, it’s complexity is dependent on Version of Oracle.

Steps

  1. Create Table
    • Identity Column
      • To me identity column is a bit easier that Sequence
  2. Run Statement N Times
    • In Transact SQL, we use go
    • In Oracle
      • We can use the Hierarchy function “connect by level”
      • For Statement

 

SQL

SQL – Identity – Assign Default


create table "number"
    (
        "id" INTEGER
            GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1)
            NOT NULL

        , constraint "PK_Lab_Number"
            primary key
            (
                "id"
            )
    )

;
/

truncate table "number";
/

DECLARE
    l_time PLS_INTEGER;
    l_cpu PLS_INTEGER;

begin

    l_time := DBMS_UTILITY.get_time;
    l_cpu := DBMS_UTILITY.get_cpu_time;

    FOR iLoop IN 1..5000
    LOOP

        insert into "number"
        (
	        "id"
        )
        values
        (
	        default
        );

	END LOOP;

   DBMS_OUTPUT.put_line
      (
        'Time = ' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs '
      );

   DBMS_OUTPUT.put_line
        (
          'CPU Time = ' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs '
        );

end;

/

drop table "number";
/

SQL – Identity – Assign Explicit


create table "number"
    (
        "id" INTEGER
            -- GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) 
            GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1) 
            NOT NULL
        
        , constraint "PK_Lab_Number"
            primary key
            (
                "id"
            )
    )
    
;
/


truncate table "number";
/

DECLARE 
        l_time     PLS_INTEGER;
        l_cpu      PLS_INTEGER;
        lMaxNumber integer;

begin

    l_time := DBMS_UTILITY.get_time; 
    l_cpu := DBMS_UTILITY.get_cpu_time;
 

    FOR iLoop IN 1..1
    LOOP

		insert into "number"
        (
	        "id"
        )
        with cte
		(
		    "vail"
		)
		as
		(
		    select rownum
            FROM   dual
            CONNECT BY LEVEL <= 5000
		)
        select 
           cte."vail"
           
        from cte
		;
		
        
	END LOOP;
	
    DBMS_OUTPUT.put_line
      (
        'Time = ' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs '
      );
    
       
   DBMS_OUTPUT.put_line      
        ( 
          'CPU Time = ' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs '
        );	
	

    select max("id") 
    into   lMaxNumber
    from   "number"
    ;
    
       
   DBMS_OUTPUT.put_line      
        ( 
          'lMaxNumber = ' || TO_CHAR(lMaxNumber)
        );	    
    
end;

/


drop table "number";
/



SQL Server – Create Number Table

Background

A family member and I were discussing the impact of different settings on database performance.

He kept trying to repeat the same point.

After a while, I pressed him to show me.

But, he wouldn’t.

 

Code

Outline

  1. Set statements
    • set nocount on
      • No need for number of records affected
    • set xact_abort
      • Abort sql batch if error
  2. Create Schema, if it does not exist
    • if schema_id(<schema_name> is null, exec(“create schema [schema-name]”)
  3. Create Table, if it does not exist
    • If object_id(<table-name>) is null, create table
  4. Truncate table
    • Remove previous records from table
  5. Insert records
    • Insert into <table>, default values
  6. Go < N>
    • Execute previous batch this many times

 

SQL




use [tempdb]
go

set nocount on;
go

set xact_abort on
go

if schema_id('lab') is null
begin

    exec('create schema [lab] authorization [dbo]')

end
go

if object_id('[lab].[number]') is null
begin


    create table [lab].[number]
    (
        [id] bigint not null
            identity(1,1)

        , constraint [PK_Lab_Number]
            primary key
            (
                [id]
            )
    )

end
go

truncate table [lab].[number]
go

insert into [lab].[number]
default values;
go 5000

declare @lNumberMax bigint

set @lNumberMax = SCOPE_IDENTITY()

print 'Last Assigned Identity is '
        + cast(@lNumberMax as varchar(30))




Summary

I wish my family member would have taken the time to demonstrate his point.

He chose not to.

But, thankfully I have a lab environment, and was able to use transact SQL.

I took the point further and proved him right after more work on my part.

Sid Roth – 2019/Summer

 

Background

Each of us is in one ministry or another.

There is a timing to it.

Time to shut in, time to be released in to it.

God lets us go, and then he calls us in.

 

Videos

  1. Ian and Rosemary Andrews
    • Profile
      • In this Sid Roth’s It’s Supernatural! Classic episode from 2002: When God told Ian Andrews what his calling was, Ian thought God was joking. He wasn’t. Now Ian sees thousands of people healed. Over 300 students were miraculously healed of learning disorders when Ian prayed.
    •  Videos
      • I Thought God Was Joking When He Told Me My Calling…
        • Channel :- Sid Roth’s It’s Supernatural!
          Published on 2019-June-14th
          Link
  2. Live Streams
    • Videos
      • Video #1
        • Channel :- Sid Roth’s It’s Supernatural!
          Published on 2017-May-9th
          Link
  3. Laurie Ditto
    • Videos
      • I Found Myself in Hell. The Reason Why Will Surprise You.
        • Videos
          • Video #1
            Channel :- Sid Roth’s It’s Supernatural!
            Published on 2019-April-7th
            Link

 

Conclusion

There is a lot to say.

But, the Lord, God has his own timing.

There are so many stories wrapped up in intersection of times and spaces.

 

AWS/RDS – Delete Instance

Background

For one of the projects we are standing up in AWS, I will like to test out different Instance configuration options.

Constraints

It has a big database and I did not want to continue eating the cost of standing up various instances.  It is time to stand down of some of the instances.

 

Instances

Current

Here is the current list of RDS Instances.

Image

Text

  1. dev
    • Development
  2. perf
    • Performance
  3. prod
    • Production

Delete Instance

We are not currently using Prod and so I am going to take it “permanently” down.

And, start running load test against Perf; which is the instance I brought up just yesterday.

 

Outline

  1. Access list of Database Instances
  2. Select instance targeted for deletion
  3. Choose the menu options Action/Delete
  4. If “Delete Protection” has been enabled for the Instance
    • We are prompted to “modify the database and disable deletion protection
  5. Access Instance
    • Review Instance’s “Deletion Protection”
    • Check off “Enable Deletion Protection
    • Save Changes
    • Scheduling of Modifications
      • By default changes are effected during next maintenance window
      • Choose to effect this specific change immediately
  6. Return to list of RDS Databases
  7. Choose to delete instance
    • Prompted as to whether we want to take a final snapshot
    • We made the following modifications
      • We chose not to take that final snapshot
      • Confirm deletion by entering “delete me
    • Advised that RDS Instance Deletion is being processed
  8. List of RDS Databases reflects deletion processed

Screen Images

RDS / Instances / List – 01

This database has deletion protection option enabled

 

Instance

Deletion Protection

 

Scheduling of Modifications

Apply During the Next Scheduled Maintenance Window

Apply Immediately

 

RDS / Instances / List – 02

RDS / Instances / Delete Instance ?

Initial

Final

 

RDS / Instances / Deleting Instance

RDS / Instances

SSMS – Having “Select and Print” Share Same Output Space with sqlcmd mode

Background

Ever since I can remember, transact SQL select and print statements send do not share same output area; especially when the the “Results To” is Grid.

SQL

For instance this SQL Statements produces both a Grid and a Text Output.

Code


set nocount on
go

select
        [database] = left(tblSD.[name], 30)

from   sys.databases tblSD

where  tblSD.[database_id] <= 4

order by
        1

print 'Database Current :- '
        + db_name()

Output

Grid

Text

 

Another Way

Like TC, Tevin Campbell, another way, is to have both on same output space.

 

Revised SQL

Use sqlcmd

Outline

  1. Enable SQLCMD Mode by accessing the menu option “Query” \ “SQLCMD mode”.
  2. Add the SQL Statements documented below
    • :OUT STDOUT
      • Enable Text Output for this session
    • GO
      • Execute previous batch

 

Code


set nocount on
go

:OUT STDOUT
GO

select
        [database] = left(tblSD.[name], 30)

from   sys.databases tblSD

where  tblSD.[database_id] <= 4

order by
        1

print 'Database Current :- '
        + db_name()

Output

Text

HP Array Controller Utility CLI – Usage – 01

Background

Let us quickly cover a few commands that can be issued within the HP Array Controller Utility CLI.

Initiate

Let us initiate “HP Array Controller Utility CLI”.

Desktop

Start Menu

HPArrayConfigurationUtility.CLI.01.20190611.0355PM.PNG

CLI Properties

HPArrayConfigurationUtility.CLI.App.01.20190611.0357PM.PNG

Path & App Name

“C:\Program Files (x86)\Compaq\Hpacucli\Bin\hpacucli.exe”

 

Commands

Outline

  1. Controller
    • Show Controller Status
  2. Disks
    • Physical Disk
      • Show information for all Physical Disks
      • Show information for specific Physical Disk
    • Logical Disk
      • Show information for all Logical Disks
      • Show information for specific Logical disk

Tasks

Controller

Show Controller Status

Syntax

controller all show status

Output

Explanation
  1. Smart Array
    • Smart Array P410i
      • Slot 0

 

Disks

Physical Disks

Show Physical Disks Status for all Discs
Syntax

controller slot=0 pd all show

Output

Explanation
  1. Shows information for each physical disks
    • Port
    • Box
    • Bay
    • Drive Type
      • SAS
    • Drive Size
      • 900 GB
    • Status
      • OK

 

Show Physical Disks Status for Specific Disk
Sample

controller slot=0 pd 3C:1:5 show

Output

Explanation
  1. Shows information for targeted physical disks
    • Port
    • Box
    • Bay
    • Interface Type
      • SAS
    • Drive Size
      • 900 GB
    • Rotational Speed
      • 10000
    • Firmware Revision
    • Model
      • Model: HP EG000300JWFVB
    • Physical Count
      • 2
    • Physical Transfer Rate
      • 6.0Gbps, Unknown

 

Logical Disks

Show Logical Disks Status for all Discs
Syntax

controller slot=0 ld all show

Output

Explanation
  1. Shows information for each array
    • Array
      • Logical Drive
        • Size
        • RAID Level
          • RAID 5
          • RAID 1
        • Status
          • OK
Show Logical Disks Status for targeted Disc
Syntax

controller slot=0 ld 4 show

Output

Explanation
  1. Shows information for targeted Logical Drive
    • Size
    • Fault Tolerance
      • RAID 5
    • Status
      • OK
    • Caching
      • Enabled
    • Mount Points
      • Drive F:
    • Drive Type

References

  1. HP
    • Hewlett Packard Enterprise Support Center
      • Hpacucli Utility for Linux – All Commands Guide
        Link
  2. The Geek Stuff
    • hpacucli Command Examples for Linux on HP Servers
      Link
  3. PHUOCTAI HUYNH
    • HP SERVER DISK HEALTH CHECK
      Link

Measuring IOPs on MS Windows – Day 01

Background

Wanted to cover a couple of options for measuring Storage IOPs on a MS Windows machine.

Outline

  1. SysGauge – System Monitors
  2. MS Windows Performance Monitor
    • Disk IO
      • Logical Disk
    • SQL Server ( v2014+ )

Choices

SysGuage

Download SysGuage from here.

Install it on targeted machine.

Run it while sufficient load is running.

Images

MS Windows

Performance Monitor

Counter :- Disk IO

Counter :- Disk IO \ Logical Disk
Image

Explanation
  1. You want to look under LogicalDisk
    • Disk Reads/sec
    • Disk Writes/sec
    • Disk Transfers/sec
      • Disk Reads/sec + Disk Writes/sec

Counter :- SQL Server

Object Name :- SQL Server \ Resource Pool Stats

Please keep in  mind that Resource Pool Stats is only available as of SQL Server v2014.

When not running default MS SQL Server Instance, please look for the named instance complement.

Image

Explanation
  1. SQL Server
    • Object Name :- Resource Pool Stats
      • Counter Name
        • Disk Read IO/sec
        • Disk Write IO/sec