Skip navigation.

DBA Blogs

Free Apache Cassandra Training Event in Cambridge, MA March 23

Pythian Group - Fri, 2015-03-20 14:24

I’ll be speaking, along with DataStax and Microsoft representatives at Cassandra Essentials Day this coming Monday (March 23) in Cambridge. MA. This free training event will cover the basics of Apache Cassandra and show you how to try it out quickly, easily, and free of charge on the Azure cloud. Expect to learn about the unique aspects of Cassandra and DataStax Enterprise and to dive into real-world use cases.

Space is limited, so register online to reserve a spot.

Categories: DBA Blogs

My Co-op Experience at Pythian

Pythian Group - Fri, 2015-03-20 06:30
That's me in front of our office. I promise there is a bigger Pythian logo!

That’s me in front of our office. I promise there is a bigger Pythian logo!

Unlike most other engineering physics students at Carleton who prefer to remain within the limits of engineering, I had chosen to apply for a software developer co-op position at Pythian in 2014. For those of you who do not know much about the engineering physics program (I get that a lot and so I will save you the trip to Google and tell you), this is how Stanford University describes their engineering physics program: “Engineering Physics prepares students to apply physics to tackle 21st century engineering challenges and to apply engineering to address 21st century questions in physics.” As you can imagine, very little to do with software development. You might ask, then why apply to Pythian?

Programming is changing the way our world functions. Look at the finance sectors: companies rely on complicated algorithms to determine where they should be investing their resources which in turn determines the course of growth for the company. In science and technology, algorithms help us make sense of huge amounts of unstructured data which would otherwise take us years to process, and help us understand and solve many or our 21st century problems. Clearly, learning how to write these algorithms or code cannot be a bad idea, rather, one that will be invaluable. A wise or a not so wise man once said, (you will know what I mean if you have seen the movie iRobot): “If you cannot solve a problem, make a program that can.” In a way, maybe I intend to apply physics to tackle all of 21st century problems by writing programs. (That totally made sense in my head).

Whatever it might be, my interest in programming or my mission to somehow tie physics, engineering, and programming together, I found myself looking forward to an interview with Pythian. I remember having to call in for a Skype interview. While waiting for my interviewers to join the call, I remember thinking about all the horror co-op stories I had heard: How you will be given piles of books to read over your work term (you might have guessed from this blog so far, not much of a reader, this one. If I hit 500 words, first round’s on me!). Furthermore, horror stories of how students are usually labeled as a co-op and given no meaningful work at all.

Just as I was drifting away in my thoughts, my interviewers joined the call. And much to my surprise they were not the traditional hiring managers in their formal dresses making you feel like just another interviewee in a long list of interviewees. Instead they were warm and friendly people who were genuinely interested in what I could offer to the company as a co-op student. The programming languages I knew, which one was my favourite, the kind of programs I had written, and more. They clearly stated the kind of work I could expect as a co-op student, which was exactly the same kind of work that the team was going to be doing. And most importantly, my interviewers seemed to be enjoying the kind of work they do and the place they work at.

So, when I was offered the co-op position at Pythian. I knew I had to say yes!

My pleasant experience with Pythian has continued ever since. The most enjoyable aspect of my work has been the fact that I am involved in a lot of the team projects which means I am always learning something new and gaining more knowledge each day, after each project. I feel that in an industry like this, the best way to learn is by experience and exposure. At Pythian that is exactly what I am getting.

And if those are not good enough reasons to enjoy working for this company, I also have the privilege of working with some extremely experienced and knowledgeable people in the web development industry. Bill Gates had once suggested that he wants to hire the smartest people at Microsoft and surround himself with them. This would create an environment where everyone would learn from each other and excel in their work. And I agree with that. Well now if you are the next Bill Gates, go ahead, create your multibillion dollar company and hire the best of the best and immerse yourself in the presence of all that knowledge and intelligence. But I feel I have found myself a great alternative, a poor man approach, a student budget approach or whatever you want to call it, take full advantage of working with some really talented people and learn as much as you can.

Today, five months into my yearlong placement with Pythian, I could not be more sure and proud of becoming a part of this exciting company, becoming a Pythianite. And I feel my time spent in this company has put me well in course to complete my goal of tying physics, engineering and programming together.

Categories: DBA Blogs

Log Buffer #415, A Carnival of the Vanities for DBAs

Pythian Group - Fri, 2015-03-20 06:25

This Log Buffer Edition covers the Oracle, SQL Server and MySQL with a keen look on the novel ideas.

Oracle:

The case was to roll forward a physical standby with an RMAN SCN incremental backup taken from primary.

Oracle Database 12c: Smart upgrade

This blog covers how to specify query parameters using the REST Service Editor.

Production workloads blend Cloud and On-Premise Capabilities

ALTER DATABASE BEGIN BACKUP and ALTER DATABASE END BACKUP

SQL Server:

Mail Fails with SQLCMD Error

How to get Database Design Horribly Wrong

Using the ROLLUP, CUBE, and GROUPING SETS Operators

The Right and Wrong of T-SQL DML TRIGGERs (SQL Spackle)

How converting extensive, repetitive code to a data-driven approach resolved a maintenance headache and helped identify bugs

MySQL:

Distributing innodb tables made simpler!

Choosing a good sharding key in MongoDB (and MySQL)

Update a grails project from version 2.3.8 to version 2.4.4

MySQL Enterprise Backup 3.12.0 has been released

If table is partitioned it makes it easy to maintain. Table has grown so huge and the backups are just keep running long then probably you need to think of archival or purge.

Categories: DBA Blogs

Using strace to debug application errors in linux

Pythian Group - Fri, 2015-03-20 06:24

strace is a very useful tool which traces system calls and signals for a running process. This helps a lot while debugging application level performance issues and bugs. Aim of this post is to demonstrate the power of strace in pinning down an application bug.

I came across an issue in which nagios was sending the following alerts for a RHEL6 system.

***** Nagios ***** Notification Type: PROBLEM Service: NTP Host: xxxxx Address: xx.xx.xx.xx State: UNKNOWN Date/Time: Tue Feb 17 10:08:36 EST 2015 Additional Info: cant create socket connection

On manually executing the nagios plugin on the affected system, we can see that the command is not running correctly.

# /usr/lib64/nagios/plugins/check_ntp_time -H localhost -w 1 -c 2
can’t create socket connection

I ran strace on the command. This would create a file /tmp/strace.out with strace output.

# strace -xvtto /tmp/strace.out /usr/lib64/nagios/plugins/check_ntp_time -H localhost -w 1 -c 2

Following are the options which I passed.

-x Print all non-ASCII strings in hexadecimal string format.
-v Print unabbreviated versions of environment, stat, termios, etc. calls. These structures
are very common in calls and so the default behavior displays a reasonable subset of struc?
ture members. Use this option to get all of the gory details.
-tt If given twice, the time printed will include the microseconds.
-o filename Write the trace output to the file filename rather than to stderr. Use filename.pid if -ff
is used. If the argument begins with `|’ or with `!’ then the rest of the argument is
treated as a command and all output is piped to it. This is convenient for piping the
debugging output to a program without affecting the redirections of executed programs.

Time stamps displayed with -tt option is not very useful in this example, but it is very useful while debugging application performance issues. -T which shows the time spend in each system call is also useful for those issues.

From strace output,

