Oracle 11g DB on Windows 7 – Success

Short Version

Tried to install Oracle Database 11.1.0.7 32-bit on Windows 7 32-bit. Failed at Network Configuration section. I found that in my case, the issue was that I installed in a path with spaces in it (c:\program files\oracle\….). Un-installed. Reinstalled in c:\oracle\product\11.1.0.7. Success!!!

Longer Version

OK, I admit it, I’ve been running Windows 7 since the first beta was released and I love it. Yes, my blog title has “Linux” in it, and I love Linux too, but I’ve had a few issues with Linux on a laptop in front of customers and that’s something I can’t afford to do. See, my laptop needs to be really agile. I plug it into a different networks, projectors, multiple monitors, I put a Verizon Air card in it, I plug lots of peripherals into it. Linux has failed me in each of those areas on more than one occasion. On the other hand, the fact that Linux is (in my mind) very consistent, stable, and almost completely unwilling to change is EXACTLY why I LOVE it for servers. Every “system” I currently manage is running Linux (which I chose from the start). It’s my appliance OS. I have scripted, nightly RMAN backups and auto-start scripts so these systems are 99% hands off. The only time they’ve been down is when one of pseudo-data centers has gone completely dark for network and power upgrades. They’re all internal, so I’m a bit more relaxed about OS patches, which means I basically ssh into them once every few months to apply patches. That’s it. OK, that was waaaaaayyyyy too much info to qualify why I’m running Windows on my laptop. On a related note, hey Apple, I’m happy to blog about my experience switching to say, a MacBook Pro….

Anyway, I got a bit off topic. Sorry. So I’m running Windows 7 RC1, build 7100, 32-bit. I downloaded the Windows 2008 Server version of Oracle, v. 11.1.0.7 since Server 2k8 is a similar code base to Windows 7. After I got stuck on the network configuration, I checked the install logs, but there wasn’t anything helpful there. So, I ran netca from the command-line and got error that said something about “error at c:\program blah blah blah”. It was obvious that it truncated the path at the first space. So, once I installed in a path with no spaces, everything went fine.

OHS 10.1.3.3 Had Issues Too

I downloaded Oracle HTTP Server 10.1.3.3 as well since that’s my preferred APEX configuration (no, I haven’t tried the new listener on Windows 7 yet, just Linux). The installation got stuck on the configuration section, so I eventually killed the process. However, it appeared that everything installed correctly so I created a DAD, ran opmnctl startall, and presto, APEX was ready to go. OK, I left out the part about dropping the default 11.1 APEX version and installing APEX 3.2.1, but that’s not really important here.

Hopefully this helps someone out there. I’ll take a look and see if there are bugs filed on these issues and file them if I don’t find anything. Windows 7 isn’t supported by Oracle yet, but it can’t hurt to let them know…

11g SQL Performance Analyzer Rocks!

There are a lot of myths and rules of thumb floating around about Oracle tuning:

• Should I partition this table or not?
• What partitioning scheme should I use?
• What values should I use for OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING?
• Should I regularly rebuild my indexes?

…and the list goes on. Some of the most respected experts in the field, including Tom Kyte and Jonathan Lewis, promote the methodology of “Prove It”, which I support 100%. The problem is that the time required to construct test cases and “proof” can be a bit prohibitive.

Enter SQL Performance Analyzer (SPA). This is one of my favorite 11g new features. SQL Performance Analyzer allows you to capture a SQL Tuning set, play it back multiple times with different options, then get the results of the changes.

Lets start with a quick overview of the process:

1. Capture a SQL Tuning set. This is a pretty straight forward step from Database Console. For my example later in this post, I told it to capture all SQL executed as user FLOWS_030000 for the next 2 minutes. I then ran the Application Express builder to generate the SQL.
2. Run the SQL Performance Analyzer “Guided Workflow” wizard:
   1. Replay the SQL Tuning set the First Time.
   2. Change some parameters or data structures.
   3. Replay the SQL Tuning set the Second Time.
   4. Compare the 2 Tuning Sets and store the results
   5. View the results, including SQL that improved or regressed, and any SQL Plans that changed.

Example

As an example, lets take a look the impact of changing OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING. As I mentioned before, the Tuning Set we’re going to use is just the SQL executed as the user FLOWS_030000, so this is NOT testing the full impact of this change on my database, but you could capture a tuning set for the whole db to test this. I’m not going to walk through all 5 steps of the wizard, because there isn’t much to see. I will say that this whole process, including capturing the SQL Tuning Set, required only 5 minutes.

To get to the SQL Performance Analyzer from the 11g Database Console, click the Performance tab, then click on SQL Performance Analyzer on the lower right, then click Guided Workflow. Here’s a screenshot of the Guided Workflow wizard:

Before starting Step 2, I ran the following code:

alter system set optimizer_index_cost_adj=100 scope=both;
alter system set optimizer_index_caching=0 scope=both;
alter system flush shared_pool;
alter system flush buffer_cache;

Before starting Step 3, I ran the following code:

alter system set optimizer_index_cost_adj=45 scope=both;
alter system set optimizer_index_caching=90 scope=both;
alter system flush shared_pool;
alter system flush buffer_cache;

Below is a screenshot of the results screen. Note that of the 379 SQL statements, 147 had errors. These were due to DML operations in APEX and therefore not an issue here, but this is a great feature to be aware of. Also note that there was a 78% Improvement Impact and a 0% Regression Impact.

