Go to Settings and tap Sounds. Scroll down to Tweet and tap it. Tap a new notification sound to select it. (Note that this will only change the notification sound when a tweet is sent directly to you; the notification sound for general tweets cannot be modified.)