How to tune a SQL query? I have a question about SQL query tuning. 

How can rewrite that SQL query for better performance?

Query:

select * 
from (
    select (
        select max(rn) 
        from (
            select row_number() over (order by islemno) rn, k.id 
            from cari k 
            where k.yilid = :yilid and k.id=k.baslik 
        )
    ) aktifSira
        , row_number() over (order by islemno) sira
        , ks.id 
    from cari ks 
    where yilid = :yilid and id=baslik
) where sira = (aktifSira - 1) 
    or sira = (aktifSira + 1) 
    or sira = aktifSira 
order by sira;


That's cost of query:

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    92 |  3368   (1)| 00:00:01 |        |      |         |
|   1 |  SORT AGGREGATE             |          |     1 |    13 |            |          |        |      |         |
|   2 |   PX COORDINATOR            |          |       |       |            |          |        |      |         |
|   3 |    PX SEND QC (RANDOM)      | :TQ10001 |     1 |    13 |            |          |  Q1,01 | P-&gt;S | QC (RAND)|
|   4 |     SORT AGGREGATE          |          |     1 |    13 |            |          |  Q1,01 | PCWP |         |
|   5 |      VIEW                   |          |     1 |    13 |  1684   (1)| 00:00:01 |  Q1,01 | PCWP |         |
|   6 |       WINDOW SORT           |          |     1 |   144 |  1684   (1)| 00:00:01 |  Q1,01 | PCWP |         |
|   7 |        PX RECEIVE           |          |     1 |   144 |  1683   (1)| 00:00:01 |  Q1,01 | PCWP |         |
|   8 |         PX SEND RANGE       | :TQ10000 |     1 |   144 |  1683   (1)| 00:00:01 |  Q1,00 | P-&gt;P | RANGE   |
|   9 |          PX BLOCK ITERATOR  |          |     1 |   144 |  1683   (1)| 00:00:01 |  Q1,00 | PCWC |         |
|* 10 |           TABLE ACCESS FULL |     CARI |     1 |   144 |  1683   (1)| 00:00:01 |  Q1,00 | PCWP |         |
|  11 |  PX COORDINATOR             |          |       |       |            |          |        |      |         |
|  12 |   PX SEND QC (ORDER)        | :TQ20002 |     1 |    92 |  3368   (1)| 00:00:01 |  Q2,02 | P-&gt;S | QC (ORDER)|
|  13 |    SORT ORDER BY            |          |     1 |    92 |  3368   (1)| 00:00:01 |  Q2,02 | PCWP |         |
|  14 |     PX RECEIVE              |          |     1 |    92 |  3367   (1)| 00:00:01 |  Q2,02 | PCWP |         |
|  15 |      PX SEND RANGE          | :TQ20001 |     1 |    92 |  3367   (1)| 00:00:01 |  Q2,01 | P-&gt;P | RANGE   |
|* 16 |       VIEW                  |          |     1 |    92 |  3367   (1)| 00:00:01 |  Q2,01 | PCWP |         |
|  17 |        WINDOW SORT          |          |     1 |   144 |  3367   (1)| 00:00:01 |  Q2,01 | PCWP |         |
|  18 |         PX RECEIVE          |          |     1 |   144 |  1683   (1)| 00:00:01 |  Q2,01 | PCWP |         |
|  19 |          PX SEND RANGE      | :TQ20000 |     1 |   144 |  1683   (1)| 00:00:01 |  Q2,00 | P-&gt;P | RANGE   |
|  20 |           PX BLOCK ITERATOR |          |     1 |   144 |  1683   (1)| 00:00:01 |  Q2,00 | PCWC |         |
|* 21 |            TABLE ACCESS FULL|     CARI |     1 |   144 |  1683   (1)| 00:00:01 |  Q2,00 | PCWP |         |
---------------------------------------------------------------------------------------------------------------------

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

  10 - filter("K"."ID"="K"."BASLIK" AND "K"."YILID"=SYS_OP_C2C(:YILID))
  16 - filter("SIRA"="AKTIFSIRA"-1 OR "SIRA"="AKTIFSIRA"+1 OR "SIRA"="AKTIFSIRA")
  21 - filter("ID"="BASLIK" AND "YILID"=SYS_OP_C2C(:YILID))
What makes you think you need to rewrite it? SQL Tuning is more than just trying to rewrite your query. If you do not understand what happens behind the scenes, then how will you rewrite your query?

The estimated cardinality in step 10 and 21 is 1. Still the database decides to scan the whole CARI table. Twice.

Is the above estimation correct? Would those steps really return just 1 or a few rows? If you run this query (with actual values), will this return 1 or hundreds, thousands, millions?

select count(*) from cari where id = baslik and yilid = :yilid;


Are the table statistics up-to-date?

select count(*), count(distinct yilid) from cari;
select blocks from user_segments where segment_name = 'CARI';


Compare the above values to:

select num_rows, blocks from user_tables where table_name = 'CARI';
select num_distinct, histogram from user_tab_columns where table_name = 'CARI' and column_name = 'YILID';


If there is a big difference, gather statistics.

Is there any correlation between the columns BASLIK and YILID? If there is, did you gather extended statistics on them, so the database can estimate cardinality more accurately?

If both the statistics and the above estimation are good enough, then why isn't there an index on the YILID (and BASLIK) column that the database could use to access the required rows effectively?

If both the statistics and the above estimation are good enough, then why does the database decide to use parallel query at all? Did you force it or set it at table level?

In the Predicate Information section:

10 - filter("K"."ID"="K"."BASLIK" AND "K"."YILID"=SYS_OP_C2C(:YILID))
21 - filter("ID"="BASLIK" AND "YILID"=SYS_OP_C2C(:YILID))


This is really bad: "YILID"=SYS_OP_C2C(:YILID). You did not bind the variable properly. SYS_OP_C2C means char to nchar conversion, so the column type and variable type is different. The database will implicitly convert one type to another, this time you got lucky, but the other way around it would make impossible to use an index, unless you have a function-based index especially for the affected columns - do not do that, bind properly. If the column YILID is an nvarchar2 type column, then bind :yilid as an nvarchar2 type variable.

If the above still does not help, did you actually measure the steps in the execution plan? Did you identify the steps that take long? Run the below and report back with the output:

alter session set statistics_level = all;
-- run your query here in the same session --
select * from table(dbms_xplan.display_cursor(format=&gt;'allstats last'));