Careful schema design means good data modeling and sufficient normalization -- at least third normal form; some experts advocate Boyce-Codd Normal Form (BCNF). Careful SQL programming means avoiding practices that inhibit scalability. Use bind variables, not literals; take advantage of SQL's set orientation and declarative nature to operate on many table rows at a time; in PL/SQL code, use procedural capabilities only to the extent necessary, and don't revert to SQL that operates on a row at a time unless absolutely required. One of the biggest mistakes in PL/SQL programming is to treat a PL/SQL procedure or function as though it were written in C or another 3GL, and to embed row-at-a-time SQL operations in loops or other iterative constructs. The rule of thumb should be to accomplish all database access using SQL, unless impossible; use PL/SQL only if SQL can't do the job.
If designers and developers do these jobs correctly, you can expect reasonable application performance unless your database configuration is very poor -- examples of things to avoid are placing Oracle database files on RAID 5 disk arrays in a write-intensive application, providing insufficient memory to the Oracle SGA and placing online redo logs on disk devices for which there is a lot of contention.
Briefly, the answer to your question could (and does!) fill several books, but I've tried to lay out the major principles here. I hope you find it helpful.