Question :
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->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->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->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->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->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))
Answer :
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=>'allstats last'));
It looks like you’re trying to find rows that have one more, one less, or the
same number of rows as the the highest number of rows for a particular yilid
where the id is equal to the balik. Since there will never be more rows than the max, OR sira=(aktifSira + 1) can be eliminated. The other two conditions can be combined to WHERE sira >= (aktifSira - 1).
It looks like kas_cari may just be a view of cari, but I can’t tell from your information what it may be filtering/transforming. If they are functionally equivalent you could eliminate the double querying of cari. Even if they aren’t it would be useful to merge the view into this query to see if any redundancy there can be eliminated.
Finally, the explain plan isn’t as useful as it might be due to the parallelism kicking in. Turn off the parallelism and you will see a much simpler version of the explain plan that will be easier to tune. After that turn the parallelism back on.
Update:
You have a rather complex query for what it appears to be trying to accomplish. It looks like you are retrieving the two rows from cari with the highest values for islemno. However, your inclusion of sira = (aktifSira + 1) indicates that you may be trying to retrieve something else. If you describe the results you are trying to get, that may be helpful, and perhaps contrast that with the results of this query, which should be functionally equivalent.
select * from (
select count() OVER () aktifSira, row_number() over (order by islemno) sira, id
from cari where yilid = :yilid and id=baslik
)
where sira >= aktifSira - 1
order by sira;
or this
select max(sira) OVER () aktifSira, sira, id from (
select row_number() over (order by islemno DESC) sira, id
from cari where yilid = :yilid and id=baslik
)
where sira >= 2
order by sira;