Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

Archive for the ‘Uncategorized’ Category

A Little Hard Drive History and the Big Data Problem

Posted by Tyler Muth on November 2, 2011

Most of the “Big Data Problems” I see are related to performance. Most often these are long running reports or batch processes, but also include data loads. I see a lot of these as one of my primary roles is in leading Exadata “Proof of Value” (POV) engagements with customers. It’s my observation that over 90% of the time, the I/O throughput of the existing system is grossly under-sized. To be fair, the drives themselves in a SAN or NAS are not the bottleneck, it’s the 2x 2 Gbps or 2x 4 Gbps Fibre channel connection to the SAN more often than not. However, the metrics for hard drive performance and capacity over the last 30 or so years were a lot easier to find so that’s what this post is about.

In short, our capacity to store data has far outpaced our ability to process that data. Here’s an example using “personal” class drives to illustrate the point. In the year 2000 I could by a Maxtor UDMA 20 GB hard drive connected locally via (P) ATA which provided roughly 11 MB/s of throughput (the drive might be limited to a bit less, but not much). Today, I can buy a 3 TB Seagate Barracuda XT ST32000641AS that can sustain 123 MB/s of throughput. So, lets say I need to scan (search) my whole drive. 21 years ago that would take 31 minutes. Today it would take 7 hours! What?!?! The time it takes to search the data I can store has increased by a factor of 14x. How can this be? What about Moore’s Law? Sorry, that only works for processors. Oh right, I meant Kryder’s Law. Kryder’s Law states that magnetic disk areal storage density doubles annually and has nothing to do with performance. Well, actually this phenomenon is half the problem since throughput isn’t doubling annually.

I did a little research and found some relevant data on seagate.com, tomshardware.com, and of course The Wayback Machine. Now on to the data and some graphs (click to enlarge graphs):

Seagate ST506

Rodime R032

Seagate ST3550A

Quantum Fireball ST3 2A

IBM DTTA-351010

Seagate Cheetah X15

Seagate Cheetah X15.3

Seagate Cheetah 15k.6

Seagate Cheetah 15k.7

Seagate Pulsar XT.2 SSD

Year 1979 1983 1993 1998 1999 2001 2003 2008 2011 2011
Capacity (MB) 5 10 452 3276 10240 18432 36864 460800 614400 409600
Capacity (GB) 0.005 0.010 0.441 3.2 10 18 36 450 600 400
Throughput (MB/s) 5 0.6 11.1 7.6 9.5 29 63.6 164 204 360
Capacity Factor of Change 1x 2x 90x 655x 2,048x 3,686x 7,373x 92,160x 122,880x 81,920x
Throughput Factor of Change 1x 0.1x 2x 2x 2x 6x 13x 33x 41x 72x
Capacity Percent Change 0% 100% 8,940% 65,436% 204,700% 368,540% 737,180% 9,215,900% 12,287,900% 8,191,900%
Throughput Percent Change 0% -88% 122% 52% 90% 480% 1,172% 3,180% 3,980% 7,100%
Time in Seconds to Read Full Drive 1 16 41 431 1078 636 580 2810 3012 1138

Time-in-Seconds-to-Read-Ful

Capacity-vs-Throughput---Lo

So, what does all of this mean? Well, to me it means if you’re architecting a data warehouse or even small data mart, make sure you focus on the storage. Over and over again I get drawn into discussions about the minutiae of chip speeds or whether Linux is faster than Solaris, yet when I ask about the storage the answer is almost universally “we’ll throw it on the SAN”. OK, how many HBAs and what speed? Is the SAN over-utilized already? etc, etc, etc.

So, how does this relate to Exadata? The central focus of Exadata is I/O throughput. By including processing power in the storage servers, Exadata has a fixed relationship between capacity and throughput. As you add capacity, you add throughput.

Posted in Exadata, Oracle, Uncategorized | 11 Comments »

Off-Topic: Avoid Levana Baby Monitors

Posted by Tyler Muth on September 23, 2011

I know this is completely off topic and I haven’t had time to blog in a while but there’s been a new addition in our family. I know there are a lot of parents out there and after the experience I’ve had with Levana, I’ll do everything in my power to make sure others avoid their products. Here’s a link to my review on amazon : http://sn.im/levana-review. I’ll also try and post some positive product reviews soon to offset this negativity…