10:26:11.901173 socket(PF_INET, SOCK_DGRAM, IPPROTO_IP) = 3
10:26:11.901279 connect(3, {sa_family=AF_INET, sin_port=htons(123), sin_addr=inet_addr(“127.0.0.1″)}, 16) = 0
10:26:11.901413 getsockname(3, {sa_family=AF_INET, sin_port=htons(38673), sin_addr=inet_addr(“127.0.0.1″)}, [16]) = 0
10:26:11.901513 close(3) = 0
10:26:11.901621 socket(PF_INET6, SOCK_DGRAM, IPPROTO_IP) = 3
10:26:11.901722 connect(3, {sa_family=AF_INET6, sin6_port=htons(123), inet_pton(AF_INET6, “::1″, &sin6_addr), sin6_flowinfo=0, sin6_scope_id=0}, 28) = -1 ENETUNREACH (Network is unreachable) <—————-
10:26:11.901830 close(3) = 0
10:26:11.901933 socket(PF_INET, SOCK_DGRAM, IPPROTO_UDP) = 3
10:26:11.902033 connect(3, {sa_family=AF_INET, sin_port=htons(123), sin_addr=inet_addr(“127.0.0.1″)}, 16) = 0
10:26:11.902130 socket(PF_INET6, SOCK_DGRAM, IPPROTO_UDP) = 4
10:26:11.902238 connect(4, {sa_family=AF_INET6, sin6_port=htons(123), inet_pton(AF_INET6, “::1″, &sin6_addr), sin6_flowinfo=0, sin6_scope_id=0}, 28) = -1 ENETUNREACH (Network is unreachable) <—————-
10:26:11.902355 fstat(1, {st_dev=makedev(0, 11), st_ino=3, st_mode=S_IFCHR|0620, st_nlink=1, st_uid=528, st_gid=5, st_blksize=1024, st_blocks=0, st_rdev=makedev(136, 0), st_atime=2015/02/17-10:26:11, st_mtime=2015/02/17-10:26:11, st_ctime=2015/02/17-10:16:32}) = 0
10:26:11.902490 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7fc5a8752000
10:26:11.902608 write(1, “can’t create socket connection”, 30) = 30

Let us have a deeper look,

You can see that socket() is opening a socket with PF_INET (IP v4) domain and IPPROTO_IP (tcp) protocol. This returns file descriptor 3. Then connect() is connecting to the socket using the same file descriptor and connects to ntp port (123) in localhost. Then it calls getsockname and closes the file descriptor for the socket.

10:26:11.901173 socket(PF_INET, SOCK_DGRAM, IPPROTO_IP) = 3
10:26:11.901279 connect(3, {sa_family=AF_INET, sin_port=htons(123), sin_addr=inet_addr(“127.0.0.1″)}, 16) = 0
10:26:11.901413 getsockname(3, {sa_family=AF_INET, sin_port=htons(38673), sin_addr=inet_addr(“127.0.0.1″)}, [16]) = 0
10:26:11.901513 close(3) = 0

Next it does the same but with PF_INET6 (IP v6) domain. But you can see that connect() fails with ENETUNREACH.

10:26:11.901621 socket(PF_INET6, SOCK_DGRAM, IPPROTO_IP) = 3
10:26:11.901722 connect(3, {sa_family=AF_INET6, sin6_port=htons(123), inet_pton(AF_INET6, “::1″, &sin6_addr), sin6_flowinfo=0, sin6_scope_id=0}, 28) = -1 ENETUNREACH (Network is unreachable) <—————-
10:26:11.901830 close(3)

From connect man page,

ENETUNREACH
Network is unreachable.

This process is repeated with IPPROTO_UDP (udp) protocol as well.

On checking the system, I see that that only IPv4 is enabled. ‘inet6 addr’ line is missing.

[root@pbsftp ~]# ifconfig
eth0 Link encap:Ethernet HWaddr 00:50:56:90:2E:31
inet addr:xx.xx.xx.xx Bcast:xx.xx.xx.xx Mask:xx.xx.xx.xx <——————–
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:5494691 errors:0 dropped:0 overruns:0 frame:0
TX packets:4014672 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:5877759230 (5.4 GiB) TX bytes:5608605924 (5.2 GiB)

IPv6 is disabled in the system using following /etc/sysctl.conf entries.

net.ipv6.conf.default.disable_ipv6=1
net.ipv6.conf.all.disable_ipv6 = 1

This behavior of nagios plugin is wrong as it should not die when one of the connect fails.

Issue is fixed in upstream patch.

Enabling IPv6 by removing following entries from /etc/sysctl.conf and running ‘sysctl -p’ would act as a workaround.

net.ipv6.conf.default.disable_ipv6=1
net.ipv6.conf.all.disable_ipv6 = 1

To fix the issue, the upstream patch need to be either backported manually to create an rpm or a support ticket need to be opened with the operating system vendor to backport the patch in their product release.

Categories: DBA Blogs

Parallel Execution -- 2c PX Servers

Hemant K Chitale - Wed, 2015-03-18 09:40
Adding to my two previous posts here and here about identifying usage of Parallel Execution -- exactly how many PX servers were used for a query, here is a third method.  (the first two are V$PX_PROCESS/V$PX_SESSION and V$SQLSTATS.PX_SERVERS_EXECUTIONS).  This method uses V$PQ_SESSTAT.

However, the limitation of V$PQ_SESSTAT is that it can only be queried from the same session as that which ran the Parallel Query.  The other two methods can be used by a separate "monitoring" session.

HEMANT>show parameter parallel_max

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 8
HEMANT>connect / as sysdba
Connected.
SYS>alter system set parallel_max_servers=64;

System altered.

SYS>show parameter cpu

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 4
parallel_threads_per_cpu integer 4
resource_manager_cpu_allocation integer 4
SYS>show parameter parallel_degree_policy

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string MANUAL
SYS>



