Why would you want to do this?
Because turning on "show_sql" will flood your logs with all the SQL generated by hibernate, making it difficult to find a particular section you might be debugging or optimizing. Being able to toggle the output of SQL allows you to enable it just for the sections of code you are investigating.
First, the solution, then I'll get to why:
Logger sqlLogger = Logger.getLogger("org.hibernate.SQL");
... do your stuff ...
So what's going on here?
The key is knowing what "show_sql" does and that it is not the only way to log the SQL statements generated by hibernate. Namely, "show_sql" sends all SQL to the standard output, but hibernate also uses Apache Commons Logging. So, as long as you configure commons logging to use an underlying logging framework that supports changing log levels at runtime, you can toggle the output of SQL by getting a hold of the "org.hibernate.SQL" logger and bending it to your will.