Posted in Uncategorized | 12 Comments »

Formatting Output of 11.2 Compression Advisor (DBMS_COMPRESSION)

Posted by Tyler Muth on October 21, 2010

Let me start with a few facts about compression that I feel are a bit confusing or could use better documentation. The Compression Advisor is a package named DBMS_COMPRESSION that allows takes a table as input, then estimates the compression ratio and resulting size in blocks if you compressed the table. You can download a version of it that works in any 9.2+ database from the Advanced Compression page.  However, this will only estimate Advanced Compression / OLTP Compression results. It will NOT estimate the 4 forms of Hybrid Columnar Compression (HCC) which are only available in Exadata. To estimate HCC results, you have to use an 11.2 database as the version of DBMS_COMPRESSION in 11.2 has been enhanced. You don’t need an Exadata machine, just an 11.2 database. For more information on HCC, check out this presentation (PDF, 2.7 MB). When computing HCC estimates in the 11.2 Compression Advisor, it’s important to note that for each type of compression you wish to test, it creates a complete copy of your table or partition. This is very accurate but PAINFULLY slow, so be warned. If you want to know the results of each type of compression for every table in a schema, it will literally recreate a temporary copy of each table 4 times!

Now that we have that out of the way, lets get to the real reason for this post. The 11.2 compression advisor is functional, but it really leaves it up to you to format the output. Typically you would use DBMS_OUTPUT for this, but I wanted a format that I could easily import into a spreadsheet to do some sorting and compute some aggregates. So, I wrote a pipelined function that is a wrapper on DBMS_COMPRESSION which allows you to run it in a SQL statement.  If you run it in SQL Developer, you can easily export the results as XLS.

You can download the code here. A few notes which are in the head of the function as well:

  • Tables and partitions must have at least 1,000,000 rows based on the dbms_compression.comp_ratio_minrows global.
  • Computing HCC compression recreates the entire table or partition, which is very accurate but VERY slow.
    Update: Thanks to Kam Shergill for pointing out the “subset_numrows” in dbms_compression. I’ve added a new parameter called p_subset_numrows.  You can pass in a number of rows such as 1500000 or a percentage such as 50%.  However, the number of rows per segment still has to be above 1000000 for the compression advisor to work.
  • This is an “Invokers Rights” function, so it queries “USER_” dictionary views of the schema that calls the function, not the schema it’s installed in.
  • The p_compression_level parameter is a bitwise parameter based on the table below (which comes from the package spec of DBMS_COMPRESSION). 2+4 will compute OLTP and HCC QUERY HIGH. 2+4+8+16+32 (or 62) will compute ALL levels of compression. Warning!!! Each level of HCC is very slow, so 62 will take forever.
COMP_FOR_OLTP 2
HCC COMP_FOR_QUERY_HIGH 4
HCC COMP_FOR_QUERY_LOW 8
HCC COMP_FOR_ARCHIVE_HIGH 16
HCC COMP_FOR_ARCHIVE_LOW 32

Function Spec:

create or replace function get_compression_stats(
    p_table_name            in varchar2,
    P_compression_level        in number,
    p_subset_num_rows        in varchar2 default '100%',
    p_scratch_tablespace     in varchar2 default 'USERS'
    )
return table_compression_stats 
authid current_user 
pipelined 
is
pragma autonomous_transaction;

Example 1:

-- For the EMPLOYEES_LARGE table, compute all compression estimates (2+4+8+16+32), create the temporary copy of EMPLOYEES_LARGE in the USERS tablespace
select * from table(get_compression_stats('EMPLOYEES_LARGE',2+4+8+16+32,'USERS'));

Example 2:

-- For the ALL_OBJS table, compute compression estimates for HCC COMP_FOR_QUERY_HIGH and COMP_FOR_QUERY_LOW  (4+8), create the temporary copy of ALL_OBJS in the USERS tablespace
select * from table(get_compression_stats('ALL_OBJS',4+8,'USERS'));

Output of Example 2 (HTML “unload” from SQL Developer 3.x. Empty columns removed for width):