As has been identified earlier, the PARALLEL Hint will use 16 PX Servers (limited by PARALLEL_MAX_SERVERS [see this post] because of the values of CPU_COUNT and PARALLEL_THREADS_PER_CPU (where, in this case, PARALLEL_DEGREE_POLICY is yet MANUAL).

SYS>alter system flush shared_pool;

System altered.

SYS>connect hemant/hemant
Connected.
HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

COUNT(*)
----------
4802944

HEMANT>select * from v$pq_sesstat;

STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized 1 1
DML Parallelized 0 0
DDL Parallelized 0 0
DFO Trees 1 1
Server Threads 16 0
Allocation Height 16 0
Allocation Width 1 0
Local Msgs Sent 464 464
Distr Msgs Sent 0 0
Local Msgs Recv'd 464 464
Distr Msgs Recv'd 0 0

11 rows selected.

HEMANT>
HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

COUNT(*)
----------
4802944

HEMANT>select * from v$pq_sesstat;

STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized 1 2
DML Parallelized 0 0
DDL Parallelized 0 0
DFO Trees 1 2
Server Threads 16 0
Allocation Height 16 0
Allocation Width 1 0
Local Msgs Sent 464 928
Distr Msgs Sent 0 0
Local Msgs Recv'd 464 928
Distr Msgs Recv'd 0 0

11 rows selected.

HEMANT>

As we can see, the SESSIONS_TOTAL count of Server Threads does not get updated (although the count of Queries Parallelized is updated).  This behaviour remains in 12c.  (However, there are two additional statistics available in 12c).
.
.
.
Categories: DBA Blogs

Showing Interval Partitons Code in DBMS_METADATA.GET_DDL

Pakistan's First Oracle Blog - Tue, 2015-03-17 22:32

-- If you want to display the system generated partitions as part of the CREATE TABLE DDL, then set the EXPORT parameter of the dbms_metadata to true.

-- The default behavior of "DBMS_METADATA.GET_DDL" is that it does not show Interval Partitions created by the system for interval partitioned tables and indexes.

-- In the case of Interval Partitioning, New Partitions are created automatically when corresponding row is inserted.  This newly created partition information will be displayed in "DBA_TAB_PARTITIONS" dictionary view. However when the DDL is queried using function "DBMS_METADATA.GET_DDL", then this information is not shown.


Demo:  (Following was tested on the Oracle 12c, and it should be valid for Oracle 11g too.)

-- Create table with interval partition.

CREATE TABLE mytabwithInterval
(mydate DATE,
 mynum NUMBER)
PARTITION BY RANGE (mydate) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
 (PARTITION P_20150301  VALUES LESS THAN (TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));


-- Insert some data to generate interval partitions.

INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-01-01', 'YYYY-MM-DD'),1);

INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-02-01', 'YYYY-MM-DD'),2);

INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-03-01', 'YYYY-MM-DD'),3);

INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-04-01', 'YYYY-MM-DD'),3);
COMMIT;

-- check partition information in dictionary table

col partition_name format a20
select partition_name from user_tab_partitions where table_name='MYTABWITHINTERVAL';


-- To see default behavior of dbms_metadata:


set long 100000
set pagesize 50
col DDL format a120

SELECT DBMS_METADATA.GET_DDL('TABLE' ,'MYTABWITHINTERVAL','SYS') FROM DUAL;


-- To see it with export option:


exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'EXPORT',true);
SELECT DBMS_METADATA.GET_DDL('TABLE' ,'MYTABWITHINTERVAL','SYS') FROM DUAL;


OUTPUT:


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set lines 181
SQL> set pages 100
SQL> CREATE TABLE mytabwithInterval
(mydate DATE,
 mynum NUMBER)
