How we could tuning top sql queries from AWR without using sql advisor?

Tuning Oracle is a complex topic.  Many authors have written large books on the subject.  Oracle and other organizations offer multi-day or weeklong training classes on the subject.  We aren't going to be able to cover and summarize all of that information in a few sentences here.

Basically, to make Oracle queries faster you look for ways to reduce the number of "buffer gets" (also called "logical block reads" ) that Oracle needs to do to process the query.  Some options include:
1. Adding index(es) that match bind variable value(s) that the queries provide
2. Re-writing the queries by changing the order of tables in the "from" clause and the order of the "where" clauses, both top-to-bottom and right-to-left so they match the order that Oracle should join them in based on the bind variables provided, and the indexes available.
3. Partitioning the tables (and indexes) so the data is organized closer to how it is queried.  This can also allow you to compress historical partitions so there are fewer blocks that Oracle needs to read.
4. Creating materialized views to summarize and/or sort large sets of rows in advance of your queries.

If you have specific queries with problems, you can post specific queries here that we can give you specific suggestions for.

how i do it ... is like this

i have a folder on my win laptop
c:\orasql

in it, this trc.sql script:

define qrynum=&1
alter session set statistics_level = all;
set time on
set timing on
set autot trace
@qry&qrynum.
set autot off

Select allOpen in new window

i create a file, for example: qry100.sql and place the statement i want to check :

select * from employee natural join department;

Select allOpen in new window

i start sqlplus from c:\orasql

cmd
cd /d c:\orasql
sqlplus hr@samples

Select allOpen in new window

and then i let the trc.sql run my qry1 twice and check the output:

14:55:09 >@trc 100

Session altered.

Elapsed: 00:00:00.06

32 rows selected.

Elapsed: 00:00:00.19

Execution Plan
----------------------------------------------------------
Plan hash value: 2052257371

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |    11 |   990 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |             |    11 |   990 |     6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| DEPARTMENTS |    11 |   231 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMPLOYEES   |   107 |  7383 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID"
              AND "EMPLOYEES"."MANAGER_ID"="DEPARTMENTS"."MANAGER_ID")
   2 - filter("DEPARTMENTS"."MANAGER_ID" IS NOT NULL)

Note
-----
   - this is an adaptive plan


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         17  consistent gets
          0  physical reads
          0  redo size
       4154  bytes sent via SQL*Net to client
        574  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         32  rows processed

14:56:12 >

Select allOpen in new window

17  consistent gets
is what you check after changing something
the lower it gets the better and faster it should be

i usually have qry1.sql as the original query
and make additional ones, qry2.sql, ... to do the changes

it allows for comparisons if you have something like beyond compare ... scootersoftware.com
or any other text comparison program