USCG has transmitters/receivers on several mountain tops https://www.navcen.uscg.gov/images/marcomms/cgcomms/Rescue21/SecSanFran.jpg
You should hear them easily with the stock antenna and probably inside your home. An antenna outside your home and mounted up as high as you can reasonably get it will allow you to hear the ship/boaters better. Almost all VHF Marine traffic is simplex, so there won't be high level repeaters to listen to. With simplex, it'll work as well as your antenna lets it. If you have some altitude above the bay, you shouldn't have a problem. If you are down on the flatlands, you'll really want an antenna mounted at or above roofline.
You should load in the following channels at minimum:
Channel 16 - 156.800 (Distress and Calling)
Channel 09 - 156.450 (Commercial/Non-Commercial calling)
Channel 12 - 156.600 Offshore VTS (you probably will not be able to hear vessels from outside the gate, but you'll hear USCG answering them)
Channel 14 - 156.700 Inshore VTS (Inside the bay, with a good antenna you should be able to hear most traffic on the bay)
All the VHF channel/frequencies are listed here: U.S. VHF Channel Information
Ideally, load them all in, with the exception of the two AIS channels at the bottom of the page, and scan them.
I used to live in Redwood ****y (not to be confused with the nicer Redwood Shores or Emerald Hills areas). There's a lot of mundane & interesting maritime-related stuff to hear in SF Bay and with a decent antennas, you'll hear stuff on the other side of the ridgeline (Pacific Ocean) too (& don't forget the USCG UHF aviation and VHF P25 channels too), but I think if you're especting to hear a lot of activity related to the Port of Redwood City, you'll be somewhat disappointed. Some areas of it are nice, all areas are interesting to explore, so I suggest you drive/walk/sit around there & look for antennas on buildings as well as look & listen for local comms. You could leave your HP2 in Close-Call auto store mode while you scan/search with the SOS100. You may find more Port/maritime-related comms on business freqs than you find local use of the VHF Marine channels.