PARTITION BY RANGE (mydate) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
 (PARTITION P_20150301  VALUES LESS THAN (TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
  2    3    4    5 
Table created.

SQL> INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-01-01', 'YYYY-MM-DD'),1);

INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-02-01', 'YYYY-MM-DD'),2);

INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-03-01', 'YYYY-MM-DD'),3);

INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-04-01', 'YYYY-MM-DD'),3);
COMMIT;
1 row created.

SQL> SQL>
1 row created.

SQL> SQL>
1 row created.

SQL> SQL>
1 row created.

SQL>

Commit complete.

SQL> col partition_name format a20
select partition_name from user_tab_partitions where table_name='MYTABWITHINTERVAL';SQL>

PARTITION_NAME
--------------------
P_20150301
SYS_P561
SYS_P562

SQL>


SQL>
SQL>
SQL> set long 100000
set pagesize 50
col DDL format a120

SELECT DBMS_METADATA.GET_DDL('TABLE' ,'MYTABWITHINTERVAL','SYS') FROM DUAL;
SQL> SQL> SQL> SQL>

DBMS_METADATA.GET_DDL('TABLE','MYTABWITHINTERVAL','SYS')
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."MYTABWITHINTERVAL"
   (    "MYDATE" DATE,
    "MYNUM" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"
  PARTITION BY RANGE ("MYDATE") INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
 (PARTITION "P_20150301"  VALUES LESS THAN (TO_DATE(' 2015-03-01 00:00:00', 'SYY
YY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" )


SQL> SQL> exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'EXPORT',true);
SELECT DBMS_METADATA.GET_DDL('TABLE' ,'MYTABWITHINTERVAL','SYS') FROM DUAL;

PL/SQL procedure successfully completed.

SQL>

DBMS_METADATA.GET_DDL('TABLE','MYTABWITHINTERVAL','SYS')
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."MYTABWITHINTERVAL"
   (    "MYDATE" DATE,
    "MYNUM" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"
  PARTITION BY RANGE ("MYDATE") INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) TRANSITION
 ("P_20150301")
 (PARTITION "P_20150301"  VALUES LESS THAN (TO_DATE(' 2015-03-01 00:00:00', 'SYY
YY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ,
 PARTITION "SYS_P561"  VALUES LESS THAN (TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-
MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ,
 PARTITION "SYS_P562"  VALUES LESS THAN (TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-
MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" )

Enjoy!!!
Categories: DBA Blogs

Use a #db12c PDB for the #em12c OMR…Finally

DBASolved - Mon, 2015-03-16 15:57

In an earlier post, I mentioned that Oracle has finally, offcially supported the Oracle Management Repository (OMR) on Database 12c (12.1.0.2).  As I’ve been working on a DBaaS project, I built a new Oracle Enterprise Manager (OEM) enivornment to test out a few things.  Since I was rebuilding, I decided to try out the PDB as an OMR (afterall I’ve been asking about this approach).  In the past, installation would fail around 63%.  This time around, OEM installed in a PDB with no issue at all!  

To verify that the OMR was actually in an PDB, I had to dig around a bit.  Durning the installation, I had to provide connection information.  This configuration can be verified by going to Management Services and Repository page within OEM.  Once on this page, the Repository Details section (see image) will show you the database name and the connection string used.  The connection string identified the PDB being used in the Service Name part of the connection.


In the image, you can see that I’m using a database named MGMT, yet the connection string is going to service name OEM1.acme.com.  OEM1 is the PDB that is running under the MGMT consoldiated database (see image).  

What makes this work this time around?  There are a few patches that have to be applied to the Oracle Database (12.1.0.2) before the PDB can be used for the OMR.  These patches are 19769480, 19877336, and 20243268.  These patches are required (more details here).  These patches require using OPatch 12.1.0.4.0 or higher which can be downloaded from MOS Note: 274526.1 (Patch 6880880).  

Overall, Oracle did a good job in resolving this issue and giving the ability to host OEM in a PDB.  There are a few questions that come to mind now.  Here are just a few that I have:

1. What use cases will come out of this?  
2. How will performance of OEM look in using a PDB?  
3. How will licensing change with OEM and PDBs?

Anyways, give it a try!  

Enjoy!!

about.me: http://about.me/dbasolved
 


Filed under: OEM
Categories: DBA Blogs

DCLI to back up Oracle home and inventory before patch

Bobby Durrett's DBA Blog - Mon, 2015-03-16 15:48

I was preparing for my weekend patch of our Exadata system and I needed to back up all of our Oracle homes and inventories on our production system.  On our 2 node dev and qa clusters I just ran the backups by hand like this:

login as root

cd /u01/app/oracle/product/11.2.0.4

tar -cvf - dbhome_1 | gzip > dbhome_1-20150211.tgz

cd /u01/app

cp -r oraInventory oraInventory.20150211

But the production cluster has 12 nodes so I had to figure out how to use DCLI to run the equivalent on all 12 nodes instead of doing them one at a time.  To run a DCLI command you need go to the directory that has the list of database server host names.  So, first you do this:

login as root

cd /opt/oracle.SupportTools/onecommand

The file dbs_group contains a list of the database server host names.

Next, I wanted to check how much space was free on the filesystem and how much space the Oracle home occupied so I ran these commands:

dcli -g dbs_group -l root "df|grep u01"

dcli -g dbs_group -l root "cd /u01/app/oracle/product/11.2.0.4;du -ks ."

The first command gave me how much space was free on the /u01 filesystem on all database nodes.   The second command gave me how much space the 11.2.0.4 home consumed.  I should have done “du -ks dbhome_1″ since I’m backing up dbhome_1 instead of everything under 11.2.0.4, but there wasn’t much else under 11.2.0.4 so it worked out.

Now that I knew that there was enough space I ran the backup commands using DCLI.

dcli -g dbs_group -l root "cd /u01/app/oracle/product/11.2.0.4;tar -cvf - dbhome_1 | gzip > dbhome_1-20150316.tgz"

dcli -g dbs_group -l root "cd /u01/app;cp -r oraInventory oraInventory.20150316"

I keep forgetting how to do this so I thought I would post it.  I can refer back to this later and perhaps it will be helpful to others.

– Bobby

 

Categories: DBA Blogs

MySQL Sounds Like Fun

Pythian Group - Mon, 2015-03-16 07:48

I love finding out new things about MySQL. Last week, I stumbled on a query that had the phrase “SOUNDS LIKE” in it. Sounds made-up, right? Turns out MySQL is using a known “soundex” algorithm common to most databases, and popular in use cases in geneaology.

The basic idea is that words are encoded according to their consonants. Consonants that sound similar (like M and N) are given the same code. Here’s a simple example:

(“soundex” and “sounds like” are different ways of doing the same thing in these queries)

MariaDB> select soundex("boom");
+-----------------+
| soundex("boom") |
+-----------------+
| B500            |
+-----------------+

MariaDB> select soundex("bam");
+----------------+
| soundex("bam") |
+----------------+
| B500           |
+----------------+

MariaDB> select soundex("bin");
+----------------+
| soundex("bin") |
+----------------+
| B500           |
+----------------+

This simple example isn’t terribly useful, but if you were trying to find similar, but differently spelled, names across continents, it could be helpful:

MariaDB> select soundex("William");
+--------------------+
| soundex("William") |
+--------------------+
| W450               |
+--------------------+

MariaDB> select soundex("Walaam");
+-------------------+
| soundex("Walaam") |
+-------------------+
| W450              |
+-------------------+

MariaDB> select soundex("Willem");
+-------------------+
| soundex("Willem") |
+-------------------+
| W450              |
+-------------------+

MariaDB> select soundex("Williama");
+---------------------+
| soundex("Williama") |
+---------------------+
| W450                |
+---------------------+

And you could probably agree these variations match as well:

MariaDB> select soundex("Guillaume");
+----------------------+
| soundex("Guillaume") |
+----------------------+
| G450                 |
+----------------------+

MariaDB> select soundex("Uilleam");
+--------------------+
| soundex("Uilleam") |
+--------------------+
| U450               |
+--------------------+

MariaDB> select soundex("Melhem");
+-------------------+
| soundex("Melhem") |
+-------------------+
| M450              |
+-------------------+

MariaDB> select soundex("Uilliam");
+--------------------+
| soundex("Uilliam") |
+--------------------+
| U450               |
+--------------------+

Well, that’s pretty neat. Of course, I want to try the silliest word I can think of:

MariaDB> select soundex("supercalifragilisticexpealidocious");
+-----------------------------------------------+
| soundex("supercalifragilisticexpealidocious") |
+-----------------------------------------------+
| S162416242321432                              |
+-----------------------------------------------+

So the algorithm doesn’t stop at 3 digits; good to know.

What does the algorithm do? Luckily MySQL is open source, and so we can look in the source code:

This looks like the raw mapping. And then this is called into a function that loops through the characters in the word.

/* ABCDEFGHIJKLMNOPQRSTUVWXYZ */
/* :::::::::::::::::::::::::: */
const char *soundex_map= "01230120022455012623010202";

Note that even though it’s called “sounds like” it is really simply a character mapping based on an agreement by the developers’ ears which characters sounds similar. That’s of course an oversimplification, and I see in the code comments the following:

/****************************************************************
* SOUNDEX ALGORITHM in C *
* *
* The basic Algorithm source is taken from EDN Nov. *
* 14, 1985 pg. 36. *

But despite hitting up several librarians, I can’t seem to get a copy of this. Someone out there has a copy sitting around, right?

As a side note, this is obviously specific to the English language. I found references to German and other languages having soundex mappings, and would be curious to see those and hear of any language-specific ways to do this.

Curiosity aside, here’s a real use.

I pulled down some government climate data. Let’s say the location field has some of my favorite misspellings of “Durham” in it:

MariaDB [weather]> select distinct(two), count(two) from weather.temps group by two;
+--------------------------------------------+------------+
| two                                        | count(two) |
+--------------------------------------------+------------+
| NULL                                       |          0 |
| DRM                                        |         51 |
| DURHAM                                     |    1101887 |
| DURM                                       |         71 |
| NCSU                                       |    1000000 |
| RALEIGH DURHAM INTERNATIONAL AIRPORT NC US |    1096195 |
| RDU AIRPORT                                |    1000000 |
+--------------------------------------------+------------+

A “LIKE” clause won’t work terribly well here.

I confirmed the misspellings would match as I expected:

MariaDB [weather]> select soundex("Durham"), soundex("Durm"), soundex("DRM");
+-------------------+-----------------+----------------+
| soundex("Durham") | soundex("Durm") | soundex("DRM") |
+-------------------+-----------------+----------------+
| D650              | D650            | D650           |
+-------------------+-----------------+----------------+

So instead of manually creating a query like:

MariaDB [weather]> select count(two) from weather.temps where two='DRM' or two='DURHAM' or two='DURM';
+------------+
| count(two) |
+------------+
|    1102009 |
+------------+

I can simply do this:

MariaDB [weather]> select count(two) from weather.temps where two sounds like 'Durham';
+------------+
| count(two) |
+------------+
|    1102009 |
+------------+

There are more than several ways to do string comparisons, but I enjoyed finding this one.

(Bonus points will be granted to the first person who comments that RDU is also Durham and submits a unique query to include it in the count.)

Categories: DBA Blogs

Monitoring Cassandra with Grafana and Influx DB

Pythian Group - Mon, 2015-03-16 07:37

Hello,

In this post I will explain how to set up Cassandra monitoring with influxDB and Grafana. This can also be used to connect to other monitoring systems (Graphite, Collectd, etc…) but since both influxDB and Grafana are hot topics at the moment I decided to follow the trend! I was asked why I was doing this when a tool like OpsCenter is available, but sometimes you want to have all your systems reporting to a single dashboard. And if your dashboard is Grafana and your Backend is influxDB then you will learn how to connect Cassandra to it!

Assumptions:
– You are running a Linux system (This post is based on CentOS 7)
– You are using Cassandra 1.2+ (I’m using 2.1.3 in this case)

Prerequisites
  • Cassandra Installation
  • Graphite Metrics Jar
  • influxDB – http://influxdb.com/
  • Grafana – http://grafana.org/
  • Apache (Any webserver would do)
Installing and configure influxDB

This one is dead easy, once you have the package install it (rpm -i, dpkg -i). Start the service:

service influxdb start

Once the service is running, go to the configuration (/opt/influxdb/shared/config.toml) and edit the file so that under [input_plugins] it looks like this:

# Configure the graphite api
[input_plugins.graphite]
enabled = true
# address = "0.0.0.0" # If not set, is actually set to bind-address.
port = 2003
database = "cassandra-metrics" # store graphite data in this database
udp_enabled = true

Save the file, reload the service:

service influxdb reload

Now go to your browser localhost:8083, click connect (no credentials should be needed), and after you logged in, enter in a database name (use cassandra-metrics) and click Create (This should be your only option). Now you can click the database, and add an user to it (and make it admin). Now create another database, with name “grafana”, create an admin for that database also.
Now you are done with influxDB.

Installing Grafana

Grafana is a bit more tricky, since it is needed to configure a webserver also. Let’s assume apache is installed, and the home directory for www is /var/www/html.

So get the grafana package and extract it to /var/www/html. So the end result should be something like /var/www/html/grafana.

Now do the following:

cd /var/www/html/grafana
cp config.sample.js config.js

Now let’s configure the connection between influXDB and Grafana. Open for edit the new copied file config.js and edit it so it looks like this:

datasources: {
  influxdb: {
    type: 'influxdb',
    url: "http://localhost:8086/db/cassandra-metrics",
    username: 'admin',
    password: 'admin',
  },
  grafana: {
    type: 'influxdb',
    url: "http://localhost:8086/db/grafana",
    username: 'admin',
    password: 'admin',
    grafanaDB: true
  },
},

Now redirect your browser to localhost/grafana and you will have the Grafana default dashboard.

Preparing Cassandra

Now the final piece of the puzzle. Now we follow more or less the Cassandra guide that exists here, but we need to make some changes to make it more valuable (and allow multiple nodes to provide metrics).

So, first of all, put the metrics-graphite-2.2.0.jar in all the Cassandra nodes /lib directory.
Now create a yaml file with similar to the Datastax example, lets call it influx-reporting.yaml and store it on /conf directory. Now edit the file again so it looks like this:

graphite:
-
  period: 60
  timeunit: 'SECONDS'
  prefix: 'Node1'
  hosts:
  - host: 'localhost'
    port: 2003
  predicate:
    color: "white"
    useQualifiedName: true
    patterns:
    - ".*"

What did we change here, first we added a prefix field, this will allow us to identify the node that is providing the metrics. It must be different for every node, otherwise the metrics will overwrite/mix with each other. Then we decided to allow all patterns (“.*”), this means that Cassandra will push out all the metrics into influxDB. You can decide whether or not this is too much and just enable the metrics you want (find out more about it here).

Now edit the cassandra-env.sh so that it will read the yaml file to provide the metrics. Add the following line to the end of the file:

JVM_OPTS="$JVM_OPTS -Dcassandra.metricsReporterConfigFile=influx-reporting.yaml"

If all is done correctly, you can restart the Cassandra node (or nodes, but don’t do it all at the same time, 2min between each is recommended) and if the log file has the following message:

INFO [main] YYYY-MM-DD HH:MM:SS,SSS CassandraDaemon.java:353 - Trying to load metrics-reporter-config from file: inf
lux-reporting.yaml
INFO [main] YYYY-MM-DD HH:MM:SS,SSS GraphiteReporterConfig.java:68 - Enabling GraphiteReporter to localhost:2003

All is good!

Graphing!

Grafana is not that difficult to use, so once you start exploring a bit (And reading the documentation) you will find out doing nice graphs. This could be a long post only about graphing out, so I’m just go and post some images of the graphs I’m getting out of Grafana so that you can see how it can be powerful and help you on keeping your Cassandra Healthy.

Grafana_cassandra-test3 Grafana_cassandra-test2 Grafana_cassandra-test1
Categories: DBA Blogs

Cassandra 101 : Understanding What Cassandra Is

Pythian Group - Mon, 2015-03-16 07:35

As some of you may know, in my current role at Pythian, I am tackling OSDB and currently Cassandra is on my radar. So one of the things I have been trying to do is learn what Cassandra is, so in this series, I’m going to share a bit of what I have been able to learn.

According to the whitepaper “Solving Big Data Challenges for Enterprise Application Performance Management” , Cassandra is a “distributed key value store developed at Facebook. It was designed to handle very large amounts of data spread out across many commodity servers while providing a highly available service without single point of failure allowing replication even across multiple data centers as well as for choosing between synchronous or asynchronous replication for each update.”

Cassandra, in layman’s terms, is a NoSQL database developed in JavaOne. One of Cassandra’s many benefits is that it’s an open source DB with deep developer support. It is also a fully distributed DB, meaning that there is no master DB, unlike Oracle or MySQL, so this allows this database to have no point of failure. It also touts being linearly scalable, meaning that if you have 2 nodes and a throughput of 100,000 transactions per second, and you added 2 more nodes, you would now get 200,000 transactions per second, and so forth.

2015-03-12_1145

Cassandra is based on 2 core technologies, Google’s Big Table and Amazon’s Dynamo, which Facebook uses to power their Inbox Search feature and released it as an open source project on Google Code and then incubated at Apache, and is nowadays a Top-Level-Project. Currently there exists 2 versions of Cassandra:

Since Cassandra is a distributed system, it follows the CAP Theorem, which is awesomely explained here, and it states that, in a distributed system, you can only have two out of the following three guarantees across a write/read pair:

  • Consistency.- A read is guaranteed to return the most recent write for a given client.
  • Availability.-A non-failing node will return a reasonable response within a reasonable amount of time (no error or timeout).
  • Partition Tolerance.-The system will continue to function when network partitions occur.

Also Cassandra is a BASE (Basically Available, Soft state, Eventually consistent) type system, not an ACID (Atomicity, Consistency, Isolation, Durability) type system, meaning that the system is optimistic and accepts that the database consistency will be in a state of flux, not like ACID which is pessimistic and it forces consistency at the end of every transaction.

Cassandra stores data according to the column family data model where:

  • Keyspace is the container for your application data, similar to a schema in a relational database. Keyspaces are used to group column families together. Typically, a cluster has one keyspace per application.It also defines the replication strategy and data objects belong to a single keyspace
  • Column Family is a set of  one,two or more individual rows with a similar structure
  • Row is a collection of sorted columns, it is the the smallest unit that stores related data in Cassandra, and any component of a Row can store data or metadata
    •  Row Key uniquely identifies a row in a column family

      •  Column key uniquely identifies a column value in a row
      •  Column value stores one value or a collection of values
keyspace

Also we need to understand the basic architecture of Cassandra, which has the following key structures:

  • Node is one Cassandra instance and is the basic infrastructure component in Cassandra. Cassandra assigns data to nodes in the cluster, each node is assigned a part of the database based on the Row Key. Usually corresponds to a host, but not necessarily, specially in Dev or Test environments.
  • Rack is a logical set of nodes
  • Data Center is a logical set of Racks, a data center can be a physical data center or virtual data center. Replication is set by data center
  • Cluster contains one or more data centers and is the full set of nodes which map to a single complete token ring
Cassandra_Arch

Conclusion

Hopefully this will help you understand the basic Cassandra concepts. In the next series, I will go over architecture concepts of what a Seed node is, the purpose of the Snitch and topologies, the Coordinator node, replication factors, etc

Note 1:

André Araújo, a great friend of mine and previous Pythianite, wrote about his first experience with Cassandra : My First Experience with Cassandra – Part 1

Note 2:

This post was originally published in my personal blog: rene-ace.com

Categories: DBA Blogs

Log Buffer #414, A Carnival of the Vanities for DBAs

Pythian Group - Mon, 2015-03-16 07:22

This Log Buffer Edition picks the sea shells from Blogs across the seas of Oracle, SQL Server and MySQL and arrange them for you in this Edition. Enjoy.

Oracle:

12c Parallel Execution New Features: Concurrent UNION ALL

Visualizing Statspack Performance Data in SQL Developer

Optimizer statistics – Gathering Statistics and Histograms

Big Data Made Actionable with Omar TawaKol at SXSW

Mobile backend with REST services and JSON payload based on SOA Suite 12c

SQL Server:

Setting Different Colors for Connections in SSMS

Defusing Database Time Bombs: Avoiding the Need to Refactor Databases

This article shows a step by step tutorial to create a virtual machine in 15 min on Windows Azure.

What SQL Statements Are Currently Using The Transaction Logs?

SQL Server Random Sorted Result Set

MySQL:

Oracle Linux 7.1 and MySQL 5.6

MySQL Workbench 6.3.2 RC has been released

MariaDB CONNECT storage engine now offers access to JSON

Avoiding MySQL ERROR 1052 by prefixing column names in multi table queries

MySQL 5.7.6 DMR: Packages, Repos, Docker Images

Categories: DBA Blogs

Conditions Based On Inequalities Can’t Use Indexes – How To Resolve?

Oracle in Action - Mon, 2015-03-16 02:55

RSS content

Conditions based on inequalities (!=, <>) cannot make use of index(es). I will illustrate this limitation and show you how to optimize SQL statements hitting it.

For the demonstration, I have  a table  students table having a column named result that  can contain the values – ‘Pass’, ‘Fail’, ‘To be evaluated’. The column is characterized by a very non-uniform distribution having most of the rows  set to value Passed (P). Here’s the example:

SQL>drop table students purge;
    create table students (id , result )
    as
    select rownum, decode (mod(rownum, 30), 0, 'F', 1, 'T',  'P')
    from  all_tables;

    create index students_idx on students (result);
    exec dbms_stats.gather_table_stats (USER, 'STUDENTS', cascade => TRUE);

     SELECT result , count(*)
     FROM students
     GROUP BY result;
RESULT COUNT(*)
---------- ----------
P              100
T                4
F                3

Let’s execute the  query to select all students who have not passed (result = ‘T’ or ‘F’). Even though the query has a very strong selectivity and the result column is indexed, the query optimizer chooses a full table scan for reading 7 rows as the predicate involves inequality.

SQL>select * from students where result <> 'P';
    select * from table(dbms_xplan.display_cursor);

ID RESULT
---------- ----------
1 T
30 F

....

7 rows selected.

PLAN_TABLE_OUTPUT
---------------------------------------------------------
SQL_ID f2wkxqy3b6b5h, child number 0
-------------------------------------
select * from students where result <> 'P'

Plan hash value: 4078133427
---------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| STUDENTS | 71 | 355 | 3 (0)| 00:00:01 |
---------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RESULT"<>'P')

In a case like this, where the inequality condition has a strong selectivity, we can advantage of an index using folowing three techniques :

First, the inequality condition can be rewritten into an IN condition. This is an option only when the number of values to be selected is known and the number is limited. For example, if the query is modified as shown, index range scan is employed.

SQL>select * from students where result in ('F', 'T');
select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------
SQL_ID 672mnj9pggkq7, child number 0
-------------------------------------
select * from students where result in ('F', 'T')

Plan hash value: 2871222462
---------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| STUDENTS | 71 | 355 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | STUDENTS_IDX | 71 | | 1 (0)| 00:00:01 |
---------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("RESULT"='F' OR "RESULT"='T'))

Second,   manually rewrite the query to make sure that both component queries can take advantage of an index range scan. This technique  can be applied if the values are unknown or the number of values to be specified is too high.   Hence, if  the query is rewritten as shown, it will be able to to take advantage of the or expansion query transformation:

SQL>select * from students where result < 'P'
    union all
    select * from students where result > 'P' ;
    select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------- 
SQL_ID gqrp063y9c5a5, child number 0
-------------------------------------
select * from students where result < 'P' union all select * from
students where result > 'P'

Plan hash value: 2171568329
--------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------- 
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| STUDENTS | 76 | 380 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | STUDENTS_IDX | 76 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| STUDENTS | 36 | 180 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | STUDENTS_IDX | 36 | | 1 (0)| 00:00:01 |
--------------------------------------------------------- 

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("RESULT"<'P')
5 - access("RESULT">'P')

The third technique simply forces an index full scan with, for example, the index hint. From a performance point of view, it’s not optimal,as, for a query with very strong selectivity, full index has to be scanned.

SQL>SELECT /*+ index(students) */ * FROM students where result != 'P';
select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------- 
SQL_ID 2hyrf6n7kb8pr, child number 0
-------------------------------------
SELECT /*+ index(students) */ * FROM students where result != 'P'

Plan hash value: 635752001
---------------------------------------------------------  
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------- 
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| STUDENTS | 71 | 355 | 2 (0)| 00:00:01|
|* 2 | INDEX FULL SCAN | STUDENTS_IDX | 71 | | 1 (0)| 00:00:01 |
--------------------------------------------------------- 

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RESULT"<>'P')

Conclusion:

In cases where the inequality condition having a strong selectivity is notable to make use of an index, we can advantage of an index using following three techniques : 

  • First, the inequality condition can be rewritten into an IN condition. This is an option only when the number of values to be selected is known and the number is limited.
  • Second,   manually rewrite the query to make sure that both component queries can take advantage of an index range scan. This technique  can be applied if the values are unknown or the number of values to be specified is too high.
  • The third technique simply forces an index full scan with, for example, the index hint. From a performance point of view, it’s not optimal,as, for a query with very strong selectivity, full index has to be scanned.

References:
Troubleshooting Oracle Performance (second edition ) by Christian Antognini
—————————————————————————————————————

Related links:

Home
Tuning Index

————————-

 



Tags:  

Del.icio.us
Digg

Comments:  1 (One) on this item
You might be interested in this:  
Copyright © ORACLE IN ACTION [Conditions Based On Inequalities Can't Use Indexes - How To Resolve?], All Right Reserved. 2015.

The post Conditions Based On Inequalities Can’t Use Indexes – How To Resolve? appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

Loads of fun with DBA_HIST_OSSTAT

Bobby Durrett's DBA Blog - Fri, 2015-03-13 17:35

I saw a load of 44 on a node of our production Exadata and it worried me.  The AWR report looks like this:

Host CPU
            Load Average
 CPUs     Begin       End     %User   %System      %WIO     %Idle
----- --------- --------- --------- --------- --------- ---------
   16     10.66     44.73      68.3       4.3       0.0      26.8

So, why is the load average 44 and yet the CPU is 26% idle?

I started looking at ASH data and found samples with 128 processes active on the CPU:

     select
  2  sample_time,count(*)
  3  from DBA_HIST_ACTIVE_SESS_HISTORY a
  4  where
  5  session_state='ON CPU' and
  6  instance_number=3 and
  7  sample_time
  8  between
  9  to_date('05-MAR-2015 01:00:00','DD-MON-YYYY HH24:MI:SS')
 10  and
 11  to_date('05-MAR-2015 02:00:00','DD-MON-YYYY HH24:MI:SS')
 12  group by sample_time
 13  order by sample_time;

SAMPLE_TIME                    COUNT(*)
---------------------------- ----------
05-MAR-15 01.35.31.451 AM           128

... lines removed for brevity

Then I dumped out the ASH data for one sample and found all the sessions on the CPU were running the same parallel query:

select /*+  parallel(t,128) parallel_index(t,128) dbms_stats ...

So, for some reason we are gathering stats on a table with a degree of 128 and that spikes the load.  But, why does the CPU idle percentage sit at 26.8% when the load starts at 10.66 and ends at 44.73?  Best I can tell load in DBA_HIST_OSSTAT is a point measurement of load.  It isn’t an average over a long period.  The 11.2 manual describes load in v$osstat in this way:

Current number of processes that are either running or in the ready state, waiting to be selected by the operating-system scheduler to run. On many platforms, this statistic reflects the average load over the past minute.

So, load could spike at the end of an hour-long AWR report interval and still CPU could average 26% idle for the entire hour?  So it seems.

– Bobby

Categories: DBA Blogs

Parallel Execution -- 2b PX Servers

Hemant K Chitale - Fri, 2015-03-13 09:05
Continuing my previous post, here I demonstrate  using V$SQLSTATS.PX_SERVERS_EXECUTIONS and a couple of issues around it.

I have restarted the database.

[oracle@localhost ~]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 13 22:49:20 2015

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

HEMANT>show parameter cpu

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 4
parallel_threads_per_cpu integer 4
resource_manager_cpu_allocation integer 4
HEMANT>select degree from user_tables where table_name = 'LARGE_TABLE';

DEGREE
----------------------------------------
1

HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

COUNT(*)
----------
4802944

HEMANT>select executions, px_servers_executions, sql_fulltext
2 from v$sqlstats
3 where sql_id = '8b0ybuspqu0mm';

EXECUTIONS PX_SERVERS_EXECUTIONS SQL_FULLTEXT
---------- --------------------- --------------------------------------------------------------------------------
1 16 select /*+ PARALLEL */ count(*) from Large_Table

HEMANT>
HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

COUNT(*)
----------
4802944

HEMANT>select executions, px_servers_executions, sql_fulltext
2 from v$sqlstats
3 where sql_id = '8b0ybuspqu0mm';

EXECUTIONS PX_SERVERS_EXECUTIONS SQL_FULLTEXT
---------- --------------------- --------------------------------------------------------------------------------
2 32 select /*+ PARALLEL */ count(*) from Large_Table

HEMANT>

[Note : To understand why the executions took 16 PX Servers inspite of the degree on table being 1, see this post]
So we see that PX_SERVERS_EXECUTIONS shows cumulative statistics.  Let's try a slight twist.

HEMANT>connect / as sysdba
Connected.
SYS>alter system set parallel_max_servers=8;

System altered.

SYS>connect hemant/hemant
Connected.
HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

COUNT(*)
----------
4802944

HEMANT>select executions, px_servers_executions, sql_fulltext
2 from v$sqlstats
3 where sql_id = '8b0ybuspqu0mm';

EXECUTIONS PX_SERVERS_EXECUTIONS SQL_FULLTEXT
---------- --------------------- --------------------------------------------------------------------------------
3 40 select /*+ PARALLEL */ count(*) from Large_Table

HEMANT>

Because I set PARALLEL_MAX_SERVERS to 8, my query on Large_Table could take only 8 PX Servers at the next execution.  V$SQLSTATS.PX_SERVERS_EXECUTIONS now shows a cumulative count of 40 for 3 executions. There is no way to determine how many PX Servers were used in each of the 3 executions, because the history of executions is not maintained.
(In my controlled experiment, we know, by deduction, that the 3rd execution took 8 PX Servers simply because we know already that the first 2 executions took a cumulative count of 32 PX Servers -- by deducting 32 from 40 to get 8 for the 3rd execution)

What happens if the SQL is invalidated ?

HEMANT>alter table large_table parallel 4;

Table altered.

HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

COUNT(*)
----------
4802944

HEMANT>select executions, px_servers_executions, sql_fulltext
2 from v$sqlstats
3 where sql_id = '8b0ybuspqu0mm';

EXECUTIONS PX_SERVERS_EXECUTIONS SQL_FULLTEXT
---------- --------------------- --------------------------------------------------------------------------------
1 8 select /*+ PARALLEL */ count(*) from Large_Table

HEMANT>

The ALTER TABLE, being a DDL, had invalidated the query on Large_Table.  So, V$SQLSTATS also got reset.  Therefore, EXECUTIONS reset to 1 and PX_SERVES_EXECUTIONS got reset to 8.

.
.

.
Categories: DBA Blogs

Not NULL Constraint Influences Access Path

Oracle in Action - Thu, 2015-03-12 23:12

RSS content

The optimizer can make use of explicitly defined Not NULL constraints to take advantage
of an index in order to avoid a full table scan since a B-tree index stores only not NULL values .
When  count (constant) or count(*)  is queried,  we want to count no. of rows in the table. Hence , if there is a column which is defined as not NULL and has an index on it, the number of index entries  in the index are bound to be same as the number of rows. The query optimizer uses the index to count no. of rows in the table.

Similarly, when  a count (not-nullable-column) is queried,  we want to count the no. of rows having not null values in the column. Since the column  has a not NULL constraint on it, every row in the table will have a not null value in it and count(not-nullable-column) is  same as count(*). As a result, the query optimizer can use  the index on the column to process the query.
In fact, in both the cases above, any B-tree containing at least a not-nullable column can serve the purpose.

When a count (nullable-column) is queried, we want to count the no. of rows having not null values in the column. If we have an index on the column, the index will store only not NULL values and hence can be effectively used by  the query optimizer to give the result.
In fact, the optimizer can use any index containing the nullable column for this purpose.

To demonstrate the above functionality, I have created a  table HR.TEST with two columns – NOTNULL having not NULL constraint
NULLABLE
. having same data as column NOTNULL but has not been declared not NULL
. has a B-tree index on it

SQL>drop table hr.test purge;
    create table hr.test (notnull number not null, nullable number);
    insert into hr.test select rownum, rownum from all_tables;
    create index hr.test_idx on hr.test(nullable);
    exec dbms_stats.gather_table_stats ('HR','TEST', cascade => true);

Now I will query count for various arguments and check if optimizer can use the index on NULLABLE column.

Note that to process count(*),  count(1) and   count(notnull), the query optimizer uses Full Table Scan. Although the column NULLABLE has non-null values in all the rows but since it has not been explicitly declared not null , the  optimizer does not know that no. of entries in index reflect the count correctly and hence does not use the index .

SQL>select count(*) from hr.test;
            select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 1mat065c25crk, child number 0
-------------------------------------
select count(*) from hr.test

Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 108 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------

SQL>select count(1) from hr.test;
    select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID gzpsn7ff3ncmc, child number 0
-------------------------------------
select count(1) from hr.test

Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 108 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------

SQL>select count(notnull) from hr.test;
    select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 6kxdzxbac62b4, child number 0
-------------------------------------
select count(notnull) from hr.test

Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 108 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------

To process count(nullable), the optimizer uses index on column NULLABLE because we want to count not null values in column nullable and Btree index stores only not null values.

SQL> select count(nullable) from hr.test;
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID bz8rxw5rmmv8g, child number 0
-------------------------------------
select count(nullable) from hr.test

Plan hash value: 2284640995
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | INDEX FULL SCAN| TEST_IDX | 108 | 432 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------

Now I will declare not NULL constraint on  column NULLABLE.

SQL> alter table hr.test modify (nullable not null);

Now if query count(*), count(1), count(notnull) and count(nullable), the optimizer is able to avoid Full Table Index by making  use of the index  on NULLABLE column in all the cases . Since the column NULLABLE having index has been declared not null and optimizer knows that entries in the index represent all the rows of the table.

SQL>select count(*) from hr.test;
    select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------- 
SQL_ID 1mat065c25crk, child number 0
-------------------------------------
select count(*) from hr.test

Plan hash value: 2284640995
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| TEST_IDX | 108 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------

SQL>select count(1) from hr.test;
    select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------- 
SQL_ID gzpsn7ff3ncmc, child number 0
-------------------------------------
select count(1) from hr.test

Plan hash value: 2284640995
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| TEST_IDX | 108 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------

SQL>select count(notnull) from hr.test;
    select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------- 
SQL_ID 6kxdzxbac62b4, child number 0
-------------------------------------
select count(notnull) from hr.test

Plan hash value: 2284640995
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| TEST_IDX | 108 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------

SQL> select count(nullable) from hr.test;
     select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------- 
SQL_ID bz8rxw5rmmv8g, child number 0
-------------------------------------
select count(nullable) from hr.test

Plan hash value: 2284640995
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| TEST_IDX | 108 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------

Hence, It is advisable to declare NOT NULL constraint on relevant columns so that optimizer can choose index access in relevant cases.

References:
Troubleshooting Oracle Performance (second edition ) by Christian Antognini
—————————————————————————————————————

Related links:

Home
Tuning Index

————————-



Tags:  

Del.icio.us
Digg

Comments:  0 (Zero), Be the first to leave a reply!
You might be interested in this:  
Copyright © ORACLE IN ACTION [Not NULL Constraint Influences Access Path], All Right Reserved. 2015.

The post Not NULL Constraint Influences Access Path appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

Arizona Oracle User Group Meeting March 18

Bobby Durrett's DBA Blog - Thu, 2015-03-12 09:39
I just found out that this meeting was cancelled.  We will have to catch the next one. :) – 3/16/2015

Sign up for the Arizona Oracle User Group (AZORA) meeting next week: signup url

The email that I received from the meeting organizer described the topic of the meeting in this way:

“…the AZORA meetup on March 18, 2015 is going to talk about how a local business decided to upgrade their Oracle Application from 11i to R12 and give you a first hand account of what went well and what didn’t go so well. ”

Description of the speakers from the email:

Becky Tipton

Becky is the Director of Project Management at Blood Systems located in Scottsdale, AZ. Prior to coming to Blood Systems, Becky was an independent consultant for Tipton Consulting for four years.

Mike Dill

Mike is the Vice President of Application Solutions at 3RP, a Phoenix consulting company. Mike has over 10 years of experience implementing Oracle E-Business Suite and managing large-scale projects.

I plan to attend.  I hope to see you there too. :)

– Bobby

Categories: DBA Blogs

Delphix User Group Presentation

Bobby Durrett's DBA Blog - Wed, 2015-03-11 16:30

My Delphix user group presentation went well today. 65 people attended.  It was great to have so much participation.

Here are links to my PowerPoint slides and a recording of the WebEx:

Slides: PowerPoint

Recording: WebEx

Also, I want to thank two Delphix employees, Ann Togasaki and Matthew Yeh.  Ann did a great job of converting my text bullet points into a visually appealing PowerPoint.  She also translated my hand drawn images into useful drawings.  Matthew did an amazing job of taking my bullet points and my notes and adding meaningful graphics to my text only slides

I could not have put the PowerPoint together in time without Ann and Matthew’s help and they did a great job.

Also, for the first time I wrote out my script word for word and added it to the notes on the slides.  So, you can see what I intended to say with each slide.

Thank you to Adam Leventhal of Delphix for inviting me to do this first Delphix user group WebEx presentation.  It was a great experience for me and I hope that it was useful to the user community as well.

– Bobby

Categories: DBA Blogs

#db12c now certified for #em12c repository (MOS Note: 1987905.1) with some restrictions

DBASolved - Wed, 2015-03-11 11:06

Last October (2014), at Oracle Open World 2014, I posted about a discussion where there was confusion on if Oracle Database 12c was supported as the Oracle Management Repository (OMR).  At the time, Oracle had put a temporary suspension on support for the OMR running on Oracle Database 12c. 

Over the last week or so, in discussions with some friends I heard that there may be an announcement on this topic soon.  As of yesterday, I was provided a MOS note number to reference (1987905.1) for OMR support on database 12c.  In checking out the note, it appears that the OMR can now be ran on a database 12c instance (12.1.0.2) with some restrictions.

These restrictions are:

  • Must apply database patch 20243268
  • Must apply patchset 12.1.0.2.1 (OCT PSU) or later

This note (1987905.1) is welcomed by many in the community who want to build their OMS on the latested database version.  What is missing from the note is if installing the OMR into a pluggable database (PDB) is support.  Guess the only way to find out is to try building a new Oracle Enterprise Manager 12c on top of a pluggable and see what happens.  At least for now, Oracle Database 12c is supported as the OMR.

Enjoy!

about.me: http://about.me/dbasolved


Filed under: OEM
Categories: DBA Blogs

Log Buffer #413, A Carnival of the Vanities for DBAs

Pythian Group - Mon, 2015-03-09 21:15

This Log Buffer Editions scours the Internet and brings some of the fresh blog posts from Oracle, SQL Server and MySQL.

Oracle:

Most of Kyles’ servers tend to be Linux VMs on VMware ESX without any graphics desktops setup, so it can be disconcerting trying to install Oralce with it’s graphical “runInstaller” being the gate way we have to cross to achieve installation.

Working around heatbeat issues caused by tracing or by regexp

APEX 5 EA Impressions: Custom jQuery / jQuery UI implementations

Introduction to the REST Service Editor, Generation (PART 2)

Due to recent enhancements and importance within Oracle’s storage portfolio, StorageTek Storage Archive Manager 5.4 (SAM-QFS) has been renamed to Oracle Hierarchical Storage Manager (Oracle HSM) 6.0.

SQL Server:

There are different techniques to optimize the performance of SQL Server queries but wouldn’t it be great if we had some recommendations before we started planning or optimizing queries so that we didn’t have to start from the scratch every time? This is where you can use the Database Engine Tuning Advisor utility to get recommendations based on your workload.

Data Mining Part 25: Microsoft Visio Add-Ins

Stairway to Database Source Control Level 3: Working With Others (Centralized Repository)

SQL Server Hardware will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS.

Questions About SQL Server Transaction Log You Were Too Shy To Ask

MySQL:

The post shows how you can easily read the VCAP_SERVICES postgresql credentials within your Java Code using the maven repo. This assumes your using the ElephantSQL Postgresql service. A single connection won’t be ideal but for demo purposes might just be all you need.

MariaDB 5.5.42 Overview and Highlights

How to test if CVE-2015-0204 FREAK SSL security flaw affects you

Using master-master for MySQL? To be frankly we need to get rid of that architecture. We are skipping the active-active setup and show why master-master even for failover reasons is the wrong decision.

Resources for Highly Available Database Clusters: ClusterControl Release Webinar, Support for Postgres, New Website and More

Categories: DBA Blogs