I noticed something for the first time tonight when I was playing around in the Enkitec lab – something that I have been doing wrong for a while. When working in the lab, I often rely on the crsctl command to shut down the entire cluster stack for me. It’s really easy to use “crsctl stop cluster -all” followed by “dcli -l root -g ~/dbs_group /u01/app/184.108.40.206/grid/bin/crsctl stop crs” to get everything down (more...)
One of the problems of functions is that the optimizer generally doesn’t have any idea on how a predicate based on function(col) might affect the cardinality. However, the optimizer group are constantly refining the algorithms to cover an increasing number of special cases more accurately. This is a good thing, of course – but it does mean that you might be unlucky on an upgrade where a better cardinality estimate leads to a less efficient execution plan. Consider for example (more...)
As usual the latest version can be downloaded here.
These are the notes from the change log:
- More info for RAC Cross Instance Parallel Execution: Many sections now show a GLOBAL aggregate info in addition to instance-specific data
- The Parallel Execution Server Set detection and ASSUMED_DEGREE info now makes use of the undocumented PX_STEP_ID and PX_STEPS_ARG info (bit mask part (more...)
We had a client that was running into a strange issue on their Exadata where new connections coming in through the SCAN were failing. After doing some troubleshooting, it was discovered that it was related to one of the SCAN listeners not properly accepting requests from new sessions. The VIP and listener were running, and everything looked normal.
We had the following SCAN setup:
|SCAN VIP #||VIP IP|
One of the problems of “knowing” so much about Oracle is that the more you know the more you have to check on each new release of the software. An incoming ping on my posting “Lock Horror” reminded me that I was writing about 220.127.116.11, and the terminal release is 18.104.22.168, and the whole thing may have changed in 22.214.171.124 – so I ought to re-run some tests (more...)
Here’s a little test you might want to try. Examine the following script, and decide what sort of symptoms you would see in the AWR report.
create global temporary table gtt1(n1 number); execute dbms_workload_repository.create_snapshot; insert into gtt1 values(1); truncate table gtt1; -- repeat insert/truncate for a total of 100 cycles execute dbms_workload_repository.create_snapshot; -- generate an AWR report across the interval.
I don’t need anyone to tell me their results – but if your (more...)
Craig Shallahamer wrote excellent article “When is v$sesstat really updated?”.
And my today post just a little addition and correction about the difference of updating ‘Db time’ and ‘CPU used by this session’ statistics.
In this test I want to show that the statistics will be updated after every fetch call.
I have set arraysize=2, so sql*plus will fetch by 2 rows:
-- Result will be fetched by (more...)
I ran into this error with the Ben Admin Snapshot process. It was the second time I ran into it, so I thought that I had better make sure that I got this in my notes so I would remember it.
Here’s the error…
Costs Not Found For Pgm/Plan/Optn: <benefit program>/<plan type>/<option code>. Application Program Failed In Pgm Section : MOVE-TO-PDEFN Application Program Failed In Pgm Section : TABLE-ACCESS(PSPBATBL) Application Program Failed In Pgm Section (more...)
Special thanks to my colleague who spotted odd results which led us to this finding.
My test (virtual) environment is:
OS: Oracle Enterprise Linux 5.8
DB: Oracle EE 126.96.36.199.12
In test I (more...)
In the Oracle technical universe, it seems that the end of the calendar year is always eventful. First there’s OpenWorld: obviously significant for official announcements and insight into Oracle’s strategy. It’s also the week when many top engineers around the world meet up in San Francisco to catch up over (more...)
As usual the latest version can be downloaded here.
The new version comes with numerous improvements and (more...)
rman target /
Recovery Manager: Release 188.8.131.52.0 - Production on Wed Sep 18 20:47:38 2013
Oracle BI Publisher Desktop Microsoft Visual Basic System Error &H8000FFFF -2147418113 Catastrophic failure
Not too sure what I changed on my laptop, possibly just installation of another product or perhaps Microsoft or similar updates, but when I went to edit a BI Publisher RTF template I got the following error message on clicking ANY button in the BI Publisher plugin for Microsoft Word.
Environment: Oracle EE 184.108.40.206 on OEL 5.7
I will post shortened version of the query with fake table names and columns.
SELECT p.name p_name, r.status r_status FROM table_1 r, ( SELECT ri.r_id, MAX ( DECODE (ri.c_name, 'SPACE', ri. (more...)
There was an interesting issue at one of the customer sites. Few tables in the database were altered and the dependent objects became invalid. But the attempts to compile the objects using utlrp.sql or manually were failing. In all the cases it was giving the same error:
SQL> alter function SCOTT.SOME_FUNCTION compile; alter function SCOTT.SOME_FUNCTION compile * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 27 SQL>
At first look it sounded like some issue with the dictionary as (more...)
I’ve been working on a lot of good schtuff lately on the area of capacity planning. And I’ve greatly improved my time to generate workload characterization visualization and analysis using my AWR scripts which I enhanced to fit on the analytics tool that I’ve been using.. and that is Tableau.
So I’ve got a couple of performance and capacity planning use case scenarios which I will blog in parts in the next few days or weeks. But before that I need to familiarize you on how I mine this valuable AWR performance data.
Let’s get started with the AWR top (more...)
Lately I’ve been busy on projects and research so I’m putting more and more stuff on my wiki/braindump… and really I need to catch up on blogging.
I have a longer draft post about SQLTXPLAIN.. but I haven’t finished it yet so I’ll just go with the quick post for now.
I’ve been using SQLTXPLAIN for quite a while, and I can say I’m a really big fan. It’s a cool tool to use for systematic SQL Tuning so I got them all neatly documented here http://karlarao.tiddlyspot.com/#SQLTXPLAIN.
BTW, Carlos Sierra, the author of the tool will (more...)
This is 8-core 32-bit OEL5 (2.6.18-92.el5PAE) server with 4GB RAM running two Oracle 10g (10.2.0.4) SE databases. SGA's of those databases where 1,2 GB and 700MB with pga_aggregate_target 64M and 128M.
As databases were small (under 50G) and not very active I didn't expected any performance problems.
Using vmstat, mpstat, top and sar tools I've noticed that we were having slight issues with swapping, despite that there (more...)