DNS cache poisoning is a condition where an attacker injects a false hostname-to-IP-address mapping into the cache of DNS server. Once the attacker is successful in making such injection the DNS server will provide this false information when any client will query for this host name. This way an attacker can choose to divert the traffic for a particular target to a fake server.
For example if such injection is done on a DNS server for the host name www.google.com, the attacker can divert traffic for www.google.com to a server of his choice for all clients making host name to IP address resolution from this DNS server.
The effect of this attack can be large depending upon the number of clients being served by a DNS server. If attacker get success in poisoning cache of your DNS server, only your clients are affected. If the attacker is successful in poisoning cache of ISP's DNS server all clients served by this ISP will be affected. The effect gets larger as you go higher in DNS hierarchy.
DNS cache poisoning attack may lead to denial-of-service and man-in-the-middle attacks.