TABLE_NAME PARTITION_NAME COMMENTS CURRENT_SIZE_MB NUM_ROWS QUERY_HIGH_MB QUERY_LOW_MB QUERY_HIGH_RATIO QUERY_LOW_RATIO
ALL_OBJS 142 1205180 0.66 0.8 170.7 142.2

Posted in Uncategorized | 1 Comment »

ODTUG Kaleidoscope 2010

Posted by Tyler Muth on July 1, 2010

I had the great opportunity to attend and work ODTUG this year in Washington, DC. I went into it expecting to work a few of the hands-on labs for APEX and hopefully catch up with a few old friends. However, I really underestimated the value of getting that many brilliant people together. Every session I attended was really well thought out and packed with great information. From the APEX plugin panel to Cary Millsap’s discussion on SQL trace files. I also had a lot of great discussions with people that were not presenting that shared some really creative ideas. Even the questions people were asking in the hands-on labs were really insightful and thought provoking. If you ever get the opportunity, I strongly recommend you attend.

It was also great to finally meet a lot of people face to face that I’ve “worked with” online for years. The Oracle community, especially the APEX corner of it is a exceptional resource and I’m really lucky to be a part of it. I’ve dabbled in other technologies (which shall remain nameless) and tried to learn from their respective communities and I’ve never seen one as friendly and willing to help as ours. On a related note, thanks to everyone for the positive feedback on logger.  I really had no idea there were so many people using it.

Finally, it was really great to hang out with my friends from the APEX team, both old and new. They’re some of the most talented and motivated people I’ve had the pleasure of working with. It’s too bad this event isn’t in DC every year… twice a year 😉

Posted in Uncategorized | Leave a Comment »

Exadata Smart Scan – Just The Columns I Want

Posted by Tyler Muth on June 23, 2010

In my last post I talked about the Smart Scan component of cell offload processing.  I showed how much network traffic this can save between storage and the Database. That savings comes from the fact that the storage filters the results down just the rows and columns you asked for.  This is just a quick post to demonstrate the difference in bytes shipped across the wire between selecting one column and selecting all columns.

-- query v$mystat

select *
 from all_objs
 where object_type = 'TABLE'
 and object_name like '%RESOURCE%';

-- query v$mystat

select object_name
 from all_objs
 where object_type = 'TABLE'
 and object_name like '%RESOURCE%';

-- query v$mystat

 ALL_COLUMNS_MB    ONE_COLUMN_MB      FACTOR_OF_IMPROVEMENT
------------------ ------------------ ---------------------
 5.4               1.8                3.0

Posted in Exadata, Oracle, Uncategorized | 2 Comments »

Employees Sample Data Simplified

Posted by Tyler Muth on February 16, 2010

I use the HR schema sample data for a lot of examples or when I just need to work through a problem.  It’s great data, but many times I want to use the EMPLOYEES table without joining in DEPARTMENTS and JOBS as well as a self join of EMPLOYEES to get the manager name.  So, I used the following CTAS to create a simplified EMPLOYEES_ROLLUP table. I then exported the data as insert statements using SQL Developer (love that feature).

create table employees_rollup as
select e.employee_id,e.first_name, e.last_name, lower(e.email)||'@nowhere.com' email, e.phone_number, e.hire_date,e.salary,
       j.job_title,
       e2.first_name ||' '|| e2.last_name manager,e.manager_id,
       d.department_name
  from hr.employees e, hr.departments d, hr.employees e2, hr.jobs j
 where e.department_id = d.department_id
   and e.manager_id = e2.employee_id
   and e.job_id = j.job_id
/

The result is:

select * from employees_rollup;

EMPLOYEE_ID            FIRST_NAME           LAST_NAME                 EMAIL                                 PHONE_NUMBER         HIRE_DATE                 SALARY                 JOB_TITLE                           MANAGER                                        MANAGER_ID             DEPARTMENT_NAME
---------------------- -------------------- ------------------------- ------------------------------------- -------------------- ------------------------- ---------------------- ----------------------------------- ---------------------------------------------- ---------------------- ------------------------------
200                    Jennifer             Whalen                    jwhalen@nowhere.com                      515.123.4444                  17-SEP-87                 4400                   Administration Assistant            Neena Kochhar                                  101                    Administration
201                    Michael              Hartstein                 mhartste@nowhere.com                  515.123.5555         17-FEB-96                 13000                  Marketing Manager                   Steven King                                    100                    Marketing
202                    Pat                  Fay                       pfay@nowhere.com                      603.123.6666         17-AUG-97                 6000                   Marketing Representative            Michael Hartstein                              201                    Marketing