Let’s drill into the details of one of the SQL statements so we can see the details of the change on this particular statement:

Finally, the next two cropped screenshots are from the details of a SQL plan that changed, showing us the old and new plans. I cropped the screen into 2 parts so it would fit on this web page.
Old Plan:

New Plan:

As you can see, the new plan uses an index that the old plan did not.

Traditionally, testing these changes against 1 SQL Statement would be relatively simple, but testing the impact on 300+ statements, or for that matter every SQL Statement in the whole database, would be next to impossible.  This is one of those features that DBA’s and developers alike should embrace.   As a developer, you can test any performance change that you want your DBA to make before you make the request.  As a DBA, you can review the impact of any potential changes before you put them in production, drastically reducing the risk.

APEX + Flashback + SQL Developer = Undo

Application Express doesn’t really have an undo button. Fortunately, since it runs in the database it can leverage one of my favorite database features: Flashback. I strongly urge every developer and DBA to get more familiar with flashback (OTN overview here). Some things to consider before you start working with flashback:

• The default value for UNDO_RETENTION parameter is 900 seconds (15 minutes). This is WAY TOO LOW in my opinion. If you have plenty of disk space, I’d aim for something more like 48 hours.
• You need to grant execute on dbms_flashback to schemas that need to use that package. APEX has the grant, but for the SQL Developer demos of this post, I had to grant execute on that package to the schema I was using in SQL Developer.
• The SQL Developer integration requires SQL Developer 1.2.1 and APEX 3.0.1.

The basic process of using flashback with APEX to recover from mistakes is:

1. Export a page or application “as of” some time in the past.
2. Import that component, either as a new application number, or overwriting your existing application.

Here’s a screenshot of the web interface to export an application “as of” a previous time:

The problem with the web interface is that if you delete an application, there’s no longer an interface to export that application. This is where SQL Developer 1.2.1 comes in. Below is a screenshot of SQL Developer after I’ve deleted my APEX application 104. As you can see there are no application in the list:

Now we can flashback our whole APEX session using the following code:
exec dbms_flashback.enable_at_time(SYSTIMESTAMP – INTERVAL ’10’ minute);

Notice that my deleted APEX application has magically reappeared since it’s now 10 minutes ago (and we didn’t need 1.21 gigawatts to do it).

I can then right-click on it and export it:

Just for fun, I tested this concept in 11g with Flashback Data Archive (Total Recall). I wrote a block of PL/SQL to loop over every table in APEX and add it to a Flashback Archive. Sure enough, it worked like a charm! Keep in mind this NOT supported (yet), but I did demo it to the APEX team, so we’ll see what happens in the future. This would allow you to view / export any application as of, say, a year ago! Here’s the code I used:

-- connect as sys
grant flashback archive administer to flows_030000
/

create tablespace apex_fb_db_arch1
datafile 'apex_fb_db_arch1.dbf'
size 100m reuse autoextend on next 1m maxsize 500m
nologging extent management local segment space management auto
/

alter user flows_030000 quota unlimited on apex_fb_db_arch1
/

alter session set current_schema=FLOWS_030000
/

create flashback archive apex_fb1
tablespace apex_fb_db_arch1
quota 500m
retention 1 year
/

declare
  fb_enabled        exception;
  pragma 			exception_init (fb_enabled, -55600);
begin
  for c1 in (select table_name from all_tables where owner='FLOWS_030000' and table_name like 'WWV_%')
  loop
    begin
      dbms_output.put_line(c1.table_name);
      execute immediate 'alter table '||c1.table_name||' flashback archive apex_fb1';
      exception
        -- ignore errors from any tables already in an archive, allow all other errors to raise
        when fb_enabled then null;
      end;
  end loop;
end;
/

11g: SQL Pivot

Just wanted to post a quick example of the new 11g SQL Pivot syntax (documented here).

Here’s the old pivot method (I believe Tom Kyte invented this one):

select sum("10") "10",sum("20") "20",
       sum("30") "30",sum("40") "40",
	   sum("50") "50",sum("60") "60",
	   sum("70") "70",sum("80") "80",
	   sum("90") "90",sum("100") "100",
	   sum("110") "110"
  from(
select max(decode(department_id,10,salary,null)) "10",
       max(decode(department_id,20,salary,null)) "20",
       max(decode(department_id,30,salary,null)) "30",
	   max(decode(department_id,40,salary,null)) "40",
       max(decode(department_id,50,salary,null)) "50",
       max(decode(department_id,60,salary,null)) "60",
	   max(decode(department_id,70,salary,null)) "70",
	   max(decode(department_id,80,salary,null)) "80",
	   max(decode(department_id,90,salary,null)) "90",
       max(decode(department_id,100,salary,null)) "100",
	   max(decode(department_id,110,salary,null)) "110"
  from employees
  group by department_id);

It’s a very effective method that I’ve used many times, but the syntax is a bit clunky.

And now, the 11g Pivot syntax:

select *
  from (select department_id,sum(salary) salary
          from employees
         where department_id > 0
         group by department_id)
  pivot (sum(salary) 
    for department_id in (10,20,30,40,50,60,70,80,90,100,110));



10     20     30     40     50     60     70     80      90     100    110   
------ ------ ------ ------ ------ ------ ------ ------- ------ ------ ------
4400   19000  24900  6500   156400 28800  10000  319500  58000  51600  20300 

1 rows selected