Unfortunately, we can only guess about this small fragment of the plan.

See [url https://forums.oracle.com/forums/thread.jspa?threadID=863295]How to post a sql tuning request

What you really need to know is where the time is being spent, which steps have significantly inaccurate estimates

As you're on 11gR2, if you're licensed for diagnostic pack then I would recommend using real time sql monitoring via DBMS_SQLTUNE.REPORT_SQL_MONITOR.
But, given that you have a long sql statement, you're highly likely to hit the length limit enforced by parameter sqlmonmax_planlines.

So, fall back to good old fashioned sql trace.

If licensed for diagnostic pack, you might also look at the raw ASH data in v$active_session_history to see what steps the sql statement spends most time on. This is sampled so is a limited view and not ideal for sql tuning.

The Top 5 timed events section of the AWR you have posted showing DB CPU to be 80% is not sufficient at all to state about wether your are CPU bound or not. In order to have an idea about the real CPU utilisation you need to have (a) the duration of the AWR snapshot and (b) the number of CPU your system has.

Let me suppose that the snapshot of this AWR is 60 minutes(3600 seconds) and you have 40 CPU machines. Then the arithmetic to know if you are CPU bound or not is:

3600/2805 = *1,28 CPU* used at 100%

If you have only one CPU then of course you are CPU bound. But when you have 40 CPU, you need to divide the above result by the number of available CPU

1,28/40 = 0,032

Which means in this case that your CPU is used at *3%* of its total capacity.

Always cross check and do few arithmetics before jumping to wrong conclusions

1) you only have one CPU -- so whenever a session is performing a high amount of work, your CPU usage will be near 100% (unless you use Resource Manager to enforce CPU quotas). it's not a good idea to run an Oracle database on a single CPU server
2) the main source of CPU usage by queries is logical reads -- your query is probably doing hundreds of thousands of them; however, the plan you posted shows that the optimizer only expects much less. That means that the optimizer doesn't have the correct information about the amount of data your query will have to process in order to answer it -- either table stats are incorrect (stale or non-representative) or because the predicates are too complex to allow accurate selectivity estimation. In any case, since the optimizer is wrong about the cost of the query, we cannot trust its judgement
3) since you're on 11g, you can use also SQL monitor to see where your query is spending most of the time, in addition to other tools like dbms_xplan.

Can you perform the 10032 trace on the query to find the sorts (memory), how many comparisons are carried.
I suspect, you can try to check existing index or create new index with above segments which hold information on your
caridnality. So that we can re-write the query to use the PGA for sorting (we can try to reduce)

I see out 1013 - finally a row of aggreate one is fetching across.

3. SYS_OP_DESCEND -- I hope your indexes are picking FBI (function based index), we might try to optmize with correct index (doubtful on this step)

I can only say "MERGE JOIN CARTESIAN" which was influencing the sort - reasons for that would be missing joining columns with respect choosen indexes for query.
Secondly, I see or might be data type issue/conversions.

Why in Explain plan I do not have Time column ? How to have it ?

Check whether your plan table is older, just incase, else it would have dispalyed across.

So far there is no indication whatsoever that it needs to be "reduced".

Why in Explain plan I do not have Time column ? How to have it ?

You don't need the Time column. It's the same as COST, only expressed in different units. The important thing is that this is not the actual timing, it's an optimizer estimate thereof. Actual timings show up in A-time column. It's displayed if:

1) the query was executed with statistics_level = all or gather_plan_statistics hint
2) the format parameter of dbms_xplan was set appropriately (e.g. 'iostats last' or 'allstats last')

You can find some useful information about displaying A-time and A-rows in my blog: http://savvinov.com/category/dbms_xplan/

Thanks to all.
The same query , on another server runs quickly (just 15 mins). I compared the explain plan of both.
In the good one there is no BUFFER SORT. In the very very slow one (we should do ctrl ^c to stop it) I have :