Unroll the following code block to the full DDL including insert statements (hint: you can use “View Source” or “Copy to Clipboard” on the overlay panel to save it locally):

create table employees_rollup (
	employee_id number(6,0), 
	first_name varchar2(35), 
	last_name varchar2(35), 
	email varchar2(100), 
	phone_number varchar2(20), 
	hire_date date, 
	salary number(8,2), 
	job_title varchar2(50), 
	manager varchar2(70), 
	manager_id number(6,0), 
	department_name varchar2(50) not null enable, 
	 constraint employees_rollup_pk primary key (employee_id) enable
)
/

create index employees_rollup_idx1 on employees_rollup(first_name,last_name)
/
create index employees_rollup_idx2 on employees_rollup(department_name)
/
create index employees_rollup_idx3 on employees_rollup(hire_date)
/
create index employees_rollup_idx4 on employees_rollup(employee_id,manager_id)
/
 

Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (200,'Jennifer','Whalen','jwhalen@nowhere.com','515.123.4444',to_timestamp('17-SEP-87 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),4400,'Administration Assistant','Neena Kochhar',101,'Administration')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (201,'Michael','Hartstein','mhartste@nowhere.com','515.123.5555',to_timestamp('17-FEB-96 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),13000,'Marketing Manager','Steven King',100,'Marketing')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (202,'Pat','Fay','pfay@nowhere.com','603.123.6666',to_timestamp('17-AUG-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),6000,'Marketing Representative','Michael Hartstein',201,'Marketing')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (115,'Alexander','Khoo','akhoo@nowhere.com','515.127.4562',to_timestamp('18-MAY-95 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),3100,'Purchasing Clerk','Den Raphaely',114,'Purchasing')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (116,'Shelli','Baida','sbaida@nowhere.com','515.127.4563',to_timestamp('24-DEC-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2900,'Purchasing Clerk','Den Raphaely',114,'Purchasing')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (117,'Sigal','Tobias','stobias@nowhere.com','515.127.4564',to_timestamp('24-JUL-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2800,'Purchasing Clerk','Den Raphaely',114,'Purchasing')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (118,'Guy','Himuro','ghimuro@nowhere.com','515.127.4565',to_timestamp('15-NOV-98 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2600,'Purchasing Clerk','Den Raphaely',114,'Purchasing')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (119,'Karen','Colmenares','kcolmena@nowhere.com','515.127.4566',to_timestamp('10-AUG-99 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2500,'Purchasing Clerk','Den Raphaely',114,'Purchasing')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (114,'Den','Raphaely','drapheal@nowhere.com','515.127.4561',to_timestamp('07-DEC-94 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),11000,'Purchasing Manager','Steven King',100,'Purchasing')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (203,'Susan','Mavris','smavris@nowhere.com','515.123.7777',to_timestamp('07-JUN-94 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),6500,'Human Resources Representative','Neena Kochhar',101,'Human Resources')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (198,'Donald','OConnell','doconnel@nowhere.com','650.507.9833',to_timestamp('21-JUN-99 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2600,'Shipping Clerk','Kevin Mourgos',124,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (199,'Douglas','Grant','dgrant@nowhere.com','650.507.9844',to_timestamp('13-JAN-00 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2600,'Shipping Clerk','Kevin Mourgos',124,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (141,'Trenna','Rajs','trajs@nowhere.com','650.121.8009',to_timestamp('17-OCT-95 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),3500,'Stock Clerk','Kevin Mourgos',124,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (142,'Curtis','Davies','cdavies@nowhere.com','650.121.2994',to_timestamp('29-JAN-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),3100,'Stock Clerk','Kevin Mourgos',124,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (143,'Randall','Matos','rmatos@nowhere.com','650.121.2874',to_timestamp('15-MAR-98 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2600,'Stock Clerk','Kevin Mourgos',124,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (144,'Peter','Vargas','pvargas@nowhere.com','650.121.2004',to_timestamp('09-JUL-98 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2500,'Stock Clerk','Kevin Mourgos',124,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (137,'Renske','Ladwig','rladwig@nowhere.com','650.121.1234',to_timestamp('14-JUL-95 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),3600,'Stock Clerk','Shanta Vollman',123,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (138,'Stephen','Stiles','sstiles@nowhere.com','650.121.2034',to_timestamp('26-OCT-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),3200,'Stock Clerk','Shanta Vollman',123,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (139,'John','Seo','jseo@nowhere.com','650.121.2019',to_timestamp('12-FEB-98 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2700,'Stock Clerk','Shanta Vollman',123,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (140,'Joshua','Patel','jpatel@nowhere.com','650.121.1834',to_timestamp('06-APR-98 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2500,'Stock Clerk','Shanta Vollman',123,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (133,'Jason','Mallin','jmallin@nowhere.com','650.127.1934',to_timestamp('14-JUN-96 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),3300,'Stock Clerk','Payam Kaufling',122,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (134,'Michael','Rogers','mrogers@nowhere.com','650.127.1834',to_timestamp('26-AUG-98 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2900,'Stock Clerk','Payam Kaufling',122,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (135,'Ki','Gee','kgee@nowhere.com','650.127.1734',to_timestamp('12-DEC-99 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2400,'Stock Clerk','Payam Kaufling',122,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (136,'Hazel','Philtanker','hphiltan@nowhere.com','650.127.1634',to_timestamp('06-FEB-00 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2200,'Stock Clerk','Payam Kaufling',122,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (129,'Laura','Bissot','lbissot@nowhere.com','650.124.5234',to_timestamp('20-AUG-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),3300,'Stock Clerk','Adam Fripp',121,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (130,'Mozhe','Atkinson','matkinso@nowhere.com','650.124.6234',to_timestamp('30-OCT-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2800,'Stock Clerk','Adam Fripp',121,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (131,'James','Marlow','jamrlow@nowhere.com','650.124.7234',to_timestamp('16-FEB-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2500,'Stock Clerk','Adam Fripp',121,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (132,'TJ','Olson','tjolson@nowhere.com','650.124.8234',to_timestamp('10-APR-99 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2100,'Stock Clerk','Adam Fripp',121,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (125,'Julia','Nayer','jnayer@nowhere.com','650.124.1214',to_timestamp('16-JUL-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),3200,'Stock Clerk','Matthew Weiss',120,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (126,'Irene','Mikkilineni','imikkili@nowhere.com','650.124.1224',to_timestamp('28-SEP-98 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2700,'Stock Clerk','Matthew Weiss',120,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (127,'James','Landry','jlandry@nowhere.com','650.124.1334',to_timestamp('14-JAN-99 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2400,'Stock Clerk','Matthew Weiss',120,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (128,'Steven','Markle','smarkle@nowhere.com','650.124.1434',to_timestamp('08-MAR-00 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2200,'Stock Clerk','Matthew Weiss',120,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (120,'Matthew','Weiss','mweiss@nowhere.com','650.123.1234',to_timestamp('18-JUL-96 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),8000,'Stock Manager','Steven King',100,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (121,'Adam','Fripp','afripp@nowhere.com','650.123.2234',to_timestamp('10-APR-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),8200,'Stock Manager','Steven King',100,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (122,'Payam','Kaufling','pkauflin@nowhere.com','650.123.3234',to_timestamp('01-MAY-95 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),7900,'Stock Manager','Steven King',100,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (123,'Shanta','Vollman','svollman@nowhere.com','650.123.4234',to_timestamp('10-OCT-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),6500,'Stock Manager','Steven King',100,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (124,'Kevin','Mourgos','kmourgos@nowhere.com','650.123.5234',to_timestamp('16-NOV-99 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),5800,'Stock Manager','Steven King',100,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (104,'Bruce','Ernst','bernst@nowhere.com','590.423.4568',to_timestamp('21-MAY-91 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),6000,'Programmer','Alexander Hunold',103,'IT')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (105,'David','Austin','daustin@nowhere.com','590.423.4569',to_timestamp('25-JUN-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),4800,'Programmer','Alexander Hunold',103,'IT')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (106,'Valli','Pataballa','vpatabal@nowhere.com','590.423.4560',to_timestamp('05-FEB-98 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),4800,'Programmer','Alexander Hunold',103,'IT')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (107,'Diana','Lorentz','dlorentz@nowhere.com','590.423.5567',to_timestamp('07-FEB-99 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),4200,'Programmer','Alexander Hunold',103,'IT')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (103,'Alexander','Hunold','ahunold@nowhere.com','590.423.4567',to_timestamp('03-JAN-90 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),9000,'Programmer','Lex De Haan',102,'IT')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (204,'Hermann','Baer','hbaer@nowhere.com','515.123.8888',to_timestamp('07-JUN-94 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),10000,'Public Relations Representative','Neena Kochhar',101,'Public Relations')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (145,'John','Russell','jrussel@nowhere.com','011.44.1344.429268',to_timestamp('01-OCT-96 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),14000,'Sales Manager','Steven King',100,'Sales')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (101,'Neena','Kochhar','nkochhar@nowhere.com','515.123.4568',to_timestamp('21-SEP-89 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),17000,'Administration Vice President','Steven King',100,'Executive')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (102,'Lex','De Haan','ldehaan@nowhere.com','515.123.4569',to_timestamp('13-JAN-93 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),17000,'Administration Vice President','Steven King',100,'Executive')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (109,'Daniel','Faviet','dfaviet@nowhere.com','515.124.4169',to_timestamp('16-AUG-94 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),9000,'Accountant','Nancy Greenberg',108,'Finance')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (110,'John','Chen','jchen@nowhere.com','515.124.4269',to_timestamp('28-SEP-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),8200,'Accountant','Nancy Greenberg',108,'Finance')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (111,'Ismael','Sciarra','isciarra@nowhere.com','515.124.4369',to_timestamp('30-SEP-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),7700,'Accountant','Nancy Greenberg',108,'Finance')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (112,'Jose Manuel','Urman','jmurman@nowhere.com','515.124.4469',to_timestamp('07-MAR-98 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),7800,'Accountant','Nancy Greenberg',108,'Finance')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (113,'Luis','Popp','lpopp@nowhere.com','515.124.4567',to_timestamp('07-DEC-99 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),6900,'Accountant','Nancy Greenberg',108,'Finance')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (108,'Nancy','Greenberg','ngreenbe@nowhere.com','515.124.4569',to_timestamp('17-AUG-94 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),12000,'Finance Manager','Neena Kochhar',101,'Finance')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (205,'Shelley','Higgins','shiggins@nowhere.com','515.123.8080',to_timestamp('07-JUN-94 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),12000,'Accounting Manager','Neena Kochhar',101,'Accounting')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (206,'William','Gietz','wgietz@nowhere.com','515.123.8181',to_timestamp('07-JUN-94 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),8300,'Public Accountant','Shelley Higgins',205,'Accounting')
/

Posted in Oracle, Uncategorized | Leave a Comment »

The Best Unix-like Windows Command Prompt

Posted by Tyler Muth on November 30, 2009

If you follow this blog you know that my laptop is Windows 7 but I live my life in Linux Servers.  As such, I’m a big fan of the bash shell.  I’ve been running Cygwin on my laptops for as long as I can remember to get all of my favorite Linux commands on Windows.  However, the Windows Command Prompt has always felt a little clunky.  I’ve been using Console2 for a while, which I really like but when you combine it with Cygwin on Windows 7, it hangs a lot which is NOT what I want from a shell. I love the feel of Putty when ssh-ing to a Linux / Unix machine, but something about running sshd on my laptop felt like overkill.

Then I discovered PuttyCyg. Essentially it’s a one-off port of Putty that in addition to ssh, lets you connect to your local installation of Cygwin.  Make sure you read the FAQ on that page.  Note the additional option highlighted in yellow:

The main quirk I ran into was with sqlplus and rman.  Just like on Linux / Unix, it lacks the up-arrow functionality which I can’t live without.  So, make sure you also install rlwrap when installing Cygwin.  Then, in your ~/.bashrc file add the following aliases:

alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
To summarize:
  1. Download and install Cygwin.  Make sure you include the rlwrap package.
  2. Create the 2 aliases in your ~/.bashrc file so sqlplus and RMAN use rlwrap.
  3. Download and unzip PuttyCyg.
  4. Optionally create a shortcut to PuttyCyg in your start menu with the following syntax (substituting your actual directories):
"C:\Program Files\puttycyg-20071202\putty.exe" -cygterm -

Now when you run PuttyCyg you have a Putty client connected to your local Windows machine.  Command Prompt bliss…

Posted in Uncategorized | Tagged: , , | 6 Comments »

“Pretty” Date Format Function

Posted by Tyler Muth on June 26, 2009

I’m not sure who originally wrote this function (probably Tom Kyte), but I needed it yesterday and couldn’t find it. Thankfully Chris Beck tracked it down in some other code I wrote.  I made a few enhancements, but the bulk of the function is the same.  The output is almost identical to the “SINCE” date format available in Application Express.  So, if you pass in a date in the past to this function, the result will be similar to “25 seconds ago”, “1 minute ago”, “5 months ago”, etc.

Function

create or replace function date_text_format (p_date in date)
return varchar2
as
	x	varchar2(255);
begin
	x := 	case
				when sysdate-p_date < 1/1440
					then round(24*60*60*(sysdate-p_date)) || ' seconds'
				when sysdate-p_date < 1/24
					then round(24*60*(sysdate-p_date)) || ' minutes'
				when sysdate-p_date < 1
					then round(24*(sysdate-p_date)) || ' hours'
				when sysdate-p_date < 14
					then trunc(sysdate-p_date) || ' days'
				$IF $$BRITISH $THEN
					when mod(trunc(sysdate-p_date),14) = 0
						then trunc(sysdate-p_date) / 14 || ' fortnights'
				$END
				when sysdate-p_date < 60
					then trunc((sysdate-p_date)/7) || ' weeks'
				when sysdate-p_date < 365
					then round(months_between(sysdate,p_date)) || ' months'
				else round(months_between(sysdate,p_date)/12,1) || ' years'
		   end;
	x:= regexp_replace(x,'(^1 &#91;&#91;:alnum:&#93;&#93;{4,10})s','\1');
	x:= x || ' ago';
	return x;
end date_text_format;
/&#91;/sourcecode&#93;
<h3>Examples</h3>
select date_text_format(sysdate - 3/86400) the_date from dual;
select date_text_format(sysdate - 5/1440) the_date from dual;
select date_text_format(sysdate - 1/24) the_date from dual;
select date_text_format(sysdate - 3.141549) the_date from dual;
select date_text_format(sysdate - 15) the_date from dual;
select date_text_format(sysdate - 120) the_date from dual;
select date_text_format(sysdate - 365) the_date from dual;
--------------------------------------------------------------------
3 seconds ago
5 minutes ago
1 hour ago
3 days ago
2 weeks ago
4 months ago
1 year ago

Posted in Oracle, PLSQL, Uncategorized | Tagged: | 12 Comments »

Rope Memory

Posted by Tyler Muth on February 27, 2009

I watched the Discovery Science series “Moon Machines” a few weeks ago, as I’m pretty much obsessed with all things space or flying. In the “Navigation Computer” episode, there was a section about the memory they used for the Apollo machines that I found fascinating. They actually sent their programs off to a factory to have them woven into a “rope” of 1’s and 0’s!!!  This makes punch-cards look convenient.

Check out the video on YouTube from about 3:00 to 6:00 min:

Posted in Uncategorized | 10 Comments »

What Would You Tell a Potential Blogger?

Posted by Tyler Muth on October 12, 2008

I have a great opportunity to speak to a group of University students about blogging.  I have a lot of advice based on my experiences, but I discovered early on that one of the most valuable aspects of blogging is that you can learn more from your community than they can learn from you (if you are willing to listen).  So, what advice would you offer someone who is considering starting a blog?  These students are undergraduates in the Business School and the title of the course is “Social Networking and Business”. 

The irony of this question is that one piece of advice I have for them is to post often, yet I’ve been slacking lately.  It’s not because I don’t want to blog, I really enjoy it, but I’ve had some pretty big “projects” going on lately and something had to give.  More on that in another post…

Posted in Uncategorized | 3 Comments »